Strange errors / messages on slave server
Strange errors / messages on slave server
am 02.06.2010 09:40:50 von machielr
------=_NextPart_000_005C_01CB0237.B4631400
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: 7bit
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
------=_NextPart_000_005C_01CB0237.B4631400--
RE: Strange errors / messages on slave server
am 02.06.2010 09:56:23 von Machiel Richards
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:machielr@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=gcdmg-mysql-2@m.gmane.org
RE: Strange errors / messages on slave server
am 02.06.2010 14:22:06 von Jerry Schwartz
>-----Original Message-----
>From: machiel.richards [mailto:machiel.richards@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:machielr@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=jerry@gii.co.jp
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Re: Strange errors / messages on slave server
am 02.06.2010 14:32:01 von Machiel Richards
--0016e68ef470d92a3004880b46ab
Content-Type: text/plain; charset=ISO-8859-1
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 wrote:
> >-----Original Message-----
> >From: machiel.richards [mailto:machiel.richards@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:machielr@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=jerry@gii.co.jp
>
>
>
>
>
--
I am a bomb technician...If you see me running, TRY TO KEEP UP!
--0016e68ef470d92a3004880b46ab--
RE: Strange errors / messages on slave server
am 02.06.2010 14:55:10 von Martin Gainty
--_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@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)=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=3 D=====
=============3D
> >
> >100601 9:57:00 INNODB MONITOR OUTPUT
> >
> >===================3D=3 D=====
=============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@gii.co.jp
>=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@hotmail.com
>=20
=20
____________________________________________________________ _____
Hotmail is redefining busy with tools for the New Busy. Get more from your =
inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID283 26::T:WLMTAGL:O=
N:WL:en-US:WM_HMP:042010_2=
--_a04ecb6b-edc5-4a2d-a3da-5d43155360ac_--
Re: Strange errors / messages on slave server
am 02.06.2010 16:23:33 von Johan De Meersman
--0016368e30e5bf760404880cd509
Content-Type: text/plain; charset=ISO-8859-1
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 wrote:
>
> > In less technical terms, if the master goes faster than the slave, the
> slave
> > will puke.
>
> MG>then the master will have to teach the slave
>
> MG>is 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:machielr@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=jerry@gii.co.jp
> >
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=mgainty@hotmail.com
> >
>
>
> ____________________________________________________________ _____
> Hotmail is redefining busy with tools for the New Busy. Get more from your
> inbox.
>
> http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326 ::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2
>
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
--0016368e30e5bf760404880cd509--