Mysql配置参数innodb_buffer_pool_size的学习与整理(配合之前文章配置数据库)

最新推荐文章于 2024-10-04 00:42:13 发布

Houzhyan

最新推荐文章于 2024-10-04 00:42:13 发布

阅读量1.4k

点赞数

分类专栏： mysql 文章标签： Mysql配置参数 innodb_buffer_pool_s SQL插入性能优化 mysql MYSQL千万级

mysql 专栏收录该内容

62 篇文章 0 订阅

订阅专栏

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

原文地址：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之后改进了恢复性能和日志文件恢复的代价。