Today to a customer inspection of the situation issued from the library does not have the business situation MYSQLD CPU's a core occupies 100%. Chacku Slow query also does not have written SQL.
It can be said that the simple process of copying a CPU full of the resulting. Know the reason, it's good to analyze.
Analyze what is written in the Binlog and see where it can be optimized or accelerated. Tools for use: Pasrebinlog
Use show slave status\g; Check the current sync to the node, and then parse the day value.
git clone https://github.com/wubx/mysql-binlog-statistic.git
cd mysql-binlog-statistic/bin/
parsebinlog/ u1/mysql/logs/mysql-bin.000806 ...
====================================
Table xx_db.xxtable:
type DELETE opt:101246
type INSERT opt:103265
================================
...
Look at the largest number of numbers, positioning to: xx_db.xxtable, for a day value can be deleted 10 tens of thousands of, write 10 tens of thousands of. Is this table write more slowly?
To view InnoDB from the library:
Mysql> show engine InnoDB status\g; ...---TRANSACTION 1c0c2dfdf, ACTIVE 3 sec fetching rows MySQL tables in use 1, locked 1 3361 lock struct (s), Heap size 40 7992, 477888 row lock (s), undo log Entries MySQL thread ID, OS thread handle 0x7fc1800c4700, query ID 1908504 readin G Event from the Relay log TABLE LOCK table xx_db.xxtable Trx ID 1c0c2dfdf LOCK mode IX record 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 gap record lock S 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 gap record LOCKS Space ID 1002 Page no 6967 n bits 256 index ' gen_clust_index ' table xx_db.xxtable Trx ID 1 C0c2dfdf Lock_mode X Locks Rec but not gap record locks Space ID 1002 Page no 6973 n bits 264 index ' gen_clust_index ' of t Able xx_db.xxtable Trx ID 1c0c2dfdf lock_mode X locks Rec but not gap record locks Space ID 1002 Page No 6MB n BITS 256 index ' gen_clust_index ' of table xx_db.xxtable Trx ID 1c0c2dfdf lock_mode X locks Rec but not gap record LO Cks Space ID 1002 Page no 6983 n bits 256 index ' gen_clust_index ' of table xx_db.xxtable Trx ID 1C0C2DFDF lock_mode X lock S rec but not gap record 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 gap record 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 gap record 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 gap TOO MANY Locks
For this trx:suppressing further prints----------------------------end of INNODB MONITOR OUTPUT ...
From the InnoDB monitor output can also see xx_db.xxtable This table is already table-level table, resulting in a relatively low concurrency, and there are a lot of: Gen_clust_index and belong to a transaction. Gen_clust_index that there is no main building, the internal generation of a main building, for the internal generation of the main building is very easy to cause page split operation.
The problem here is basically a way to solve the problem:
Add a master build to xx_db.xxtable. Here is a xx_db.xxtable for adding a business-free ID int self-building. This can immediately see mysqld occupy CPU single core down to about 3%, at the same time the subsequent synchronization of all normal, view the day did not appear synchronization delay problem.