線上調整InnoDB Buffer Pool Size_ZenDei技術網路在線

InnoDB Buffer Pool主要是用來緩存數據表和索引數據的記憶體區域,它的預設值為134217728位元組(128MB)。最大值取決於CPU架構;32位系統上的最大值為4294967295(232-1)，64位系統上的最大值為18446744073709551615(264-1)。在32位系統 ...

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定義)
•轉換哈希表、列表和指針，以在記憶體中使用新地址
在這些操作中，只有對緩衝池進行碎片整理和回收頁面才能允許其他線程併發地訪問緩衝池。