Replication gets "stuck" back in time. Mysql 5.5.40

[Trey Ormsbee]

I have an extremely odd reoccurring issue with a MASTER/MASTER mysql pair running 5.5.40. The issue does not happen often but it is a real pain when it does; and nothing seems to fix it it ends up going away on its own.

Issue is like so:

At some point one of the Slave's slowly falls behind in binlog, generaly 2-3 (even 4-5 hours) behind. Rarely is seconds behind master anything greater then "0" but occasionally you will see it 3000/4000 or in my case right now 9500+ seconds behind.

If I run show slave status multiple times in a row, one out of 10 will show the 9500 seconds of slave lag.


Here is a ZERO second show slave status output:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xxx.xxx.xxx.101
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.347207
Read_Master_Log_Pos: 52752914
Relay_Log_File: db-relay-bin.000028
Relay_Log_Pos: 52744612
Relay_Master_Log_File: mysql-bin.347207
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema
Replicate_Do_Table:
Replicate_Ignore_Table: xxx.table1,xxx.table2
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 52752842
Relay_Log_Space: 52744880
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
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: 1
1 row in set (0.00 sec)

mysql>


Here is an out put with the slave lag:

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.0.70
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.347207
Read_Master_Log_Pos: 39460538
Relay_Log_File: db-relay-bin.000028
Relay_Log_Pos: 39422180
Relay_Master_Log_File: mysql-bin.347207
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema
Replicate_Do_Table:
Replicate_Ignore_Table: mms.smscs,mms.ppgs
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 39429888
Relay_Log_Space: 39453026
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: 9475
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: 1
1 row in set (0.00 sec)


AS for the server its self, there is no more or less disk i/o then usual, load is very low (0.24) The servers in question are exactly the same.

Any ideas what to look for?