The lack of a mysql primary key causes the standby database to be hang and mysqlhang.

The lack of a mysql primary key causes the standby database to be hang and mysqlhang.

Slave latency occurs frequently online recently. It is found that when you delete data, the table's primary key is missing and the deletion condition is not indexed, or the deletion condition filters are very poor, resulting in the emergence of hang in slave, seriously affecting the stability of the production environment. I also hope to use this blog to deepen the importance of primary keys in the innodb engine, we hope that when you use RDS to design your own tables, you must add a primary key to the table. The primary key can be considered as the life of the innodb Storage engine, next we will analyze this case (the binlog mode of the production environment in this case is row, and the myisam storage engine has the same problem ):
(1). symptom slave:
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xxx. xx
Master_User: replicator
Master_Port: 3006
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 47465657
Relay_Log_File: slave-relay.100383
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 18057461
Relay_Log_Space: 29409335
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 1339
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
The Seconds_Behind_Master of slave has been increasing, and the hang of slave appears.
(2). parse the binlog at the location where the current slave is executed:
Mysqlbinlog-vvv/home/mysql/data3006/mysql/mysql-bin.000006-start-position = 18057461>/tmp/2.log
### UPDATE qianyi. dmpush_message_temp
### WHERE
###@ 1 = 'fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54'/* VARSTRING (108) meta = 108 nullable = 1 is_null = 0 */
###@ 2 = 133/* LONGINT meta = 0 nullable = 1 is_null = 0 */
###@ 3 = '000000'/* VARSTRING (42) meta = 42 nullable = 1 is_null = 0 */
###@ 4 = '0'/* VARSTRING (24) meta = 24 nullable = 1 is_null = 0 */
(3) Analysis:
Simulation Scenario:
1. If no primary key exists in the table, update the entire table:
Master:
Table Structure:
Create table 'dmpush _ message_temp '(
'Clientid' varchar (36) default null,
'Infoid' bigint (10) default null,
'Endtime' varchar (14) default null,
'STAT' varchar (8) DEFAULT NULL
) ENGINE = innodb default charset = utf8;

Mysql> update dmpush_message_temp set stat = 1;
Query OK, 226651 rows affected (1.69 sec)
Rows matched: 226651 Changed: 226651 Warnings: 0
A. the first update transaction log in binlog:
Mysqlbinlog-vvv/home/mysql/data3006/mysql/mysql-bin.000007 & gt;/tmp/test. log

2281772 ### UPDATE qianyi. dmpush_message_temp
2281773 ### WHERE
2281774 ###@ 1 = 'fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54'/* VARSTRING (108) meta = 108 nullable = 1 is_null = 0 */
2281775 ###@ 2 = 133/* LONGINT meta = 0 nullable = 1 is_null = 0 */
2281776 ###@ 3 = '000000'/* VARSTRING (42) meta = 42 nullable = 1 is_null = 0 */
2281777 ###@ 4 = '0'/* VARSTRING (24) meta = 24 nullable = 1 is_null = 0 */
2281778 ### SET
2281779 ###@ 1 = 'fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54'/* VARSTRING (108) meta = 108 nullable = 1 is_null = 0 */
2281780 ###@ 2 = 133/* LONGINT meta = 0 nullable = 1 is_null = 0 */
2281781 ###@ 3 = '000000'/* VARSTRING (42) meta = 42 nullable = 1 is_null = 0 */
2281782 ###@ 4 = '1'/* VARSTRING (24) meta = 24 nullable = 1 is_null = 0 */

