Bug #15914 Slave I/O thread lags behind master
Submitted: 21 Dec 2005 18:21 Modified: 23 Jan 2006 8:17
Reporter: Partha Dutta Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0.15 OS:Linux (Linux Gentoo 2.6.10)
Assigned to: CPU Architecture:Any

[21 Dec 2005 18:21] Partha Dutta
Description:
While the master server is busy writing to binary logs, a connected slave server does not copy the log entries to the relay log immediately, and sometimes gets stuck.  The master server is running version 4.1.13, and the slave is running 5.0.15.  Here is the releveant output:

master server:
mysql> show master status\G
*************************** 1. row ***************************
            File: binlog.000615
        Position: 671989259
    Binlog_Do_DB: rpt_network_prod
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

slave server:
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: nypa072
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: binlog.000614
        Read_Master_Log_Pos: 264721699
             Relay_Log_File: relay-bin.000453
              Relay_Log_Pos: 3588244
      Relay_Master_Log_File: binlog.000614
           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: 264721699
            Relay_Log_Space: 265732508
            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: 0
1 row in set (0.00 sec)

Both slave threads are running, but is completely idle.  The slave thinks its "up-to-date".  The only workaround I have is to stop, then start the slave.

How to repeat:
Hard to completely reproduce at will.
[22 Dec 2005 0:25] Partha Dutta
Here's some more info:

When the slave i/o thread is idle (nothing being written to the master's binary log), then i/o thread goes to sleep and does not wake up.  I am not sure of how long this inactivity time is.  We have jobs that run at the top of the hour inserting hundreds of thousands of records.

One workaround that I have come up with is to insert a record into a table every 5 minutes.  This seems to keep the slave i/o thread awake.
[23 Dec 2005 8:17] Valeriy Kravchuk
Thank you for a problem report. Please, send the my.cnf content from you master and slave. Check with SHOW PROCESSLIST and netstat that connection from slave to master exists when this will happen next time.
[24 Jan 2006 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".