Mysql 8.0 之调整 innodb buffer

innodb_buffer_pool_size参数

这是一个内存区域，用来缓存InnoDB存储引擎的表中的数据和索引数据，以便提高对InnoDB存储引擎表中数据的查询访问速度。InnoDB buffer pool 里包含：

• 数据缓存
  InnoDB数据页面
• 索引缓存
  索引数据
• 缓冲数据
  脏页（在内存中修改尚未刷新(写入)到磁盘的数据）
• 内部结构
  如自适应哈希索引，行锁等。

查看innodb_buffer_pool_size：

mysql> show variables like 'innodb_buffer_pool%';

+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
10 rows in set (0.03 sec)

在查看 innodb_buffer_pool_size ​这个参数值的时候，是以 字节(byte) 为单位进行展现的。它的默认值为 134217728字节，也就是 128MB (计算：134217728/1204/1024=128MB，1MB=1024KB,1KB=1024Byte)。

缓冲池大小必须始终等于innodb_buffer_pool_chunk_size *  innodb_buffer_pool_instances。如果更改 innodb_buffer_pool_chunk_size， innodb_buffer_pool_size 则会自动舍入为等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。初始化缓冲池时会进行调整。

关联参数：

 innodb_buffer_pool_chunk_size 默认值为 134217728字节，即 128MB。它可以按照1MB的单位进行增加或减小。

为了避免潜在的性能问题，chunk的数目不要超过1000这个阈值。也就是说： innodb_buffer_pool_size/innodb_buffer_pool_chunk_size 的值需要在[1,1000)之间。

⚠️参数 innodb_buffer_pool_chunk_size的修改，不能在数据库服务运行的过程中修改。

​

 innodb_buffer_pool_instances 的默认值为1，最大可以设置为64。

​当 innodb_buffer_pool_instances 不为1的时候，表示需要启用多个缓冲池实例，即把整个 innodb_buffer_pool_size 在逻辑上划分为多个缓存池，多实例可以提高并发性，可以减少不同线程读写缓存页面时的争用。

⚠️参数 innodb_buffer_pool_instances的修改，不能在数据库服务运行的过程中修改。

调整InnoDB缓冲池大小

#进入数据库调整(只能调整innodb_buffer_pool_size)
mysql> SET GLOBAL innodb_buffer_pool_size = 2147483648;

#命令行调整(3个参数均可调整)
$ mysqld --innodb-buffer-pool-size=2147483648 --innodb-buffer-pool-instances=4 --innodb-buffer-pool-chunk-size=1073741824

innodb_buffer_pool_size的取值范围

建议设置为物理服务器内存的50%～75%，要留一些内存供服务器操作系统本身使用。如果在数据库服务器上面除了部署MySQL数据库服务，还有部署其他应用和服务，则可以适当再减少对应的百分比。原则上是去除其他应用服务，剩余的可用内存的50%～70%作为innodb_buffer_pool_size的配置值。

判断现有的innodb_buffer_pool_size是否合适

percent = innodb_buffer_pool_read_requests (innodb_buffer_pool_reads + innodb_buffer_pool_read_requests) * 100%

当percent>=99%​，则表示当前的buffer pool满足当前的需求。否则需要考虑增加innodb_buffer_pool_size的值。

• innodb_buffer_pool_reads：The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk. >> 缓存池中不能满足的逻辑读的次数，这些读需要从磁盘中直接读取。 注：逻辑读是指从缓冲池中读，物理读是指从磁盘读。
• innodb_buffer_pool_read_requests：The number of logical read requests. >> 从buffer pool中逻辑读请求次数。