A case _mysql in Mysql from library latency Troubleshooting

Source: Internet
Author: User
Tags git clone

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.