B. The last update transaction log in binlog:
5313201 ### UPDATE qianyi. dmpush_message_temp
5313202 ### WHERE
5313203 ###@ 1 = 'fffff4fc-0b72-4ba2-9749-0189658af6d5 '/* VARSTRING (108) meta = 108 nullable = 1 is_null = 0 */
5313204 ###@ 2 = 133/* LONGINT meta = 0 nullable = 1 is_null = 0 */
5313205 ###@ 3 = '000000'/* VARSTRING (42) meta = 42 nullable = 1 is_null = 0 */
5313206 ###@ 4 = '0'/* VARSTRING (24) meta = 24 nullable = 1 is_null = 0 */
5313207 ### SET
5313208 ###@ 1 = 'fffff4fc-0b72-4ba2-9749-0189658af6d5 '/* VARSTRING (108) meta = 108 nullable = 1 is_null = 0 */
5313209 ###@ 2 = 133/* LONGINT meta = 0 nullable = 1 is_null = 0 */
5313210 ###@ 3 = '000000'/* VARSTRING (42) meta = 42 nullable = 1 is_null = 0 */
5313211 ###@ 4 = '1'/* VARSTRING (24) meta = 24 nullable = 1 is_null = 0 */
Note that because there is no primary key in the table, the update of each transaction entry is a full table scan. If there is a lot of data in the table, when the slave database executes the updated transaction entry, there will be a lot of full table scan updates;

C. Calculate the number of transaction entries:
Root @ xxxxxxxxx # cat/tmp/test. log | grep 'Update qianyi. dmpush_message_temp '-A 10 | wc-l
2521492

Mysql> select 2521492/11; -- 11 indicates the number of rows occupied by an update transaction entry.
+ ----- +
| 1, 2521492/11 |
+ ----- +
| 1, 229226.5455 |
+ ----- +

