MySQL · 配置参数 · innodb_buffer_pool_size

发表于 | 更新于 | 分类于 | 阅读次数：

简介

缓存是计算机系统非常重要的组成部分，因为访问硬盘相比缓存可能会慢 100 到 100000 倍，取决于访问的数据量。MySQL 作为一个存储系统，同样具有缓冲池（Buffer Pool）机制，以避免每次查询数据都进行磁盘 IO，不同存储引擎缓存方式有所区别：

  • MyISAM 使用的是操作系统的文件系统缓存，来缓存那些经常被查询的数据。

  • InnoDB 不依赖操作系统的缓存，自己在 InnoDB Buffer Pool 处理缓存。

① InnoDB Buffer Pool 主要作用？

缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘 IO，起到加速访问的作用。

② 为什么不把所有数据都放到缓冲池里？

凡事都具备两面性，抛开数据易失性不说，访问快速的反面是存储容量小：

  • 缓存访问快，但容量小，数据库存储了 200G 数据，缓存容量可能只有 64G；
  • 内存访问快，但容量小，买一台笔记本磁盘有 2T，内存可能只有 16G；

因此，只能把“最热”的数据放到“最近”的地方，以“最大限度” 的降低磁盘访问。

③ 如何管理与淘汰缓冲池，使得性能最大化呢？

参数说明

参数：innodb_buffer_pool_size

介绍：配置缓冲池的大小，在内存允许的情况下，DBA 往往会建议调大这个参数，越多数据和索引放到内存里，数据库的性能会越好。

参数：innodb_old_blocks_pct

介绍：老生代占整个 LRU 链长度的比例，默认是 37，即整个 LRU 中新生代与老生代长度比例是 63:37。

参数：innodb_old_blocks_time

介绍：老生代停留时间窗口，单位是毫秒，默认是 1000，即同时满足“被访问”与“在老生代停留时间超过 1 秒两个条件，才会被插入到新生代头部。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select @@innodb_buffer_pool_size/1024/1024;
+-----------------------------------	--+
| @@innodb_buffer_pool_size/1024/1024 |
+-------------------------------------+
|                       1024.00000000 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_old%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_old_blocks_pct  | 37    |
| innodb_old_blocks_time | 1000  |
+------------------------+-------+
2 rows in set (0.01 sec)

InnoDB Buffer Pool 缓存命中率计算：

1
2
脏页率计算公式：innodb_buffer_pool_pages_dirty / innodb_buffer_pool_pages_data * 100%
使用率计算公式：innodb_buffer_pool_pages_data / ( innodb_buffer_pool_pages_data + innodb_buffer_pool_pages_free ) * 100%

将缓冲池划分为多个单独的实例，通过减少不同线程读取和写入缓存的争用来提高并发性。Buffer Pool 可以存放多个 Instance，每个 Instance 由多个 Chunk 组成。Instance 的数量范围和 Chunk 的总数量范围分别为 1-64，1-1000。

img

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 默认值
innodb_buffer_pool_size 默认值为128M
innodb_buffer_pool_instances 默认值为1
innodb_buffer_pool_chunk_size 默认值为128M

# 计算公式
innodb_buffer_pool_size = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances * N

# 总缓存大小
select @@innodb_buffer_pool_size/1024/1024;

# 缓存池数量
select @@innodb_buffer_pool_instances;

# 每个缓存池的大小
select @@innodb_buffer_pool_chunk_size/1024/1024;

Tips：当设置的 Buffer Pool 不等于 Chunk * Instance 倍数时，MySQL 会自动调整 Buffer Pool 的大小 ( 向上取值到最近的一次倍数 )。

小结：

  • 缓冲池（buffer pool）是一种常见的降低磁盘访问的机制

  • 缓冲池通常以页（page）为单位缓存数据

  • 缓冲池的常见管理算法是 LRU，memcache，OS，InnoDB 都使用了这种算法

  • InnoDB 对普通 LRU 进行了优化：（1）将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，页被访问才进入新生代，以解决预读失效的问题；（2）页被访问，且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题。

设置 InnoDB Buffer Pool

InnoDB Buffer Pool 缓存了表数据和二级索引在内存中，设置 innodb_buffer_pool_size 合理数值对实例性能影响很大。

  • 设置偏小，会导致数据库大量直接对磁盘的访问，查询效率低。
  • 设置过大，会导致数据库进程占用内存太多，发生 OOM。

独立服务器

如果仅 MySQL 服务独享服务器资源，推荐设置 innodb_buffer_pool_size 为服务器总可用内存的 80%，具体设置多少，需要根据服务器内存大小和消耗进行调整。

设置 80% 的原因，是因为其它也需要内存：

  • 单次查询至少需要几K（甚至几M）的内存
  • 其他各种内部的 MySQL 结构和缓存
  • 有一些 MySQL 文件是在 OS 缓存里（binary 日志，relay 日志，事务日志等）
  • 操作系统消耗内存

修改方法

  • MySQL 5.7 版本之后，可以在线修改
1
2
3
4
5
6
7
set global innodb_buffer_pool_size = size_in_bytes;
select @@innodb_buffer_pool_size/1024/1024;
+-------------------------------------+
| @@innodb_buffer_pool_size/1024/1024 |
+-------------------------------------+
|                        128.00000000 |
+-------------------------------------+
1
2
3
Tips:
① innodb_buffer_pool_size 在线修改时，用户的请求将会阻塞，需要在业务低峰期和没有大事务操作时候进行
② 同时要修改配置文件 my.cnf，防止重启后恢复到原来的值
  • MySQL 5.7 版本之前，需要修改 my.cnf 配置文件，然后重启 MySQL 服务
1
2
[mysqld]
innodb_buffer_pool_size  = 20480M

参考

数据库内核月报

INNODB_BUFFER_POOL_SIZE：设置最佳内存值