A case for troubleshooting slave database latency in MySQL: mysql troubleshooting

A case for troubleshooting slave database latency in MySQL: mysql troubleshooting

When a customer is inspected today, a core of the cpu of mysqld occupies 100% in the case of no business in the slave database. There is no SQL statement for querying the slow query of the master database.
It can be said that the typical single-process replication causes a cpu to be fully occupied. If you know the reason, you can analyze it.
Analyze what is written in the binlog to see where it can be optimized or accelerated. Use tool: pasrebinlog
Use show slave status \ G; to check the currently synchronized to the node, and then parse the value to the day.

git clone https://github.com/wubx/mysql-binlog-statistic.gitcd mysql-binlog-statistic/bin/parsebinlog /u1/mysql/logs/mysql-bin.000806...====================================Table xx_db.xxtable:Type DELETE opt： 101246Type INSERT opt： 103265================================...

 

In sorting by the maximum number, we can find: xx_db.xxtable. For a daily value, we can delete tens of thousands and write tens of thousands. Is this table slower to write.
View innodb information in the slave database:

MySQL> show engine innodb status\G;...---TRANSACTION 1C0C2DFDF, ACTIVE 3 sec fetching rowsmysql tables in use 1, locked 13361 lock struct(s), heap size 407992, 477888 row lock(s), undo log entries 42MySQL thread id 43, OS thread handle 0x7fc1800c4700, query id 1908504 Reading event from the relay logTABLE LOCK table xx_db.xxtable trx id 1C0C2DFDF lock mode IXRECORD LOCKS space id 1002 page no 1975 n bits 1120 index `AK_movieid` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gapRECORD LOCKS space id 1002 page no 6965 n bits 264 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gapRECORD LOCKS space id 1002 page no 6967 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gapRECORD LOCKS space id 1002 page no 6973 n bits 264 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gapRECORD LOCKS space id 1002 page no 6982 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gapRECORD LOCKS space id 1002 page no 6983 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gapRECORD LOCKS space id 1002 page no 6987 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gapRECORD LOCKS space id 1002 page no 6999 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gapRECORD LOCKS space id 1002 page no 7000 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gapTOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS----------------------------END OF INNODB MONITOR OUTPUT...

From the monitor output of Innodb, we can also see that the table xx_db.xxtable is already a table-level table, resulting in low concurrency and a large number of: GEN_CLUST_INDEX and belongs to a transaction. GEN_CLUST_INDEX indicates that no primary key is created and a primary key is generated internally. The primary key generated internally can easily cause page splitting.

The problem is basically solved here:
Add a primary key to xx_db.xxtable. here, an id int auto-incrementing primary key is added to xx_db.xxtable. in this way, we can immediately see that the single-core cpu used by mysqld has been reduced to about 3%. At the same time, everything will be synchronized normally in the future, so there will be no synchronization latency issues for one day.