One of my slave is no longer replicating. The
seconds_behind_master continue to increase,
Exec_Master_Log_Pos does not increase, and
Relay_Log_Space does increase.
yes (unless I stop it, or encounter the
I've tried the solutions on this thread which sounded similar but haven't had any luck, Slave SQL thread got hanged. I also tried a
RESET SLAVE which still produce the same behavior.
Additionally when I run:
on my instance it takes +30 seconds to execute.
Query OK, 0 rows affected (33.97 sec)
show slave status\G
Slave_IO_State: Waiting for master to send event Master_Host: 10.0.40.203 Master_User: replicant Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000779 Read_Master_Log_Pos: 881930813 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000779 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: test Replicate_Do_Table: Users,corporations,dates,systemspecs,test_replication,domains,test,ips,deleteddate,percona_checksum,accesslevels,status,collectionsdata,orders,email_to_user,requests,userprops,percona_checksum,useremails,requests_site,sections,ordertosection,UserToGroup,validkeys Replicate_Ignore_Table: Replicate_Wild_Do_Table: percona.% Replicate_Wild_Ignore_Table: test.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 771399898 Relay_Log_Space: 110531372 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: 4784 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: 2222 Master_UUID: example Master_Info_File: /mnt/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: updating 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
I have four other slaves of the master that all are functional so I know the master logs aren't corrupt.
If I leave the replication running I end up with a
Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.
SHOW PROCESSLIST brought back:
348 | replicant | serverDNS | NULL | Binlog Dump | 1107340 | Master has sent all binlog to slave; waiting for binlog to be updated
After finding this we altered
50, its default value, to
14400. This allowed the replication to process again. However it is unclear why the
50 timeout would occur on only one of 5 slaves. All slaves are
m5.2xlarge AWS instances so they have the same number of resources.
Additionally, should I stop at
14400 or should I just set this to the max
If I issue a restart for the mysql service replication processes as expected for about a day then the issue reproduces.
Additionally this slave is also a master of another system if that makes a difference. The slave of this master is running fine.
Current relevant (or in my eyes) slave output lines:
Master_Log_File: mysql-bin.000786 Read_Master_Log_Pos: 131895019 Relay_Log_File: mysqld-relay-bin.000025 Relay_Log_Pos: 52668949 Relay_Master_Log_File: mysql-bin.000786 Exec_Master_Log_Pos: 91692081 Relay_Log_Space: 131895472 Seconds_Behind_Master: 12163
91692081 is the value it currently is stuck at.
Looking into it further
OS file reads,
OS file writes, and
OS fsyncs are consistently increasing. I also have found a warning being logged:
Warning: difficult to find free blocks in the buffer pool (324 search iterations)! 0 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a newer version of your operating system may help. Look at the number of fsyncs in diagnostic info below.