Fault case: slave latency is high, and slave latency is

Fault case: slave latency is high, and slave latency is

Case 1: lvm model. After the slave database is created, the qps of the master database is 2 w and the slave database is more than 6 K. The latency increases slowly from a certain point in time until more than seconds. The util of the slave database io is very high.

Cause of failure: Check the configuration and find that log_slave_updates has been enabled for this slave database, and binlog has been generated. When this parameter is disabled or sync_binlog is set to 0, util immediately drops down, the synchronization latency gradually decreases until it is 0. Previously, we found that if the sync_binlog = 1 is enabled for the machine that uses lvm logical volume management or SSD for multiple times, it seems that the disk util is very high, and I have no idea why.

Case 2: slave latency keeps increasing. The qps of the slave database and master database is very low, io is very low, and the slave database cpu is 100%

Cause of failure: Because the execution of SQL statements from the slave database is single-threaded, only one CPU resource can be used. When the cpu usage reaches 100%, the whole database gets stuck, no matter what the operation is slow; only one simple primary key update operation is performed on the database, so it is strange why primary key update is still so slow.

ID: 6933
USER: system user
HOST:
DB: gangao
COMMAND: Connect
TIME: 457156
STATE: Updating
INFO: UPDATE 'sTK _ DAILYQUOTEFA 'SET 'id' = 0x3631323538393739303330, 'secucode' = 0x3237353233, 'tradingday' = '2017-03-18 00:00:00 ', 'tradingstate' = 0x31, 'prevclosingpriceba '= 0x312E363238, 'openingpriceba' = offline, 'hihestpriceba '= offline, 'lowestpriceba' = offline, 'closingpriceba '= offline, 'entrytime' = '2017-07-24 15:41:13 ', 'updatetime' = '2017-07-24 15:41:13', 'backgroundtime' = '2017-07-24 15:41:13 ', 'updateid' = 0x3631323538393739303330, 'resourceid' = 0x43616C63, 'recordid' = null where 'id' = 0x3631323538393739303330

Later we found that the ID here is a string represented by the 0x3631323538393739303330 sixteen mechanism.

Query plan for normal 10-digit int Value

Mysql> explain select * from gangao. STK_DAILYQUOTEFA where id = 60737669922;
+ ---- + ------------- + ------------------ + ------- + --------------- + --------- + ------- + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------------------ + ------- + --------------- + --------- + ------- + ------ + ------- +
| 1 | SIMPLE | STK_DAILYQUOTEFA | const | PRIMARY | 8 | const | 1 |
+ ---- + ------------- + ------------------ + ------- + --------------- + --------- + ------- + ------ + ------- +
1 row in set (2.84 sec)

Note that the conversion here is actually a character, and the problem is that
Mysql> select hex ('20140901 ');
+ ------------------------ +
| Hex ('000000') |
+ ------------------------ +
| 1, 3630373337363639393232 |
+ ------------------------ +
1 row in set (0.00 sec)

Mysql> select 0x3630373337363639393232;
+ -------------------------- +
| 0x3630373337363639393232 |
+ -------------------------- +
| 1, 60737669922 |
+ -------------------------- +
1 row in set (0.00 sec)

Parse the execution plan and find that the record is not found.
Mysql> explain select * from gangao. STK_DAILYQUOTEFA where id = 0x3630373337363639393232;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + --------------------------------------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + --------------------------------------------------------- +
| 1 | SIMPLE | NULL | Impossible WHERE noticed after reading const tables |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + --------------------------------------------------------- +
1 row in set (3.10 sec)

Actually, the record exists.
Mysql> select * from gangao. STK_DAILYQUOTEFA where id = 60737669922;
+ ------------- + ---------- + Hour + -------------- + hour + ---------------- + --------------- + -------------- + hour + --------------- + ------------ + ----------
| ID | SECUCODE | TRADINGDAY | TRADINGSTATE | required | OPENINGPRICEBA | HIGHESTPRICEBA | LOWESTPRICEBA | CLOSINGPRICEBA | ENTRYTIME | UPDATETIME | GROUNDTIME | UPDATEID | RESOURCEID | RECORDID |
+ ------------- + ---------- + Hour + -------------- + hour + ---------------- + --------------- + -------------- + hour + --------------- + ------------ + ----------
| 60737669922 | 20041 | 00:00:00 | 1 | 33.9315 | 33.8687 | 35.8897 | 33.7851 | 35.2277 | 15:40:01 | 15:40:01 | 15:40:01 | 60737669922 | Calc | NULL |
+ ------------- + ---------- + Hour + -------------- + hour + ---------------- + --------------- + -------------- + hour + --------------- + ------------ + ----------
1 row in set (1.94 sec)
However, if it is converted to the hexadecimal character format, it will be returned.

Mysql> select * from gangao. STK_DAILYQUOTEFA where id = 0x3630373337363639393232;
Empty set (0.40 sec)

The true hexadecimal value of this value should be

Mysql & gt; select hex (60737669922 );
+ ------------------ +
| Hex (1, 60737669922) |
+ ------------------ +
| E243F4B22 |
+ ------------------ +
1 row in set (0.00 sec)

Add 0 here.

Mysql> select 0xE243F4B22 + 0;
+ --------------- +
| 0xE243F4B22 + 0 |
+ --------------- +
| 1, 60737669922 |
+ --------------- +
1 row in set (0.00 sec)

Then parse

Mysql> explain select * from gangao. STK_DAILYQUOTEFA where id = 0xE243F4B22 + 0;
+ ---- + ------------- + ------------------ + ------- + --------------- + --------- + ------- + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------------------ + ------- + --------------- + --------- + ------- + ------ + ------- +
| 1 | SIMPLE | STK_DAILYQUOTEFA | const | PRIMARY | 8 | const | 1 |
+ ---- + ------------- + ------------------ + ------- + --------------- + --------- + ------- + ------ + ------- +
1 row in set (2.83 sec)

Mysql> select * from gangao. STK_DAILYQUOTEFA where id = 0xE243F4B22 + 0;
+ ------------- + ---------- + Hour + -------------- + hour + ---------------- + --------------- + -------------- + hour + --------------- + ------------ + ----------
| ID | SECUCODE | TRADINGDAY | TRADINGSTATE | required | OPENINGPRICEBA | HIGHESTPRICEBA | LOWESTPRICEBA | CLOSINGPRICEBA | ENTRYTIME | UPDATETIME | GROUNDTIME | UPDATEID | RESOURCEID | RECORDID |
+ ------------- + ---------- + Hour + -------------- + hour + ---------------- + --------------- + -------------- + hour + --------------- + ------------ + ----------
| 60737669922 | 20041 | 00:00:00 | 1 | 33.9315 | 33.8687 | 35.8897 | 33.7851 | 35.2277 | 15:40:01 | 15:40:01 | 15:40:01 | 60737669922 | Calc | NULL |
+ ------------- + ---------- + Hour + -------------- + hour + ---------------- + --------------- + -------------- + hour + --------------- + ------------ + ----------
1 row in set (4.25 sec)

Therefore, the problem is that the customer converts the 10-digit integer 123456 into the hexadecimal format of the string '123', resulting in useless indexing, slow query, and even no effort, these values do not exist at all.

Case 3: only write operations are performed on the master database. The io of the master database is relatively low, the io of the slave database is very high, and the master database does not have a peak value.

Cause Analysis: Later, we compared the master-slave configuration and found that innodb_flush_log_at_trx_commit of the master database was set to 2, while innodb_flush_log_at_trx_commit of the slave database was 1, and then we changed the slave database to 2 or, i/O comparison between the master and slave databases before adjustment.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.