mysql缓冲池大小_InnoDB 缓冲池大小调整

原标题：InnoDB 缓冲池大小调整

导读

翻译者：魏新平

译文校稿：叶师傅

原文出处：《Chunk Change: InnoDB Buffer Pool Resizing》

https://www.percona.com/blog/2018/06/19/chunk-change-innodb-buffer-pool-resizing/

原文作者：David Ducos

关键词：InnoDB, Insight for DBAs, MySQL, open source databases, Percona Server for MySQL buffer pool, InnoDB buffer pool, InnoDB buffer pool size, InnoDB Performance, memory, Memory

从MySQL 5.7.5开始，我们可以动态修改InnoDB Buffer Pool的大小。这个新特性同时也引入了一个参数--innodb_buffer_pool_chunk_size，buffer pool会根据这个参数值的整数倍增加或减小。这个参数不是动态修改的，如果配置错误，可能会导致不想看到的结果。

Since MySQL 5.7.5, we have been able to resize dynamically the InnoDB Buffer Pool. This new feature also introduced a new variable — innodb_buffer_pool_chunk_size — which defines the chunk size by which the buffer pool is enlarged or reduced. This variable is not dynamic and if it is incorrectly configured, could lead to undesired situations.

首先我们观察一下innodb_buffer_pool_size , innodb_buffer_pool_instances and innodb_buffer_pool_chunk_size如何相互影响。buffer pool可以存放多个instance，每个instance由多个chunk组成。instance的数量范围和chunk的总数量范围分别为1-64，1-1000。

Let’s see first how innodb_buffer_pool_size , innodb_buffer_pool_instances and innodb_buffer_pool_chunk_size interact:

The buffer pool can hold several instances and each instance is divided into chunks. There is some information that we need to take into account: the number of instances can go from 1 to 64 and the total amount of chunks should not exceed 1000.

一个3G内存的服务器，128MB的chunk值，2GB的buffer pool，8个instance，那么每个instance就有2个chunk。

So, for a server with 3GB RAM, a buffer pool of 2GB with 8 instances and chunks at default value (128MB) we are going to get 2 chunks per instance:

这意味着一共有16个chunks。 This means that there will be 16 chunks.

本文只关注修改buffer pool大小的影响，所以不会阐述多个instance的好处。那为什么要修改buffer pool的大小呢？有多个理由，比如：

I’m not going to explain the benefits of having multiple instances, I w