当前位置：theitroad>为 InnoDB 的 ALTER TABLE 优化 MySQL

为 InnoDB 的 ALTER TABLE 优化 MySQL

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/654594/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-31 12:56:28 　来源:igfitidea点击:

Optimizing MySQL for ALTER TABLE of InnoDB

mysqlalter-tableperformance

提问by schuilr

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.

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

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).

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

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?

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

回答by Tadas Sasnauskas

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

您可能想查看Percona 工具包中的pt-online-schema-change。本质上它的作用是：

  • Copies original table structure, runs ALTER.
  • Copies rows from old table to newly created one.
  • Uses triggers to track and sync changes while copying.
  • When everything is finished it swaps tables by renaming both.
  • 复制原始表结构，运行 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: I know it's an old question, just answering in case someone hits this via search engine.

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

回答by staticsan

You need to think about your requirements a little more carefully.

您需要更仔细地考虑您的要求。

At the simplest level, the "fastest" way to get the table changed is to do it in as few ALTER TABLEstatements as possible, preferably one. This is because MySQL copies a table's data to change the schema and making fifteen changes whilst make a single copy is obviously (and really is) faster than copying the table fifteen times, making one change at a time.

在最简单的层面上，改变表的“最快”方法是在尽可能少的ALTER TABLE语句中完成，最好是一个。这是因为 MySQL 复制表的数据以更改模式并进行 15 次更改，而制作单个副本显然（并且确实）比复制表 15 次（一次进行一次更改）快。

But I suspect you're asking how to do this change with the least amount of downtime. The way I would do that, you basically synthesize the way a non-block ALTER TABLEwould work. But it has some additional requirements:

但是我怀疑您是在问如何以最少的停机时间进行此更改。我这样做的方式，你基本上综合了非块的ALTER TABLE工作方式。但它有一些额外的要求：

  1. you need a way to track added and changed data, such as with a "modified" date field for the latter, or an AUTO_INCREMENTfield for the former.
  2. you need space to have two copies of your table on the database.
  3. you need a time period where alterations to the table won't get too far ahead of a snapshot
  1. 您需要一种方法来跟踪添加和更改的数据，例如对后者使用“修改”日期字段，或AUTO_INCREMENT对前者使用字段。
  2. 您需要空间才能在数据库上保存您的表的两个副本。
  3. 您需要一个时间段，在该时间段内对表的更改不会比快照提前太多

The basic technique is as you suggested, i.e. using an INSERT INTO ... SELECT .... At least you're in front because you're starting with an InnoDB table, so the SELECTwon't block. I recommend doing the ALTER TABLEon the new, empty table, which will save MySQL copying all the data again, which will mean you need to list all the fields correctly in the INSERT INTO ... SELECT ...statement. Then you can do a simple RENAMEstatement to swap it over. Then you need to do another INSERT INTO ... SELECT ... WHERE ...and perhaps an UPDATE ... INNER JOIN ... WHERE ...to grab all the modified data. You need to do the INSERTand UPDATEquicklyor your code will starting adding new rows and updates to your snapshot which willinterfere with your update.(You won't have this problem if you can put your app into maintenence mode for a few minutes from before the RENAME.)

基本技术如您所建议的那样，即使用INSERT INTO ... SELECT .... 至少你在前面，因为你从一个 InnoDB 表开始，所以SELECT不会阻塞。我建议ALTER TABLE在新的空表上执行此操作，这将节省 MySQL 再次复制所有数据，这意味着您需要在INSERT INTO ... SELECT ...语句中正确列出所有字段。然后你可以做一个简单的RENAME语句来交换它。然后你需要做另一个INSERT INTO ... SELECT ... WHERE ...，也许是UPDATE ... INNER JOIN ... WHERE ...获取所有修改过的数据。您需要快速执行此操作INSERT，否则您的代码将开始向快照添加新行和更新，这将干扰您的更新。UPDATE（如果您可以将您的应用程序从RENAME.

Apart from that, there are some key and buffer related settings you can change for just one session that may help the main data move. Things like read_rnd_buffer_sizeand read_buffer_sizewould be useful to increase.

