MySQL 9.0 發行說明
您可以在離線或伺服器執行時設定 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 |
+------------------------------------------+
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_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 (自適應雜湊索引)
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定義) 移除頁面轉換雜湊表、清單和指標以使用記憶體中的新位址
在這些操作中，只有整理緩衝池和撤回頁面允許其他執行緒同時存取緩衝池。