From: Martin Gainty Date: June 2 2010 12:55pm Subject: RE: Strange errors / messages on slave server List-Archive: http://lists.mysql.com/mysql/221789 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_a04ecb6b-edc5-4a2d-a3da-5d43155360ac_" --_a04ecb6b-edc5-4a2d-a3da-5d43155360ac_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable > In less technical terms=2C if the master goes faster than the slave=2C th= e slave=20 > will puke. MG>then the master will have to teach the slave=20 MG>is the master the entrenched bureacucrat or is that the slave? =20 >=20 > Regards=2C >=20 > Jerry Schwartz > Global Information Incorporated > 195 Farmington Ave. > Farmington=2C CT 06032 >=20 > 860.674.8796 / FAX: 860.674.8341 >=20 > www.the-infoshop.com >=20 >=20 >=20 > > > > > >Machiel Richards > > > > > >-----Original Message----- > >From: Machiel Richards [mailto:machielr@stripped] > >Sent: 02 June 2010 9:41 AM > >To: mysql@stripped > >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)=2C I noticed that the usage went up from 44% yesterday t= o > >98.7% today=2C 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 explaini= ng > >this to me and some possible corrective actions: > > > > > > > > When running show slave status=2C it seems that there was an > >error logged which show in the output as below: > > > > > > > >mysql> show slave status=3B > > > >+----------------------------------+----------------------+-------------= +--- > >----------+---------------+------------------+---------------------+----= ---- > >--------------+---------------+-----------------------+-----------------= -+-- > >-----------------+-----------------+---------------------+--------------= ---- > >--+------------------------+-------------------------+------------------= ---- > >-------+------------+---------------------------------------------------= ---- > >------------------------------------------------------------------------= ---- > >--------------------------------------------------------------------+---= ---- > >-------+---------------------+-----------------+-----------------+------= ---- > >------+---------------+--------------------+--------------------+-------= ---- > >---------+-----------------+-------------------+----------------+-------= ---- > >------------+ > > > >| 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_Ignor= e_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_Ke= y | > >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 s= et > >`value` =3D REPLACE(`value`=2C '.'=2C '') where list_item_id =3D 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=2C including > > > >InnoDB: lock heap and hash index sizes. > > > >100601 9:56:54 [ERROR] Slave: Error 'The total number of locks exceeds t= he > >lock table size' on query. Default database: 'profiler'. Query: 'update > >profile_options set `value` =3D REPLACE(`value`=2C '.'=2C '') where list= _item_id =3D > >11'=2C Error_code: 1206 > > > >100601 9:56:54 [ERROR] Error running query=2C slave SQL thread aborted. = Fix > >the problem=2C and restart the slave SQL thread with "SLAVE START". We s= topped > >at log 'mysql-bin.000326' position 598540693 > > > > > > > >=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > > > >100601 9:57:00 INNODB MONITOR OUTPUT > > > >=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > > > >Per second averages calculated from the last 61 seconds > > > >---------- > > > >SEMAPHORES > > > >---------- > > > >OS WAIT ARRAY INFO: reservation count 246260=2C signal count 246194 > > > >Mutex spin waits 0=2C rounds 2310610=2C OS waits 10307 > > > >RW-shared spins 423365=2C OS waits 208580=3B RW-excl spins 28923=2C OS w= aits 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=2C firstly the same error is showing > >in the logs as well. > > > > Secondly=2C 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=3Djerry@stripped >=20 >=20 >=20 >=20 >=20 > --=20 > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@stripped >=20 =20 _________________________________________________________________ Hotmail is redefining busy with tools for the New Busy. Get more from your = inbox. http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID28326::T:WLMTAGL:O= N:WL:en-US:WM_HMP:042010_2= --_a04ecb6b-edc5-4a2d-a3da-5d43155360ac_--