MySQL Replication Slave is lagging more and more

Problem

My MySQL Replication Slave is lagging more and more and I have no clue why. This happens since last weekends application change. I see that one Core is running at 100% user time but the I/O system is nearly completely idle. Any help is appreciated!

Solution

I assume this is a known problem related to MySQL Row Based Replication with tables on the Slave without Primary Keys.

Please check:

• If Replication is Row Based (SHOW GLOBAL VARIABLES LIKE ‘binlog_format’; )
• If there are some tables on the Slave without a Primary Key
• If the currently executed event by the SQL thread is affecting one of those Primary Key less tables
• You can additionally check with SHOW GLOBAL STATUS WHERE variable_name IN (‘Handler_read_rnd_next’, ‘Innodb_rows_read’); if there are more reads than expected. The first one is an indication of full table scans which would support my theory.