在高并发下在线修改表结构时异常 [mysql]

以前我们遇到metadata lock的问题， 当时是因为业务逻辑导致了。 现在的场景是在线修改ddl语句，确切来说就是alter修改表结构 。 我们知道直接alter修改表结构，肯定会进行锁表，业务很瘦影响的。如果想避免锁表，可以采用pt-online-schema-change的方式。

关于在线修改表结构，原文链接  http://xiaorui.cc/?p=2144

介绍pt-online-schema-change:

那pt-online-schema-change又是什么原理，这里做些直接alter跟pt-online-schema-change的对比。 

一.直接执行 alter table XXX  ……
1.按照原始表（original_table）的表结构和DDL语句，新建一张不可见的临时表。
2.在原表上加write lock，此时对原表的所有U D I DDL 都是阻塞的。
3.执行insert into tmp_table select * from oldtable;
4.rename oldtable 和 tmp_table，再drop oldtable。
5.释放 write lock。
二.pt-online-schema-change

1.如果存在外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理。

2.创建一个和你要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)。

3.在新表执行alter table 语句，

4.在原表中创建触发器（3个）三个触发器分别对应insert,update,delete操作

5.从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表。

6.Rename 原表到old表中，在把临时表Rename为原表，默认最后将原表删除，将原表上所创建的触发器删除。

限制：

1.该工具所适用的表必须是单一列的主键或者单一唯一键。

2.有外键参照的表要修改则不成功，需要指定参数–alter-foreign-keys-method=auto|rebuild_constraints|drop_swap|none

3.要修改的表上不能有trigger，否则修改失败。 

确实被第三条给坑过 ！ 第三条的意思是说在原表上不能有触发器，但是有可能你的pt-online-schema-change被干掉，工具没有来得及删除触发器。 当你再去执行的时候，会提示你的原表已经存在trigger。

 

 

 

 

 

 

 

 

Python

 

1 2 3 4 5

Cannot connect to D=buzz_master,h=xxxx,p=...,u=root No slaves found.  See --recursion-method if host wx-buzz-kv02 has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. The table `buzz_master`.`article` has triggers.  This tool needs to create its own triggers, so the table cannot already have triggers.

 

怎么解决？  直接删掉触发器和未完成的临时表。 pt-online-schema-change 貌似不支持任务暂停继续，只能重新来了。  

 

 

 

 

 

 

 

Python

 

1 2 3 4 5

DROP TRIGGER IF EXISTS `buzz_master`.`pt_osc_buzz_master_article_del`; DROP TRIGGER IF EXISTS `buzz_master`.`pt_osc_buzz_master_article_upd`; DROP TRIGGER IF EXISTS `buzz_master`.`pt_osc_buzz_master_article_ins`; drop table _article_new;

 

 

另外还有就是pt-online默认是有系统过载保护的，我想大家虽然都想要过载保护，但更想要稳定，别好不容易alter了一般，结果自己退出了。  解决的方法，在mysql里把连接数加大，然后pt-online-schema-change的启动参数里加上  –critical-load Threads_running=500 ，意思是说，可以承受500个链接， pt-online-schema-change在500 *1.2的时候，才提示报错。 

 

 

 

 

 

 

 

 

 

Python

 

1 2 3 4 5 6

2015-10-13T10:53:25 Dropped triggers OK. 2015-10-13T10:53:25 Dropping new table... 2015-10-13T10:53:34 Dropped new table OK. `weixin_master`.`page` was not altered. 2015-10-13T10:53:25 Error copying rows from `weixin_master`.`page` to `weixin_master`.`_page_new`: Threads_running=108 exceeds its critical threshold 100

 

 

 

咱们看看触发器的样子，很明显看到他的REPLACE INTO导数据的语句。 

 

 

 

 

 

 

 

 

Python

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

