MySQL Replication latency Troubleshooting

Received the alarm today, prompted from the library delay, of course, first of all to look at the situation, first look at the machine load, as follows:

You can see that using the CPU has 100%,io no waiting. So look at what MySQL is, execute show Processlist did not find any exceptions, perform show slave status View delay, found that the delay has been increasing, and stuck in a POS point is not moving, has been hang. This from the library does not run any business.

Go ahead and run show engine InnoDB status to see if there are any exceptions:

I rub, and really found the problem, how can I remind the Lock 23 table? Continue to troubleshoot, according to the stuck POS point, parsing binlog, found to have been operating a table, and is a very simple DELETE statement. View the table found to be a partitioned table. It just divided 23 partitions. Look closely at the table structure and find that there are only normal indexes, no unique indexes, and no primary keys. It also explains that the above shows a 23-sheet lock. After the reason is found, adding a unique index resolves the problem (adding a business-independent self-ID master key can also be resolved.) ) The load drops and the delay slowly decreases. Hey, history left the pit, slowly tread it.

Summarize:

For the table of InnoDB, it is recommended to use an auto-increment ID as the primary key in case of no special requirement.

Some key features of the InnoDB engine table:

1. InnoDB engine table is an index organization table (IOT) based on the B + tree;
2. Each table needs to have a clustered index (clustered index);
3. All row records are stored in the leaf nodes of the B + tree (leaf pages of the tree);
4. The efficiency of increment, delete, change and check is the highest based on the aggregation index;
5. If we define the primary key (PRIMARY key), then InnoDB will be the selector as a clustered index;
6. If you do not explicitly define a primary key, InnoDB selects the first unique index that does not include a null value as the primary key index;
7. If there is no such unique index, then InnoDB chooses the built-in 6-byte rowid as an implied clustered index (ROWID is incremented with the write of the row record and the ROWID is not as referenced as the ROWID of Oracle, which is implied).

In summary, if the data write order of the InnoDB table is consistent with the leaf node order of the B + Tree index, then the access efficiency is the highest, that is, the following conditions are the highest access efficiency:

1. Use the self-increment column (int/bigint type) The master key, when the write order is self-increment, and B + number leaf node splitting sequence consistent;
2. The table does not specify the self-increment key, and there is no unique index that can be selected as the primary key (the above conditions), when InnoDB will choose the built-in ROWID as the primary key, the write order and ROWID growth sequence consistent;
In addition, if a InnoDB table does not display a primary key, there is no unique index that can be selected as the primary key, but the unique index may not be an incremental relationship (for example, a string, UUID, multi-field federated unique index), and the table will have poor access efficiency.

Reference article:

Http://17173ops.com/2014/09/14/mysql-faq-why-innodb-table-using-autoinc-int-as-pk.shtml?utm_source=tuicool

http://wubx.net/slave-delay/

MySQL Replication latency Troubleshooting