>-----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 | > >+----------------------------------+----------------------+-------------+--- >----------+---------------+------------------+---------------------+-------- >--------------+---------------+-----------------------+------------------+-- >-----------------+-----------------+---------------------+------------------ >--+------------------------+-------------------------+---------------------- >-------+------------+------------------------------------------------------- >---------------------------------------------------------------------------- >--------------------------------------------------------------------+------- >-------+---------------------+-----------------+-----------------+---------- >------+---------------+--------------------+--------------------+----------- >---------+-----------------+-------------------+----------------+----------- >------------+ > >| 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 the buffer pool is >occupied by > >InnoDB: lock heaps or the adaptive hash index! Check that your > >InnoDB: transactions do not set too many row locks. > >InnoDB: Your buffer pool size is 8 MB. Maybe you should make > >InnoDB: the buffer pool bigger? > >InnoDB: Starting the InnoDB Monitor to print diagnostics, including > >InnoDB: lock heap and hash index sizes. > >100601 9:56:54 [ERROR] Slave: 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', Error_code: 1206 > >100601 9:56:54 [ERROR] Error running query, slave SQL thread aborted. Fix >the problem, and restart the slave SQL thread with "SLAVE START". We stopped >at log 'mysql-bin.000326' position 598540693 > > > >===================================== > >100601 9:57:00 INNODB MONITOR OUTPUT > >===================================== > >Per second averages calculated from the last 61 seconds > >---------- > >SEMAPHORES > >---------- > >OS WAIT ARRAY INFO: reservation count 246260, signal count 246194 > >Mutex spin waits 0, rounds 2310610, OS waits 10307 > >RW-shared spins 423365, OS waits 208580; RW-excl spins 28923, OS waits 25636 > >------------ > >TRANSACTIONS > >------------ > >Trx id counter 0 12672316 > >Purge done for trx's n:o < 0 12672314 undo n:o < 0 0 > >History list length 1 > >Total number of lock structs in row lock hash table 0 > >LIST OF TRANSACTIONS FOR EACH SESSION: > >-------- > >FILE I/O > > > > Looking at the log output, firstly the same error is showing >in the logs as well. > > Secondly, It also started running this INNODB MONITOR which >generated thousands of entries in the log file (about 30000+ lines of >output). > > > > I really hope that someone can assist with this one as I am >still a fairly new dba and this is completely new to me... > >Appreciate all replies. > > > >Regards > >Machiel > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