mysql> SELECT * FROM information_schema.TRIGGERS\G; *************************** 1. row ***************************            TRIGGER_CATALOG: def             TRIGGER_SCHEMA: buzz_master               TRIGGER_NAME: pt_osc_buzz_master_article_ins         EVENT_MANIPULATION: INSERT       EVENT_OBJECT_CATALOG: def        EVENT_OBJECT_SCHEMA: buzz_master         EVENT_OBJECT_TABLE: article               ACTION_ORDER: 0           ACTION_CONDITION: NULL           ACTION_STATEMENT: REPLACE INTO `buzz_master`.`_article_new` (`id`, `category_id`, `channel_name`, `url`, `title`, `pub_date`, `author`, `comment_cou nt`, `view_count`, `brief`, `word_count`, `industry_id`, `created_on`, `url_md5`, `content_md5`, `page_type`, `status`) VALUES (NEW.`id`, NEW.`category_id`, N EW.`channel_name`, NEW.`url`, NEW.`title`, NEW.`pub_date`, NEW.`author`, NEW.`comment_count`, NEW.`view_count`, NEW.`brief`, NEW.`word_count`, NEW.`industry_i d`, NEW.`created_on`, NEW.`url_md5`, NEW.`content_md5`, NEW.`page_type`, NEW.`status`)         ACTION_ORIENTATION: ROW              ACTION_TIMING: AFTER ACTION_REFERENCE_OLD_TABLE: NULL ACTION_REFERENCE_NEW_TABLE: NULL   ACTION_REFERENCE_OLD_ROW: OLD   ACTION_REFERENCE_NEW_ROW: NEW                    CREATED: NULL                   SQL_MODE: NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION                    DEFINER: root@localhost       CHARACTER_SET_CLIENT: latin1       COLLATION_CONNECTION: latin1_swedish_ci         DATABASE_COLLATION: utf8_general_ci

 

 

理解了在线改表结构的原理，那么我们开始操作alter。 

 

 

 

 

 

 

 

 

Python

 

1 2	pt-online-schema-change --user=root --password=xxxx  --alter "ADD city varchar(80) null"  D=buzz_master,t=article --execute

 

 

alter修改表结构是个危险的操作，大家要经常看看应用状况，可以通过show processlist; 我们可以看到有没有锁，如果有锁，那么就kill掉有可能造成锁的连接。Metadata lock是个常见的问题，由于我们的场景是数据分析，所以可以直接kill。  

 

 

 

 

 

 

 

 

 

Python

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

mysql> show processlist; +-----------+--------------+-------------------+---------------+-------------+---------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+ | Id        | User         | Host              | db            | Command     | Time    | State                                                                 | Info                                                                                                 | +-----------+--------------+-------------------+---------------+-------------+---------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+ | 198177222 | repl         | 10.50.89.66:11104 | NULL          | Binlog Dump | 1613849 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL                                                                                                 | | 212041234 | root         | localhost         | NULL          | Sleep       |   15435 |                                                                       | NULL                                                                                                 | | 212049415 | monitor_user | 10.50.89.152:14489 | buzz_monitor  | Sleep       |   14769 |                                                                       | NULL                                                                                                 | | 212049417 | monitor_user | 10.50.89.152:14490 | buzz_monitor  | Sleep       |   14769 |                                                                       | NULL                                                                                                 | | 212121702 | buzz         | 10.50.89.121:50085 | buzz_master   | Sleep       |       0 |                                                                       | NULL                                                                                                 | | 212135643 | buzz         | 10.50.89.121:51779 | buzz_master   | Sleep       |     507 |                                                                       | NULL                                                                                                 | | 212169485 | buzz         | 10.50.89.121:47422 | buzz_master   | Sleep       |     391 |                                                                       | NULL                                                                                                 | | 212175777 | root         | localhost         | NULL          | Query       |       0 | init                                                                  | show processlist                                                                                     | | 212184983 | weixin_user  | 10.50.89.110:27042 | weixin_master | Sleep       |       0 |                                                                       | NULL                                                                                                 | | 212185001 | root         | localhost         | buzz_master   | Query       |      25 | Waiting for table metadata lock                                       | CREATE TRIGGER `pt_osc_buzz_master_article_del` AFTER DELETE ON `buzz_master`.`article` FOR EACH ROW | | 212185002 | root         | localhost         | buzz_master   | Sleep       |      88 |                                                                       | NULL                                                                                                 | | 212185018 | weixin_user  | 10.50.89.110:33022 | weixin_master | Sleep       |       0 |                                                                       | NULL                                                                                                 | | 212185032 | weixin_user  | 10.50.89.110:35121 | weixin_master | Sleep       |       0 |                                                                       | NULL                                                                                                 | | 212185040 | weixin_user  | 10.50.89.110:36290 | weixin_master | Sleep       |       0 |                                                                       | NULL                                                                                                 | | 212185041 | weixin_user  | 10.50.89.110:36412 | weixin_master | Sleep       |       0 |                                                                       | NULL                                                                                                 | .........   mysql> kill 212121702; Query OK, 0 rows affected (0.00 sec)

 

 

