Failure case: Slave delay is very large

Case 1:LVM model, after the creation of the library, the main library of QPS 2w, from the library 6k more. The delay starts at a certain point in time, and it goes up to 7w for more than a few seconds to find a deal; util from library io is high

Failure reason: View configuration found this from the library opened Log_slave_updates, has been producing binlog, when this parameter is disabled or set sync_binlog=0, util immediately down, the synchronization delay will slowly become smaller until 0, The UDB that has previously been found to use LVM logical volume management or SSD machines as long as the sync_binlog=1 is turned on, as if the disk util are very high, and did not delve into why.

Case 2:slave Latency has been increasing, the QPS from the library and the main library is very low, Io is low, from the library CPU 100%

Cause of failure: Because SQL is single-threaded from the library, it can only take advantage of a CPU's resources, when the CPU utilization to 100%, the entire library is stuck, no matter what the operation is very slow, and found that only a simple primary key update operation is performed from the library, so it is strange why the primary key update is 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 ' = ' 2003-03-18 00:00:00 ', ' tradingstate ' =0x31, ' Prevclosingpriceba ' =0x312e363238, ' Openingpriceba ' =0x312e36323633, ' Highestpriceba ' =0x312e36333332, ' Lowestpriceba ' =0x312e36303734, ' Closingpriceba ' =0x312e363136, ' ENTRYTIME ' = ' 2015-07-24 15:41:13 ', ' updatetime ' = ' 2015-07-24 15:41:13 ', ' groundtime ' = ' 2015-07-24 15:41:13 ', ' updateid ' = 0x3631323538393739303330, ' RESOURCEID ' =0x43616c63, ' RECORDID ' =null WHERE ' ID ' =0x3631323538393739303330

It was later discovered that the ID here is a string represented by the 0x3631323538393739303330 16 mechanism

Query plan for normal 10 binary int values

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 | PRIMARY | 8 |    Const |       1 | |
+----+-------------+------------------+-------+---------------+---------+---------+-------+------+-------+
1 row in Set (2.84 sec)

Note that this turns around is actually a character, and the problem is out here.
Mysql> Select Hex (' 60737669922 ');
+------------------------+
| Hex (' 60737669922 ') |
+------------------------+
| 3630373337363639393232 |
+------------------------+
1 row in Set (0.00 sec)

Mysql> Select 0x3630373337363639393232;
+--------------------------+
| 0x3630373337363639393232 |
+--------------------------+
| 60737669922 |
+--------------------------+
1 row in Set (0.00 sec)

The execution plan is resolved here and the record cannot be 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 | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- ------------------------+
1 row in Set (3.10 sec)

Actually, the records are there.
Mysql> SELECT * from Gangao. Stk_dailyquotefa where id = 60737669922;
+-------------+----------+---------------------+--------------+--------------------+----------------+---------- ------+---------------+----------------+---------------------+---------------------+---------------------+----- --------+------------+----------+
| ID | Secucode | Tradingday | Tradingstate | Prevclosingpriceba | Openingpriceba | Highestpriceba | Lowestpriceba | Closingpriceba | Entrytime | UpdateTime | Groundtime | UpdateID | RESOURCEID | RECORDID |
+-------------+----------+---------------------+--------------+--------------------+----------------+---------- ------+---------------+----------------+---------------------+---------------------+---------------------+----- --------+------------+----------+
|    60737669922 | 20041 | 2009-06-05 00:00:00 |            1 |        33.9315 |        33.8687 |       35.8897 |        33.7851 | 35.2277 | 2015-07-13 15:40:01 | 2015-07-13 15:40:01 | 2015-07-13 15:40:01 | 60737669922 | Calc | NULL |
+-------------+----------+---------------------+--------------+--------------------+----------------+---------- ------+---------------+----------------+---------------------+---------------------+---------------------+----- --------+------------+----------+
1 row in Set (1.94 sec)
But the characters that turn into these 16-binary representations don't come out of the way.

Mysql> SELECT * from Gangao. Stk_dailyquotefa where id = 0x3630373337363639393232;
Empty Set (0.40 sec)

And the true 16 binary value of this value should be

Mysql> Select Hex (60737669922);
+------------------+
| Hex (60737669922) |
+------------------+
| E243f4b22 |
+------------------+
1 row in Set (0.00 sec)

We need to add 0 here.

Mysql> Select 0xe243f4b22+0;
+---------------+
| 0xe243f4b22+0 |
+---------------+
| 60737669922 |
+---------------+
1 row in Set (0.00 sec)

and then the analysis

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 | PRIMARY | 8 |    Const |       1 | |
+----+-------------+------------------+-------+---------------+---------+---------+-------+------+-------+
1 row in Set (2.83 sec)

Mysql> SELECT * from Gangao. Stk_dailyquotefa where id = 0xe243f4b22+0;
+-------------+----------+---------------------+--------------+--------------------+----------------+---------- ------+---------------+----------------+---------------------+---------------------+---------------------+----- --------+------------+----------+
| ID | Secucode | Tradingday | Tradingstate | Prevclosingpriceba | Openingpriceba | Highestpriceba | Lowestpriceba | Closingpriceba | Entrytime | UpdateTime | Groundtime | UpdateID | RESOURCEID | RECORDID |
+-------------+----------+---------------------+--------------+--------------------+----------------+---------- ------+---------------+----------------+---------------------+---------------------+---------------------+----- --------+------------+----------+
|    60737669922 | 20041 | 2009-06-05 00:00:00 |            1 |        33.9315 |        33.8687 |       35.8897 |        33.7851 | 35.2277 | 2015-07-13 15:40:01 | 2015-07-13 15:40:01 | 2015-07-13 15:40:01 | 60737669922 | Calc | NULL |
+-------------+----------+---------------------+--------------+--------------------+----------------+---------- ------+---------------+----------------+---------------------+---------------------+---------------------+----- --------+------------+----------+
1 row in Set (4.25 sec)

So the problem is that the client has processed the 16-in-the-binary form of the 10-in-integer 123456 to the string ' 123456 ', causing the index to be useless, the query is slow, and even has been doing no work, there is no such value at all.

Case 3: There are only write operations on the main library, low IO for the main library, high from the library Io, and no spikes in the main library.

Cause analysis: Later compared to master-slave configuration, found that the main library Innodb_flush_log_at_trx_commit set to 2, and from the library innodb_flush_log_at_trx_commit is 1, and later will be adjusted from the library to 2 or 0 is no problem, The specific meaning of this parameter is not much to say, is not adjusted before the master-slave io comparison

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Failure case: Slave delay is very large