Mysql> use qianyi
Database changed
Mysql> select count (*) from dmpush_message_temp;
+ ---- +
| Count (*) |
+ ---- +
| 1, 226651 |
+ ---- +
We can see that the number of entries in the binlog is not much the same as that in the table, that is, the number of rows updated during full table Update (in row mode, the number of transaction entries;
2. Add a primary key for the dmpush_message_temp table:
Mysql> alter table dmpush_message_temp add column id int not null auto_increment, add PRIMARY Key (id );
Query OK, 226651 rows affected (1.09 sec)
Records: 226651 Duplicates: 0 Warnings: 0

Mysql> update dmpush_message_temp set stat = 0;
Query OK, 226651 rows affected (1.69 sec)
Rows matched: 226651 Changed: 226651 Warnings: 0

Parse transaction entries in binlog:
Root @ xxxxxxxxx # mysqlbinlog-vvv/home/mysql/data3006/mysql/mysql-bin.000008>/tmp/test1.log

### UPDATE qianyi. dmpush_message_temp
### WHERE
###@ 1 = 'fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54'/* VARSTRING (108) meta = 108 nullable = 1 is_null = 0 */
###@ 2 = 133/* LONGINT meta = 0 nullable = 1 is_null = 0 */
###@ 3 = '000000'/* VARSTRING (42) meta = 42 nullable = 1 is_null = 0 */
###@ 4 = '1'/* VARSTRING (24) meta = 24 nullable = 1 is_null = 0 */
###@ 5 = 1/* INT meta = 0 nullable = 0 is_null = 0 */
### UPDATE qianyi. dmpush_message_temp
### WHERE
###@ 1 = 'fb5bdc4f-da8a-4f03-aa5e-27677d7c8ac3 '/* VARSTRING (108) meta = 108 nullable = 1 is_null = 0 */
###@ 2 = 133/* LONGINT meta = 0 nullable = 1 is_null = 0 */
###@ 3 = '000000'/* VARSTRING (42) meta = 42 nullable = 1 is_null = 0 */
###@ 4 = '1'/* VARSTRING (24) meta = 24 nullable = 1 is_null = 0 */
###@ 5 = 2/* INT meta = 0 nullable = 0 is_null = 0 */
We can see that the transaction entry here already has a primary key, that is, @ 5 (the @ 5 of the first transaction entry Update and the second transaction entry update are incremental, that is, the primary key ), in this way, the transaction log will be updated based on the primary key, and the execution of the standby database will not be stuck;

Solution:
The cause of the problem has been found. Because the table does not have a primary key, in ROW mode, a full table scan is performed for each piece of data to be deleted, that is, a delete statement. If 10 rows are deleted, 10 full table scans .... So slave has been stuck;
1. slave: Stop slave;
Mysql> stop slave;
Ctrl-C-sending "kill query 59028" to server...
Ctrl-C-query aborted.
Ctrl-C-sending "KILL 59028" to server...
Ctrl-C-query aborted.
Ctrl-C-exit!
Aborted
2. At this time, it is found that the slave has been stuck and cannot perform any operations. At this time, only the mysql process is forcibly killed.
Root@xxxxxxxx.com # ps-ef | grep 3006
Root 4456 1 0 Oct11? 00:00:00/bin/sh/usr/bin/mysqld_safe-defaults-file =/etc/my3006.cnf
Mysql 6828 4456 0 Oct11? 00:39:27/usr/sbin/mysqld-defaults-file =/etc/my3006.cnf-basedir =/-datadir =/home/mysql/data3006/dbs3006-user = mysql-log-error = /home/mysql/data3006/mysql/master-error.log-open-files-limit = 8192-pid-file =/home/mysql/data3006/dbs3006/xxxxxxxx.com. pid-socket =/home/mysql/data3006/tmp/mysql. sock-port = 3006

Kill-9 4456 6828

Because our slave replication is automatically started when mysqld is started, we need to disable it here:
Add skip-slave-start to vi/etc/my3006.cnf and start it with mysqld_safe;

2. because the binlog of the master database has been passed into the slave database, the transaction log without primary key update will be hang during slave execution. In this case, a clever method can be used to avoid it, this is to clear the data in the table in the slave database. When slave executes the realy log, it will report a 1032 error. We will write a script to delete these errors by skip, after the slave database catches up with the master database, we are restoring the table of the master database to the slave database through mysqldump or insert select, so that slave can run properly, then notify the customer of primary key transformation;
A. Run the following command on slave:
Slave: Clear the problematic tables in the slave Database
Set SQL _log_bin = off;
Truncate table qianyi. dmpush_message_temp;
Start slave;

An error occurred while skipping the table:
Sh/tmp/skip. sh 3006 dmpush_message_temp

B. After the slave database catches up with the master database, run the following command:
Master:

Lock tables qianyi. dmpush_message_temp read;

Create table a2 like qianyi. dmpush_message_temp;

Lock tables a2 write, qianyi. dmpush_message_temp read;

Insert into a2 select * from qianyi. dmpush_message_temp;

Slave:

Set SQL _log_bin = off;

Drop table qianyi. dmpush_message_temp;

Create table qianyi. dmpush_message_temp like a2;

Insert into qianyi. dmpush_message_temp select * from a2;

C. Finally let the application transform and add the primary key:
Mysql> alter table dmpush_message_temp add column id int not null auto_increment, add PRIMARY Key (id );

3. When slave gets stuck, you can parse the binlog to check whether the slave is stuck in the transaction. Below is a simple method to check the table opened by salve:
Mysql> show open tables;
+ ---- + ------- + --- + ----- +
| Database | Table | In_use | Name_locked |
+ ---- + ------- + --- + ----- +
| Qianyi | dmpush_message_temp | 1 | 0 |
| Qianyi | test | 0 | 0 |
| Qianyi | anson | 0 | 0 |
| Mysql | db | 0 | 0 |
| Mysql | slow_log | 0 | 0 |
| Mysql | event | 0 | 0 |
+ ---- + ------- + --- + ----- +
We can see that dmpush_message_temp is always on. Here we can directly locate the root cause of the problem;

Summary: The primary key is very important for innodb. When designing each table, you should add the primary key by default, no matter whether you need it or not, in addition, it is best to select an auto-incrementing primary key for primary key design. Here you can also slightly mention the advantages of the auto-incrementing primary key:
A. the auto-incrementing primary key facilitates the improvement of insertion performance;
B. the auto-incrementing primary key design (int, bigint) can reduce the space of secondary indexes and improve the memory hit rate of secondary indexes;
C. The auto-incrementing primary key can reduce page fragments and improve space and memory usage.