Shulou(Shulou.com)06/01 Report--

Recently, there are frequent slave delays online. After investigation, it is found that when users delete data, due to the lack of table primary key, the deletion condition has no index, or the deletion condition has no index, or the deletion condition filter is very poor, which leads to slave hang residence, which seriously affects the stability of the production environment. I also hope that through this blog, we can deepen the importance of the primary key in the innodb engine. I hope that users are using RDS. When designing your own table, be sure to add a primary key to the table. The primary key can be regarded as the life of the innodb storage engine. Let's analyze this case (the binlog in the production environment of this case is row schema, and there is the same problem with 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:

The Seconds_Behind_Master of slave is increasing all the time, and hang is living in slave.

(2)。 Parse the binlog of 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-93b2Mub 94dc9e1dd54' / * VARSTRING (108) meta=108 nullable=1 is_null=0 * /

# @ 2o133 / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 3 collect 20121012220000 / * VARSTRING (42) meta=42 nullable=1 is_null=0 * /

# @ 4 hours / * VARSTRING (24) meta=24 nullable=1 is_null=0 * /

(3) Analysis:

Simulate the scene:

1. There is no primary key in the table, and the whole table is updated:

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

Mysqlbinlog-vvv / home/mysql/data3006/mysql/mysql-bin.000007 > / tmp/test.log

The first update transaction log in binlog: 2281772 # UPDATE qianyi.dmpush_message_temp

2281773 # WHERE

2281774 # # @ 1='fb5c72c9-0ac2-4800-93b2murb94dc9e1dd54' / * VARSTRING (108) meta=108 nullable=1 is_null=0 * /

2281775 # # @ 2o133 / * LONGINT meta=0 nullable=1 is_null=0 * /

2281776 # # @ 3 meta=42 nullable=1 is_null=0 / / 20121012220000' / * VARSTRING (42)

2281777 # # @ 4 hours / * VARSTRING (24) meta=24 nullable=1 is_null=0 * /

2281778 # SET

2281779 # # @ 1='fb5c72c9-0ac2-4800-93b2murb94dc9e1dd54' / * VARSTRING (108) meta=108 nullable=1 is_null=0 * /

2281780 # # @ 2o133 / * LONGINT meta=0 nullable=1 is_null=0 * /

2281781 # # @ 3 meta=42 nullable=1 is_null=0 / / 20121012220000' / * VARSTRING (42)

2281782 # # @ 4 VARSTRING (24) meta=24 nullable=1 is_null=0 * /

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 # # @ 2o133 / * LONGINT meta=0 nullable=1 is_null=0 * /

5313205 # # @ 3 meta=42 nullable=1 is_null=0 / / 20121012220000' / * VARSTRING (42)

5313206 # # @ 4 hours / * 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 # # @ 2o133 / * LONGINT meta=0 nullable=1 is_null=0 * /

5313210 # # @ 3 meta=42 nullable=1 is_null=0 / / 20121012220000' / * VARSTRING (42)

5313211 # # @ 4 VARSTRING (24) meta=24 nullable=1 is_null=0 * /

Note that because there is no primary key in the table, each transaction entry is updated by a full table scan. If there is a lot of data in the table, when the standby database executes the updated transaction entry, there will be a lot of full table scan updates.

Calculate how many transaction entries there are:

Root@xxxxxxxxx # cat / tmp/test.log | grep 'UPDATE qianyi.dmpush_message_temp'-A 10 | wc-l

2521492

Mysql > select 2521492 the number of rows occupied by 11 Murray 11 for a update transaction entry

+-+

| | 2521492Univer 11 |

+-+

| | 229226.5455 |

+-+

Mysql > use qianyi

Database changed

Mysql > select count (*) from dmpush_message_temp

+-- +

| | count (*) |

+-- +

| | 226651 |

+-- +

You can see that the number of entries in binlog is consistent with the data volume of the table, that is, there are as many transaction entries as the rows are updated when the whole table is updated (in row mode).

4. Optimize

Add a primary key to 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 the 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-93b2Mub 94dc9e1dd54' / * VARSTRING (108) meta=108 nullable=1 is_null=0 * /

# @ 2o133 / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 3 collect 20121012220000 / * VARSTRING (42) meta=42 nullable=1 is_null=0 * /

# @ 4 hours / * VARSTRING (24) meta=24 nullable=1 is_null=0 * /

# @ 5room1 / * INT meta=0 nullable=0 is_null=0 * /

# UPDATE qianyi.dmpush_message_temp

# WHERE

# @ 1 VARSTRING fb5bdc4fmurda8aMu4f03Mua5e 27677d7c8ac3' / * VARSTRING (108c8ac3) /

# @ 2o133 / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 3 collect 20121012220000 / * VARSTRING (42) meta=42 nullable=1 is_null=0 * /

# @ 4 hours / * VARSTRING (24) meta=24 nullable=1 is_null=0 * /

# @ 5room2 / * INT meta=0 nullable=0 is_null=0 * /

You can see that because there is already a primary key in the transaction entry here, that is, @ 5 (the @ 5 of the first transaction entry update and the second transaction entry update is incremental, that is, the primary key), so that the transaction log will be updated according to the primary key, and the standby database execution will not get stuck.

Resolve:

The cause of the problem has been found. Because there is no primary key in the table, in ROW mode, a full table scan will be done for each item of data deleted, that is to say, a delete. If 10 items are deleted, a full table scan will be done 10 times. So slave keeps getting 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-

Aborted

two。 At this time, it is found that the slave has been stuck and no operation can be carried out. At this time, the only way is to forcibly kill the mysql process.

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

Since our slave replication starts automatically when mysqld starts, we need to turn it off here:

Add: skip-slave-start to vi / etc/my3006.cnf, start with mysqld_safe

3. Since the binlog of the main database has been transferred to the standby database, at this time, the transaction log of slave execution will be hang if there is no primary key update. At this time, an ingenious method can be taken to avoid it, that is, the table in the standby database will be emptied. When slave executes realy log, it will report a 1032 error. We write a script to skip these errors. When the standby database catches up with the main database, we pass the table of the main database through mysqldump. Or restore the insert select to the standby library, so that the slave can run normally, and then notify the customer to modify the primary key.

A . Execute the following command on slave:

Slave: clear the problematic tables on the standby database

Set sql_log_bin=off

Truncate table qianyi.dmpush_message_temp

Start slave

Skip errors 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 be modified by adding the primary key:

Mysql > alter table dmpush_message_temp add column id int not null auto_increment,add PRIMARY Key (id)

3. When slave is stuck, you can parse binlog to see where slave is stuck and which transaction it is. Here is a simple way to look at the table currently 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 | |

+-+-- +

You can see that dmpush_message_temp has been open all the time, so you can directly locate the root cause of the problem.

Summary: the primary key is very important for innodb. When designing each table, the primary key should be added by default, no matter whether you need it or not, and the design of the primary key is best to choose a self-increasing primary key. Here you can also briefly mention the benefits of self-increasing primary key:

a. Self-increasing primary key to improve insertion performance

b. Self-increasing primary key design (int,bigint) can reduce the space of secondary index and improve the memory hit ratio of secondary index.

c. Self-increasing primary keys can reduce page fragmentation and improve space and memory usage.

Very similar to this article: http://wubx.net/slave-delay/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.