## 背景 在寫壓力負載比較重的MySQL實例上，InnoDB可能積累了較長的沒有被purge掉的transaction history，導致實例性能的衰減，或者空閑空間被耗盡，下面就來看看它是怎么產生的，或者有沒有什么方法來減輕，避免這樣的問題出現。 ## InnoDB purge 概要 InnoDB是一個事務引擎，實現了MVCC特性，也就是在存儲引擎里對行數據保存了多個版本。在對行數據進行delete或者update更改時，行數據的前映像會保留一段時間，直到可以被刪除的時候。 在大部分OLTP負載情況下，前映像會在數據操作完成后的數秒鐘內被刪除掉，但在一些情況下，假設存在一些持續很長時間的事務需要看到數據的前映像，那么老版本的數據就會被保留相當長一段時間。 雖然MySQL 5.6版本增加了多個purge threads來加快完成老版本數據的清理工作，但在write-intensive workload情況下，不一定完全湊效。 ## 測試案例 Peter Zaitsev 使用sysbench的update進行的測試，無論是 innodb_purge_threads=1 還是8的時候，顯示的transaction history快速增長的情況，如下圖所示:  transaction history增長情況 下面看一下同步測試過程中purge的速度(可以通過`I_S.innodb_metrics`進行查詢)：  InnoDB purge 情況 顯示在并發 process 的過程中，purge thread 其實處在饑餓狀態，待sysbench結束，purge線程滿載運行清理工作。 對于這個測試結果，這里需要說明下： 1. 對于Peter Zaitsev的測試，其實主要是為了說明transaction history的情況，如果是用sysbench進行小事務的OLTP測試，并不會產生這么明顯的transaction history增長而purge thread 跟不上的情況，或者他在測試的時候，對sbtest表進行了全表查詢吧，或者設置了RR級別，不過這只是猜測。 2. 對于undo page大部分被cache在buffer pool的情況下，purge thread還是比較快的，但如果因為buffer pool的不足而導致undo page被淘汰到disk上的情況，purge操作就會被受限IO情況， 而導致跟不上。 ## 問題分析 我們來看下出現transaction history增長最常見的兩種場景： 大查詢 如果你在一張大表上發起一個長時間運行的查詢，比如mysqldump，那么purge線程必須停下來等待查詢結束，這個時候transaction undo就會累積。如果buffer pool中 free page緊張，undo page 還會被置換到disk上，加劇purge的代價。 MySQL重啟 即使transaction history并沒有急劇增加，但MySQL重啟操作，buffer pool的重新預熱，還是導致purge變成IO密集型操作。不過MySQL 5.6提供了InnoDB buffer pool的dump和reload方法，可以顯著減輕purge的IO壓力。 這里介紹一下如何查看buffer pool中undo page的cache情況，percona的版本上提供了`I_S.innodb_rseg`記錄undo的分配和使用情況： ~~~ mysql> select sum(curr_size)*16/1024 undo_space_MB from innodb_rseg; +---------------+ | undo_space_MB | +---------------+ | 1688.4531 | +---------------+ 1 row in set (0.00 sec) mysql> select count(*) cnt, count(*)*16/1024 size_MB, page_type from innodb_buffer_page group by page_type; +--------+-----------+-------------------+ | cnt | size_MB | page_type | +--------+-----------+-------------------+ | 55 | 0.8594 | EXTENT_DESCRIPTOR | | 2 | 0.0313 | FILE_SPACE_HEADER | | 108 | 1.6875 | IBUF_BITMAP | | 17186 | 268.5313 | IBUF_INDEX | | 352671 | 5510.4844 | INDEX | | 69 | 1.0781 | INODE | | 128 | 2.0000 | SYSTEM | | 1 | 0.0156 | TRX_SYSTEM | | 6029 | 94.2031 | UNDO_LOG | | 16959 | 264.9844 | UNKNOWN | +--------+-----------+-------------------+ 10 rows in set (1.65 sec) ~~~ 從這兩個information_schema下的兩張表可以看到：undo space使用的總大小是1.7G，而buffer pool中cached不足100M。 ## InnoDB 優化方法 在一定的寫壓力情況下，并發進行一些大查詢，transaction history就會因為undo log無法purge而一直增加。 InnoDB提供了兩個參數`innodb_max_purge_lag`，`innodb_max_purge_lag_delay`?來調整，即當`trx_sys->rseg_history_len`超過了設置的`innodb_max_purge_lag`，就影響DML操作最大delay不超過`innodb_max_purge_lag_delay`設置的時間，以microseconds來計算。 其核心計算代碼如下： ~~~ /*******************************************************************//** Calculate the DML delay required. @return delay in microseconds or ULINT_MAX */ static ulint trx_purge_dml_delay(void) /*=====================*/ { /* Determine how much data manipulation language (DML) statements need to be delayed in order to reduce the lagging of the purge thread. */ ulint delay = 0; /* in microseconds; default: no delay */ /* If purge lag is set (ie. > 0) then calculate the new DML delay. Note: we do a dirty read of the trx_sys_t data structure here, without holding trx_sys->mutex. */ if (srv_max_purge_lag > 0) { float ratio; ratio = float(trx_sys->rseg_history_len) / srv_max_purge_lag; if (ratio > 1.0) { /* If the history list length exceeds the srv_max_purge_lag, the data manipulation statements are delayed by at least 5000 microseconds. */ delay = (ulint) ((ratio - .5) * 10000); } if (delay > srv_max_purge_lag_delay) { delay = srv_max_purge_lag_delay; } MONITOR_SET(MONITOR_DML_PURGE_DELAY, delay); } return(delay); } ~~~ 但這兩個參數設計有明顯的兩個缺陷： 缺陷1：針對total history length 假設transaction history中保留兩類records，一類是是馬上可以被purge的，一類是因為active transaction而不能purge的。但大多數時間，我們期望的是purgable history比較小，而不是整個history。 缺陷2：針對大小而非變化 `trx_sys->rseg_history_len`是一個當前history的長度，而不是一個interval時間段內undo的增長和減少的變化情況，導致`trx_sys->rseg_history_len`一旦超過`innodb_max_purge_lag`這個設定的值，就對DML產生不超過`innodb_max_purge_lag_delay`的時間delay，一旦低于這個值馬上delay 時間就又恢復成 0。 在對系統的吞吐監控的時候，會發現系統抖動非常厲害，而不是一個平滑的曲線。類似于下圖：  Purge 造成系統抖動 ## InnoDB purge 設計思路 針對InnoDB的purge功能，可以從以下幾個因素來綜合考慮： 1. 增加默認 purge thread 的個數； 2. 測量 purgable history 長度而不是總的長度； 3. 針對變化進行調整 delay 數值，以應對 shrinking； 4. 基于 undo space 的大小，而不是事務的個數； 5. 調整 undo page 在 buffer pool 中的緩存策略，類似 insert buffer； 6. 針對 undo page 使用和 index page 不同的預讀策略。 以上6條可以針對purge線程進行一些改良。 ## 當前調優方法 在當前的 MySQL 5.6 版本上，我們能做哪些調整或者調優方法，以減少transaction history增加帶來的問題呢？ 監控 監控`trx_sys`的`innodb_history_list_length`，為它設置報警值，及時關注和處理。 調整參數 如果你的實例是寫壓力比較大的話，調整`innodb_purge_threads=8`，增加并發purge線程數。 謹慎調整`innodb_max_purge_lag`和`innodb_max_purge_lag_delay`參數，依據現在的設計，可能你的實例的吞吐量會急劇的下降。 purge完之后再shutdown 大部分的case下，MySQL實例重啟后，會發現purge的性能更差，因為undo page未命中的原因，并且是random IO請求。 如果是正常shutdown，就等purge完成再shutdown；如果是crash，就啟動后等purge完成再接受業務請求。 預熱 使用MySQL 5.6 提供的`innodb_buffer_pool_dump_at_shutdown=on`?和?`innodb_buffer_pool_load_at_startup=on`進行預熱，把undo space page預熱到buffer pool中。