InnoDB性能调优基础（进阶）

https://blog.csdn.net/zhu19774279/article/details/38645663

硬件

今天，大数据集（largedatasets）的内存一般都是数以百G，即使是T级别也没什么好惊讶。MySQL需要足够多的内存，以提供最佳性能，如缓存热点数据、索引、大量变化（ongoing changes）。InnoDB有更短的响应时间，并能以更合理的方式利用磁盘IO。从CPU来看，当然是核越多、速度越快越好。32/64核的CPU已经越来越普遍，最新版本的MySQL在对多核CPU的支持上也比以前的版本要好得多。再来看存储，SSD正在取代传统的机械硬盘，并获得了巨大的成功，当然这是用钱来换最好的性能。RAID 10仍然是多数情况下的首选，但要确保你的RAID控制器能够很好的支持SSD硬盘，以免成为瓶颈。当然如果你对IOPS有很高的需求，可以选择多个PCI-e Flash卡。

操作系统

Linux是最常见的高性能MySQL的服务器。请确保使用最新的内核和现代的文件系统，如EXT 4或XFS。两种文件系统都有各自的优缺点：如XFS删大文件时比较快，EXT4却能让SSD硬盘有更好的性能。选择权在你手上。这篇文章（www.ssdperformanceblog.com/2013/04/testing-the-micron-p320h/）（很奇怪这个网站用Firefox、chrome或IE都打不开，翻墙也打不开，实在不知道什么原因——译者注）告诉你如何让EXT4比XFS有更好的性能。当你的表特别多，而你又使用了innodb_file_per_table参数的时候，noatime和nodiratime将会给你带来一点点的性能提升。Linux的默认I/O调度程序是CFQ（Completely Fair Queuing），实际上多数情况下Noop/Deadline有更好的性能。对于MySQL专用服务器，将swappiness设为0是一个不错的选择，因为这可以降低swapping的延迟。要确保MySQL的主机不会出现内存不足的情况。swapping对MySQL是有害的，并且会阻止内存中的缓存。想了解更多有关swapping的知识，可以阅读这篇文章（www.mysqlperformanceblog.com/2008/04/06/should-you-have-your-swap-file-enabled-while-running-mysql/）。

MySQL InnoDB 配置

