mysql - InnoDB 性能调整

Question

我正在将一个大表从 myISAM 切换到 InnoDB。关于切换为何有意义的讨论很多，但关于如何在确保表性能良好的情况下进行切换的讨论不多。

假设我在数据库中有 InnoDB 和 MyISAM 表，是否应该更改 MySQL conf 文件中的参数以促进更好的性能？

可以提高任何其他默认值以调整性能吗？

Answer 1

您的innodb_buffer_pool_size应设置为您拥有的 InnoDB 数据和索引的数量。运行这个查询，它会告诉你 MySQL 当前 Innodb 数据的最小推荐设置

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHERE
engine='InnoDB') A,(SELECT 3 pw) B;

如果您的 InnoDB 数据远远超过 DB 服务器上安装的 RAM，我建议在盒子上安装 RAM 的 75%。因此，如果您有 16GB 服务器，请使用 12G 作为 innodb_buffer_pool_size。

您还必须将 innodb_log_file_size 设置为 innodb_buffer_pool_size 的 25% 或 2047M，以较小者为准。要更改文件 ib_logfile0 和 ib_logfile1，您必须:

mysql -uroot -p -e"SET GLOBAL innodb_fast_shutdown = 0;"
service mysql stop
rm ib_logfile0 ib_logfile1
service mysql start

如果您使用的是 MySQL 5.5，请设置以下内容:

innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=20000 (set this to your device's IOPs)

如果您要保留 MyISAM 数据，请运行此查询以获得 key_buffer_size 的理想设置:

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_key_buffer_size
FROM (SELECT SUM(index_length) KBS FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A,
(SELECT 3 pw) B;

更新 2013-02-13 12:55 EDT

我最近学会了不要设置 innodb_io_capacity非常高，如果有的话。在商用硬件和 VM 上尤其如此:

• http://www.mysqlperformanceblog.com/2012/02/17/the-relationship-between-innodb-log-checkpointing-and-dirty-buffer-pool-pages/
• http://www.mysqlperformanceblog.com/2010/12/20/mysql-5-5-8-and-percona-server-being-adaptive/