【MySQL】MySQL优化-innodb_buffer_pool_size

王大熊爱挠门

已于 2023-04-12 09:59:41 修改

阅读量1.5k

点赞数 3

分类专栏：【数据库】文章标签： mysql 数据库服务器

于 2023-04-07 16:01:11 首次发布

本文链接：https://blog.csdn.net/Ankie_/article/details/130011666

版权

4 篇文章 0 订阅

订阅专栏

优化业务背景
1. 服务中抽取中存在数据较大事物，每次抽取一次的数量可能超过40M左右的数据在事物中，可能会更多，导致MySQL不支持报错。
2. 有时也可能因为事物导致该数据始终保持加锁状态。因为MySQL使用的版本为5.7.1，默认的innodb_buffer_pool_size为128M，导致出现性能问题。

指代的一个内存区域，用来缓存InnoDB存储引擎的表中的数据和索引数据，用来提高对InnoDB存储引擎表中的数据查询和访问速度；
该参数以字节(byte)为单位，我自己笔记本的MySQL中的默认是8388608byte，也就是8MB。正常服务器上的默认的都是128M，并且在MySQL5.7.5版本之后，可以在不重启MySQL服务的情况下，动态修改这个参数的值。
默认情况下：innodb_buffer_pool_chunk_size和innodb_buffer_pool_size相同，都为
134217728字节，即 128MB。可以理解innodb_buffer_pool_chunk_size是innodb_buffer_pool_size的最小单位。也就是设定innodb_buffer_pool_size这个值即可。
默认情况下：innodb_buffer_pool_instances为1，取值范围为[1, 64]，当 innodb_buffer_pool_instances不为1的时候，表示需要启用多个缓冲池实例，即把整个 innodb_buffer_pool_size在逻辑上划分为多个缓存池，多实例可以提高并发性，可以减少不同线程读写缓存页面时的争用。参数 innodb_buffer_pool_instances的修改，需要重启MySQL数据库服务，不能再数据库服务运行的过程中修改。
innodb_buffer_pool_size = innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size * x

innodb_buffer_pool_instances即表示缓存区实例，缓存区本身是一块内存空间，在多线程并发访问缓存的情况下，为了保证缓存页数据的正确性，可能会对缓存区单实例锁互斥访问，如果缓存区非常大并且多线程并发访问非常高的情况下，单实例缓存区的可能会影响请求的处理速度。
InnoDB学习（一）之BufferPool，该文章里面介绍了缓存区实例应对QPS较高的时候，MySQL服务器的多实例可以避免请求争抢互斥锁的问题。

chunk的数目最多到1000，instance的数目最多到64。一个 instance里面可以有一个或多个 chunk，到底有几个 chunk，取决于 chunk的大小是多少。在 buffer pool大小一定的情况下， instance数目一定的情况下，每一个 instance下面放多少给 chunk，取决于每一个 chunk的大小。例如： buffer pool大小设置为 4GB， instance的值设置为 8，那么 chunk的值可以设置为默认的 128MB，也可以设置为 512MB，也可以设置为 256MB。
4GB=8*128MB*4，此时 chunk的值为 128MB,每个 instance里有 4个 chunk
4GB=8*512MB*1，此时 chunk的值为 512MB,每个 instance里有 1个 chunk
4GB=8*256MB*2，此时 chunk的值为 256MB,每个 instance里有 2个 chunk

结合我自己的业务需求，进行参数调节：

并发较高，可增大innodb_buffer_pool_instances实例，用于应对并发互斥，但需保证：
innodb_buffer_pool_size / innodb_buffer_pool_instances > 1GB
配置innodb_buffer_pool_size需要考虑服务器内存，一般占用服务器的50%~75%：
1G或以下：如果系统内存在，不需要调整，
1G-32G：可以看服务器的占用情况，可以使用公式：256MB+256*log2(RAM大小GB)
32G以上：可以按照占用服务器的50%~75%来计算。
Mysql优化之innodb_buffer_pool_size篇，该文档中第四部分讲了innodb的缓冲池的性能：
Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100
即计算得出的大部分请求，看从磁盘完成读取的占比有多少，如果非常小，那表示没有必要增加innodb_buffer_pool_size的值。