这时候，我们查看应用的日志，还是看到了不少的错误。 Deadlock found when trying to get lock; try restarting transaction 。   我的开发环境是python，在官方文档及wiki’上查找些有用的信息，问题确定在 mysqldb autocommit身上。 你需要做的是，conn.autocommit(True) 。  虽然错误日志看似比以前少了不少， 但还会有异常。 结果随着把跟mysql有互动的计算节点从60个降到10个后，异常现象明显好了不少。 但异常就是异常，不能因为偶尔的异常就忽视不管。 

我觉得在不影响业务的情况下，有这么几种解决方法。 

1.   直接给用户说，你系统正在调整 ！  现在支付宝，淘宝每个月也有那么一次调整上线，跟大姨妈似的！ 

2.   当然很多时候，我们不能把对外的接口都关闭，这样太不友好。  可以采用把数据临时堆放到kafka或者redis里面。等表结构修改完毕，再开启消费程序。 

3.   对于一些异常的问题，可以使用事务的方法，避免插入脏数据。 

我问过sina的dba，问他们在高并发下如何处理表结构修改的问题。 他们很是粗暴 ！ 看是否涉及到钱？  不涉及，那好上面的方法就够用了。 需要注意的是，pt-online的alter操作，会让dml语句变成串行。在线表结构修改时，尽量减少并发连接才是真理。 任务场景到了高并发，做一些大改动都时候不好把控的。 

 

 

 

 

 

 

 

 

Python

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Traceback (most recent call last):   File "/data/buzzExtractor/buzz/lib/arbiter.py", line 250, in spawn_worker     worker.run()   File "/data/buzzExtractor/buzz/lib/workers/sync.py", line 45, in run     self.handle_request()   File "/data/buzzExtractor/buzz/lib/extractor.py", line 137, in handle_request     first_page_url = extract_result.save()   File "/data/buzzExtractor/buzz/lib/extractor.py", line 328, in save     article_id = self.save_article() if need_save_article else None  # BBS first page, QA main post, normal detail page   File "/data/buzzExtractor/buzz/lib/extractor.py", line 380, in save_article     return article.save()   File "/data/buzzExtractor/buzz/lib/extractor.py", line 447, in save     self.comment_count, self.view_count, self.content_md5, article_id))   File "/usr/local/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 205, in execute     self.errorhandler(self, exc, value)   File "/usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler     raise errorclass, errorvalue OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction')

 

 

 

总结:

    一切看场景，这次改表结构确实有些凶险 ！  过度的信赖pecona pt-online-schema-change在线修改表结构工具，结果造成半夜锁表，而且是死锁。 大家分析锁的时候可以利用下面的语句进行分析。 

 

 

 

 

 

 

 

Python

 

1 2 3 4 5 6 7 8 9

查看正在锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;     #查看等待锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;     #正在进行的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

 

 

我再来说说pt-online-schema-change的性能问题,   他本身资源消耗不大，压力都在于mysql上。他的alter table和导表过程是非常的消耗磁盘io的，所以如果你的服务器不适ssd固态硬盘的话，肯有可能就磁盘傻逼了。 mysql单表300G的数据，5亿条数据，用了将近6个小时才搞完。在小范围业务的影响下，还算可以承受。