【MySQL】 5.7 中意外缓慢的alter table操作
2022-06-04 20:35 abce 阅读(1298) 评论(0) 编辑 收藏 举报通常人们会期望algorithm=copy的alter table会比默认的algorithm=inplace慢。本文描述了并非如此的情况。 这种行为的原因之一是alter table(默认 algorithm=inplace)鲜为人知的限制避免了redo操作。因此，被修改的表/表空间的所有脏页都必须在alter table完成之前刷新。
很久之前，MySQL的alter table操作是通过创建一个新表，然后拷贝原表的数据到新表，最后重命名表来实现的。在操作期间，为了防止数据不一致，表是被锁定的。 对于InnoDB表，新的算法被引入。新算法不会涉及全表拷贝，有些操作不会施加表级锁：首先是InnoDB引入了在线增加索引的算法,然后非阻塞增加列或非阻塞在线ddl特性被引入。
在我们的一个"小型"Percona服务器中，将一列添加到13GB的InnoDB表需要超过5分钟的时间。然而，在另一个"大型"Percona服务器上，同一个大小为30GB的表，添加同一列需要4个多小时。
在验证了两台服务器上的磁盘I/O吐量相同后，我们使用PMM记录和审查性能，调查了alter table helios add column的持续时间差异如此大的原因。
在较小的服务器上，alter table速度更快，相关的PMM监控图显示：
在我们的Percona Server版本5.7 中，alter table helios add column以in place方式执行。在左侧，我们可以观察到表重建的稳定速率，随后是四个峰值，对应于四个索引的重建。 同样有趣的是，带有inplace algorithm的alter table（这是添加字段的默认设置)将需要强制刷新所有脏页并等待它完成。这是一个鲜为人知的事实，而且文献很少。原因是此操作禁用了undo和redo日志记录：
algorithm=inplace不需要undo日志记录或关联的redo日志记录。这些undo和redo操作增加了使用algorithm=copy的ddl语句的开销。https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html
在这种情况下，唯一的选择是刷新所有脏页，否则数据可能会变得不一致。Percona Server for MySQL中对alter table进行了特殊处理。
回到我们的例子——在表重建期间，InnoDB缓冲池变得越来越脏：
该图显示了与表数据大小相对应的大约9 GB的峰值。最初我们的印象是，随着脏页被刷新到磁盘，内存中的脏页量以Percona自适应刷新算法确定的速率减少。事实证明，通过alter刷新和自适应刷新没有关系：两者同时发生。 通过alter刷新是单页刷新，通过迭代刷新列表中的页面并刷新所需space_id的页面（一个接一个）来完成。这可能解释了如果服务器有更多RAM，则刷新速度可能会更慢，因为它必须扫描更大的列表。
在最后一个缓冲池I/O请求（来自最后一个索引构建）结束后，算法会提高剩余脏页的刷新率。当内存中没有更多脏页时，alter table操作完成。
可以在下图中清楚地看到I/O速率增加了六倍：
相反，在“大”的服务器上，alter table的行为有所不同。虽然，一开始它以类似的方式进行：
在左侧，我们可以观察到表重建的稳定速率，随后是四个峰值，对应于四个表索引的重建。 在表重建期间，缓冲池变得越来越脏：
紧接着21 GB的表数据，有四个峰值对应于四个索引构建。 完成这30 GB表的alter table处理大约需要二十分钟。在某种程度上，这相当于上面完成13 GB 表的ALTER TABLE处理的类似部分大约需要4分钟。 但是，自适应刷新算法在该服务器上表现不同。完成从内存中刷新脏页花了四个多小时
这与“小”服务器相比，缓冲池I/O仍然非常低：
这不是硬件限制，因为PMM监控显示，在其他时候，“大”服务器的缓冲池I/O速率提高了10 倍，例如：
结论
要意识到alter table ... add column 的性能较慢（默认算法是 inplace）的原因。 在配置高的服务器上，差异可能很大：缓冲池越小，刷新列表越小，刷新速度越快，因为alter表具有更小的 flush_lists来迭代。在某些情况下，使用 alter table algorithm=copy 可能会更好（并且具有更可预测的时间）。
