问题描述
我正在将一个大表从myISAM切换到InnoDB.关于切换为何有意义的讨论很多，但如何确保表格表现良好却没有太多讨论.
I'm switching a large table to InnoDB from myISAM. There has been a lot of discussion regarding why switching makes sense, but not much about HOW to do it while making sure the table performs well.
假设我将在数据库中包含InnoDB和MyISAM表，那么在MySQL conf文件中是否应该更改参数以提高性能?
Assuming I'll have InnoDB and MyISAM tables in on database, are there parameters I should change in the MySQL conf file to facilitate better performance?
还有其他默认设置可以提高性能吗?
Any other defaults that can be bumped up to tweak performance?
推荐答案
您的 innodb_buffer_pool_size 应该设置为您拥有的InnoDB数据和索引的数量.运行此查询，它将告诉您mysql当前Innodb数据的最低推荐设置
Your innodb_buffer_pool_size should be set to the amount of InnoDB data and indexes you have. Run this query and it will tell you the Minimum recommended setting for mysql's current Innodb Data
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数据远远超出了数据库服务器上已安装的RAM，我建议包装盒上已安装RAM的75％.因此，如果您有16GB的服务器，请使用12G作为innodb_buffer_pool_size.
If your InnoDB data far exceeds the installed RAM on the DB server, I recommend 75% of the installed RAM on the box. So, if you have a 16GB server, use 12G as the innodb_buffer_pool_size.
还必须将innodb_log_file_size设置为innodb_buffer_pool_size的25％或2047M，以较小者为准.要更改文件ib_logfile0和ib_logfile1，您必须:
You must also set innodb_log_file_size to 25% of innodb_buffer_pool_size or 2047M, which ever is smaller. To change the file ib_logfile0 and ib_logfile1, you must:
mysql -uroot -p -e"SET GLOBAL innodb_fast_shutdown = 0;"
service mysql stop
rm ib_logfile0 ib_logfile1
service mysql start
如果您使用的是MySQL 5.5，请设置以下内容:
If you are using MySQL 5.5, set the following:
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的理想设置运行此查询:
If you will retain MyISAM data run this query for the ideal setting for 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上尤其如此:
UPDATE 2013-02-13 12:55 EDT
I have learned lately not to set innodb_io_capacity very high, if at all. This is especially true on commodity hardware and VMs:
- 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/
- 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/
这篇关于InnoDB性能调整的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！