Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

My MySQL slave is spending a lot of time in Slave_SQL_Running_State: System lock. I can see that the system is currently I/O write bound, and that it is processing the log, although slowly. Show processlist doesn't show anything other than "Waiting for master to send event" and "System lock" when it is in this state.

All my tables (other than the system tables) are InnoDB, and external locking is disabled. What is the slave doing in this state?

Here's some info that has been requested:

First, this is MySQL 5.6 community on an Amazon EC2 instance, with all storage on EBS.

mysql> show processlist;
+----+-------------+-----------+---------------+---------+--------+----------------------------------+------------------+
| Id | User        | Host      | db            | Command | Time   | State                            | Info             |
+----+-------------+-----------+---------------+---------+--------+----------------------------------+------------------+
|  1 | system user |           | NULL          | Connect |  26115 | Waiting for master to send event | NULL             |
|  2 | system user |           | NULL          | Connect | 402264 | System lock                      | NULL             |
| 14 | readonly    | localhost | theshadestore | Query   |      0 | init                             | show processlist |
+----+-------------+-----------+---------------+---------+--------+----------------------------------+------------------+
3 rows in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 184.106.16.14
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin-log.000764
          Read_Master_Log_Pos: 505452667
               Relay_Log_File: relay-log.000197
                Relay_Log_Pos: 345413863
        Relay_Master_Log_File: bin-log.000746
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 345413702
              Relay_Log_Space: 19834085375
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 402263
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 307009
                  Master_UUID: b1bf9a19-dac0-11e2-8ffa-b8ca3a5bce90
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)
share|improve this question
1  
Anything going on with your storage? If it's local disk, are you getting any SMART warnings, or is it in a degraded RAID array possibly? –  nedm Nov 4 '13 at 21:28
    
It's an EC2 EBS volume. There are no errors in dmesg. –  Greg Nov 4 '13 at 22:01
1  
note that this simply might be a bug of 5.6, consider checking against another version (e.g. 5.5): forums.mysql.com/read.php?22,598354,598354 –  the-wabbit Nov 4 '13 at 22:38

1 Answer 1

Please provide few relevant entries from mysqld.log when replication broke at the first time AND post output from following:

mysql> SHOW SLAVE STATUS\G;
mysql> SHOW FULL PROCESSLIST;
share|improve this answer
    
As far as I can tell, replication isn't broken, it's just slow. Seconds_Behind_Master is decreasing over time. I'm just curious about what is happening when Slave_SQL_Running_State = System lock –  Greg Nov 4 '13 at 22:02
    
perhaps you should double check replication user privileges (just to make sure), you can also look into logs and find out what happened before as well. –  alexus Nov 5 '13 at 15:38
    
The privileges look fine. Again though: it is processing the log, it's just slow, and I'm wondering what it's doing when it says that it is in "System lock" –  Greg Nov 5 '13 at 21:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.