针对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表或ALERT 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?

推荐答案

您可能想看看

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.

关于此 PS:我知道这是一个古老的问题，只是在有人通过搜索引擎点击该问题的情况下回答.

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