MySQL Slaves lag behind master

[hieuhtr]

Issue:

• Having a lot of hits from app server/background-worker to master DB
• Seconds_Behind_Master = 14657
• Sometime Slave_IO_State is in system lock
• Error from /var/log/mysql/error.log: Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013)

Root cause:

• Data transfer from the master cannot be sent to slave because net_read_timeout is too small, timeout from slave database
• Packet data transfer is too big from the master, and the current max_allowed_packet value in slave is too small

Solution:

• Increase net_read_timeout from 10 to 360
• Increase max_allowed_packet from 4MB to 8MB

Think more

mysql> SHOW variables like `net_read_timeout` 
mysql> SET GLOBAL `net_read_timeout` = 360;

Reference:

1. https://dev.mysql.com/doc/refman/5.6/en/error-lost-connection.html
2. https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_slave-parallel-workers