加快从MyISAM到InnoDB的转换 [英] Speeding up conversion from MyISAM to InnoDB

问题描述

我正在生产一个MySQL 1.5 GB MyISAM表(1.0 GB数据，0.5 GB索引)，我打算将其转换为InnoDB.

I have a MySQL 1.5 GB MyISAM-table (1.0 GB data, 0.5 GB indexes) in production which I'm about to convert into InnoDB.

由于该表用于生产中，所以我希望使停机时间尽可能短.

Since the table is used in production I'd like to make the downtime as short as possible.

我的问题:

• 应调整哪些MySQL配置选项以加快ALTER TABLE table_name ENGINE=InnoDB;?

还有什么其他技巧可以用来加快生产数据库表从MyISAM到InnoDB的转换?

What other tricks can be used in order to speed up conversion of a production database table from MyISAM to InnoDB?

推荐答案

• 设置较大的innodb_buffer_pool_size(2GB或更多)
• 使用shell命令预读旧的myisam数据/索引文件
• 增加innodb_log_file_size(256 MB)
• 在X个并行线程中执行alter table，其中X是服务器上CPU内核的数量
• 仅对转换进行其他次要调整(innodb_doublewrite = 0，innodb_flush_log_at_trx_commit = 0)
• Setting a large innodb_buffer_pool_size (2GB or more)
• preread your old myisam data/index files using shell commands
• increase innodb_log_file_size (256 MB)
• Do the alter table in X parallel threads, where X is the qty of CPU cores on your server
• other minor tweaks for conversion only (innodb_doublewrite=0, innodb_flush_log_at_trx_commit=0)

设置innodb_buffer_pool_size尽可能高是加快innodb表创建的典型方法-您的数据集看起来可以容纳在2GB innodb缓冲池中，因此任何合适的64位服务器都应允许这样做. alter table type = innodb也比dump + reimport解决方案更快，并且易于并行运行.

setting innodb_buffer_pool_size as high as possible is the typical way to speed up innodb tables creation - your dataset looks like it could fit inside a 2GB innodb buffer pool, so any decent 64 bits server should allow that. alter table type=innodb is also faster than dump+reimport solution, and is easy to run in parallel.

还要确保将innodb_log_file_size从默认的5Mb增加到128或256MB.小心一点，它需要彻底关闭并删除旧的ib_logfile *.

Also make sure you have increased the innodb_log_file_size from the default of 5Mb to 128 or 256MB. Careful with that, and it needs a clean shutdown + erasing the old ib_logfile*.

如果您的服务器有大约8GB的ram，并且您运行的是64位版本的mysql，我建议您使用2GB的innodb_buffer_pool，甚至可以在关闭停机之前预读旧的MYD和MYI文件，这样它们就可以当实际工作开始时，将其置于操作系统的页面缓存中.

If your server has something like 8GB of ram, and that you run a 64 bits version of mysql I would suggest a 2GB innodb_buffer_pool, and you can even preread the old MYD and MYI files before closing for downtime, so that they will be in the OS's page cache when the real work starts.

如果您还进行细微调整，请记住，在转换后(另一次停机时间较小)，您需要撤消它们以确保数据安全，但是我怀疑对于如此小的数据集，这样做是否值得.

If you also go for the minor tweaks, please keep in mind that you need to undo them after the conversion (another small downtime) to have your data safe, I doubt they are worth it for such a small dataset though.

祝你好运.

这篇关于加快从MyISAM到InnoDB的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！