网站建设资讯

NEWS

网站建设资讯

【Mysql】mysql主键的缺少导致备库hang

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

  1. (1).现象slave:
  1. MySQL> show slave status\G;
  2. *************************** 1. row ***************************
  3. Slave_IO_State: Waiting for master to send event
  4. Master_Host: xxx.xx.xx.xx
  5. Master_User: replicator
  6. Master_Port: 3006
  7. Connect_Retry: 60
  8. Master_Log_File: mysql-bin.000006
  9. Read_Master_Log_Pos: 47465657
  10. Relay_Log_File: slave-relay.100383
  11. Relay_Log_Pos: 251
  12. Relay_Master_Log_File: mysql-bin.000006
  13. Slave_IO_Running: Yes
  14. Slave_SQL_Running: Yes
  15. Replicate_Do_DB:
  16. Replicate_Ignore_DB:
  17. Replicate_Do_Table:
  18. Replicate_Ignore_Table:
  19. Replicate_Wild_Do_Table:
  20. Replicate_Wild_Ignore_Table:
  21. Last_Errno: 0
  22. Last_Error:
  23. Skip_Counter: 0
  24. Exec_Master_Log_Pos: 18057461
  25. Relay_Log_Space: 29409335
  26. Until_Condition: None
  27. Until_Log_File:
  28. Until_Log_Pos: 0
  29. Master_SSL_Allowed: No
  30. Master_SSL_CA_File:
  31. Master_SSL_CA_Path:
  32. Master_SSL_Cert:
  33. Master_SSL_Cipher:
  34. Master_SSL_Key:
  35. Seconds_Behind_Master: 1339
  36. Master_SSL_Verify_Server_Cert: No
  37. Last_IO_Errno: 0
  38. Last_IO_Error:
  39. Last_SQL_Errno: 0
  40. Last_SQL_Error:
  41. slave的Seconds_Behind_Master一直在增加,slave出现hang住。

(2).解析当前slave执行到的位置的binlog:
  1. mysqlbinlog -vvv /home/mysql/data3006/mysql/mysql-bin.000006 –start-position=18057461 >/tmp/2.log
  2. ### UPDATE qianyi.dmpush_message_temp
  3. ### WHERE
  4. ### @1=’fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′ /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
  5. ### @2=133 /* LONGINT meta=0 nullable=1 is_null=0 */
  6. ### @3=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */
  7. ### @4=’0′ /* VARSTRING(24) meta=24 nullable=1 is_null=0 */

(3)分析:
  1. 模拟场景:
    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;

    十余年的杜集网站建设经验,针对设计、前端、开发、售后、文案、推广等六对一服务,响应快,48小时及时工作处理。成都全网营销推广的优势是能够根据用户设备显示端的尺寸不同,自动调整杜集建站的显示方式,使网站能够适用不同显示终端,在浏览器中调整网站的宽度,无论在任何一种浏览器上浏览网站,都能展现优雅布局与设计,从而大程度地提升浏览体验。创新互联建站从事“杜集网站设计”,“杜集网站推广”以来,每个客户项目都认真落实执行。

    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

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

    3. 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 */
      注意这里由于表中没有主键,所以导致了每一个事务条目的更新都是全表扫描,如果表中很很多的数据,则备库执行该更新的事务条目的时候,就会出现很多的全表扫描更新;
    计算有多少事务条目:
    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模式下)更新多少行,就有多少事务的事务条目;



4.优化
  1. 为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是递增的,即主键),这样事务日志就会根据主键来更新,备库执行则不会卡住;




解决:
  1. 问题的原因已经找到了,由于表中没有主键,ROW模式下,每删一条数据都会做全表扫,也就是说一条delete,如果删了10条,会做10次全表扫。。。。所以slave一直卡住;
  2. 1.slave:停止slave;
  3. mysql> stop slave;
  4. Ctrl-C — sending “KILL QUERY 59028” to server …
  5. Ctrl-C — query aborted.
  6. Ctrl-C — sending “KILL 59028” to server …
  7. Ctrl-C — query aborted.
  8. Ctrl-C —
  9. Aborted
  10. 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启动;


  11. 3.由于主库的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的碎片,提升空间和内存的使用。



与这篇文章很像:http://wubx.net/slave-delay/
分享题目:【Mysql】mysql主键的缺少导致备库hang
标题URL:http://cdweb.net/article/ppcspe.html