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同行回答
。。。建议看看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
收起