淺入淺出 MySQL Ep9 : 如何透過 InnoDB 排查資料庫查詢效能？

前言

Ep 8 介紹 InnoDB 的寫入機制以及透過 show engine innodb status 指令確認是否有寫入延遲的情況，那麼 innodb status 是否也能觀察查詢延遲的情況呢？答案是肯定的，我們也可以透過 show engine innodb status 排查是否有查詢延遲的問題，若該問題不一定是 index 的設計問題，就可能跟 LRU 快取有關，就讓我們一起看究竟如何排查吧！

內文

在 Ep7 有提到 InnoDB Buffer Pool 提供 LRU 記憶體快取功能提升資料查詢效能，所以當你遇到某個查詢 index 有命中且之前正常但最近突然變慢，很有可能就是跟 LRU 快取有關，那我們該如何排查 LRU 的狀況來定位查詢問題呢？

問題一：LRU 如何影響查詢效能？

問題二：如何排查 LRU 狀況？

問題三：哪些操作會影響 LRU 快取內容？

問題一：LRU 如何影響查詢效能？

原本好好的查詢突然變慢，其中一個可能就是 LRU 快取沒命中必需透過硬碟 I/O 讀取資料，我們如何確認快取命中率呢？

透過 show engine innodb status 指令的結果，在 **BUFFER POOL AND MEMORY **的段落中有一個資訊是 Buffer pool hit rate 1000 / 1000 代表一千次查詢中有幾次是透過 LRU 記憶體回傳的，hit rate 理論上要接近 100 %，如果有顯著的下降 (e.g 98–95%) 就會影響查詢效能。

為何理論上 hit rate 能接近 100 %?

在真實應用場景，大部分都是查詢最近的資料，例如剛留言的內容，而最近的資料是剛被建立的資料，建立的資料在 INSERT 時就會存放在 LRU 快取中了，所以當你 INSERT 完馬上查詢資料就一定會在 LRU 快取中被命中，另外一種常見情境是查詢附近的資料，例如前一天或後一天的訂單，而 LRU 在儲存快取時是已 Page 為單位，一個 Page 可能會有多個連續的資料，因此當你讀取某筆資料時，其周圍連帶的資料也會一起被放入 LRU 快取中，而這就是為何理想上 hit rate 能趨近 100%。

那麼當我們發現 hit rate 長時間下降到 98% 以下時，我們該如何排查具體原因呢？

問題二：如何排查 LRU 狀況？

如果 hit rate 下降了，首先要關注可能造成 LRU 快取不夠用的原因，透過 show engine innodb status 中 **BUFFER POOL AND MEMORY **的 0.00 reads/s, 0.00 creates/s, 37.32 writes/s一窺究竟：

• writes/s — 平均有多少寫入 I/O
• creates/s — 平均有多少新的 Page 在 LRU 快取中被建立
• reads/s — 平均代表有多少查詢需要經過 I/O

writes/s 數值過高代表有大量的 UPDATE 或 INSERT 導致 flush 執行頻繁，不過 flush 只是大量寫入跟查詢有何關係？如果writes/s 很高 reads/s 很低同時 hit rate 下降，這表示大量的 Write I/O 壓縮到 Read I/O 的空間，Read I/O 需等待 Write I/O 完成導致查詢的時間拉長了，這時可以透過調低 innodb_io_capacity 或者 調高innodb_max_dirty_pages_pct_lwm 降低寫入 I/O 頻率，也可以調整 [innodb_read_io_threads](https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_read_io_threads) 和 [innodb_write_io_threads](https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_write_io_threads) 的數字來提升讀和降低寫的併發數，但 writes/s 主要影響硬碟效能，不影響到 LRU 快取，所以他並不是造成 hit rate 下降的主因，頂多是間接影響到 Read I/O 變慢，但 creates/s 就會影響了。

creates/s增加代表 INSERT 新資料或建立新 index，此時原本最新的 Page 容量不夠需要建立新 Page ，或者查詢中使用到 temporary table (e.g UNION ALL or ORDER … LIMIT 跟GROUP BY沒有 index ) ， creates/s 大量增加代表 LRU 的 Old Sublist 增加很多新 Page ，可能造成記憶體不夠逐出尾端的 Page 而降低了 hit rate，可以調大[innodb_old_blocks_pct](https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_old_blocks_pct) 讓 Old Sublist 記憶體增加進而降低重要 Page 被逐出 Old Sublist 機率以提高移到 New Sublist 的可能，或優化使用到 temporary table 的 SQL，例如優化 ORDER … LIMIT 使用 index sort 不是 file sort。

reads/s 飆高也跟 hit rate 有直接關係，造成原因通常是頻繁查詢大量資料，例如 Full Table Scan ，Full Index Scan 或者 Index 過濾不了太多資料 (e.g select * from users where gender = 1)，此時 LRU 頻繁載入大量資料到 Old Sublist 導致記憶體不夠且頻繁執行置換降低 hit rate，首先要先定位 slow query 並優化，隨後一樣可以嘗試調大[innodb_old_blocks_pct](https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_old_blocks_pct) ，但聰明如你可能會想，如果調大 [innodb_old_blocks_pct](https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_old_blocks_pct) 不會壓縮 New Sublist 空間導致 hit rate 降低？

