MySQL 8.4 發行說明
您可以離線或在伺服器執行時配置 InnoDB 緩衝池大小。本節中描述的行為適用於兩種方法。如需線上配置緩衝池大小的其他資訊，請參閱線上配置 InnoDB 緩衝池大小。
當增加或減少 innodb_buffer_pool_size 時，操作會以區塊執行。區塊大小由 innodb_buffer_pool_chunk_size 設定選項定義，其預設值為 128M。如需更多資訊，請參閱配置 InnoDB 緩衝池區塊大小。
緩衝池大小必須始終等於或為 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍數。如果您將 innodb_buffer_pool_size 配置為不等於或不是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 倍數的值，則緩衝池大小會自動調整為等於或為 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍數的值。
在以下範例中，innodb_buffer_pool_size 設定為 8G，且 innodb_buffer_pool_instances 設定為 16。innodb_buffer_pool_chunk_size 為 128M，這是預設值。
8G 是有效的 innodb_buffer_pool_size 值，因為 8G 是 innodb_buffer_pool_instances=16 * innodb_buffer_pool_chunk_size=128M 的倍數，即 2G。
$> mysqld --innodb-buffer-pool-size=8G --innodb-buffer-pool-instances=16mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
| 8.000000000000 |
+------------------------------------------+在此範例中，innodb_buffer_pool_size 設定為 9G，且 innodb_buffer_pool_instances 設定為 16。innodb_buffer_pool_chunk_size 為 128M，這是預設值。在此情況下，9G 不是 innodb_buffer_pool_instances=16 * innodb_buffer_pool_chunk_size=128M 的倍數，因此 innodb_buffer_pool_size 會調整為 10G，這是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍數。
$> mysqld --innodb-buffer-pool-size=9G --innodb-buffer-pool-instances=16mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
| 10.000000000000 |
+------------------------------------------+
可使用 1MB (1048576 位元組) 為單位來增加或減少 innodb_buffer_pool_chunk_size，但只能在啟動時透過指令列字串或 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_buffer_pool_chunk_size將會被截斷為innodb_buffer_pool_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_buffer_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的影響，以確保產生的緩衝池大小是可以接受的。
注意：
為避免潛在的效能問題，區塊數量 (innodb_buffer_pool_size / innodb_buffer_pool_chunk_size) 不應超過 1000。
可以使用 SET 陳述式動態設定 innodb_buffer_pool_size 組態選項，讓您無需重新啟動伺服器即可調整緩衝池大小。例如：
mysql> SET GLOBAL innodb_buffer_pool_size=402653184;注意：
緩衝池大小必須等於或為 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍數。變更這些變數設定需要重新啟動伺服器。
在調整緩衝池大小之前，應先完成作用中的交易和透過 InnoDB API 執行的作業。當啟動調整大小作業時，該作業會等到所有作用中的交易都完成才會開始。一旦調整大小作業正在進行中，需要存取緩衝池的新交易和作業都必須等到調整大小作業完成。規則的例外情況是，當緩衝池進行碎片整理並在緩衝池大小縮小時撤回頁面時，允許同時存取緩衝池。允許同時存取的缺點是，當頁面被撤回時，可能會導致可用頁面的暫時短缺。
注意：
如果在緩衝池調整大小作業開始後啟動巢狀交易，則可能會失敗。
Innodb_buffer_pool_resize_status 變數會報告一個字串值，指示緩衝池調整大小的進度；例如：
mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------+
| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |
+----------------------------------+----------------------------------+您也可以使用 Innodb_buffer_pool_resize_status_code 和 Innodb_buffer_pool_resize_status_progress 狀態變數來監控線上緩衝池調整大小作業，這些變數會報告數值，較適合程式化監控。
Innodb_buffer_pool_resize_status_code 狀態變數會報告一個狀態碼，指示線上緩衝池調整大小作業的階段。狀態碼包括：
0：沒有正在進行的調整大小作業
1：正在開始調整大小
2：正在停用 AHI (Adaptive Hash Index，自適應雜湊索引)
3：正在撤回區塊
4：正在取得全域鎖定
5：正在調整集區大小
6：正在調整雜湊大小
7：調整大小失敗
Innodb_buffer_pool_resize_status_progress 狀態變數會報告一個百分比值，指示每個階段的進度。在處理完每個緩衝池執行個體後，會更新百分比值。當狀態 (由 Innodb_buffer_pool_resize_status_code 報告) 從一個狀態變更為另一個狀態時，百分比值會重設為 0。
以下查詢會傳回一個字串值，指示緩衝池調整大小的進度、一個指示作業目前階段的代碼，以及該階段的目前進度 (以百分比值表示)：
SELECT variable_name, variable_value
FROM performance_schema.global_status
WHERE LOWER(variable_name) LIKE "innodb_buffer_pool_resize%";緩衝池調整大小進度也會顯示在伺服器錯誤日誌中。此範例顯示在增加緩衝池大小時記錄的附註：
[Note] InnoDB: Resizing buffer pool from 134217728 to 4294967296. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was added.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 134217728 to 4294967296.
[Note] InnoDB: re-enabled adaptive hash index.此範例顯示在縮小緩衝池大小時記錄的附註：
[Note] InnoDB: Resizing buffer pool from 4294967296 to 134217728. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : start to withdraw the last 253952 blocks.
[Note] InnoDB: buffer pool 0 : withdrew 253952 blocks from free list. tried to relocate
0 pages. (253952/253952)
[Note] InnoDB: buffer pool 0 : withdrawn target 253952 blocks.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was freed.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 4294967296 to 134217728.
[Note] InnoDB: re-enabled adaptive hash index.以 --log-error-verbosity=3 啟動伺服器，會在線上緩衝池調整大小作業期間將額外資訊記錄到錯誤日誌中。額外資訊包括 Innodb_buffer_pool_resize_status_code 報告的狀態碼，以及 Innodb_buffer_pool_resize_status_progress 報告的百分比進度值。
[Note] [MY-012398] [InnoDB] Requested to resize buffer pool. (new size: 1073741824 bytes)
[Note] [MY-013954] [InnoDB] Status code 1: Resizing buffer pool from 134217728 to 1073741824
(unit=134217728).
[Note] [MY-013953] [InnoDB] Status code 1: 100% complete
[Note] [MY-013952] [InnoDB] Status code 1: Completed
[Note] [MY-013954] [InnoDB] Status code 2: Disabling adaptive hash index.
[Note] [MY-011885] [InnoDB] disabled adaptive hash index.
[Note] [MY-013953] [InnoDB] Status code 2: 100% complete
[Note] [MY-013952] [InnoDB] Status code 2: Completed
[Note] [MY-013954] [InnoDB] Status code 3: Withdrawing blocks to be shrunken.
[Note] [MY-013953] [InnoDB] Status code 3: 100% complete
[Note] [MY-013952] [InnoDB] Status code 3: Completed
[Note] [MY-013954] [InnoDB] Status code 4: Latching whole of buffer pool.
[Note] [MY-013953] [InnoDB] Status code 4: 14% complete
[Note] [MY-013953] [InnoDB] Status code 4: 28% complete
[Note] [MY-013953] [InnoDB] Status code 4: 42% complete
[Note] [MY-013953] [InnoDB] Status code 4: 57% complete
[Note] [MY-013953] [InnoDB] Status code 4: 71% complete
[Note] [MY-013953] [InnoDB] Status code 4: 85% complete
[Note] [MY-013953] [InnoDB] Status code 4: 100% complete
[Note] [MY-013952] [InnoDB] Status code 4: Completed
[Note] [MY-013954] [InnoDB] Status code 5: Starting pool resize
[Note] [MY-013954] [InnoDB] Status code 5: buffer pool 0 : resizing with chunks 1 to 8.
[Note] [MY-011891] [InnoDB] buffer pool 0 : 7 chunks (57339 blocks) were added.
[Note] [MY-013953] [InnoDB] Status code 5: 100% complete
[Note] [MY-013952] [InnoDB] Status code 5: Completed
[Note] [MY-013954] [InnoDB] Status code 6: Resizing hash tables.
[Note] [MY-011892] [InnoDB] buffer pool 0 : hash tables were resized.
[Note] [MY-013953] [InnoDB] Status code 6: 100% complete
[Note] [MY-013954] [InnoDB] Status code 6: Resizing also other hash tables.
[Note] [MY-011893] [InnoDB] Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] [MY-011894] [InnoDB] Completed to resize buffer pool from 134217728 to 1073741824.
[Note] [MY-011895] [InnoDB] Re-enabled adaptive hash index.
[Note] [MY-013952] [InnoDB] Status code 6: Completed
[Note] [MY-013954] [InnoDB] Status code 0: Completed resizing buffer pool at 220826 6:25:46.
[Note] [MY-013953] [InnoDB] Status code 0: 100% complete調整大小作業由背景執行緒執行。當增加緩衝池的大小時，調整大小作業會：
以「區塊」(區塊大小由
innodb_buffer_pool_chunk_size定義) 增加頁面轉換雜湊表、清單和指標，以使用記憶體中的新位址
將新頁面新增至可用清單
當這些作業正在進行時，其他執行緒會被阻止存取緩衝池。
當縮小緩衝池的大小時，調整大小作業會：
對緩衝池進行碎片整理並撤回 (釋放) 頁面
以「區塊」(區塊大小由
innodb_buffer_pool_chunk_size定義) 移除頁面轉換雜湊表、清單和指標，以使用記憶體中的新位址
在這些作業中，只有對緩衝池進行碎片整理和撤回頁面允許其他執行緒同時存取緩衝池。