MySQL优化（一）innodb_buffer_pool 引擎缓存的配置

深藏阁楼爱情的钟 2022-12-13 09:58 665阅读 0赞

# MySQL优化（一）innodb\_buffer\_pool 引擎缓存的配置 #

## 为什么要用缓存 ##

在数据库中，数据都是最终落到磁盘上的，磁盘读写和内存读写的速度不在一个数量级，想要达成快速的读写，必然要依靠缓存技术。

Innodb的这个缓存区就是Innodb\_buffer\_pool，当读取数据时，就会先从缓存中查看是否数据的页（page）存在，不存在的话才去磁盘上检索，查到后缓存到这个pool里。同理，插入、修改、删除也是先操作缓存里数据，之后再以一定频率更新到磁盘上。控制刷盘的机制，叫做`Checkpoint`。

## 初识innodb\_buffer\_pool ##

**查看缓存大小**

mysql安装后，默认pool的大小是128M

> show variables like ‘innodb\_buffer\_pool\_size’;

我这里是已经调整过了，显示536870912，这个单位是Byte(字节)，换算成MB是512MB。`1024Byte(字节)=1KB,1024KB=1MB`

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjA2MDEw_size_16_color_FFFFFF_t_70_pic_center]  
**查看缓存相关信息**

> show variables like ‘innodb\_buffer\_pool%’;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjA2MDEw_size_16_color_FFFFFF_t_70_pic_center 1]

## 详解 innodb\_buffer\_pool ##

**三个参数：**

`innodb_buffer_pool_size`（缓冲池大小）

`innodb_buffer_pool_chunk_size`（定义InnoDB缓冲池大小调整操作的块大小）

`innodb_buffer_pool_instances`（InnoDB 缓冲池划分为的区域数）

**关于几个参数的关系，在 MySQL5.7 的官方文档中：**  
[MySQL5.7 Reference Manual][]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjA2MDEw_size_16_color_FFFFFF_t_70_pic_center 2]

**翻译为：**

> innodb\_buffer\_pool\_size必须始终等于innodb\_buffer\_pool\_chunk\_size或 innodb\_buffer\_pool\_instances的倍数。如果将缓冲池大小更改为不等于innodb\_buffer\_pool\_chunk\_size 或 innodb\_buffer\_pool\_instances的倍数，则缓冲池大小将自动调整为等于innodb\_buffer\_pool\_chunk\_size 或 innodb\_buffer\_pool\_instances的倍数。

**所以得到第一个结论：**

1、innodb\_buffer\_pool\_size必须为 innodb\_buffer\_pool\_instances 的倍数。

**再继续看官方文档关于innodb\_buffer\_pool\_instances参数的说明：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjA2MDEw_size_16_color_FFFFFF_t_70_pic_center 3]  
通过这一部分的内容，我们可以知道 innodb\_buffer\_pool\_instances参数的功能 是：

*  对于缓冲池在数千兆字节范围内的系统，通过减少争用不同线程对缓存页面进行读写的争用，将缓冲池划分为多个单独的实例可以提高并发性。
 *  使用散列函数将存储在缓冲池中或从缓冲池读取的每个页面随机分配给其中一个缓冲池实例。每个缓冲池管理自己的空闲列表， 刷新列表， LRU和连接到缓冲池的所有其他数据结构，并受其自己的缓冲池互斥量保护。

除32位Windows系统的其它所有平台上innodb\_buffer\_pool\_instances参数的默认值为：

*  innodb\_buffer\_pool\_size<1G时，默认值为1；
 *  innodb\_buffer\_pool\_size>1G时，默认值为8。

32位Windows系统上的默认值取决于的值 innodb\_buffer\_pool\_size，如下所述：

*  如果 innodb\_buffer\_pool\_size 大于1.3GB，则默认 innodb\_buffer\_pool\_instances 值为 innodb\_buffer\_pool\_size/ 128MB，每个块都有单独的内存分配请求。选择1.3GB作为边界，在该边界处32位Windows无法分配单个缓冲池所需的连续地址空间存在很大风险。否则，默认值为1。

**所以可得第二个结论：**

2、`innodb_buffer_pool_instances`对提高系统并发有作用，`instances`大小与操作系统有关，在64位windows系统 和 Linux系统中，当`innodb_buffer_pool_size`大于或等于1GB 时，默认值为8 。否则，默认值为1。

**那究竟如何设置才能更好优化MySQL缓冲池的性能？ 继续看官方文档：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjA2MDEw_size_16_color_FFFFFF_t_70_pic_center 4]

> 对于具有多个gb范围的缓冲池的系统，将缓冲池划分为单独的实例可以提高并发性，这可以减少不同线程读写缓存页面时的争用。该特性通常用于缓冲池大小在多个gb范围的系统。使用innodb\_buffer\_pool\_instances配置选项配置多个缓冲池实例，还可以调整innodb\_buffer\_pool\_size值。

> 当InnoDB缓冲池很大时，许多数据请求可以通过从内存中检索来满足。您可能会遇到多个线程同时访问缓冲池的瓶颈。您可以启用多个缓冲池来最小化这种争用。使用散列函数，将存储在缓冲池中或从缓冲池中读取的每个页面随机分配给其中一个缓冲池。每个缓冲池管理自己的空闲列表、刷新列表、LRUs和连接到缓冲池的所有其他数据结构，并由自己的缓冲池互斥锁保护。

