mysql分区尾部添加分区非常慢?

我的一个zabbix数据库,history表是按照clock分区的。现在在加分区的时候,非常慢,现在这个表已经超过600G以上了。每天一个分区。
alter table history add partition (partition p20200303 values less than(unix_timestamp('20200304')));
alter table history add partition (partition p20200304 values less than(unix_timestamp('20200305')));
alter table history add partition (partition p20200305 values less than(unix_timestamp('20200306')));

第一个语句非常慢,后面几个就会慢慢变快。第一个语句达到了接近4分钟。而我看当时这个会话的状态是commiting。只是这个语句阻塞了其他的很多insert操作而已。
请问,为啥加分区那么慢啊?

1回答

韩成亮韩成亮  数据库管理员 , KE
yinxin赞同了此回答
。。。建议看看show processlist 你就知道为啥慢了 至于你说的后面几个快,副本已经复制了省去了这个步骤 Performance and Space Requirements ALTER TABLE operations are processed using one of the following algorithms: COPY: Operations are performed on a co...显示全部

。。。建议看看show processlist 你就知道为啥慢了 至于你说的后面几个快,副本已经复制了省去了这个步骤

Performance and Space Requirements

ALTER TABLE operations are processed using one of the following algorithms:

  • COPY: Operations are performed on a copy of the original table, and table data is copied from the original table to the new table row by row. Concurrent DML is not permitted.
  • INPLACE: Operations avoid copying table data but may rebuild the table in place. An exclusive metadata lock on the table may be taken briefly during preparation and execution phases of the operation. Typically, concurrent DML is supported.

The ALGORITHM clause is optional. If the ALGORITHM clause is omitted, MySQL uses ALGORITHM=INPLACE for storage engines andALTER TABLE clauses that support it. Otherwise, ALGORITHM=COPY is used.

更多参考
https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html

收起
 2020-01-15
浏览201

提问者

wangzk0206数据库管理员, scrcu

分布式数据库选型优先顺序调查

向社区表达您的观点,参与即得50金币。

问题状态

  • 发布时间:2020-01-15
  • 关注会员:2 人
  • 问题浏览:1251
  • 最近回答:2020-01-15