Mysql – How to diagnose missing rows on a MySQL slave

Munin's formula for "device utilization" is (milliseconds spent doing I/O)/second, which assumes you can't do any I/O in parallel, so I'm not sure that this is a meaningful metric. However, you do clearly have a genuine performance issue here since replication can't stay caught up.

Is there a significant difference in the I/O subsystems between the servers?

The way I would approach this is to compare the I/O subsystems and then if there isn't a glaring difference in architecture, run some I/O benchmarking at a quiet time with a tool like bonnie++ or fio to narrow down the difference in performance.

Note that the slave likely has an equal write workload to the master because it has replay all the writes, plus it may have a considerable read workload (for reading any parts of the database that have to be updated) and might be less efficient for reads than the master because it has less RAM for caching. In this situation I would not necessarily expect the slave to be able to catch up because it's entirely possible for the master to generate work at a rate faster than the slave can consume it.

Queries on slave are being applied by system user, so doing show full processlist and queries running with system user are the ones executed by slave sql thread.

Moreover you can view relay logs with the help of mysqlbinlog utlity and replacing binlog file with relay log file.

You can also introduce triggers on processlist table in information_schema database in this way you would be able to capture queries.

To get current query as string use following statement.

SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()  

To get queries using trigger a sample trigger is as follows.

First create table logging_table with column as varchar(1024).

DELIMITER |

CREATE TRIGGER log_queries_sample BEFORE INSERT ON `table_name`  
FOR EACH ROW  
BEGIN  
    DECLARE original_query_string VARCHAR(1024);   
    SET original_query_string = (SELECT info
                                 FROM INFORMATION_SCHEMA.PROCESSLIST
                                 WHERE id >= CONNECTION_ID());  
    INSERT INTO `logging_table`(`query`) VALUES (original_query_string);  
END;  
|
DELIMITER ;  

Create triggers of your choice accordingly.

Hope it helps.