为 InnoDB 的 ALTER TABLE 优化 MySQL [英] Optimizing MySQL for ALTER TABLE of InnoDB

问题描述

很快我们将需要对我们的生产数据库进行架构更改.我们需要尽量减少这项工作的停机时间，但是，ALTER TABLE 语句将运行很长时间.我们最大的表有1.5亿条记录，最大的表文件是50G.所有表都是 InnoDB，它被设置为一个大数据文件(而不是一个文件每个表).我们在 8 核机器、16G 内存和 RAID10 配置上运行 MySQL 5.0.46.

Sometime soon we will need to make schema changes to our production database. We need to minimize downtime for this effort, however, the ALTER TABLE statements are going to run for quite a while. Our largest tables have 150 million records, largest table file is 50G. All tables are InnoDB, and it was set up as one big data file (instead of a file-per-table). We're running MySQL 5.0.46 on an 8 core machine, 16G memory and a RAID10 config.

我在 MySQL 调优方面有一些经验，但这通常侧重于从多个客户端读取或写入.在 Internet 上可以找到很多关于此主题的信息，但是，关于(临时)调整 MySQL 服务器以加快 InnoDB 表上的 ALTER TABLE 或 INSERT INTO 的最佳实践的可用信息似乎很少.. SELECT FROM(我们可能会使用它而不是 ALTER TABLE 以获得更多机会来加快速度).

I have some experience with MySQL tuning, but this usually focusses on reads or writes from multiple clients. There is lots of info to be found on the Internet on this subject, however, there seems to be very little information available on best practices for (temporarily) tuning your MySQL server to speed up ALTER TABLE on InnoDB tables, or for INSERT INTO .. SELECT FROM (we will probably use this instead of ALTER TABLE to have some more opportunities to speed things up a bit).

我们计划进行的架构更改是向所有表添加一个整数列并使其成为主键，而不是当前的主键.我们还需要保留旧"列，因此不能选择覆盖现有值.

The schema changes we are planning to do is adding a integer column to all tables and make it the primary key, instead of the current primary key. We need to keep the 'old' column as well so overwriting the existing values is not an option.

尽快完成此任务的理想设置是什么?

What would be the ideal settings to get this task done as quick as possible?

推荐答案

你可能想看看 pt-online-schema-change 来自 Percona 工具包.本质上它的作用是:

You might want to look at pt-online-schema-change from Percona toolkit. Essentially what it does is:

• 复制原始表结构，运行 ALTER.
• 将旧表中的行复制到新创建的表中.
• 在复制时使用触发器来跟踪和同步更改.
• 当一切都完成后，它通过重命名来交换表.

对于单实例数据库非常有效，但如果您使用复制并且您无法承受停止从属数据库并在以后重建它们，则可能会非常棘手.

Works very well for single instance databases, but might be quite tricky if you use replication and you can't afford stopping slaves and rebuilding them later.

还有一个关于这个的不错的网络研讨会 这里.

There's also a nice webinar about this here.

PS:我知道这是一个老问题，只是在有人通过搜索引擎点击时回答.

这篇关于为 InnoDB 的 ALTER TABLE 优化 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！