Missing MySQL primary key causes standby to hang

The recent online frequent occurrence of slave delay, after the detection of the user in the deletion of data, due to the table primary key of the missing primary key, while deleting the condition is not indexed, or delete the condition of the filter is very poor, resulting in slave hang, seriously affect the stability of the production environment, Also hope that through this blog, to deepen the importance of the primary key in the InnoDB engine, I hope that users in the use of RDS, design their own tables, it is important to add a primary key to the table, the primary key can be considered InnoDB storage engine life, Let's analyze this case (the production environment of this case is binlog in row mode, and the same problem applies to the MyISAM storage engine):
(1). Phenomenon Slave:
Mysql> show Slave status\g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
Master_Host:xxx.xx.xx.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:
Slave's seconds_behind_master has been increasing, slave appears hang.
(2). Resolve the binlog of the position to which 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= ' 20121012220000′/* varstring meta=42 nullable=1 is_null=0 * *
# # # @4= ' 0′/* varstring meta=24 nullable=1 is_null=0 * *
(3) Analysis:
Simulation Scenario:
1. There is no primary key in the table, the whole table is updated:
Master
Table structure:
CREATE TABLE ' Dmpush_message_temp ' (
' ClientID ' varchar DEFAULT NULL,
' Infoid ' bigint (Ten) DEFAULT NULL,
' endtime ' varchar 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.binlog The first update that appears Transaction log:
mysqlbinlog-vvv/home/mysql/data3006/mysql/mysql-bin.000007 >/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= ' 20121012220000′/* varstring ($) meta=42 nullable=1 is_null=0 */
2281777 # # # @4= ' 0′/* varstring () meta=24 nullable=1 is_null=0 */
2281778 # # # SET
2281779 # # @1= ' fb5c72c9-0ac 2-4800-93b2-b94dc9e1dd54′/* varstring (108) meta=108 nullable=1 is_null=0 */
2281780 # # @2=133 * * LONGINT meta=0 nulla ble=1 is_null=0 */
2281781 # # @3= ' 20121012220000′/* varstring ($) meta=42 nullable=1 is_null=0 */
2281782 # # # @4= ' 1′/* varstring () meta=24 nullable=1 is_null=0 */

Last update transaction log in B.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= ' 20121012220000′/* varstring meta=42 nullable=1 is_null=0 * *
5313206 # # # @4= ' 0′/* varstring () 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= ' 20121012220000′/* varstring meta=42 nullable=1 is_null=0 * *
5313211 # # # @4= ' 1′/* varstring () meta=24 nullable=1 is_null=0 * *
Note that there is no primary key in the table, so the update of each transaction entry is a full-table scan, and if there is a lot of data in the table, there will be many full-table scan updates when the repository executes the updated transaction entry.

C. Calculate how many transaction entries you have:
[Email protected] # cat/tmp/test.log|grep ' UPDATE qianyi.dmpush_message_temp '-A ten |wc-l
2521492

Mysql> Select 2521492/11;--11 the number of rows occupied for an update transaction entry
+ ————-+
| 2521492/11 |
+ ————-+
| 229226.5455 |
+ ————-+

Mysql> Use Qianyi
Database changed
Mysql> Select COUNT (*) from dmpush_message_temp;
+ ———-+
| COUNT (*) |
+ ———-+
| 226651 |
+ ———-+
As you can see, the number of entries in the Binlog is consistent with the amount of data in the table, that is, the number of transactions that are updated at the time of the full table update (in row mode), and the transaction entries for the transaction;
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

To parse the transaction entry in Binlog:
[Email protected] # 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= ' 20121012220000′/ * varstring meta=42 nullable=1 is_null=0 */
# # # @4= ' 1′/* varstring (+) 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= ' 20121012220000′/* varstring ($) meta=42 nullable=1 is_null=0 */
# # # @4= ' 1′/* VARST RING meta=24 nullable=1 is_null=0 */
# # @5=2 */INT meta=0 nullable=0 is_null=0 */
You can see the transaction entry here because there is already a primary key, which is @5 ( The first transaction entry update and the second transaction entry update @5 are incremented, i.e. the primary key) so that the transaction log is updated according to the primary key, and the repository execution does not get stuck;

Solve:
The cause of the problem has been found, because there is no primary key in the table, row mode, every deletion of data will do a full table sweep, that is, a delete, if the deletion of 10, will do 10 full table sweep .... 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, found that slave is stuck, unable to do anything, this time only to forcibly kill the MySQL process
[Email protected] # 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

Since our slave replication is started automatically when the mysqld is started, we need to close it here:
Vi/etc/my3006.cnf added: Skip-slave-start, in the use of Mysqld_safe start;

2. As the main library binlog has been introduced to the repository, this time, slave do not have the primary key update of the transaction log will hang, this time can take a clever way to avoid, that is, the table in the standby library data is emptied, slave in the execution of realy log, will be reported 1032 error, we write a script to skip these errors, when the repository catch up with the main library, we are in the main library table through mysqldump, or insert Select to restore to the standby, so that the slave can run normally, And then notify the customer to change the primary key;
A. Execute the following command on the slave:
Slave: Empty the problematic table on the standby library
Set Sql_log_bin=off;
TRUNCATE TABLE qianyi.dmpush_message_temp;
Start slave;

To skip the error on this table:
sh/tmp/skip.sh 3006 Dmpush_message_temp

B. After the standby library catches up with the main library, execute 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 retrofit, 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 stuck, you can parse the binlog to see, slave exactly stuck there, is that transaction, here is a simple way to see the current Salve Open table:
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 |
+ ———-+ ——————— +--–+ ————-+
Can see here Dmpush_message_temp has been in open state, here can directly locate the root of the problem;

Summary: The primary key for InnoDB, is very important, each table design, should be the primary key by default, regardless of whether you need him, and the design of the primary key is best to choose the self-increment of the primary key, here can also briefly mention the advantages of self-enhanced primary key:
A. Self-increasing primary key to facilitate the improvement of insertion performance;
B. The self-enhanced primary key design (Int,bigint) can reduce the space of two-level indexes and increase the memory hit ratio of the two-level index;
C. Auto-increment primary key can reduce page fragmentation, increase space and memory usage

Missing MySQL primary key causes standby to hang