Why a MySQL Replica Core Hits 100% CPU and How Adding a Primary Key Fixes It

Problem Description

During a customer inspection, a MySQL replica showed 100% CPU usage on a single core while the master had no slow write queries, indicating the replica’s single‑process replication was consuming all CPU.

Investigation Steps

1. Examine the binlog to see what statements are being replayed, using the parsebinlog tool from the mysql-binlog-statistic repository.

git clone https://github.com/wubx/mysql-binlog-statistic.git
cd mysql-binlog-statistic/bin/
parsebinlog /u1/mysql/logs/mysql-bin.000806

The output highlighted a table xx_db.xxtable with roughly 100 k DELETE and 100 k INSERT operations per day.

2. Verify the replication status on the replica:

show slave status\G

3. Check InnoDB lock information on the replica:

show engine innodb status\G;

The monitor output listed many row‑level locks on xx_db.xxtable, all belonging to a single transaction and showing “TOO MANY LOCKS PRINTED”. The table lacked a primary key, causing InnoDB to generate a hidden clustered index ( GEN_CLUST_INDEX) and leading to extensive page splits and low concurrency.

Solution

Adding an explicit primary key to xx_db.xxtable resolves the issue. An auto‑increment integer column was added, which eliminated the hidden clustered index, reduced lock contention, and dropped the replica’s CPU usage from near‑full to about 3 %.

Result

After the primary key addition, replication proceeded normally with no observed lag over a full day of monitoring.