Mysql配置参数innodb_buffer_pool_size的学习与整理

原创
2015/01/15 17:46
阅读数 10.1W

    原文地址:Mysql配置参数innodb_buffer_pool_size的学习与整理

    这半个月来,一直在做一些关于服务器交易端性能的提升工作,主要是分析和讨论交易端性能的瓶颈,找出导致性能减慢的原因,拟定出合理的解决方案,主要是通过几个方面进行研究和学习,今天总算有了一点点突破,主要是涉及mysql核心参数innodb_buffer_pool_size的学习和讨论,这里简单的整理和总结一下。
   

    首先简单的介绍一下服务器交易端的环境,使用的是Spring + MyBatis + Mysql的架构,代码中使用了Spring声明式事务进行管理,性能的瓶颈主要是存在于事务提交上面,通过测试和分析日志,发现代码在进行事务提交时耗时比较严重,这半个月的研究也走了许多弯路,总算有得有失,对于其中的一些知识也有了一定的理解,主要是包含下面几个方面:
    MyBatis

    第一点想到的可能原因是MyBatis对Connection的管理,由于对MyBatis的理解不深入,简单的分析日志,发现MyBatis日志中出现大量的create SqlSession,于是简单的研究了一下MyBatis SqlSession,这里有一篇总结的文章:关于MyBatis sqlSession的一点整理

    通过研究讨论和查阅源码,对MyBatis SqlSession有了一定的理解,发现问题可能不再这里。

     Mysql

    Mysql的配置是怀疑导致性能瓶颈的原因,不过没有证据证明,同事提到了thread_concurrency参数,于是开始了mysql配置参数的学习,但是mysql的配置参数很多,短时间内无法做到面面俱到,于是通过分析线上运行的mysql的配置文件进行学习,这里也整理出了几篇文章:

  关于Mysql thread_concurrency和innodb_thread_concurrency参数的一点整理 

  Mysql一些重要配置参数的学习与整理(一) 

  Mysql一些重要配置参数的学习与整理(二) 

  Mysql一些重要配置参数的学习与整理(三)

    通过对线上mysql一些配置参数的学习和讨论,感觉这些所了解到的参数的配置较为合理,于是研究的重心发生了偏移,转向了Spring事务管理机制的研究。
    Spring

    Spring的事务管理机制,自己的理解也不清晰,对于源码也没有过认真的研读,于是花费了点时间对Spring Transaction事务管理机制和MyBatis SqlSession进行了再次的学习和研究讨论,也算有了一点点收获,期间也做了总结和整理,并根据自己的理解做了时序图,详情见这里:Spring Transaction + MyBatis SqlSession事务管理机制研究学习

    但是问题又来了,通过与同事的讨论和阅读源码,更加的迷惑了,好像性能的瓶颈也不是Spring Transaction的问题。不知道接下来该怎么突破了,一次偶然的与同事交流,出现了转机,在本机进行测试时,发现我本机的性能要明显优于同事的电脑,那么问题来了,是什么导致了性能的差异?

    首先是硬件的不同,虽然内存一致,但是CPU版本却完全不同,CPU的差异是导致性能的一部分原因,这是第一个突破点;其次是本地mysql配置的不同,发现我本地使用的是mysql默认生成的配置,同事本地使用的是服务器上的mysql配置,第二个突破点就是mysql配置的差异,主要是参数的不同,于是重心又转移到了mysql配置文件的研究学习讨论上面。

    Mysql   

    中间出现的曲折就不说了,说多了都是泪,结果反复的测试,发现在增大或减小一个参数的值时,性能差异明显,经过与同事讨论和测试,发现这个参数在高并发高I/O时正确的配置非常重要,可能带来很大的性能提升,这个参数就是innodb_buffer_pool_size,下面,就来详细的学习和整理一下这个参数的意义。       

    innodb_buffer_pool_size参数表示缓冲池字节大小,InnoDB缓存表和索引数据的内存区域。mysql默认的值是128M。最大值与你的CPU体系结构有关,在32位操作系统,最大值是 4294967295 (2^32-1) ,在64 位操作系统,最大值为18446744073709551615 (2^64-1)。在32位操作系统中,CPU和操作系统实用的最大大小低于设置的最大值。如果设定的缓冲池的大小大于1G,设置innodb_buffer_pool_instances的值大于1,在服务器繁忙的时候可以提高伸缩性,不过在实际的测试中,发现带来的性能提升并不明显,而且参考了这里的一篇文章mysql优化---第7篇:参数 innodb_buffer_pool_instances设置,初步设置innodb_buffer_pool_instances为1。

    这个值设置的越大,在不止一次的访问相同的数据表数据时,消耗的磁盘I / O就越少。在一个专用的数据库服务器,则可能将其设置为高达80%机器物理内存大小。不过在实际的测试中,发现无限的增大这个值,带来的性能提升也并不显著,对CPU的压力反而增大,设置合理的值才是最优。在出现以下问题时,你就需要考虑减少这个参数的值了:

  • 物理内存的竞争可能会导致操作系统分页。

  • InnoDB储备额外的内存缓冲区和控制结构,以便总分配空间大于指定的大小大约是10%。

  • 地址空间必须是连续的在通过DLL加载特定地址的Windows系统中,这可能存在问题。

  • 初始化缓冲池的时间大致与它的大小成正比。在大型系统中初始化的时间可能很显著。例如:在现代化的Linux x86_64服务器上,初始化一个10GB的缓冲池大小,大约需要6秒钟。


    在MySQL 5.7.5版本后,innodb_buffer_pool_size参数的值可以动态的设置,这意味着你可以在不启动服务器的情况下,重新设置缓冲区的大小。这种调整的操作是按块执行的。可以通过innodb_buffer_pool_chunk_size参数配置块的大小。Innodb_buffer_pool_resize_status状态变量记录了从调整操作的状态。


    同样的,在mysql的众多参数中,关系到磁盘IO的参数还有两个,分别是:innodb_log_buffer_size和innodb_log_file_size。      

    innodb_log_buffer_size表示InnoDB写入到磁盘上的日志文件时使用的缓冲区的字节数,默认值为8M。一个大的日志缓冲区允许大量的事务在提交之前不写日志到磁盘。因此,如果你有很多事务更新,插入或删除很操作,通过这个参数会大量节省了磁盘I / O。

    innodb_log_file_size表示一个日志组每个日志文件的字节大小。日志文件的总大小innodb_log_file_size* innodb_log_files_in_group不能超过最高512GB。例如一对255 GB的日志文件,已经接近了极限,不能超过。默认值是48M。比较合适值的范围是从1MB到1 / N个缓冲池大小其中N是该组中的日志文件的数量该值越大缓冲池中必要的检查点刷新活动就会越少,节省磁盘I/ O。但是越大的日志文件,mysql的崩溃恢复就越慢,尽管在mysql5.5之后改进了恢复性能和日志文件恢复的代价。

    上面的两个参数,并没有进行mysql性能的测试,也并没有特定的进行配置,下一步,会仔细的研究测试一下,敬请期待。

展开阅读全文
加载中

作者的其它热门文章

打赏
3
18 收藏
分享
打赏
1 评论
18 收藏
3
分享
返回顶部
顶部