mysql主键的缺少导致备库hang

       最近线上频繁的出现slave延时的情况,经排查发现为用户在删除数据的时候,由于表主键的主键的缺少,同时删除条件没有索引,或或者删除的条件过滤性极差,导致slave出现hang住,严重的影响了生产环境的稳定性,也希望通过这篇博客,来加深主键在innodb引擎中的重要性,希望用户在使用RDS,设计自己的表的时候,一定要为表加上主键,主键可以认为是innodb存储引擎的生命,下面我们就来分析一下这个案例(本案例的生产环境的binlog为row模式,对于myisam存储引擎也有同样的问题):

   (1).现象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的Seconds_Behind_Master一直在增加,slave出现hang住。

   (2).解析当前slave执行到的位置的binlog:

   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(42) meta=42 nullable=1 is_null=0 */

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

   (3)分析:

   模拟场景:

   1.表中无主键,全表进行更新:

   master:

   表结构:

   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.binlog中第一个出现的update事务日志:

   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(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=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */

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

   b.binlog中最后出现的update事务日志:

   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(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=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */

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

   注意这里由于表中没有主键,所以导致了每一个事务条目的更新都是全表扫描,如果表中很很多的数据,则备库执行该更新的事务条目的时候,就会出现很多的全表扫描更新;

   c.计算有多少事务条目:

   root@xxxxxxxxx # cat /tmp/test.log|grep ‘UPDATE qianyi.dmpush_message_temp’  -A 10 |wc -l

   2521492

   mysql> select 2521492/11;—-11为一个update事务条目占用的行数

   +————-+

   | 2521492/11  |

   +————-+

   | 229226.5455 |

   +————-+

   mysql> use qianyi

   Database changed

   mysql> select count(*) from dmpush_message_temp;

   +———-+

   | count(*) |

   +———-+

   |   226651 |

   +———-+

   可以看到,binlog的条目数和该表的数据量查不多是一致,也就是在全表更新的时候(在row模式下)更新多少行,就有多少事务的事务条目;

   2.为dmpush_message_temp表添加主键:

   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

   解析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=’20121012220000′ /* 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=’20121012220000′ /* 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 */

   可以看到这里的事务条目中由于已经有了主键,也就是@5(第一个事务条目更新和第二个事务条目更新的@5是递增的,即主键),这样事务日志就会根据主键来更新,备库执行则不会卡住;

   解决:

   问题的原因已经找到了,由于表中没有主键,ROW模式下,每删一条数据都会做全表扫,也就是说一条delete,如果删了10条,会做10次全表扫。。。。所以slave一直卡住;

   1.slave:停止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.这个时候,发现slave已经卡住,无法进行任何操作,这个时候只有强行kill掉mysql进程

   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

   由于我们的slave复制是在mysqld启动的时候自动启动,所以这里我们需要将其关闭:

   vi /etc/my3006.cnf中加入:skip-slave-start,在用mysqld_safe启动;

   2.由于主库的binlog已经传入备库,这个时候,slave执行没有主键更新的事务日志就会hang住,这个时候可以采取一种巧妙的方法来避过,就是将备库中的这张表进行数据清空,slave在执行realy log的时候,就会报1032错误,我们写一个脚本进行skip掉这些错误,当备库追赶上主库后,我们在把主库的表通过mysqldump,或者insert select 还原到备库,这样就可以让slave正常的运行起来,然后在通知客户进行主键的改造;

   a。slave上执行以下命令:

   slave:清空备库上有问题的表

   set  sql_log_bin=off;

   truncate table qianyi.dmpush_message_temp;

   start slave;

   跳过该表上的错误:

   sh /tmp/skip.sh 3006 dmpush_message_temp

   b.等备库追上主库后,执行以下命令:

   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.最后让应用改造,添加上主键:

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

   3.当slave卡住的时候,可以通过解析binlog来看看,slave到底卡住在那里,是那个事务,下面是一个简单的方法,来看当前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 |

   +———-+———————+——-+————-+

   可以看到这里dmpush_message_temp一直处于打开状态,这里就可以直接定位问题的根源了;

   总结:主键对于innodb来说,是非常重要的,每张表的设计的时候,都应该把主键默认的加上,不管你需不需要他,而且主键的设计最好选择自增型的主键,这里也可以略提一下自增主键的好处:

   a.自增型主键以利于插入性能的提高;

   b.自增型主键设计(int,bigint)可以降低二级索引的空间,提升二级索引的内存命中率;

   c.自增型的主键可以减小page的碎片,提升空间和内存的使用。

觉得文章有用?立即: 和朋友一起 共学习 共进步!

猜您喜欢

2 thoughts on “mysql主键的缺少导致备库hang

  1. Pingback: MySQL大数据量主库如何部署从库 | 极客521

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

*

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>