Mysql 参数 innodb_buffer_pool_size_mysql innodb_buffer_pool_size

作者：小蓝xlanll | 2024-04-14 06:09:00

踩

mysql innodb_buffer_pool_size

以下考虑主要为 Innodb 引擎, key_buffer_size 不考虑。对于实例级别或线程级别参数设置,暂不考虑。

【 innodb_buffer_pool_size 】

用于缓存索引和数据的内存大小, 这个当然是越多越好, 数据读写在内存中非常快, 减少了对磁盘的读写。当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中。然而内存还有操作系统或数据库其他进程使用, 一般设置 buffer pool 大小为总内存的 3/4 至 4/5。若设置不当, 内存使用可能浪费或者使用过多。对于繁忙的服务器, buffer pool 将划分为多个实例以提高系统并发性, 减少线程间读写缓存的争用。buffer pool 的大小首先受 innodb_buffer_pool_instances 影响, 当然影响较小。

【 innodb_buffer_pool_instances 】

buffer pool 被划分为多个缓存实例的数量, 为固定值,不动态变更。当较多数据加载到内存时, 使用多缓存实例能减少缓存争用情况。当 innodb_buffer_pool_size 大于 1GB 时, innodb_buffer_pool_instances 默认为 8。如有更多buffer pool, 平均每个instances 至少1GB。

对 innodb_buffer_pool_size 的小小影响如下：

shell> mysqld --innodb_buffer_pool_size=8G --innodb_buffer_pool_instances=16
mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           8.000000000000 |
+------------------------------------------+
1
2
3
4
5
6
7

shell> mysqld --innodb_buffer_pool_size=9G --innodb_buffer_pool_instances=16
mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
±-----------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
±-----------------------------------------+
| 10.000000000000 |
±-----------------------------------------+【 innodb_buffer_pool_chunk_size 】

innodb_buffer_pool_chunk_size 默认 128MB (更改不需重启),增加单位为 1MB 。
innodb_buffer_pool_chunk_size 的最大值估算如下：
MAX(innodb_buffer_pool_chunk_size) = innodb_buffer_pool_size / innodb_buffer_pool_instances

综合以上三个参数：

buffer pool 估算公式：(N 为正整数; buffer pool 应为总内存的 3/4 至 4/5)
innodb_buffer_pool_size = N * (innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances)

当 N=1时, 使三个参数设置刚好满足以下公式：
innodb_buffer_pool_size = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

若此时再设置 innodb_buffer_pool_chunk_size 少 1MB , 那么 buffer pool 大小几乎翻倍, 因为 chunk_size * instances < buffer_pool_size, 此时有 N=2, buffer_pool_size 为(chunk_sizeinstances) 的倍数大小

innodb_buffer_pool_size = 2 * (innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances)

#所以较优设置：假设系统内存 = 128 GB, buffer pool 大小预计100GB(128GB80%)
innodb_buffer_pool_instances = 8 #默认值,或者逻辑CPU数量
innodb_buffer_pool_chunk_size = 128MB #默认值
innodb_buffer_pool_size = 100 GB # N8128MG = N GB ,N 刚好为正整数。设 N=100使得 buffer pool 为总内存的 3/4 至 4/5。

【 innodb_page_size 】

innodb_page_size 默认 16kb, 数据存储页, 应与操作系统块大小一致(同 innodb_log_write_ahead_size)。对于 SSD 更小的页可能更好。innodb_page_size 为32k and 64k 时, 行长度最大为 16000 bytes, 且不支持 ROW_FORMAT=COMPRESSED。

一个 innodb_buffer_pool_chunk_size 中包含的页数量取决于 innodb_page_size。

默认地： chunk可存储的页数量= innodb_buffer_pool_chunk_size / innodb_page_size = 128*1024/16 = 8192

查看：


 
 
   
   
    
    
   
   
   
   
    
    
     
     mysql> 
     
     SELECT @@innodb_buffer_pool_size;
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     mysql> 
     
     SELECT @@innodb_buffer_pool_instances;
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     mysql> 
     
     SELECT @@innodb_buffer_pool_chunk_size;
    
    
   
   
 
 
1

参考：Configuring InnoDB Buffer Pool Size

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小蓝xlanll/article/detail/420547