除此之外，您可以仅针对一个会话更改一些与密钥和缓冲区相关的设置，这可能有助于主要数据的移动。像read_rnd_buffer_size和read_buffer_size这样的东西增加会很有用。

回答by noonex

  1. Setup slave
  2. Stop replication.
  3. Make ALTER on slave
  4. Let slave catch up the master
  5. swap master and slave, so slave becomes production server with changed structure and minimum downtime
  1. 设置从站
  2. 停止复制。
  3. 在奴隶上做 ALTER
  4. 让奴隶追上主人
  5. 交换 master 和 slave，因此 slave 成为具有改变结构和最小停机时间的生产服务器

回答by RC.

Unfortunately, this is not always as simple as staticsanleads on in his answer. Creating the new table while online and moving the data over is easy enough, and doing the cleanup while in maintenance mode is also do-able enough, however, the Mysql RENAME operation automatically manipulates any foreign key references to your old table. What this means is that any foreign key references to the original table will still point to whatever you rename the table to.

不幸的是，这并不总是像staticsan在他的回答中所引导的那样简单。在线创建新表和移动数据很容易，在维护模式下进行清理也很容易，但是，Mysql RENAME 操作会自动操作对旧表的任何外键引用。这意味着对原始表的任何外键引用仍将指向您将表重命名为的任何内容。

So, if you have any foreign key references to the table you're trying to alter you're stuck either altering those tables to replace the reference to your new table, or worse if that table is large you have to repeat the process with large table number two.

因此，如果您对要更改的表有任何外键引用，则您要么更改这些表以替换对新表的引用，要么更糟糕的是，如果该表很大，则必须使用 large 重复该过程二号桌。

Another approach that has worked for us in the past has been to juggle a set of Mysql replicas handling the alter. I'm not the best person to speak to the process, but it basically consists of breaking replication to one slave, running the patch on that instance, turning replication back on once the alter table is completed so that it catches up on replication. Once the replication catches up, you put the site into maintenance mode (if necessary) to switch from your master to this new patched slave as the new master database.

过去对我们有用的另一种方法是处理一组处理更改的 Mysql 副本。我不是谈论这个过程的最佳人选，但它基本上包括将复制中断到一个从属，在该实例上运行补丁，在更改表完成后重新打开复制，以便它赶上复制。一旦复制赶上，您将站点置于维护模式（如有必要）以从主数据库切换到这个新的打补丁的从数据库作为新的主数据库。

The only thing I can't remember is exactly when you point the other slaves at the new master so that they also get the alter applied. One caveat to this process, we typically use this to roll alter patches before the code needs the change, or after the code has changed to no longer reference the columns/keys.

我唯一不记得的就是你将其他奴隶指向新主人的确切时间，以便他们也应用改变。对此过程的一个警告，我们通常使用它在代码需要更改之前或在代码更改为不再引用列/键之后滚动更改补丁。

回答by Peter Lamberg

I tested various strategies to speed up one alter table. Eventually I got about 10x speed increase in my particular case. The results may or may not apply to your situation. However, based on this I would suggest experimenting with InnoDB log file/buffer size parameters.

我测试了各种策略来加速一个alter table。最终，在我的特定情况下，速度提高了大约 10 倍。结果可能适用于您的情况，也可能不适用。但是，基于此，我建议尝试使用 InnoDB 日志文件/缓冲区大小参数。

In short, only increasing innodb_log_file_size and innodb_log_buffer_size had a measurable effect(Be careful! Changing innodb_log_file_size is risky. Look below for more info).

简而言之，只有增加 innodb_log_file_size 和 innodb_log_buffer_size 才具有可衡量的效果（小心！更改innodb_log_file_size 是有风险的。查看下面的更多信息）。

Based on the rough write data rate (iostat) and cpu activity the bottleneck was io based, but not data throughput. In the faster 500s runs the write throughput is at least in the same ballpark that you would expect from the hard disk.

根据粗略的写入数据速率 (iostat) 和 CPU 活动，瓶颈是基于 io 的，而不是数据吞吐量。在更快的 500 秒运行中，写入吞吐量至少与您对硬盘的期望相同。