从5.5开始，InnoDB是默认的引擎，所以这些参数能够比以前更好的优化性能。最重要的是以下这些：

    innodb_buffer_pool_size：缓冲池对InnoDB的影响很大，一定要配置正确，因此要给这个值分配足够的内存。通常是可用内存的70%-80%。更确切地说，如果你的内存比数据集大那么一点，那么让这个值比数据库的大小大一点最好，当然你需要注意数据库大小的增长，并经常调整innodb缓冲池的大小，使二者大小保证一致。（这句话没看太懂，原文是：More precisely, if you have RAM bigger than your dataset setting itbit larger should be appropriate with that keep in account of your databasegrowth and re-adjust innodb buffer pool size accordingly.）如果你用的是PerconaServer 5.1或Percona Server 5.5，那么在代码级别对InnoDB的缓冲有优化，相关知识可以看这篇文章http://www.percona.com/doc/percona-server/5.5/scalability/innodb_split_buf_pool_mutex.html。

    innodb_buffer_pool_instances：InnoDB1.1和MySQL5.5中介绍了多Innodb缓冲池。5.5中这个值默认大小是1，而5.6中是8。这个参数的取值范围是1-64。在高并发的任务中允许使用innodb_buffer_pool_instances会非常有效，因为它能够减少全局互斥锁（global mutexes）的存在。（这个参数在CentOS版的MySQL-5.5.39的配置文件中没有，不知原文作者说的5.5中的默认值哪来的——译者注）

    Dump/Restore Buffer Pool：这个参数能够在重启时，提高保存、恢复缓冲池内容的速度。这个特性最早出现在Percona Server 5.5中，可以通过这两篇文章做进一步了解（http://www.percona.com/doc/percona-server/5.5/management/innodb_lru_dump_restore.html?id=percona-server:features:innodb_lru_dump_restore，http://www.mysqlperformanceblog.com/2010/01/20/xtradb-feature-save-restore-buffer-pool/）。MySQL 5.6中也加入了该特性，要在数据库启动或关闭时自动dump数据，需要将innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup设置为ON。

    innodb_log_file_size：足够大的InnoDB事务日志，对于稳定的、高质量写性能至关重要。但巨大的日志文件同样意味着崩溃以后的恢复会很慢。当然，在5.5进行巨大改进以后，这一点已经不再是经常争论的重点。MySQL 5.6将默认值从5MB调整到50MB，但这个值对于很多情况依然太小。另外，在MySQL 5.6中，如果这个参数值被改变，那么MySQL将会在重启的时候自动调整大小。在MySQL 5.6中，合并后的日志文件大小从4G调整到512G。有关日志最佳大小的问题，可以阅读这篇文章（http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/）

    innodb_log_buffer_size：InnoDB的写操作，将数据写入到内存中的日志缓存中，由于InnoDB在事务提交前，并不将改变的日志写入到磁盘中，因此在大事务中，可以减轻磁盘I/O的压力。通常情况下，如果不是写入大量的超大二进制数据（a lot of huge blobs），4MB-8MB已经足够了。

    innodb_flush_log_at_trx_commit：这个值设为1时，将会在每次日志提交时，将日志缓冲（log buffer）中的内容刷入硬盘，以确保数据最大限度的完整性，当然这样也会对性能带来影响。如果设为2，表示每次日志提交时，仅仅将日志缓冲中的内容刷入操作系统的文件缓存。如果你对ACID不那么关注，并且对于OS崩溃前最后一两秒的事务丢失不那么在意的话，设为2将带来更好的性能。

    innodb_thread_concurrency：随着InnoDB的改善，现在推荐大家使用默认值0，即由InnoDB引擎自己控制并发。如果你看到并发相关的讨论，可以调整这个参数。推荐值的简单计算方法是（CPU的数量+硬盘数量）*2(2 times the number of CPUs plus the number of disks)。这是一个动态变量，这意味着改变这个值不用重启MySQL即可生效。

    innodb_flush_method：DIRECT_IO能减轻I/O的压力。直接I/O不进行缓存，如果设置为O_DIRECT可以避免缓冲池与文件系统的双缓冲，但是需要有RAID控制器以及电池备份的写缓存。（原文不是这样，但原文没看太懂Given that you have hardware RAID controller and battery-backedwrite cache.）

    innodb_file_per_table：从MySQL 5.6开始，这个值默认是ON。这个是默认值是因为可以避免产生巨大的共享表空间，并且可以在drop和truncate一张表的时候，迅速释放空间。独立的表空间同样在Xtrabackup的部分备份时有优势。

除了以上这些，Percona Server 5.5和MySQL 5.6对InnoDB有很多增强。在Percona Server 5.5的XtraDB中加入了Persistentoptimizer statistics，通过innodb_use_sys_stats_table参数控制，相关知识可以阅读这篇文章（http://www.percona.com/doc/percona-server/5.5/diagnostics/innodb_stats.html?id=percona-server:features:innodb_stats&redirect=1#innodb_use_sys_stats_table），在MySQL 5.6中也引入了这个特性。在MySQL5.6中，使用两个新表mysql.innodb_index_stats和mysql.innodb_table_stats来存储persistentstats。通过这个，在查询（query）的时候，能够保证更精确和一致性。更多细节请阅读文档这里http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-persistent-stats。同样PerconaServer 5.5里介绍了来自于线程池的特性，可以阅读这个官方文档http://www.percona.com/doc/percona-server/5.5/performance/threadpool.html。另外线程池的知识还可以还可以阅读这里http://www.mysqlperformanceblog.com/2013/03/16/simcity-outages-traffic-control-and-thread-pool-for-mysql/。

PerconaServer是免费且开源的。是在MySQL基础上修改的，上面有的特性只有Percona Server才有。

还有很多参数可以调整，但是在这篇文章我们只关注InnoDB。

为InnoDB优化应用程序

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

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

总结

本文几乎覆盖了所有和MySQL性能有关的参数，包括InnoDB的基础或进阶参数、OS的参数和硬件配置。通过这些修改，可以确保所有MySQL服务器的性能都得到优化。

InnoDB性能调优基础（进阶）

评论已关闭

最新文章

分类

归档