MYSQL資料庫服務磁碟IO高問題分析與最佳化

標籤：MYSQL服務磁碟IO高問題分析

   MYSQL資料庫服務磁碟IO高問題分析與最佳化

     壓力測試過程中，如果因為資源使用瓶頸等問題引發最直接效能問題是業務交易回應時間偏大，TPS逐漸降低等。而問題定位分析通常情況下，最優先排查的是監控伺服器資源使用率，例如先用TOP 或者nmon等查看CPU、記憶體使用量情況，然後在排查IO問題，例如網路IO、磁碟IO的問題。 如果是磁碟IO問題，一般問題是SQL文法問題、MYSQL參數配置問題、伺服器自身硬體瓶頸導致IOPS吞吐率問題。

今天主要是講解MYSQL 參數配置不合理導致在高並發下磁碟IO問題，而MYSQL整體監控最佳化方案後面會整理《如何輕量化MYSQL服務效能監控》文章出來。

1、 開啟日誌跟蹤引起的磁碟IO問題

      例如：MySQL的日誌包括錯誤記錄檔（ErrorLog），更新日誌（UpdateLog），二進位日誌（Binlog），查詢日誌（QueryLog），慢查詢日誌（SlowQueryLog）等，正常情況下，在生產系統或者壓力測試環境中很少有系統會時時開啟查詢日誌。因為查詢日誌開啟之後會將MySQL中執行的每一條Query都記錄到日誌中，會該系統帶來比較大的IO負擔，而帶來的實際效益卻並不是非常大。

2、 SQL寫法問題引起磁碟IO高

     例如：曾經在做某一個項目時，在看到資料庫磁碟IO使用率偏高，前端查詢業務交易loadrunner顯示事物回應時間偏長，通過監控工具抓取對應SQL，通過計劃分析，發現該SQL 中使用distinct 又多表關聯且是大表、然後使用order by，最終顯示10筆資料，而在產生中間過程資料進行篩選時，使用的是暫存資料表，並把資料放入暫存資料表中，記憶體剛好設定不大，於是放到磁碟中導致IO偏高。

          備忘：MySQL在執行SQL查詢時可能會用到暫存資料表，暫存資料表儲存，MySQL會先建立記憶體暫存資料表，但記憶體暫存資料表超過配置指定的值後，MySQL會將記憶體暫存資料表匯出到磁碟暫存資料表；

3、 MYSQL參數配置問題

      MYSQL預設配置效能低下，只能通過並發下嘗試調整參數配置來逐步最佳化資料庫效能，2017年底根據公司要求配合協助某一家銀行業務系統做效能測試，因為測試環境硬體資源有限，我跟公司申請了幾台過時的筆記本，然後根據生產環境軟體版本等配置要求，進行類比搭建效能測試環境，基礎軟體包含：MYSQL5.6 、centos7.2、tomcat7、 JDK1.7、redis。使用的是聯想L421 筆記本當MYSQL資料庫伺服器、L440當tomcat應用伺服器，壓力測試工具loadrunner、並發使用者100，壓力測試業務情境：使用者登入退出、相關票據資訊查詢、電子匯票交易流程等，在壓力測試過程中發現部分交易在50使用者並發時，資料庫磁碟I0使用率都偏高，特別是寫操作一直很高，例如測試登入退出交易，經監控資料庫磁碟IO率一直偏高，如下案例分析講解：

最佳化前

  壓力測試時，資料庫磁碟IO使用率大於75%，回應時間1.6秒，通過NMON監控到的資料庫資源使用方式，如一與圖二：

圖一：

 

圖二

 

最佳化後

資料庫伺服器資源使用率：

 圖四

圖五

1.3 最佳化內容

  通過最佳化innndb等影響IO、記憶體的一些參數後，效能問題明顯解決，最佳化參數內容，例如：innodb_write_io_threads、 innodb_read_io_threads、

 innodb_flush_log_at_trx_commi等InnoDB 引擎最佳化IO 子系統參數配置若干。

 

MYSQL資料庫服務磁碟IO高問題分析與最佳化