問題:
我們都知道 innodb_buffer_pool_instances 參數,將 buffer pool 分成幾個區,每個區用獨立的鎖保護,這樣就減少了訪問 buffer pool 時需要上鎖的粒度,以提高性能。
那麼我們如何觀察它是如何影響性能呢?
實驗:
準備一個空數據庫,
在這裡我們將 performance_schema_events_waits_history_long_size 調大,是為了讓之後實驗數據能採集的更多,在此不多做介紹。
使用 sysbench,準備一些數據,
對數據進行預熱 60s,可以看到預熱期間的性能會不太穩定,預熱後會比較穩定,
設置 performance_schema,這次我們將僅開啟觀察項(生產者)hash_table_locks,並開啟 waits 相關收集端(消費者)。(相關介紹參看 )
小貼士
為什麼我們知道觀察項應該選擇 hash_table_locks?
在 performance_schema.setup_instruments 表中,列出了所有觀察項,但我們很難從中選出我們應觀察哪個觀察項。
這時候,可以將所有觀察項都啟用,然後設計一些對比實驗,比如使用幾種不同的 SQL,觀察這些操作影響了哪些觀察項,找到共性或者區。
還有一種高效的方式是搜索別人的經驗,或者閱讀 MySQL 源碼。
本例中 hash_table_locks 隱藏的比較深,使用了閱讀 MySQL 源碼和對比試驗結合的方法。
由於 MySQL 有一些後臺進程會使用 buffer pool,比如刷盤線程,會影響我們的觀察,所以需要將 MySQL 的後臺線程排除在外。
萬事就緒,再運行一次 sysbench 壓力。運行前記得將已有的觀察結果清除:
運行 sysbench 壓力,持續60秒,
等待壓力結束後,對 performance_schema 中記錄的數據進行分析。
確實採集了 100 萬條對 hash_table_locks 的觀測數據。
我們取其操作時長的平均值,90% 分位數,99% 分位數:
整理到如下表格,但我們先忽略其時間單位,放到最後討論,
我們按照如上方法,分別再測試 innodb_buffer_pool_instances 為 2 和 4 的情況(記得多測幾次,取平均值會更為準確,本實驗由於偷懶,只測預熱後的一次結果)。
表格更新為:
可以看到如下結:
- 平均值都在 99% 分位數以上,意味著有極大的數據嚴重影響的平均值(有幾次對 buffer pool 鎖的獲取,等待了非常久)。
- 隨著 innodb_buffer_pool_instances 增大,這種嚴重的影響會逐漸減小。
- 在實驗的場景中,innodb_buffer_pool_instances 的增大,對 90% 和 99% 分位數影響都不大,即不會影響到大部分 SQL 對 buffer pool 鎖的獲取時間。
重要說明:
- 本實驗以介紹實驗手法為目的,實驗的結論不可作為參考。
- 如果大家多做幾次實驗,會發現在同一個配置下,平均值的變化很大,也就是說那幾次非常久的等待時間非常不穩定,受到其他因素影響。
- 如果更換數據壓力或者更換測試環境,會看到不同的現象。
我們再來看看這些時間的單位是什麼?
可以看到 wait 一族的觀察項,單位是 cycle,那麼 cycle 又是多少秒?
可以看到,1 cycle = 1/2387771144 秒。
我們重新看一下 innodb_buffer_pool_instances=1 時,獲取 buffer pool 鎖的平均時間是 6535546 cycle,大約是 2.7 ms。
2.7 ms,還是比較長的一段時間(我的虛擬機配置比較一般)。
結論
本次實驗介紹了對 innodb_buffer_pool_instances 參數對性能影響的觀測手法,大家可以在進行壓力測試時,使用此方法觀察 innodb_buffer_pool_instances 的取值對性能的影響,從而決定應該取值多少。
參數設置一直是個平衡問題,innodb_buffer_pool_instances 設置太小,鎖衝突集中;設置太大,維護成本升高。
再次強調,本次實驗僅介紹手法,結果不足取信。不同壓力,不同 buffer pool 配置,不同環境會呈現效果,唯有手法是可以傳承的。
按照正常實驗的規則,95% 分位數以上的極值可看作是實驗誤差,但對於本次實驗中的極大值進行進一步分析後,我們認為可以用於說明實驗效果。
關於 MySQL 的技術內容,你們還有什麼想知道的嗎?趕緊留言告訴小編吧!
閱讀更多 愛可生 的文章