更改大型MySQL InnoDB表 [英] Changing Large MySQL InnoDB Tables

问题描述

对于MySQL中具有超过1000万行的大型innodb表，添加新列或添加新索引可能要花费数小时和数天的时间.在这两种情况下，提高大型innodb表性能的最佳方法是什么?更多内存，调整配置(例如，增加 sort_buffer_size 或 innodb_buffer_pool_size )，还是某种技巧?与其直接更改表，不如创建一个新表，对其进行更改，然后将旧数据复制为新表，这样对于多个更改:

Adding a new column or adding a new index can take hours and days for large innodb tables in MySQL with more than 10 million rows. What is the best way to increase the performance on large innodb tables in these two cases? More memory, tweaking the configuration (for example increasing the sort_buffer_size or innodb_buffer_pool_size), or some kind of trick? Instead of altering a table directly, one could create a new one, change it, and copy the old data the new, like this which is useful for ISAM tables and multiple changes:

CREATE TABLE tablename_tmp LIKE tablename;
ALTER TABLE tablename_tmp ADD fieldname fieldtype;
INSERT INTO tablename_tmp SELECT * FROM tablename;
ALTER TABLE tablename RENAME tablename_old;
ALTER TABLE tablename_tmp RENAME tablename;

是否也推荐使用innodb表，或者这仅仅是ALTER TABLE命令执行的操作吗?

Is it recommendable for innodb tables, too, or is it just what the ALTER TABLE command does anway?

推荐答案

编辑2016::我们最近(2016年8月)发布了gh-ost，修改了我的答案以反映出来.

Edit 2016: we've recently (August 2016) released gh-ost, modifying my answer to reflect it.

今天，有几种工具可让您在线进行MySQL的Alter表.这些是:

Today there are several tools which allow you to do online alter table for MySQL. These are:

• 编辑2016: gh-ost :GitHub的无触发模式迁移工具(免责声明:我是该工具的作者)
• oak-online-alter-表，作为 openark-kit 的一部分(免责声明:我是该工具的作者)
• pt-online-schema-change ，作为 Percona工具包
的一部分
• Facebook的 MySQL的在线架构更改

• edit 2016: gh-ost: GitHub's triggerless schema migration tool (disclaimer: I am author of this tool)
• oak-online-alter-table, as part of the openark-kit (disclaimer: I am author of this tool)
• pt-online-schema-change, as part of the Percona Toolkit
• Facebook's online schema change for MySQL

一张大桌子需要很长时间才能到达ALTER. innodb_buffer_pool_size很重要，其他变量也很重要，但是在很大的表上，它们都可以忽略不计.这只需要时间.

A large table will take long time to ALTER. innodb_buffer_pool_size is important, and so are other variables, but on very large table they are all negligible. It just takes time.

MySQL对表ALTER的作用是创建具有新格式的新表，复制所有行，然后切换.在此期间，表已完全锁定.

What MySQL does to ALTER a table is to create a new table with new format, copy all rows, then switch over. During this time the table is completely locked.

它很可能会在所有选项中表现最差.这是为什么?因为您使用的是InnoDB表，所以INSERT INTO tablename_tmp SELECT * FROM tablename可以进行事务处理. 巨大交易.它将产生比普通ALTER TABLE更大的负载.

It will most probably perform worst of all options. Why is that? Because you're using an InnoDB table, the INSERT INTO tablename_tmp SELECT * FROM tablename makes for a transaction. a huge transaction. It will create even more load than the normal ALTER TABLE.

此外，您此时必须关闭您的应用程序，以便它不会将(INSERT，DELETE，UPDATE)写入表中.如果可以，那么您的整个交易毫无意义.

Moreover, you will have to shut down your application at that time so that it does not write (INSERT, DELETE, UPDATE) to your table. If it does - your whole transaction is pointless.

工具并非都一样.但是，这些基本知识是共享的:

The tools do not all work alike. However, the basics are shared:

• 他们创建了具有已更改架构的影子"表
• 他们创建并使用触发器将更改从原始表传播到幻像表
• 他们慢慢地将表中的所有行复制到影子表.他们这样做是成块的:例如，一次1,000行.
• 在您仍然可以访问和操作原始表的同时，它们会执行上述所有操作.
• 满意后，他们使用RENAME交换两者.

• They create a "shadow" table with altered schema
• They create and use triggers to propagate changes from original table to ghost table
• They slowly copy all the rows from your table to shadow table. They do so in chunks: say, 1,000 rows at a time.
• They do all the above while you are still able to access and manipulate the original table.
• When satisfied, they swap the two, using a RENAME.

openark-kit 工具已经使用了3.5年. Percona工具已经使用了几个月，但可能比前者经过了更多的测试.据说Facebook的工具很适合Facebook，但没有为普通用户提供通用的解决方案.我自己没有用过.

The openark-kit tool has been in use for 3.5 years now. The Percona tool is a few months old, but possibly more tested then the former. Facebook's tool is said to work well for Facebook, but does not provide with a general solution to the average user. I haven't used it myself.

编辑2016: gh-ost是一种无触发解决方案，可以显着减少主服务器上的主服务器写入负载，从而将迁移写入负载与正常负载分离.它是可审核，可控制，可测试的.我们在GitHub内部进行了开发，并将其作为开源发布.我们今天通过gh-ost进行所有生产迁移.在此处.

Edit 2016: gh-ost is a triggerless solution, which significantly reduces master write-load on the master, decoupling the migration write load from the normal load. It is auditable, controllable, testable. We've developed it internally at GitHub and released it as open source; we're doing all our production migrations via gh-ost today. See more here.

每种工具都有其自身的局限性，请仔细阅读文档.

Each tool has its own limitations, look closely at documentation.

保守的方法是使用主动-被动主-主复制，在备用(被动)服务器上执行ALTER，然后切换角色并在以前用作主动服务器的位置上再次执行ALTER，现在变成被动了.这也是一个不错的选择，但是需要额外的服务器和更深的复制知识.

The conservative way is to use an Active-Passive Master-Master replication, do the ALTER on the standby (passive) server, then switch roles and do the ALTER again on what used to be the active server, now turned passive. This is also a good option, but requires an additional server, and deeper knowledge of replication.

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