如果说你的数据量并发量不大,或者你的数据量很少没有到千万级别,也许pt-osc
、gh-osc
,online-ddl这些工具都用不着。但是,如果你的数据量很大,数据又很热。如果你没有这些工具,你可能无法完成对一个数据库新增一个字段或者任何一个简单的DDL
语句。
简单的分析一下,为了保证数据一致性问题,我们在哪儿都会遇到锁的问题,锁是用来保证顺序性的。谁先拥有锁,谁就可以先执行。锁也会存在力度问题,它跟你要做的一件事情息息相关,我们也会在性能上去做取舍,所有就好了行锁、表锁等。
说一下我遇到的这个场景,数据量数据大概在800W左右,但是表非常的热,长事务也很多。当我要对一个表新增字段的时候,这个时候如果你经验不够足,可能就会“量成大祸”。一般在做DDL
会出现:Waiting for table metadata lock。
如果长时间获取不到锁的话,就出现一个可怕的情况:
DDL
语句DDL
语句未执行之前,后面的请求全部是被hold住的这样子就会导致一前一后同时夹击,导致整个业务不可用。那么出现Waiting for table metadata lock可能是由哪些原因导致的?
通过show processlist
可以看到TableA
上有正在进行的操作(包括读),此时alter table语句无法获取到metadata
独占锁,会进行等待。
这是最基本的一种情形,这个和mysql 5.6
中的online ddl
并不冲突。一般alter table
的操作过程中,在after create
步骤会获取metadata
独占锁,当进行到altering table
的过程时(通常是最花时间的步骤),对该表的读写都可以正常进行,这就是online ddl
的表现,并不会像之前在整个alter table
过程中阻塞写入。(当然,也并不是所有类型的alter操作都能online的,具体可以参见官方手册:http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html)
处理方法: kill 掉 DDL所在的session.
# 找出所有执行时间超过 5 分钟的线程,拼凑出 kill 语句,方便后面查杀
select concat('kill ', id, ';')
from information_schema.processlist
where Command != 'Sleep' AND COMMAND != 'Binlog Dump GTID' AND COMMAND != 'Binlog Dump'
and Time > 300
order by Time desc;
通过show processlist
看不到TableA
上有任何操作,但实际上存在有未提交的事务,可以在 information_schema.innodb_trx中查看到。在事务没有完成之前,TableA上的锁不会释放,alter table
同样获取不到metadata
的独占锁。
处理方法: 通过 select * from information_schema.innodb_trx\G
, 找到未提交事物的sid
, 然后 kill
掉,让其回滚。
通过show processlist
看不到TableA
上有任何操作,在information_schema.innodb_trx
中也没有任何进行中的事务。这很可能是因为在一个显式的事务中,对TableA
进行了一个失败的操作(比如查询了一个不存在的字段),这时事务没有开始,但是失败语句获取到的锁依然有效,没有释放。从performance_schema.events_statements_current
表中可以查到失败的语句。
官方手册上对此的说明如下:
If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.
也就是说除了语法错误,其他错误语句获取到的锁在这个事务提交或回滚之前,仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解,因为错误的语句根本不会被记录到二进制日志。
处理方法: 通过performance_schema.events_statements_current找到其sid, kill 掉该session. 也可以 kill
掉DDL
所在的session
.
上述是手动操作,毕竟是一个比手速的过程,而且也不能保证保证100%,还需要经常的kill进程才行。
请看参考地址,对于一些online ddl工具研究后,对gt-osc做了一个初步的使用。
pt-online-schema-change --host=xxx -uxxx -pxxx \
--alter "add xxx int(1) NOT NULL DEFAULT '0' COMMENT 'xxx', add xxx varchar(10) NOT NULL DEFAULT '0' COMMENT 'xxx' , lock=none" D=xxx,t='xxx' \
--execute \
--print \
--statistics \
--no-check-alter
执行后其实它做了这些工作,具体如下:
db
.table_name
TO db
._table_name_old
, db
._table_name_new
TO db
.table_name
可以去看一下网站的说明:https://www.percona.com/blog/2019/06/07/how-pt-online-schema-change-handles-foreign-keys/
http://static.cyblogs.com/Jietu20211018-000822.jpg