Strange errors / messages on slave server
Good day all I hope someone can assist me with this. While doing the normal routine daily health checks on one of our clients' servers I came across some strange behaviour from the slave server. (two servers setup in master / slave replication) While looking at the current Innodb buffer pool usage (master server), I noticed that the usage went up from 44% yesterday to 98.7% today, however nothing on the master server suggested why. I went on to look at the save server and found very strange behaviour (for me anyway) and I am hoping someone can assist in explaining this to me and some possible corrective actions: When running show slave status, it seems that there was an error logged which show in the output as below: mysql show slave status; +--+--+-+--- --+---+--+-+ --+---+---+--+-- -+-+-+-- --++-+-- ---++--- +--- ---+-+-+-+-- --+---+++--- -+-+---++--- + | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | +--+--+-+--- --+---+--+-+ --+---+---+--+-- -+-+-+-- --++-+-- ---++--- +--- ---+-+-+-+-- --+---+++--- -+-+---++--- + | Waiting for master to send event | MASTER.SERVER | repladmin | 3306 |60 | mysql-bin.000327 | 672223064 | SLAVE-relay-bin.001016 | 598540830 | mysql-bin.000326 | Yes | No| | | || | | 1206 | Error 'The total number of locks exceeds the lock table size' on query. Default database: 'profiler'. Query: 'update profile_options set `value` = REPLACE(`value`, '.', '') where list_item_id = 11' |0 | 598540693 | 1746329551 | None|| 0 | No ||| | || NULL | +--+--+-+--- --+---+--+-+ --+---+---+--+-- -+-+-+-- --++-+-- ---++--- +--- ---+-+-+-+-- --+---+++--- -+-+---++--- + 1 row in set (0.00 sec) Then looking at the log files the following was found: 100601 9:56:54 InnoDB: WARNING: over 67 percent of
RE: Strange errors / messages on slave server
Hi All Just one other note on this issue experienced. I used google to try and find some solutions / clues and all the suggestions are to increase the innodb buffer pool size. This was however recently done on the database to increase this to 4Gb already. However, this change is only done on the master server and not on the slave server. I am not sure how the buffer pools are handled in replication though, but would this not perhaps need to be set on the slave server as well. My thinking around this is that the error occurred specifically on the slave server, where there are no specific configuration to increase the innodb buffer pool size. Thus to me this looks like the slave is still using the default 8mb and should perhaps be changed to be the same as the master server? Machiel Richards -Original Message- From: Machiel Richards [mailto:machi...@rdc.co.za] Sent: 02 June 2010 9:41 AM To: mysql@lists.mysql.com Subject: Strange errors / messages on slave server Good day all I hope someone can assist me with this. While doing the normal routine daily health checks on one of our clients' servers I came across some strange behaviour from the slave server. (two servers setup in master / slave replication) While looking at the current Innodb buffer pool usage (master server), I noticed that the usage went up from 44% yesterday to 98.7% today, however nothing on the master server suggested why. I went on to look at the save server and found very strange behaviour (for me anyway) and I am hoping someone can assist in explaining this to me and some possible corrective actions: When running show slave status, it seems that there was an error logged which show in the output as below: mysql show slave status; +--+--+-+--- --+---+--+-+ --+---+---+--+-- -+-+-+-- --++-+-- ---++--- +--- ---+-+-+-+-- --+---+++--- -+-+---++--- + | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | +--+--+-+--- --+---+--+-+ --+---+---+--+-- -+-+-+-- --++-+-- ---++--- +--- ---+-+-+-+-- --+---+++--- -+-+---++--- + | Waiting for master to send event | MASTER.SERVER | repladmin | 3306 |60 | mysql-bin.000327 | 672223064 | SLAVE-relay-bin.001016 | 598540830 | mysql-bin.000326 | Yes | No| | | || | | 1206 | Error 'The total number of locks exceeds the lock table size' on query. Default database: 'profiler'. Query: 'update profile_options set `value` = REPLACE(`value`, '.', '') where list_item_id = 11' |0 | 598540693 | 1746329551 | None|| 0 | No ||| | |
MySQL University session on June 3: New features in Connector/NET 6.3
MySQL University: New features in Connector/NET 6.3 http://forge.mysql.com/wiki/New_Features_in_Connector/NET_6.3 This Thursday (June 3rd, 14:00 UTC), Reggie Burnett, head of Connector/NET development, will present the New Features in Connector/NET 6.3. For MySQL University sessions, point your browser to this page (you need a browser with a working Flash plugin): http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone. All sessions (slides audio) are recorded; the links to these recordings will be on the respective MySQL University session pages which are listed on the MySQL University home page. -- Cheers, Stefan Hinz stefan.h...@sun.com, MySQL Documentation Manager Phone: +49-30-82702940, Fax: +49-30-82702941, http://dev.mysql.com/doc Sun Microsystems GmbH, Sonnenallee 1, 85551 Kirchheim-Heimstetten Amtsgericht Muenchen: HRB161028 Geschaeftsfuehrer: Juergen Kunz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
large table issue
Hi all, do you guys know how to deal with the large tables? here's my problem: I have two web servers( running Nginx ) , two DB servers( running MySQL 5.1.35 ) and a server for load balancing. What I'm maintaining is a game data tracking system. There's a game_log table which will record all detail info from many games. here's the structure: `game_log_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `game_id` int(10) unsigned NOT NULL, `event_id` int(10) unsigned NOT NULL, `player_id` int(10) unsigned NOT NULL, `session_id` varchar(128) NOT NULL COMMENT 'flash session id', `score` int(10) unsigned DEFAULT NULL, `handle_statu` int(1) unsigned NOT NULL DEFAULT '1' COMMENT '1:not handle 2:been handle', `game_end` bigint(20) DEFAULT NULL, `game_start` bigint(20) unsigned NOT NULL DEFAULT '0', `event_time` float DEFAULT '0', PRIMARY KEY (`game_log_id`), KEY `game_id` (`game_id`), KEY `event_id` (`event_id`), KEY `player_id` (`player_id`) it currently has about 1220 records( 2 or 3 of the other tables have around a million records for each ). now, it's very slow to query this table even I just query this single table. most of the time it failed. do you guys know what the problem is? or how to make it more efficient and faster? thanks in advance CK _ 一张照片的自白――Windows Live照片的可爱视频介绍 http://windowslivesky.spaces.live.com/blog/cns!5892B6048E2498BD!889.entry
Re: large table issue
Hi, Can you please send us the query along with the explain . Also , have u thought of partitioning the data. regards anandkl 2010/6/2 曹凯 tx...@hotmail.com Hi all, do you guys know how to deal with the large tables? here's my problem: I have two web servers( running Nginx ) , two DB servers( running MySQL 5.1.35 ) and a server for load balancing. What I'm maintaining is a game data tracking system. There's a game_log table which will record all detail info from many games. here's the structure: `game_log_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `game_id` int(10) unsigned NOT NULL, `event_id` int(10) unsigned NOT NULL, `player_id` int(10) unsigned NOT NULL, `session_id` varchar(128) NOT NULL COMMENT 'flash session id', `score` int(10) unsigned DEFAULT NULL, `handle_statu` int(1) unsigned NOT NULL DEFAULT '1' COMMENT '1:not handle 2:been handle', `game_end` bigint(20) DEFAULT NULL, `game_start` bigint(20) unsigned NOT NULL DEFAULT '0', `event_time` float DEFAULT '0', PRIMARY KEY (`game_log_id`), KEY `game_id` (`game_id`), KEY `event_id` (`event_id`), KEY `player_id` (`player_id`) it currently has about 1220 records( 2 or 3 of the other tables have around a million records for each ). now, it's very slow to query this table even I just query this single table. most of the time it failed. do you guys know what the problem is? or how to make it more efficient and faster? thanks in advance CK _ 一张照片的自白――Windows Live照片的可爱视频介绍 http://windowslivesky.spaces.live.com/blog/cns!5892B6048E2498BD!889.entry
Re: large table issue
给我看看你的表的索引及你的慢查询的sql语句 在 2010-6-2，下午5:08， 曹凯 写道： Hi all, do you guys know how to deal with the large tables? here's my problem: I have two web servers( running Nginx ) , two DB servers( running MySQL 5.1.35 ) and a server for load balancing. What I'm maintaining is a game data tracking system. There's a game_log table which will record all detail info from many games. here's the structure: `game_log_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `game_id` int(10) unsigned NOT NULL, `event_id` int(10) unsigned NOT NULL, `player_id` int(10) unsigned NOT NULL, `session_id` varchar(128) NOT NULL COMMENT 'flash session id', `score` int(10) unsigned DEFAULT NULL, `handle_statu` int(1) unsigned NOT NULL DEFAULT '1' COMMENT '1:not handle 2:been handle', `game_end` bigint(20) DEFAULT NULL, `game_start` bigint(20) unsigned NOT NULL DEFAULT '0', `event_time` float DEFAULT '0', PRIMARY KEY (`game_log_id`), KEY `game_id` (`game_id`), KEY `event_id` (`event_id`), KEY `player_id` (`player_id`) it currently has about 1220 records( 2 or 3 of the other tables have around a million records for each ). now, it's very slow to query this table even I just query this single table. most of the time it failed. do you guys know what the problem is? or how to make it more efficient and faster? thanks in advance CK _ 一张照片的自白——Windows Live照片的可爱视频介绍 http://windowslivesky.spaces.live.com/blog/cns!5892B6048E2498BD!889.entry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: large table issue
hi huys, here's the explain of a query on this table EXPLAIN SELECT COUNT(game_log_id) AS sum2 FROM game_log AS g, player AS p WHERE g.player_id = p.player_id AND g.game_id=p.game_id=27 AND p.type=1 AND g.event_id = 32 - ; ++-+---+++--+-+++-+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | ++-+---+++--+-+++-+ | 1 | SIMPLE | g | ref| event_id,player_id | event_id | 4 | const | 237894 | | | 1 | SIMPLE | p | eq_ref | PRIMARY| PRIMARY | 4 | trigger_replay.g.player_id | 1 | Using where | ++-+---+++--+-+++-+ 2 rows in set (0.00 sec) and, index on 'game_id', 'event_id', 'player_id' CK _ 想知道明天天气如何？必应告诉你！ http://cn.bing.com/search?q=%E5%A4%A9%E6%B0%94%E9%A2%84%E6%8A%A5form=MICHJ2
Re: Strange behavior by MySQL Stored Procedure
Dear Venugopal,Here's theSample Java Code Which Calls
stored procedure :-//get the connection to databaseConnection dbConnection = getConnection();//create the call
for procedureString procedureCallStmtStr = "Call XYZ()";//create callable statement objectCallableStatement cs =
conn.prepareCall(procedureCallStmtStr);//execute the
procedurecs.execute();//obtain resultsetResultSet
result = cs.getResultSet();//Iterate to get the resultSet, if
present//commit transactionconn.commit();//close resultset, callableStatementresult.close();cs.close();But, can it be a problem if I am executing a stored procedure
anywhere? Well, I am not aware of Java so really cannot debug this.Thanks in advance.--Regards,Manasi SaveOn Tue, 1 Jun 2010 09:36:12 +0530 (IST), Venugopal Rao wrote:
Stored procedures are not executed like a query.
They are executed thru a Call { procedure} method.
Please check the same or let us know how you are executing the
Query/Calling the Procedure.
Regards,
VR Venugopal Rao
--- On Fri, 28/5/10, Manasi Save
manasi.s...@artificialmachines.com wrote:
From: Manasi Save manasi.s...@artificialmachines.comSubject: Strange behavior by MySQL Stored ProcedureTo:
mysql@lists.mysql.comDate: Friday, 28 May, 2010, 5:44 PM
Dear All,I have one stored procedure
Which inserts data into one table.But sometimes it does not
insert record. This happens when I called it from java application. But If I
called same query from mysql command line. It executes successfully.Also I have one procedure which only retrieves data from table.
and it only gives one row sometime even if there are 10 rows available in for
matching condition. This too happen when I called it from Java application and
if I called it from mysql command line it gives me proper result set of 10
rows.I am not able to understand Is it something known for
mysql? Or am I doing something wrong?Any input will be a great
help.--Thanks and Regards,Manasi Save
Re: large table issue
Hi, MySQL Partitioning will help you a lot. Try it. Regards, Krishna 2010/6/2 曹凯 tx...@hotmail.com Hi all, do you guys know how to deal with the large tables? here's my problem: I have two web servers( running Nginx ) , two DB servers( running MySQL 5.1.35 ) and a server for load balancing. What I'm maintaining is a game data tracking system. There's a game_log table which will record all detail info from many games. here's the structure: `game_log_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `game_id` int(10) unsigned NOT NULL, `event_id` int(10) unsigned NOT NULL, `player_id` int(10) unsigned NOT NULL, `session_id` varchar(128) NOT NULL COMMENT 'flash session id', `score` int(10) unsigned DEFAULT NULL, `handle_statu` int(1) unsigned NOT NULL DEFAULT '1' COMMENT '1:not handle 2:been handle', `game_end` bigint(20) DEFAULT NULL, `game_start` bigint(20) unsigned NOT NULL DEFAULT '0', `event_time` float DEFAULT '0', PRIMARY KEY (`game_log_id`), KEY `game_id` (`game_id`), KEY `event_id` (`event_id`), KEY `player_id` (`player_id`) it currently has about 1220 records( 2 or 3 of the other tables have around a million records for each ). now, it's very slow to query this table even I just query this single table. most of the time it failed. do you guys know what the problem is? or how to make it more efficient and faster? thanks in advance CK _ 一张照片的自白――Windows Live照片的可爱视频介绍 http://windowslivesky.spaces.live.com/blog/cns!5892B6048E2498BD!889.entryhttp://windowslivesky.spaces.live.com/blog/cns%215892B6048E2498BD%21889.entry
RE: Strange errors / messages on slave server
-Original Message- From: machiel.richards [mailto:machiel.richa...@gmail.com] Sent: Wednesday, June 02, 2010 3:56 AM To: mysql@lists.mysql.com Subject: RE: Strange errors / messages on slave server Hi All Just one other note on this issue experienced. I used google to try and find some solutions / clues and all the suggestions are to increase the innodb buffer pool size. This was however recently done on the database to increase this to 4Gb already. However, this change is only done on the master server and not on the slave server. I am not sure how the buffer pools are handled in replication though, but would this not perhaps need to be set on the slave server as well. My thinking around this is that the error occurred specifically on the slave server, where there are no specific configuration to increase the innodb buffer pool size. Thus to me this looks like the slave is still using the default 8mb and should perhaps be changed to be the same as the master server? [JS] I think you've hit the nail on the head. Presumably you increased the buffer pool on the master in order to get higher throughput. That means that under load the master will process more transactions per second than the slave can. You don't have to get very far into queuing theory to find out that if the rate of arriving transactions exceeds the capacity of a server (in the generic sense), then the length of the queue will grow to infinity. In less technical terms, if the master goes faster than the slave, the slave will puke. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Machiel Richards -Original Message- From: Machiel Richards [mailto:machi...@rdc.co.za] Sent: 02 June 2010 9:41 AM To: mysql@lists.mysql.com Subject: Strange errors / messages on slave server Good day all I hope someone can assist me with this. While doing the normal routine daily health checks on one of our clients' servers I came across some strange behaviour from the slave server. (two servers setup in master / slave replication) While looking at the current Innodb buffer pool usage (master server), I noticed that the usage went up from 44% yesterday to 98.7% today, however nothing on the master server suggested why. I went on to look at the save server and found very strange behaviour (for me anyway) and I am hoping someone can assist in explaining this to me and some possible corrective actions: When running show slave status, it seems that there was an error logged which show in the output as below: mysql show slave status; +--+--+-+--- --+---+--+-+ --+---+---+--+-- -+-+-+-- --++-+-- ---++--- +--- ---+-+-+-+-- --+---+++--- -+-+---++--- + | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | +--+--+-+--- --+---+--+-+ --+---+---+--+-- -+-+-+-- --++-+-- ---++--- +---
Re: Strange errors / messages on slave server
Thank you for the response... My question now is, will I be able to set the innodb buffer pool size for the slave server to be the same as the master server? If so, can I only restart the slave server and keep the master server running in order to cancel out the requirement for downtime? On Wed, Jun 2, 2010 at 2:22 PM, Jerry Schwartz je...@gii.co.jp wrote: -Original Message- From: machiel.richards [mailto:machiel.richa...@gmail.com] Sent: Wednesday, June 02, 2010 3:56 AM To: mysql@lists.mysql.com Subject: RE: Strange errors / messages on slave server Hi All Just one other note on this issue experienced. I used google to try and find some solutions / clues and all the suggestions are to increase the innodb buffer pool size. This was however recently done on the database to increase this to 4Gb already. However, this change is only done on the master server and not on the slave server. I am not sure how the buffer pools are handled in replication though, but would this not perhaps need to be set on the slave server as well. My thinking around this is that the error occurred specifically on the slave server, where there are no specific configuration to increase the innodb buffer pool size. Thus to me this looks like the slave is still using the default 8mb and should perhaps be changed to be the same as the master server? [JS] I think you've hit the nail on the head. Presumably you increased the buffer pool on the master in order to get higher throughput. That means that under load the master will process more transactions per second than the slave can. You don't have to get very far into queuing theory to find out that if the rate of arriving transactions exceeds the capacity of a server (in the generic sense), then the length of the queue will grow to infinity. In less technical terms, if the master goes faster than the slave, the slave will puke. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Machiel Richards -Original Message- From: Machiel Richards [mailto:machi...@rdc.co.za] Sent: 02 June 2010 9:41 AM To: mysql@lists.mysql.com Subject: Strange errors / messages on slave server Good day all I hope someone can assist me with this. While doing the normal routine daily health checks on one of our clients' servers I came across some strange behaviour from the slave server. (two servers setup in master / slave replication) While looking at the current Innodb buffer pool usage (master server), I noticed that the usage went up from 44% yesterday to 98.7% today, however nothing on the master server suggested why. I went on to look at the save server and found very strange behaviour (for me anyway) and I am hoping someone can assist in explaining this to me and some possible corrective actions: When running show slave status, it seems that there was an error logged which show in the output as below: mysql show slave status; +--+--+-+--- --+---+--+-+ --+---+---+--+-- -+-+-+-- --++-+-- ---++--- +--- ---+-+-+-+-- --+---+++--- -+-+---++--- + | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | +--+--+-+--- --+---+--+-+
RE: Strange errors / messages on slave server
In less technical terms, if the master goes faster than the slave, the slave will puke. MGthen the master will have to teach the slave MGis the master the entrenched bureacucrat or is that the slave? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Machiel Richards -Original Message- From: Machiel Richards [mailto:machi...@rdc.co.za] Sent: 02 June 2010 9:41 AM To: mysql@lists.mysql.com Subject: Strange errors / messages on slave server Good day all I hope someone can assist me with this. While doing the normal routine daily health checks on one of our clients' servers I came across some strange behaviour from the slave server. (two servers setup in master / slave replication) While looking at the current Innodb buffer pool usage (master server), I noticed that the usage went up from 44% yesterday to 98.7% today, however nothing on the master server suggested why. I went on to look at the save server and found very strange behaviour (for me anyway) and I am hoping someone can assist in explaining this to me and some possible corrective actions: When running show slave status, it seems that there was an error logged which show in the output as below: mysql show slave status; +--+--+-+--- --+---+--+-+ --+---+---+--+-- -+-+-+-- --++-+-- ---++--- +--- ---+-+-+-+-- --+---+++--- -+-+---++--- + | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | +--+--+-+--- --+---+--+-+ --+---+---+--+-- -+-+-+-- --++-+-- ---++--- +--- ---+-+-+-+-- --+---+++--- -+-+---++--- + | Waiting for master to send event | MASTER.SERVER | repladmin | 3306 | 60 | mysql-bin.000327 | 672223064 | SLAVE-relay-bin.001016 | 598540830 | mysql-bin.000326 | Yes | No | | | | | | | 1206 | Error 'The total number of locks exceeds the lock table size' on query. Default database: 'profiler'. Query: 'update profile_options set `value` = REPLACE(`value`, '.', '') where list_item_id = 11' | 0 | 598540693 | 1746329551 | None | | 0 | No | | | | | | NULL | +--+--+-+--- --+---+--+-+ --+---+---+--+-- -+-+-+-- --++-+-- ---++--- +--- ---+-+-+-+--
RE: large table issue
Just a note from a kibitzer: if you include an EXPLAIN, why not use \G so that it is easier to read? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -Original Message- From: ?? [mailto:tx...@hotmail.com] Sent: Wednesday, June 02, 2010 6:14 AM To: mysql@lists.mysql.com Subject: RE: large table issue hi huys, here's the explain of a query on this table EXPLAIN SELECT COUNT(game_log_id) AS sum2 FROM game_log AS g, player AS p WHERE g.player_id = p.player_id AND g.game_id=p.game_id=27 AND p.type=1 AND g.event_id = 32 - ; ++-+---+++--+- +++-+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | ++-+---+++--+- +++-+ | 1 | SIMPLE | g | ref| event_id,player_id | event_id | 4 | const | 237894 | | | 1 | SIMPLE | p | eq_ref | PRIMARY| PRIMARY | 4 | trigger_replay.g.player_id | 1 | Using where | ++-+---+++--+- +++-+ 2 rows in set (0.00 sec) and, index on 'game_id', 'event_id', 'player_id' CK _ ???! http://cn.bing.com/search?q=%E5%A4%A9%E6%B0%94%E9%A2%84%E6%8A%A5form=MICHJ2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Strange errors / messages on slave server
Depending on your mysql version and environment, another solution might be to switch to row-based binlogs. Make sure to read the documentation thoroughly, though - there's a number of caveats. On Wed, Jun 2, 2010 at 2:55 PM, Martin Gainty mgai...@hotmail.com wrote: In less technical terms, if the master goes faster than the slave, the slave will puke. MGthen the master will have to teach the slave MGis the master the entrenched bureacucrat or is that the slave? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Machiel Richards -Original Message- From: Machiel Richards [mailto:machi...@rdc.co.za] Sent: 02 June 2010 9:41 AM To: mysql@lists.mysql.com Subject: Strange errors / messages on slave server Good day all I hope someone can assist me with this. While doing the normal routine daily health checks on one of our clients' servers I came across some strange behaviour from the slave server. (two servers setup in master / slave replication) While looking at the current Innodb buffer pool usage (master server), I noticed that the usage went up from 44% yesterday to 98.7% today, however nothing on the master server suggested why. I went on to look at the save server and found very strange behaviour (for me anyway) and I am hoping someone can assist in explaining this to me and some possible corrective actions: When running show slave status, it seems that there was an error logged which show in the output as below: mysql show slave status; +--+--+-+--- --+---+--+-+ --+---+---+--+-- -+-+-+-- --++-+-- ---++--- +--- ---+-+-+-+-- --+---+++--- -+-+---++--- + | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | +--+--+-+--- --+---+--+-+ --+---+---+--+-- -+-+-+-- --++-+-- ---++--- +--- ---+-+-+-+-- --+---+++--- -+-+---++--- + | Waiting for master to send event | MASTER.SERVER | repladmin | 3306 | 60 | mysql-bin.000327 | 672223064 | SLAVE-relay-bin.001016 | 598540830 | mysql-bin.000326 | Yes | No | | | | | | | 1206 | Error 'The total number of locks exceeds the lock table size' on query. Default database: 'profiler'. Query: 'update profile_options set `value` = REPLACE(`value`, '.', '') where list_item_id = 11' | 0 | 598540693 | 1746329551 | None | | 0 | No | | | | | | NULL | +--+--+-+--- --+---+--+-+ --+---+---+--+-- -+-+-+--
how to avoid sub-query to gain performance
*hi* * * *i have a reporting query which have 2 long sub-query* SELECT r1.code_centre, r1.libelle_centre, r1.id_equipe, r1.equipe, r1.id_file_attente, r1.libelle_file_attente,r1.id_date, r1.tranche, r1.id_granularite_de_periode,r1.granularite, r1.ContactsTraites, r1.ContactsenParcage, r1.ContactsenComm, r1.DureeTraitementContacts, r1.DureeComm, r1.DureeParcage,r2.AgentsConnectes, r2.DureeConnexion, r2.DureeTraitementAgents, r2.DureePostTraitement FROM ( SELECT cc.id_centre_contact, cc.code_centre, cc.libelle_centre, a.id_equipe, a.equipe, a.id_file_attente, f.libelle_file_attente, a.id_date, g.tranche, g.id_granularite_de_periode, g.granularite,sum(Nb_Contacts_Traites) as ContactsTraites, sum(Nb_Contacts_en_Parcage) as ContactsenParcage, sum(Nb_Contacts_en_Communication) as ContactsenComm, sum(Duree_Traitement/1000) as DureeTraitementContacts, sum(Duree_Communication / 1000 + Duree_Conference / 1000 + Duree_Com_Interagent / 1000) as DureeComm, sum(Duree_Parcage/1000) as DureeParcage FROM agr_synthese_activite_media_fa_agent a, centre_contact cc, direction_contact dc, granularite_de_periode g, media m, file_attente f WHERE m.id_media = a.id_media AND cc.id_centre_contact = a.id_centre_contact AND a.id_direction_contact = dc.id_direction_contact AND dc.direction_contact ='INCOMING' AND a.id_file_attente = f.id_file_attente AND m.media = 'PHONE' AND ( ( g.valeur_min = date_format(a.id_date,'%d/%m') and g.granularite = 'Jour') or ( g.granularite = 'Heure' and a.id_th_heure = g.id_granularite_de_periode) ) GROUP by cc.id_centre_contact, a.id_equipe, a.id_file_attente, a.id_date, g.tranche, g.id_granularite_de_periode) r1, ( (SELECT cc.id_centre_contact,cc.code_centre, cc.libelle_centre, a.id_equipe, a.equipe, a.id_date, g.tranche, g.id_granularite_de_periode,g.granularite, count(distinct a.id_agent) as AgentsConnectes, sum(Duree_Connexion / 1000) as DureeConnexion, sum(Duree_en_Traitement / 1000) as DureeTraitementAgents, sum(Duree_en_PostTraitement / 1000) as DureePostTraitement FROM activite_agent a, centre_contact cc, granularite_de_periode g WHERE ( g.valeur_min = date_format(a.id_date,'%d/%m') and g.granularite = 'Jour') AND cc.id_centre_contact = a.id_centre_contact GROUP BY cc.id_centre_contact, a.id_equipe, a.id_date, g.tranche, g.id_granularite_de_periode ) UNION (SELECT cc.id_centre_contact,cc.code_centre, cc.libelle_centre, a.id_equipe, a.equipe, a.id_date, g.tranche, g.id_granularite_de_periode,g.granularite, count(distinct a.id_agent) as AgentsConnectes, sum(Duree_Connexion / 1000) as DureeConnexion, sum(Duree_en_Traitement / 1000) as DureeTraitementAgents, sum(Duree_en_PostTraitement / 1000) as DureePostTraitement FROM activite_agent a, centre_contact cc, granularite_de_periode g WHERE (g.granularite = 'Heure' AND a.id_th_heure = g.id_granularite_de_periode) AND cc.id_centre_contact = a.id_centre_contact GROUP BY cc.id_centre_contact,a.id_equipe, a.id_date, g.tranche, g.id_granularite_de_periode) ) r2 WHERE r1.id_centre_contact = r2.id_centre_contact AND r1.id_equipe = r2.id_equipeAND r1.id_date = r2.id_date AND r1.tranche = r2.tranche AND r1.id_granularite_de_periode = r2.id_granularite_de_periode GROUP BY r1.id_centre_contact , r1.id_equipe, r1.id_file_attente, r1.id_date, r1.tranche, r1.id_granularite_de_periode ORDER BY r1.code_centre, r1.libelle_centre, r1.equipe, r1.libelle_file_attente, r1.id_date, r1.id_granularite_de_periode,r1.tranche *the EXPLAIN shows* | id | select_type | table | type| possible_keys | key | key_len | ref| rows | Extra| '1', 'PRIMARY', 'derived3', 'ALL', NULL, NULL, NULL, NULL, '2520', 'Using temporary; Using filesort' '1', 'PRIMARY', 'derived2', 'ALL', NULL, NULL, NULL, NULL, '4378', 'Using where; Using join buffer' '3', 'DERIVED', 'a', 'ALL', 'fk_Activite_Agent_centre_contact', NULL, NULL, NULL, '83433', 'Using temporary; Using filesort' '3', 'DERIVED', 'g', 'ref', 'Index_granularite,Index_Valeur_min', 'Index_Valeur_min', '23', 'func', '1', 'Using where' '3', 'DERIVED', 'cc', 'ALL', 'PRIMARY', NULL, NULL, NULL, '6', 'Using where; Using join buffer' '4', 'UNION', 'g', 'ref', 'PRIMARY,Index_granularite', 'Index_granularite', '23', '', '24', 'Using where; Using temporary; Using filesort' '4', 'UNION', 'a', 'ref', 'fk_Activite_Agent_centre_contact,fk_activite_agent_TH_heure', 'fk_activite_agent_TH_heure', '5', 'reporting_acd.g.Id_Granularite_de_periode', '2979', 'Using where' '4', 'UNION', 'cc', 'ALL', 'PRIMARY', NULL, NULL, NULL, '6', 'Using where; Using join buffer' NULL, 'UNION RESULT', 'union3,4', 'ALL', NULL, NULL, NULL, NULL, NULL, '' '2', 'DERIVED', 'g', 'range',
Re: how to avoid sub-query to gain performance
On Wed, Jun 2, 2010 at 10:28 AM, Lin Chun franks1...@gmail.com wrote: *don't know it very clear, but i think is the problem of derived seems it take full scaning* Yes, it has no indexes so it isn't good for very large subqueries. You should create them as temporary tables instead and give them indexes. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Fancy partitioning scheme
Perhaps someone has already accomplished this: I have a simple table with 3 columns: mytable( myid BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, myunixtime INT(11) NOT NULL DEFAULT 0, myvalue BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 ) It is collecting millions of rows. The myunixtime column is a unix timestamp column. I'd love to know if it is possible to partition the table so that the partitions would be something like: partition A = everything one day or less old, partition B = everything 7 days old or less, partition C = everything 31 days old or less, partition D = everything older than 31 days. Can partitioning be this dynamic? If not, what solution could be suggested to handle doing date range queries on this table that can have 10's or 100's of millions of rows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Fancy partitioning scheme
MySQL doesn't support dynamic distribution of data among partitions. The usual method is to create a partition for each fixed chunk of time, such as for each month/week/hour/day or whatever time slice breaks your data up in the manageable pieces. Note that a very large number of partitions ( 1000 isn't really recommended.) Other notes: Personally, I avoid schema-less constructions like this, because they are hard to work with. Sure they're flexible, but you often pay a price in performance. 100 million rows isn't all that much with the proper indexing. It really depends on your queries and access patterns. Why not use mysql datetime or timestamp type? Storing unix timestamps as int means you're going to have to convert values to use mysql's date functions. Regards, Gavin Towey -Original Message- From: Bryan Cantwell [mailto:bcantw...@firescope.com] Sent: Wednesday, June 02, 2010 12:30 PM To: mysql@lists.mysql.com Subject: Fancy partitioning scheme Perhaps someone has already accomplished this: I have a simple table with 3 columns: mytable( myid BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, myunixtime INT(11) NOT NULL DEFAULT 0, myvalue BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 ) It is collecting millions of rows. The myunixtime column is a unix timestamp column. I'd love to know if it is possible to partition the table so that the partitions would be something like: partition A = everything one day or less old, partition B = everything 7 days old or less, partition C = everything 31 days old or less, partition D = everything older than 31 days. Can partitioning be this dynamic? If not, what solution could be suggested to handle doing date range queries on this table that can have 10's or 100's of millions of rows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
Slow when using sub-query
I've heard that sub-queries aren't well-optimized, but this case seems ridiculous. First, a little setup: SELECT pub_id FROM pub WHERE pub_code = 'GD' INTO @P; === Inner Query by Itself === us-gii SELECT prod_pub_prod_id FROM prod - WHERE pub_id = @P - AND prod_discont = 0 - GROUP BY prod_pub_prod_id - HAVING COUNT(*) 1; +--+ | prod_pub_prod_id | +--+ | NULL | | GDAE0106ICR | | GDME0002TR | | GDME0023IAR | | GDME0059IAR | +--+ 5 rows in set (0.05 sec) us-gii EXPLAIN - SELECT prod_pub_prod_id FROM prod - WHERE pub_id = @P - AND prod_discont = 0 - GROUP BY prod_pub_prod_id - HAVING COUNT(*) 1 - \G *** 1. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: pub_id,pub_id_2 key: pub_id key_len: 48 ref: const rows: 1543 Extra: Using where; Using temporary; Using filesort === Outer Query without Inner Query === us-gii SELECT prod_num FROM prod - WHERE pub_id = @P - AND prod_pub_prod_id IN - ( - NULL, - 'GDAE0106ICR', - 'GDME0002TR', - 'GDME0023IAR', - 'GDME0059IAR' - ) - ; +--+ | prod_num | +--+ |83298 | |85092 | |88728 | |97231 | |97235 | |98368 | | 107693 | | 112461 | +--+ 8 rows in set (0.01 sec) us-gii EXPLAIN - SELECT prod_num FROM prod - WHERE pub_id = @P - AND prod_pub_prod_id IN - ( - NULL, - 'GDAE0106ICR', - 'GDME0002TR', - 'GDME0023IAR', - 'GDME0059IAR' - ) - \G *** 1. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: prod_pub_prod_id,pub_id,pub_id_2 key: pub_id key_len: 48 ref: const rows: 1543 Extra: Using where === Outer Query with Sub-query === us-gii EXPLAIN - SELECT prod_num FROM prod - WHERE pub_id = @P - AND prod_pub_prod_id IN - (SELECT prod_pub_prod_id FROM prod - WHERE pub_id = @P - AND prod_discont = 0 - GROUP BY prod_pub_prod_id - HAVING COUNT(*) 1) - \G *** 1. row *** id: 1 select_type: PRIMARY table: prod type: ref possible_keys: pub_id,pub_id_2 key: pub_id key_len: 48 ref: const rows: 1543 Extra: Using where *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: prod type: index possible_keys: pub_id,pub_id_2 key: prod_pub_prod_id key_len: 768 ref: NULL rows: 72 Extra: Using where; Using filesort I don't know how long the Outer Query with Sub-query would take: I killed it after several minutes. I'm guessing that it has to do with the fact that the inner query is dependent, but why is that happening? === Rewritten as Join === us-gii SELECT prod_num FROM prod JOIN - (SELECT prod_pub_prod_id FROM prod - WHERE pub_id = @P - AND prod_discont = 0 - GROUP BY prod_pub_prod_id - HAVING COUNT(*) 1) AS x - ON prod.prod_pub_prod_id = x.prod_pub_prod_id - WHERE prod.pub_id = @P - AND prod.prod_discont = 0; +--+ | prod_num | +--+ |98368 | | 107693 | |83298 | |85092 | |88728 | |97231 | |97235 | | 112461 | +--+ 8 rows in set (0.05 sec) us-gii EXPLAIN - SELECT prod_num FROM prod JOIN - (SELECT prod_pub_prod_id FROM prod - WHERE pub_id = @P - AND prod_discont = 0 - GROUP BY prod_pub_prod_id - HAVING COUNT(*) 1) AS x - ON prod.prod_pub_prod_id = x.prod_pub_prod_id - WHERE prod.pub_id = @P - AND prod.prod_discont = 0 - \G *** 1. row *** id: 1 select_type: PRIMARY table: derived2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5 Extra: *** 2. row *** id: 1 select_type: PRIMARY table: prod type: ref possible_keys: prod_pub_prod_id,pub_id,pub_id_2 key: prod_pub_prod_id key_len: 768 ref: x.prod_pub_prod_id rows: 1 Extra: Using where *** 3. row *** id: 2 select_type: DERIVED table: prod type: ref possible_keys: pub_id,pub_id_2 key: pub_id key_len: 48 ref: rows: 1544 Extra: Using where; Using temporary; Using filesort 3 rows in set (0.03 sec) = What a difference! I don't understand it, though. Does anyone
Question on http query
Hi, I'd like to know if MySQL server supports http queries? Thanks. Kandy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Missing database file names
After a recent hard drive kerfluffle and the results of fsck, I'm left with a slew of jumbled database files. The file command can tell me the file types, like so: #15901614: MySQL table definition file Version 10 #15901615: MySQL MISAM compressed data file Version 1 #15901617: MySQL table definition file Version 10 #15901618: MySQL MISAM compressed data file Version 1 #15901620: MySQL table definition file Version 10 #15901621: MySQL MISAM compressed data file Version 1 These files are, I'm pretty sure, from my mythtv database. I'm rebuilding my box and it would be nice if I can keep the mythtv database the same as before, but how can I tell which file is which? Any ideas? Thanks much. -- Jesse F. Hughes Me: Quincy, there's only *one* Truth, isn't there? Quincy (age 4): Yeah, and it's *mine*. -- A lesson in postmodernism goes awry. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question on http query
MySQL is a tradition Relational DataBase System. It underlays something like 80% (somebody correct me if I'm out-of-date here) of the http applications populating the internet. While some RDBMSs offer extensions for RESP-like HTTP implementations, MySQL does not support this directly. It can be used in conjunction with a multitude of languages and frameworks. If you are just getting started on this path, you might want to look at something like python or ruby or PHP, they all can handle HTTP requests very efficiently and have nice interfaces to MySQL. Best of Luck. - michael dykman On Wed, Jun 2, 2010 at 7:15 PM, Kandy Wong kan...@triumf.ca wrote: Hi, I'd like to know if MySQL server supports http queries? Thanks. Kandy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
