InnoDB性能调优

官方账号 2022-05-0712:25:30技能笔记评论30字数 4868阅读16分13秒阅读模式

1.      硬件文章源自玩技e族-https://www.playezu.com/134610.html

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

1.      操作系统文章源自玩技e族-https://www.playezu.com/134610.html

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/)。文章源自玩技e族-https://www.playezu.com/134610.html

2.      MySQL InnoDB 配置文章源自玩技e族-https://www.playezu.com/134610.html

从5.5开始,InnoDB是默认的引擎,所以这些参数能够比以前更好的优化性能。最重要的是以下这些:文章源自玩技e族-https://www.playezu.com/134610.html

        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文章源自玩技e族-https://www.playezu.com/134610.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中的默认值哪来的——译者注文章源自玩技e族-https://www.playezu.com/134610.html

        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。文章源自玩技e族-https://www.playezu.com/134610.html

        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/文章源自玩技e族-https://www.playezu.com/134610.html

        innodb_log_buffer_size:InnoDB的写操作,将数据写入到内存中的日志缓存中,由于InnoDB在事务提交前,并不将改变的日志写入到磁盘中,因此在大事务中,可以减轻磁盘I/O的压力。通常情况下,如果不是写入大量的超大二进制数据(a lot of huge blobs),4MB-8MB已经足够了。文章源自玩技e族-https://www.playezu.com/134610.html

        innodb_flush_log_at_trx_commit:这个值设为1时,将会在每次日志提交时,将日志缓冲(log buffer)中的内容刷入硬盘,以确保数据最大限度的完整性,当然这样也会对性能带来影响。如果设为2,表示每次日志提交时,仅仅将日志缓冲中的内容刷入操作系统的文件缓存。如果你对ACID不那么关注,并且对于OS崩溃前最后一两秒的事务丢失不那么在意的话,设为2将带来更好的性能。文章源自玩技e族-https://www.playezu.com/134610.html

        innodb_thread_concurrency:随着InnoDB的改善,现在推荐大家使用默认值0,即由InnoDB引擎自己控制并发。如果你看到并发相关的讨论,可以调整这个参数。推荐值的简单计算方法是(CPU的数量+硬盘数量)*2(2 times the number of CPUs plus the number of disks)。这是一个动态变量,这意味着改变这个值不用重启MySQL即可生效。文章源自玩技e族-https://www.playezu.com/134610.html

        innodb_flush_method:DIRECT_IO能减轻I/O的压力。直接I/O不进行缓存,如果设置为O_DIRECT可以避免缓冲池与文件系统的双缓冲,但是需要有RAID控制器以及电池备份的写缓存。(原文不是这样,但原文没看太懂Given that you have hardware RAID controller and battery-backedwrite cache.)文章源自玩技e族-https://www.playezu.com/134610.html

        innodb_file_per_table:从MySQL 5.6开始,这个值默认是ON。这个是默认值是因为可以避免产生巨大的共享表空间,并且可以在drop和truncate一张表的时候,迅速释放空间。独立的表空间同样在Xtrabackup的部分备份时有优势。文章源自玩技e族-https://www.playezu.com/134610.html

 除了以上这些,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/文章源自玩技e族-https://www.playezu.com/134610.html

PerconaServer是免费且开源的。是在MySQL基础上修改的,上面有的特性只有Percona Server才有。文章源自玩技e族-https://www.playezu.com/134610.html

还有很多参数可以调整,但是在这篇文章我们只关注InnoDB。文章源自玩技e族-https://www.playezu.com/134610.html

3.      为InnoDB优化应用程序文章源自玩技e族-https://www.playezu.com/134610.html

对于应用程序,尤其是以前使用MyISAM引擎的程序来说,需要进行以下调整。文章源自玩技e族-https://www.playezu.com/134610.html

首先,确保在更新操作(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).)文章源自玩技e族-https://www.playezu.com/134610.html

4.      总结文章源自玩技e族-https://www.playezu.com/134610.html

本文几乎覆盖了所有和MySQL性能有关的参数,包括InnoDB的基础或进阶参数、OS的参数和硬件配置。通过这些修改,可以确保所有MySQL服务器的性能都得到优化。文章源自玩技e族-https://www.playezu.com/134610.html

注意:本文法律责任由该文章作者承担,侵权请联系2523030730▷诈骗举报◁▷新闻不符◁▷我要投稿◁
  • 我们QQ群
  • QQ扫一扫
  • weinxin
  • 微信公众号
  • 公众号扫一扫
  • weinxin
官方账号