为什么Percona pt-online-schema-change的性能如此糟糕？

Question

我们已经使用Percona OSC对我们的mysql模式进行了一段时间的修改，而不需要锁定表，它工作得很好，通常在几个小时内向“大型”innodb表(大约380万行)添加一个新的列或索引。

然而，我尝试的最后一次更新在运行了7个小时(通宵，在我们最安静的时候)后只完成了40%，估计还需要11个小时才能完成(这个数字还在不断增加)。除了RedHat服务器上的4 4GB可用内存之外，所有的可用内存都被使用了-32 4GB，我们最近从16 4GB升级到了32 4GB。

那么这是怎么回事呢？为什么时间会突然跳得这么高呢？我们是否刚刚达到了percona / mysql /服务器无法应对的某种阈值？有没有我们可以调整的配置来提高性能？

该表有32列和12个索引(包括主键和其他2个唯一索引)。我知道这是很多，但正如我所说，直到最近，它的表现还不错。

该表还有几个指向它的外键，我们将它们设置为使用drop_swap方法进行更新。

我使用的完整命令是：

pt-online-schema-change --execute --ask-pass --set-vars innodb_lock_wait_timeout=50 --alter-foreign-keys-method=drop_swap
--alter "ADD is_current TINYINT(1) DEFAULT '1' NOT NULL" u=admin,p=XXXXXXX,D=xxxxx_live,t=applicant

innodb_buffer_pool_size当前设置为2147483648 -是否应增加此值？如果是这样的话，增加了多少？web服务器(apache/php/symfony)也在这个机器上运行。

我对这个表所做的最后一个更改是将1个字段的排序规则更改为utf8_bin (其他字段为utf8_unicode_ci) -这会有什么不同吗？

Answer 1

以MB/GB为单位，该表有多大？

InnoDB在innodb缓冲池(innodb_buffer_pool_size)中缓存它的页面，这对性能很重要。在内存大于4 4GB的专用主机上，我们建议将大约70-80%的内存用于InnoDB缓冲池。

使用这篇文章中的SQL来收集表和索引的逻辑大小

https://www.percona.com/blog/2008/03/17/researching-your-mysql-table-sizes/

有了这些信息，您将能够立即判断MySQL实例(Innodb引擎)是否内存不足。

如果您的工作数据集适合内存，这很好，但如果不适合，则可能会导致缓存未命中，然后MySQL将需要执行IO来访问磁盘资源，以便将页面交换到缓冲池中。(IO在DB中始终是PITA )

pt-osc工作的性质是创建表的新的修改副本，并用来自原始的行回填新版本。还可以使用该工具设置的触发器插入/更新或删除新行。要执行这种回填，它将不得不在某个时刻接触该表中的所有行，并且该表的大部分可能是冷的(不在RAM的缓冲池中)。所以基本上你在机器上有适量的内存，但实际上InnoDB只看到了2 2GB。

您的服务器上也有正在运行的应用程序，因此需要您进行一些观察才能调优，但我希望您可以显著提高分配给缓冲池的内存级别。我还希望您的大部分RAM没有使用，但已分配给文件系统缓存。

如果您的表只有几百mb (我怀疑有4m条记录和一个很宽的模式)，那么可能还有更深层次的问题需要检查，但我相信，通过更改缓冲池大小，您将看到更好的性能。

此外，检查您的innodb_log_file_size是否与您的工作负载相适应也是一项工作。这一点很重要，这样MySQL就可以延迟IO。它现在的尺寸是多少？

Answer 2

假设所有条件都相同，我会说某种类型的阈值被超过了，或者其他一些进程正在加载数据库。您正在使用的索引数量非常高。pt-osc创建新的空的修改后的表，然后开始以“块”的形式进行复制。每个块花费的时间被动态调整为持续0.5秒(默认情况下)。你可以查看"show processlist“来查看谁在向数据库施压，以及pt-osc使用了多大的块大小来获得更多的信息。