> 要启用多个缓冲池实例，请将innodb\_buffer\_pool\_instances配置选项设置为大于1(默认值)的值，最多为64(最大值)。这个选项只在将innodb\_buffer\_pool\_size设置为1GB或1GB以上时生效。指定的总大小在所有缓冲池中划分。为了获得最佳效率，请指定innodb\_buffer\_pool\_instances和innodb\_buffer\_pool\_size的组合，以便每个缓冲池实例至少为1GB。

**所以可得第三个结论：**

3、innodb\_buffer\_pool\_instanes的值最大为64，innodb\_buffer\_pool\_instances 和 innodb\_buffer\_pool\_size的组合，每个缓冲池实例至少为1GB。如果你设置的pool的size超过了1G的话，应该再修改一下`Innodb_buffer_pool_instances=N`，将pool分成N个pool实例。

这样mysql操作的数据会按照page的hash来映射到不同的pool实例，可以大幅优化多线程情况下，并发读取同一个pool造成的锁的竞争。

## innodb\_buffer\_pool 配置与优化 ##

理论上来说，如果你给pool的内存足够大，够装下所有数据，要访问的所有数据都在pool里，那么你的所有请求都是走内存，性能将是最好的，和redis类似。

官方建议pool的空间设置为物理内存的`50%-75%`。

**调优参考计算方法：**

> val = Innodb\_buffer\_pool\_pages\_data / Innodb\_buffer\_pool\_pages\_total \* 100%

*  val > 95% 则考虑增大 innodb\_buffer\_pool\_size， 建议使用物理内存的75%
 *  val < 95% 则考虑减小 innodb\_buffer\_pool\_size， 建议设置为：Innodb\_buffer\_pool\_pages\_data \* Innodb\_page\_size \* 1.05 / (1024*1024*1024)

### 手动配置my.ini文件 ###

[MySQL Server 5.7安装目录找不到 my.ini 配置文件][MySQL Server 5.7_ my.ini]  
`修改后需要重启mysql服务`

> \#设置512M  
> innodb\_buffer\_pool\_size = 536870912  
> \#设置512M  
> innodb\_buffer\_pool\_size = 512M  
> \#设置实例个数  
> innodb\_buffer\_pool\_instances = 2

### 动态修改pool配置 ###

在mysql5.7.5之后，可以在mysql不重启的情况下动态修改pool的size

– 5. 修改缓冲区实例个数  
`set global innodb_buffer_pool_instances=4;`  
– 6. 修改缓存区大小 4G  
`set global innodb_buffer_pool_size=4294967296;`

[MySQL 报错 ‘Variable ‘XXX‘ is a read only variable‘][MySQL _ _Variable _XXX_ is a read only variable]

## 常用sql ##

-- 查看mysql版本信息
    select version();
    -- 0. 查看引擎缓存池大小
    show variables like 'innodb_buffer_pool_size';
    -- 1. 查看引擎缓存池信息
    show variables like 'innodb_buffer_pool%';
    -- 2. 查看已经被占用的和空闲的page
    show global status like '%innodb_buffer_pool_pages%';
    -- 3. 缓冲区LRU淘汰算法
    show variables like 'innodb_old_blocks_pct';

-- 4. 引擎状态信息
    show engine innodb status;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjA2MDEw_size_16_color_FFFFFF_t_70_pic_center 5]  
**动态修改**

-- 5. 修改缓冲区实例个数
    set global innodb_buffer_pool_instances=32;
    -- 5. 修改缓冲区实例个数
    set global innodb_buffer_pool_instances=4;
    -- 6. 修改缓存区大小 4G
    set global innodb_buffer_pool_size=4294967296;
    -- 6. 修改缓存区大小 512M
    set global innodb_buffer_pool_size=536870912;

这些东西基本够用啦，那么关于当pool的大小不够用了，满了怎么办？LRU算法我们后面再看。

--------------------

> 天下英雄出我辈，一入江湖岁月催  
> 我是「无间行者」，努力把实践过的解决方案分享给大家  
> 如果这篇文章对你有用，一个赞、一个评论、一个关注，我都很开心  
> 码字不易，给点鼓励吧

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjA2MDEw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/8cb6d77c916c40ea9fa676d817c8fba8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjA2MDEw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/33c8756d0a464c7ea9042937f48ace10.png
[MySQL5.7 Reference Manual]: https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-buffer-pool.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjA2MDEw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/5d792d230e3f42808e7113f770cf240e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjA2MDEw_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221123/7c497a973c264b25bdafd175c5eb2298.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjA2MDEw_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221123/863bec86db2748eb92dff91ecaa5509a.png
[MySQL Server 5.7_ my.ini]: https://editor.csdn.net/md/?articleId=109001691
[MySQL _ _Variable _XXX_ is a read only variable]: https://editor.csdn.net/md/?articleId=109002192
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjA2MDEw_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221123/68f0c0151cd94d90a260139bd98fc5d4.png