MySQL瓶頸分析與優化

。

簡介

通過sysbench的oltp_read_write測試來模擬業務壓力、以此來給指定的硬體環境配置一份比較合理的MySQL配置文件。

環境介紹

硬體配置

軟體環境

優化層級與指導思想

優化層級

MySQL資料庫優化可以在多個不同的層級進行，常見的有：

SQL優化

參數優化

架構優化

本文重點關註：參數優化

指導思想

日誌先行 -- 一個事務能否成功提交的關鍵是日誌是否成功落盤，與數據沒有太大的關係；也就是說對寫的優化可以表述為各方面的資源向寫操作傾斜。

瓶頸分析 -- 通過show global status 的各個計數器的值基本上就能分析出當前瓶頸所在，再結合一些簡單的系統層面的監控工具如top iostat 就能明確瓶頸。

整體性能是「讀」&「寫」之間的再平衡。

優化過程

優化前

my.cnf中的內容(關鍵部分)

圖像地址：

http://www.sqlpy.com/mysqlz/tuninglog/result/cm16c256g4096ssd/0/

監控數據

show global status 中Innodb_data_pending_fsyncs 這個status比較高；

iostat的util項有比較明顯的波峰，峰值使用率高達85%；

監控數據分析與優化思路

對監控數據有兩種可能的解釋：

由於最小化的安裝的buffer_pool_size比較小，所以會頻繁的觸發innodb_buffer_pool的最大臟頁的限制，使得innodb進入暴力刷盤的模式，這種情況下io使用率會明顯上升。

redo日誌重用。 最終的影響可能是兩者的疊加，這裡先從buffer_pool開始優化。

優化緩衝池

my.cnf中的內容(關鍵部分)

圖像地址：

http://www.sqlpy.com/mysqlz/tuninglog/result/cm16c256g4096ssd/1/

監控數據

show global status 中Innodb_data_pending_fsyncs 這個status減小到了 1；

iostat的util項峰值有所下降；

從性能圖像可以看出增大innodb_buffer_pool_size的值後、性能的峰值所對應的並發更高了(當innodb_buffer_pool_size默認的128M調整到200G時innodb_buffer_pool_instances自動增大到了8)

調整innodb_buffer_pool_size前後的性能對比

性能大概提高3倍

圖像地址：

http://www.sqlpy.com/mysqlz/tuninglog/compare/cm16c256g4096ssd/0/1/

監控數據分析與優化思路

針對innob_buffer_pool_size的調整取得了一定的收穫，下面將要調整的就是針對redo重用的情況了，也就是說我們要增大innodb_log_files_in_group和innodb_log_file_size到一個合適的值。

innob_buffer_pool_size取得的收穫還可以進一步擴大，那就是增大innodb_buffer_pool_instances的值。

優化日誌文件

根據對之前測試的記錄每完成一組測試LSN增大4.5G、測試持續時間大概是5分鐘；理論上把redo文件增大到5G可以做到整個測試的過程中不發生日誌重用、這樣的話測試的跑分會更高、曲也線更平滑，不過這個會影響資料庫宕機恢復的時間。MySQL在默認配置下innodb_log_files_in_group=2，innodb_log_file_size=48M也就是說跑完一組測試redo日誌要刷新48輪(1024*4.5/96 ==48) 先看一下把日誌刷新減少到9輪的情況。

my.cnf中的內容(關鍵部分)

圖像地址：

http://www.sqlpy.com/mysqlz/tuninglog/result/cm16c256g4096ssd/2/

調整innodb_log_files_in_group&innodb_log_file_size前後的性能對比

性能大概提高2倍

圖像地址：

http://www.sqlpy.com/mysqlz/tuninglog/compare/cm16c256g4096ssd/1/2/

現在看一下日誌重用控制在一輪(5G)之內的性能表現

my.cnf中的內容(關鍵部分)

圖像地址：

http://www.sqlpy.com/mysqlz/tuninglog/result/cm16c256g4096ssd/3/

調整innodb_log_files_in_group&innodb_log_file_size前後的性能對比

性能大概提高2倍

圖像地址：

http://www.sqlpy.com/mysqlz/tuninglog/compare/cm16c256g4096ssd/2/3/

監控數據分析與優化思路

增大redo到5G的情況下由於整個測試過程中幾乎沒有日誌文件重用的問題，這樣也就規避由些引發的大量數據刷盤行為，所以性能曲線也就更平滑了。

通過show global status 發現Table_open_cache_overflows=200W+、Thread_created=2k+

%Cpus : 80.5 us, 13.8 sy, 0.0 ni, 5.4 id, 0.0 wa, 0.0 hi, 0.3 si, 0.0 st 95%的使用率cpu資源成了大問題，這個使用率下能調整的參數不多了

對磁碟的監控數據表明util的峰值已經下降到14%、磁碟已經不在是問題；所以針對innodb_buffer_pool_size、innodb_log_files_in_group&innodb_log_file_size 這兩次優化的進入一步優化innodb_buffer_pool_instances、innodb_log_buffer_size 先不進行；在些採用「抓大放小」的方式先調整表緩存與線程緩存。

優化其它已知項

cpu使用率達到了95%，看到這個數值有一種發自內心的無力感，所以打算所目前status中能明確的一些問題直接一起調整了；增大table_open_cache&table_open_cache_instances用於優化表緩存、增大thread_cache_size使cpu不用頻繁的創建銷毀線程。

my.cnf中的內容(關鍵部分)

圖像地址：

http://www.sqlpy.com/mysqlz/tuninglog/result/cm16c256g4096ssd/4

調整前後的比較

圖像地址：

http://www.sqlpy.com/mysqlz/tuninglog/compare/cm16c256g4096ssd/3/4/

總結

一、考慮到cpu使用率已經達到95%且增加物理cpu不現實的情況下，決定MySQL參數優化到此為止；最後來看一眼這次優化成果。

圖像地址：

http://www.sqlpy.com/mysqlz/tuninglog/compare/cm16c256g4096ssd/0/4/

二、前面由於篇幅只給出配置文件的一部分、現在我們來看一下完整的配置文件。

說明

之所以max_prepared_stmt_count要調整到這麼是因為sysbench的oltp_read_write這個測試會用於prepare語句、如果這個值不夠大的話我們測試不了800+並發，你測試sysbench其它oltp用例可能不用這麼做，同理max_connections的配置也是如此(不過它確實設置的大了點)

有些參數在優化過程中我並沒有調整主要原因有兩個：

①.這是有方法論指導的優化、它更像定向爆破，所以沒用的我不去動、在關鍵參數上調整後已經解決問題的情況下，其它相關的參數我更加傾向不動。

②.對於從show global status 中能看出非常明確指向的我也會採取多個參數一起調整的策略。

愛可生（證券代碼：832768）依託於融合、開放、創新的數據處理技術和服務能力，為大型行業用戶的特定場景提供深度挖掘數據價值的解決方案。公司持續積累的核心關鍵技術，覆蓋到分散式資料庫集群、雲資料庫平台、資料庫大體量運管平台、海量數據集成與存儲、清洗與治理、人工智慧分析挖掘、可視化展現、安全與隱私保護等多個領域。

公司已與多個行業內的專業公司建立了長期夥伴關係，不斷促進新技術與行業知識相結合，為用戶尋求新的數據驅動的價值增長點。公司已在金融、能源電力、電信、廣電、政府等行業取得了多個大型用戶典型成功案例，獲得了市場的廣泛認可和業務持續的增長。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！