如何放心調整 [innodb_old_blocks_pct](https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_old_blocks_pct) 參數？

我們可以透過 **BUFFER POOL AND MEMORY **中的 young-making rate 0 / 1000 not 0 / 1000以及 Old database pages& Free Buffers觀察 Page 在 New Sublist 和 Old Sublist 的分布狀況：

• young-making rate —1000 次查詢中有幾次在 Old Sublist 的查詢會把 Page 移到 New Sublist
• not young-making rate — 1000 次查詢中有幾次在 Old Sublist 的查詢不會移動 Page
• Old database pages — Old Sublist 的 Page 數
• Free Buffers — 沒被用到的記憶體空間

如果發現 Old database pages 很高且 young-making rate 低 not young-making rate 高，代表查詢都在 Old Sublist 完成且 Page 都沒被移到 New Sublist ，如果 Free Buffers 還有不少，就可以大膽地調高 [innodb_old_blocks_pct](https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_old_blocks_pct) 藉此提高 Page 留存 Old Sublist 的時間和提高移到 New Sublist 機率。

但如果young-making rate 且 not young-making rate 都不高代表還是有部分查詢進入 New Sublist，此時調整 [innodb_old_blocks_pct](https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_old_blocks_pct) 就可能沒什麼幫助。

統整一下上述提到會影響 LRU 記憶體的幾個因素：

• 頻繁查詢大量資料
• 大量 INSERT 資料
• 使用到 Temporary Table 的查詢

那麼除了上述情境，還有其他可能因素嗎？

問題三：哪些操作會影響 LRU 快取內容？

首先 information_schema DB 中的 [innodb_buffer_page_lru](https://dev.mysql.com/doc/mysql-infoschema-excerpt/8.0/en/information-schema-innodb-buffer-page-lru-table.html) Table 紀錄了 LRU 快取儲存的資料，Table Schema 可以參考 link。

我們做個簡單的實驗：

CREATE TABLE local_test.orders (
 id int AUTO_INCREMENT,
 user_id int,
 price int, 
 amount int,
 primary key(id)
)

CREATE INDEX idx_uid_price ON local_test.orders (user_id, price)

INSERT INTO local_test.orders (user_id, price, amount) VALUES (1, 1,1)

建立一個 orders Table 和 index 然後 insert 一筆資料，此時去查詢 LRU 內容 **select** Table_name, Index_name, Data_size **from** information_schema.innodb_buffer_page_lru **where** table_name **like** "local_test.%" 會發現他放兩個 Page 進去 PRIMARY & idx_uid_price ，也就是主表的 B+Tree 跟 Index B+Tree 的 Page。

如果我在建立一個 index 會發現 LRU 中又多一個 Page：

CREATE INDEX idx_uid ON local_test.orders (user_id)
INSERT INTO local_test.orders (user_id, price, amount) VALUES (1, 1, 1)

這說明表的 Index 太多不僅會影響寫入效能，也會在 INSERT新資料時壓縮到 LRU 空間進而影響查詢效能，因此移除多餘 Index 是很重要的事，例如上述範例中 idx_uid 是多餘的 Index ，因為 idx_uid_price 跟 idx_uid 有同等的效果，也就是說 SELECT * FROM orders WHERE user_id = ? 會命中 idx_uid_price 或者 idx_uid Index。

此外還可以發現 primary Page 相較於其他 Index Page 的 data size 是大很多的，因此 SELECT * 不僅查詢會慢還可能載入資料量較大的 Page 因此壓縮 LRU 空間。

最後我們來看 LRU 資料的 Page Type **select** **DISTINCT**(PAGE_TYPE) **from** information_schema.innodb_buffer_page_lru ：

會發現除了資料 Index 本身還有其他系統資料，其中有一個關鍵是 Undo Log。

Undo Log 在 Ep4 有介紹是用來記錄不同版本的資料以此來實作 Isolation & Atomicity 的特性，因此假設有一個 Transaction 執行的非常久不 Commit，會造成 Undo Log 資料變大去壓縮 LRU 快取，原因是依照 MVCC 機制 InnoDB 無法刪除該 Transaction 後面的 Undo Log 資料。

你可以在 show engine innodb status 的 **Transaction **段落中查 History list length，該值代表還沒被清除的 Undo Log 數值，若該值很大可透過下面 SQL 查詢執行最久 Transaction 的 thread ID，然後透過 SHOW PROCESSLIST 找到執行的 Process 和對應的 Client 藉此直接 Kill 掉。

SELECT trx_id,
       trx_started,
       trx_mysql_thread_id,
       trx_query,
       trx_rows_modified,
       trx_state,
       trx_tables_in_use,
       trx_rows_locked,
       trx_isolation_level
FROM information_schema.innodb_trx
ORDER BY trx_started ASC
LIMIT 5;

總結

• Buffer LRU Hit Rate 理想情況要在 99~100%
• Hit Rate 長期下降到 98％ 需要透過 show engine innodb status 中的資訊排查可能原因
• Index 過多跟 Long Running Transaction 都可能壓縮到 LRU 的空間

下集預告

下集來介紹 Innodb 儲存 Row 的不同格式以及對應的使用時機，並從 Row 儲存方式講解 Table Schema 設計的小撇步。