在线调整InnoDB Buffer Pool Size

 

       innodb buffer pool主要是用来缓存数据表和索引数据的内存区域,它的默认值为134217728字节(128mb)。最大值取决于cpu架构;32位系统上的最大值为4294967295(232-1)，64位系统上的最大值为18446744073709551615(264-1)。在32位系统上，cpu体系结构和操作系统的实际最大大小可能低于标准的最大大小。当缓冲池的大小大于1gb时，将innodb_buffer_pool_instances设置为大于1的值可以提高繁忙服务器上的可伸缩性。

       innodb缓冲池更大的场景下,多次访问相同的数据表数据所需要的磁盘i/o就更少。在专用数据库服务器上，可以将缓冲池大小设置为机器物理内存大小的80%。在配置缓冲池大小时要注意以下潜在问题，并准备在必要时缩减缓冲池的大小。

◾对物理内存的竞争会导致操作系统分页。
◾innodb储备增加内存缓冲区和控制结构,以便总分配空间约10%大于指定的缓冲池大小。
◾地址空间的缓冲池必须是连续的,可以是一个问题在windows系统负载在特定地址的dll。
◾缓冲池初始化时间大约是与它的大小成正比。对于具有大型缓冲池的实例，初始化时间可能很重要。要缩短初始化周期，可以在服务器关闭时保存缓冲池状态，并在服务器启动时恢复它。

       当您增加或减少缓冲池大小时，操作将以块的形式执行。块大小由innodb_buffer_pool_chunk_size配置选项定义，默认值为128mb。

       缓冲池大小必须始终等于或多个innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances。如果将缓冲池大小更改为不等于或多个innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的值，缓冲池大小将自动调整为等于或多个innodb_buffer_pool_size * innodb_buffer_pool_instances的值。

       innodb_buffer_pool_size可以动态设置，这允许您在不重启服务器的情况下调整缓冲池的大小。innodb_buffer_pool_resize_status变量报告在线缓冲池调整大小操作的状态。

◾通过innodb api执行的活动事务和操作应该在调整缓冲池大小之前完成。
◾当启动调整大小操作时，该操作直到所有活动事务完成后才开始。调整大小操作进行后，需要访问缓冲池的新事务和操作必须等待调整大小操作完成。该规则的一个例外是，当缓冲池被碎片化并在缓冲池大小减小时回收页面时，允许对缓冲池进行并发访问。允许并发访问的一个缺点是，在提取页面时，可能会导致可用页面暂时短缺。
◾请注意如果在缓冲池调整大小操作开始后启动嵌套事务，则可能会失败。
◾在线调整innodb缓冲池大小请在业务低峰期进行。

      接下来，我们来在线调整innodb buffer pool

      查看当前系统mysql的innodb buffer pool size为128m

mysql> show global variables like '%innodb_buffer%';
+-------------------------------------+----------------+
| 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.00 sec)

view code

       通过设定全局系统变量innodb_buffer_pool_size来在线调整innodb buffer pool为1gb

mysql> set global innodb_buffer_pool_size=1073741824;
query ok, 0 rows affected (0.04 sec)

mysql> show global variables like '%innodb_buffer%';
+-------------------------------------+----------------+
| 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             | 1073741824     |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)

mysql> select 1073741824/1024/1024/1024
    -> ;
+---------------------------+
| 1073741824/1024/1024/1024 |
+---------------------------+
|            1.000000000000 |
+---------------------------+
1 row in set (0.00 sec)

view code

       当我们在线调整innodb buffer pool size的时候,我们可以通过状态变量innodb_buffer_pool_resize_status来监控resize的进度

mysql> show global status like '%innodb_buffer_pool_resize_status%';
+----------------------------------+----------------------------------------------------+
| variable_name                    | value                                              |
+----------------------------------+----------------------------------------------------+
| innodb_buffer_pool_resize_status | completed resizing buffer pool at 190320 11:30:07. |
+----------------------------------+----------------------------------------------------+
1 row in set (0.00 sec)

view code

        当然我们也可以通过查看mysql标准错误输出文件error.log观察resize的相关输出信息

#increasing the size of the buffer pool ouput
2019-03-20t03:30:06.852669z 0 [note] innodb: resizing buffer pool from 134217728 to 1073741824 (unit=134217728).
2019-03-20t03:30:06.861219z 1930980 [note] innodb: requested to resize buffer pool. (new size: 1073741824 bytes)
2019-03-20t03:30:06.872100z 0 [note] innodb: disabling adaptive hash index.
2019-03-20t03:30:06.873145z 0 [note] innodb: disabled adaptive hash index.
2019-03-20t03:30:06.873209z 0 [note] innodb: withdrawing blocks to be shrunken.
2019-03-20t03:30:06.873246z 0 [note] innodb: latching whole of buffer pool.
2019-03-20t03:30:06.873283z 0 [note] innodb: buffer pool 0 : resizing with chunks 1 to 8.
2019-03-20t03:30:07.007923z 0 [note] innodb: buffer pool 0 : 7 chunks (57344 blocks) were added.
2019-03-20t03:30:07.008025z 0 [note] innodb: resizing hash tables.
2019-03-20t03:30:07.016691z 0 [note] innodb: buffer pool 0 : hash tables were resized.
2019-03-20t03:30:07.016973z 0 [note] innodb: resizing also other hash tables.
2019-03-20t03:30:07.053148z 0 [note] innodb: resized hash tables at lock_sys, adaptive hash index, dictionary.
2019-03-20t03:30:07.053287z 0 [note] innodb: completed to resize buffer pool from 134217728 to 1073741824.
2019-03-20t03:30:07.053317z 0 [note] innodb: re-enabled adaptive hash index.
2019-03-20t03:30:07.053362z 0 [note] innodb: completed resizing buffer pool at 190320 11:30:07.

在线缓冲池调整内部大小
调整大小操作由后台线程执行。当增加缓冲池大小时，调整操作:
•以块的形式添加页面(块大小由innodb_buffer_pool_chunk_size定义)
•覆盖哈希表、列表和指针来使用内存中的新地址
•在空闲列表中添加新页面
当这些操作正在进行时，其他线程将被阻止访问缓冲池。
当减小缓冲池大小时，调整操作:
•对缓冲池进行碎片整理并回收(释放)页面
•以块的形式删除页面(块大小由innodb_buffer_pool_chunk_size定义)
•转换哈希表、列表和指针，以在内存中使用新地址
在这些操作中，只有对缓冲池进行碎片整理和回收页面才能允许其他线程并发地访问缓冲池。