【MySQL精通之路】InnoDB配置(8)-缓存池配置调优

Anakki

已于 2024-05-27 07:14:28 修改

阅读量1.5k

点赞数 10

CC 4.0 BY-SA版权

分类专栏： MySQL精通之路-Innodb存储引擎文章标签： mysql java 数据库缓存池 Innodb 配置

于 2024-05-24 07:21:20 首次发布

本文链接：https://blog.csdn.net/qq_29519041/article/details/139161435

本节提供InnoDB缓冲池的配置和调优信息。

1 配置InnoDB缓冲池大小

当增加或减少innodb_buffer_pool_size时，操作是分块执行的

区块大小由innodb_buffer_pool_chunk_size 配置选项定义，默认值为128M。

缓冲池大小必须始终等于或等于（n倍于块大小 x 缓冲池实例数）

innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。

否则缓冲池大小将自动调整为等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances倍数的值。

在以下示例中，innodb_buffer_pool_size设置为8G，innodl_buffer_poor_instances设置为16。innodb_buffer_pool_chunk_size为128M，为默认值。

8G是一个有效的。

8G是innodb_buffer_pool_instance=16*innob_buffer_pool_chunk_size=128M的倍数，即2G。

$> 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 |
+------------------------------------------+

在本例中，innodb_buffer_pool_size设置为9G，innodl_buffer_poor_instances设置为16。innodb_buffer_pool_chunk_size为128M，为默认值。在这种情况下，9G不是innodb_buffer_pool_instance=16*innodb_buffer_pool_chunk_size=128M的倍数，因此innodb_uffer_pool_size被调整为10G，这是innodd_buffer_pool_chunk_size*innodb_buffer_poor_instances的倍数。

$> 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 |
+------------------------------------------+

1.1 配置InnoDB缓存池块大小

innodb_buffer_pool_chunk_size可以以1MB（1048576字节）为单位增加或减少，但只能在启动时、命令行字符串或MySQL配置文件中进行修改。

命令行：

$> mysqld --innodb-buffer-pool-chunk-size=134217728

配置文件：

[mysqld]
innodb_buffer_pool_chunk_size=134217728

更改innodb_buffer_pool_chunk_size时，以下条件适用：

如果在初始化缓冲池时，新的innodb_buffer_pool_chunk_size值*innodb_buffer_pool_instances大于当前缓冲池大小，则innodb_uffer_pool_chunk_size将被截断为innodb_缓冲池_size/innodb_buffer_pool _instances。

例如，如果缓冲池被初始化为2GB（2147483648字节）的大小、4个缓冲池实例和1GB（1073741824字节）的块大小，则块大小被截断为等于innodb_buffer_pool_size/innodb_buffer_pool_instances的值，如下所示：

$> mysqld --innodb-buffer-pool-size=2147483648 --innodb-buffer-pool-instances=4
--innodb-buffer-pool-chunk-size=1073741824;

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                2147483648 |
+---------------------------+

mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              4 |
+--------------------------------+

# Chunk size was set to 1GB (1073741824 bytes) on startup but was
# truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       536870912 |
+---------------------------------+

缓冲池大小必须始终等于或等于innodb_Buffer_pool_chunk_size*innodb_Buffer_pool_instances的倍数。

如果您更改innodb_buffer_pool_chunk_size，innodb_buffer_pool_size会自动调整为等于innodb_uffer_pool_chunk_size*innodb_buffer_pool_instances或其倍数的值。

在初始化缓冲池时进行调整。以下示例演示了这种行为：

# The buffer pool has a default size of 128MB (134217728 bytes)

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 134217728 |
+---------------------------+

# The chunk size is also 128MB (134217728 bytes)

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       134217728 |
+---------------------------------+

# There is a single buffer pool instance

mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              1 |
+--------------------------------+

# Chunk size is decreased by 1MB (1048576 bytes) at startup
# (134217728 - 1048576 = 133169152):

$> mysqld --innodb-buffer-pool-chunk-size=133169152

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       133169152 |
+---------------------------------+

# Buffer pool size increases from 134217728 to 266338304
# Buffer pool size is automatically adjusted to a value that is equal to
# or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 266338304 |
+---------------------------+

此示例演示了相同的行为，但具有多个缓冲池实例：

# The buffer pool has a default size of 2GB (2147483648 bytes)

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                2147483648 |
+---------------------------+

# The chunk size is .5 GB (536870912 bytes)

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       536870912 |
+---------------------------------+

# There are 4 buffer pool instances

mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              4 |
+--------------------------------+

# Chunk size is decreased by 1MB (1048576 bytes) at startup
# (536870912 - 1048576 = 535822336):

$> mysqld --innodb-buffer-pool-chunk-size=535822336

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       535822336 |
+---------------------------------+

# Buffer pool size increases from 2147483648 to 4286578688
# Buffer pool size is automatically adjusted to a value that is equal to
# or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                4286578688 |
+---------------------------+

更改innodb_buffer_pool_chunk_size时应小心，因为更改此值会增加缓冲池的大小，如上面的示例所示。

在更改innodb_buffer_pool_chunk_size之前，请计算对innodb_buffer_pool_size的影响，以确保生成的缓冲池大小是可接受的。