Tried performance optimizations:

尝试性能优化：

Changing innodb_log_file_size can be dangerous.See http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely/The technique (file move) explained in the link worked nicely in my case.

更改 innodb_log_file_size 可能很危险。请参阅http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely/链接中解释的技术（文件移动）在我的情况下效果很好。

Also see http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/and http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/for information about innodb and tuning log sizes. One drawback of larger log files is longer recovery time after crash.

另请参阅http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/和http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb -log-file-size/有关 innodb 和调整日志大小的信息。较大日志文件的缺点之一是崩溃后的恢复时间较长。

Test runs and rough timings:

测试运行和大致时间：

  • The simple load data to a freshly createad table: 6500s
  • load data w. innodb_log_file_size=200M, innodb_log_buffer_size=8M, innodb_buffer_pool_size=2200M, autocommit= 0; unique_checks=0, foreign_key_checks=0: 500s
  • load data w. innodb_log_file_size=200M, innodb_log_buffer_size=8M: 500s
  • Equivalent straight alter table w. datainnodb_log_file_size=200M, innodb_log_buffer_size=8M: 500s
  • 简单加载数据到新创建的表：6500s
  • 加载数据 w。innodb_log_file_size=200M，innodb_log_buffer_size=8M，innodb_buffer_pool_size=2200M，autocommit=0；unique_checks=0，foreign_key_checks=0：500s
  • 加载数据 w。innodb_log_file_size=200M，innodb_log_buffer_size=8M：500s
  • 等效的直接改变表 w。datainnodb_log_file_size=200M，innodb_log_buffer_size=8M：500s

Testing details: Table: InnoDB, 6M rows, 2.8G on disk, single file (innodb_file_per_table option), primary key is 1 integer, +2 unque constraints/indices, 8 columns, avg. row length 218 bytes. Server: Ubuntu 12.04, x86_64, virtual machine, 8 cores, 16GB, sata consumer grade disk, no raid, no database activity, minuscule other process activity, minuscule activity in other and much smaller virtual machines. Mysql 5.1.53. The initial server config is pretty default except for increased innodb_buffer_pool_size of 1400M. The alter table adds 2 small columns. I didn't clock the raw alter table, but instead experimented with equivalent load data infile statement, finally I did the straight alter table and got comparable result.

测试细节：表：InnoDB，6M 行，2.8G 磁盘，单个文件（innodb_file_per_table 选项），主键是 1 个整数，+2 唯一约束/索引，8 列，平均。行长 218 字节。服务器：Ubuntu 12.04，x86_64，虚拟机，8 核，16GB，sata 消费级磁盘，无raid，无数据库活动，其他进程活动极少，其他和更小的虚拟机中的活动极少。mysql 5.1.53。除了增加 1400M 的 innodb_buffer_pool_size 之外，初始服务器配置是非常默认的。更改表添加了 2 个小列。我没有对原始更改表进行计时，而是尝试使用等效的加载数据 infile 语句，最后我做了直接更改表并获得了可比较的结果。

This question is related to at least following questions:

这个问题至少与以下问题有关：

回答by Seb

I really don't know how to optimize that, but it's usually a good practice to put the site in offline mode before doing such updates.

我真的不知道如何优化它，但在进行此类更新之前将站点置于离线模式通常是一个好习惯。

Then, you can run your DB scripts at, say, 3 am, so it shouldn't matter much if downtime's a big longer than ideal.

然后，您可以在凌晨 3 点运行您的数据库脚本，因此如果停机时间比理想情况长得多，那应该没什么关系。

相关推荐

使用 Mysql 对链接表执行多个 INSERT

使用 Mysql 对链接表执行多个 INSERT

MySQL 中的字符串格式化

MySQL 中的字符串格式化

MySQL 语法错误使用名为“desc”的列创建表

MySQL 语法错误使用名为“desc”的列创建表

MySQL 外键约束形成不正确？

MySQL 外键约束形成不正确？

相关推荐
最近更新
标签