InnoDB性能调优基础

本文的原文地址在此：http://www.percona.com/blog/2007/11/01/innodb-performance-optimization-basics/，以下是译文。

-----------------------------------------------------------这是一条分割线-----------------------------------------------------------

November1, 2007 by Peter Zaitsev

注意：本文的进阶知识在这：http://www.mysqlperformanceblog.com/2013/09/20/innodb-performance-optimization-basics-updated/（该文的译文在此：http://blog.csdn.net/zhu19774279/article/details/38645663）

我对那些来我们这面试的人，总喜欢问些基础的问题，如：如果你有一台16G内存的数据库服务器，用来处理常规的web应用请求，要求数据库是MySQL，表引擎是InnoDB，且数据量非常大，请问你该如何进行配置。有趣的是，大多数人都给不出什么有用的意见。所以我决定公布我想要听到的答案，包括如何调整硬件、OS、应用程序。

我把这篇文章的题目叫做“InnoDB性能调优基础”，是因为本文中的这些配置具有普适性，在多数情况下都能获得很好的效果。当然啦，如果要最好的性能，还得“具体情况具体分析”。

1. 硬件

如果你的Innodb数据库要面对非常大的数据量，那么内存大小是非常重要的因素。今天看来，16G-32G是最有效的值。2颗双核的CPU已经足够了，即使是可伸缩问题上，2颗四核的CPU也能够看到较大负载，尽管这更多的取决于程序。IO是硬件的第三要素：拥有高转速的直接存储，以及有电池备份缓存的RAID卡。通常6-8块硬盘就够了，当然有时会需要更多。请注意新的2.5″SAS硬盘，他们体积小但是更快。RAID10适用于数据存储和以读为主的情况，如果你还想要一些冗余，RAID5也可以工作的很好，但要注意随机写入RAID5。

（本人对硬件基本不了解，因此这一段的翻译本人一点把握都没有，希望路过的高人可以帮忙修改。这里列出原文：If you have large Innodb database size Memory is paramount. 16G-32G is the cost efficient value these days. From CPU standpoint2*Dual Core CPUs seems to do very well, while with even just two Quad Core CPUsscalability issues can be observed on many workloads. Though this depends onthe application a lot. The third is IO Subsystem – directly attached storagewith plenty of spindles and RAID with battery backed up cache is a good bet.Typically you can get 6-8 hard drives in the standard case and often it isenough, while sometimes you may need more. Also note new 2.5″ SAS hard drives. They are tiny but often fasterthan bigger ones. RAID10 works well for data storage and for read-mostly caseswhen you still would like some redundancy RAID5 can work pretty well as wellbut beware of random writes to RAID5.）。

2. 操作系统

首先：使用64位的操作系统。我们任然可以看见在支持64位且具有大内存的硬件上运行32位操作系统的情况。不要这么做。如果使用Linux，请为数据库目录安装LVM以获得更高效的备份。多数情况下EXT3都是很好的选择，当然如果运行在particular roadblocks，可以试试XFS。如果如果你使用了MySQL的innodb_file_per_table参数，且有很多张表，那么设置linux的noatime和nodiratime是可以小幅提升性能的。同时还要确保你的操作系统不会在内存不足的情况下，将MySQL swap out。

3.  MySQL InnoDB 配置

innodb_buffer_pool_size：参数中最重要的一项。内存的70-80%是个安全值。16G时这个值我会定为12G。附：如果你想了解更多细节，可以阅读这篇文章：http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

innodb_log_file_size：这取决于你对恢复数据的速度需求，但一般来说，256M是一个在恢复时间与高性能之间比较好的平衡点。

innodb_log_buffer_size=4M：大部分情况下，4M是很好的选择；如果要处理很大的二进制数据（large blobs），可以把这个值调大一点。

innodb_flush_log_at_trx_commit=2：如果你不是那么关注ACID，也允许在整个操作系统崩溃的情况下，丢失最后一两秒的事务，那么可以设置为2。这个值的改变有非常明显的影响，尤其是对于一些短写入事务。

innodb_thread_concurrency=8：即使当前的InnodbScalability Fixes，对于并发的限制也很有限。真实情况会比这个值略高或略低，这取决于你的应用程序，当然由8开始是不错的选择。

innodb_flush_method=O_DIRECT：避免双缓冲并降低交换（swap）的压力，多数情况下，这个配置能改善性能。要注意的是如果你没有带电池备份的RAID卡缓存，写的IO压力可能会增大。

innodb_file_per_table：如果你的表不多，可以考虑这个选项，这样你就不用担心出现无法回收的不受控制增长的主表空间。这个选项是MySQL 4.1时新加的，现在已经足够稳定。

 同样检查一下你的程序是否支持使用READ-COMMITED隔离模式运行——如果可以的话——将它设成默认值transaction-isolation=READ-COMMITTED。这个选项将带来性能的改善，尤其是5.0中的锁以及即将发布的5.1，以及行级复制。

 还有很多其他配置可以调整，但我们今天只关注和InnoDB有关的。你还可以自行阅读这两个地方：http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/，http://www.mysqlperformanceblog.com/mysql-performance-presentations/。

4.  应用程序调优

对于应用程序，尤其是以前使用MyISAM引擎的程序来说，需要进行以下调整。

首先，确保在更新操作（update）时使用事务，这样既可以保证一致性，也能够获得更好的性能。其次，如果你的程序有任何写操作，都应该要考虑可能发生的死锁。再次，请检查你的表结构，以确保他们能从InnoDB属性中获得性能改善，如：用主键聚集数据，所有索引都有主键（因此要保证主键短），用主键进行快速查找（试着使用join），大的未压缩的索引（使主键尽可能的简单。）（这一段话不太懂：clustering by primary key, having primary key in all indexes (sokeep primary key short), fast lookups by primary keys (try to use it in joins),large unpacked indexes(try to be easy on indexes).）

通过以上配置，能保证你的数据库的性能比大多数使用MySQL默认配置、没有电池备份缓存、不优化OS、应用程序，并使用MyISAM的人的要好。

-----------------------------------------------------------这是一条分割线-----------------------------------------------------------

原文写于2007.11.1，因此里面有些内容会看起来比较奇怪， 比如原文中有“即将发布的5.1”等语句。

本人英语水平一般，对Linux、硬件、MySQL了解也都非常有限，错误在所难免，还望路过的诸位看客可以不吝指正。