在高并发下在线修改表结构时异常 [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。
|
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貌似不支持任务暂停继续，只能重新来了。
|
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的时候，才提示报错。
|
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导数据的语句。
|
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。
|
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。
|
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语句变成串行。在线表结构修改时，尽量减少并发连接才是真理。 任务场景到了高并发，做一些大改动都时候不好把控的。
|
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')
|
总结:
一切看场景，这次改表结构确实有些凶险 ！ 过度的信赖peconapt-online-schema-change在线修改表结构工具，结果造成半夜锁表，而且是死锁。大家分析锁的时候可以利用下面的语句进行分析。
|
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个小时才搞完。在小范围业务的影响下，还算可以承受。
优质内容筛选与推荐>>1、java实现发送邮件服务器，SMTP协议发送邮件
2、SDN实战团分享（四十）：揭秘AristaEOS三大特性，打造非一般的云网架构
3、阿里飞猪大数据杀熟冤不冤？我们做了这个实验
4、对标蚂蚁金服，亚马逊志在颠覆美国投资界
5、更快学会任何东西的终极指南