We started noticing Seconds_Behind_Master getting relatively high throughout the day. Then, overnight, it catches up, and Seconds_Behind_Master starts growing again.

Currently, the number is ~25,000, yesterday it was ~50,000.

Slave_IO_State: Waiting for master to send event Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Last_Error: Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:

CPU usage on slave is at 100% when this happens.

What are the next steps to diagnose this?

  • SELECT @@BINLOG_FORMAT; on the master. If it is ROW, then a common cause is an InnoDB table without a primary key, which is never a good idea. Check these things? It could also happen with MIXED, though that is less likely. – Michael - sqlbot May 20 '18 at 0:53
  • Thank you, Michael. Found several tables that are InnoDB, and don't have PKs. Master is in ROW format. If the tables that fall under this condition do not require replication, could we add them to replicate-ignore-table on the slave? Would we be better off switching the binlog format to MIXED? – djdy May 20 '18 at 12:31
  • 100% CPU usually means that you have slow queries. Find them and fix them. It is often as simple as adding a composite index. – Rick James May 30 '18 at 23:47

This could mean that there are huge amount of write on the master and I suppose that the performance of the slave server is lower compared to the master. While new binlog are sent to the slave, it is possible that the slave is busy freeing old ones and does not proceed the new ones yet. This is why the slave is lagged.

So, look at the top command to see what is the most cpu-intensive process:

  • If it is mysqld, i think you'd better upgrade the server. Or if you use this slave for read queries by many clients ( servers ), try to decrease the number of the clients reading from this slave to free up the CPU.

Note: SHOW FULL PROCESSLIST can show you the actual processes running within mysqld.

  • If there are other services that consume more of the CPU, stop them if possible and disable running these at boot.
  • @djdy Additional information request from the SLAVE, please. Post on pastebin.com or here. RAM size of your MySQL Host server A) complete (not edited) my.cnf-ini Text results of: B) SHOW GLOBAL STATUS; C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report if readily available - 7 or more days uptime is helpful Optional very helpful information, if available includes - htop, top & mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when busy for an idea of IOPS by device, for server tuning analysis. – Wilson Hauck Jun 18 '18 at 1:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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