我们已经使用Percona OSC了一段时间来更改我们的mysql架构而不锁定表并且它工作得很好,通常在几个内的“大”innodb表(~380万行)中添加新的列或索引小时.

然而,我尝试的最后一次更新在运行7小时后(过夜,在我们最安静的时间段内)仅完成了40％,估计还需要11个小时才能完成(不断增加). RedHat服务器上的4GB可用内存全部使用 – 32GB,我们最近从16GB升级.

那么这里发生了什么？为什么突然间的时间突然变得如此之高？我们刚刚达到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) – 这可能有所不同吗？

解决方法:

这个表有多大MB / GB？

InnoDB将它的页面缓存在innodb缓冲池(innodb_buffer_pool_size)中,这对性能很重要.在具有>的专用主机上4GB RAM我们建议大约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中的缓冲池中).所以基本上你在机器上有一个适度的RAM,但真的InnoDB只看到2GB.

你也有应用程序在服务器上运行,所以你需要一些观察来调整,但我希望你可以大大提高分配给缓冲池的内存水平.我还希望你的大部分RAM都没有被使用,但已被分配给文件系统缓存.

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

此外,它的工作是检查您的innodb_log_file_size是否适合您的工作负载.这很重要,因此MySQL可以推迟IO.它的当前大小是多少？