導讀
有個MySQL伺服器的磁碟I/O總有過高報警，怎麼回事？
有哪些原因
MySQL伺服器最近總是報告磁碟I/O非常高，出現這種問題，一般來說，磁碟I/O很高無非是下面幾個原因引起：
磁碟子系統裝置效能差，或採用ext2/ext3之類檔案系統，或採用cfq之類的io scheduler，所以IOPS提上不去；
SQL效率不高，比如沒有索引，或者一次性讀取大量資料，所以需要更多的I/O；
可用記憶體太小，記憶體中能快取/緩衝的資料不多，所以需要更多的I/O。
方法論已有，接下來就是動手開始排查了。
怎麼排查
先看磁碟I/O裝置，是由十幾塊SSD組成的RAID 10陣列，按理說I/O效能應該不至於太差，看iops和%util的資料也確實如此。
再來看下檔案系統、io scheduler的因素，發現採用xfs檔案系統，而且io scheduler用的是noop，看來也不是這個原因。而且看了下iostat的資料，發現iops也不算低，說明I/O能力還是可以的。
再來看看當前的processlist，以及slow query log，也沒發現當前有特別明顯的slow query，所以也不是這個原因了。
現在只剩下記憶體不足這個因素了，看了下伺服器實體記憶體是64G，用系統命令 free 看了下，發現大部分都在cached，而free的也不多。觀察InnoDB相關的配置以及status，看能不能找到端倪。
首先，看下 innodb-buffer-pool-size 分配了多少：
嗯，分配了18G，好像不是太多啊~
再看一下 innodb status：
重點關注下幾個wait值，再看下show engine innodb結果：
更為詭異的是，在已經停掉SLAVE IO & SQL執行緒後，發現redo log還在一直增長...
第一次看
停掉SLAVE執行緒後過陣子再看
看到這裡，有經驗的DBA應該基本上能想明白了，主要是因為 innodb buffer pool 太小，導致了下面幾個後果：
- dirty page 和 data page 之間相互“排擠搶佔”，所以會出現 Innodb_buffer_pool_wait_free 事件；
- redo log 也沒辦法及時重新整理到磁碟中，所以在SLAVE執行緒停掉後，能看到LSN還在持續增長；
- 同時我們也看到unpurge的列表也積攢到很大（111萬），這導致了ibdata1檔案漲到了146G之大，不過這個可能也是因為有某些事務長時間未提交。
還有，不知道大家注意到沒，Innodb_row_lock_current_waits 的值竟然是 18446744073709551615（想想bigint多大），顯然不可能啊。事實上，這種情況已經碰到過幾次了，明明當前沒有行鎖，這個 status 值卻不小，查了一下官方bug庫，竟然只報告了一例，bug id是#71520。
解決
既然知道原因，問題解決起來也就快了，我們主要做了下面幾個調整：
- 調大innodb-buffer-pool-size，原則上不超過實體記憶體的70%，所以設定為40G；
- 調大innodb-purge-thread，原來是1，調整成4；
- 調大innodb_io_capacity和innodb_io_capacity_max，值分別為2萬和2.5萬；
調整完後，重啟例項（5.7版本前調整innodb-buffer-pool-size 和 innodb-purge-thread 需要重啟才生效）。再經觀察，發現IOPS下降的很快，不再告警，同時 Innodb_buffer_pool_wait_free 也一直為 0，unpurge列表降到了數千級別。
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率一直偏高，如下案例分析講解：
4、 其它原因
- 一次請求讀寫的資料量太大，導致磁碟I/O讀寫值較大，例如一個SQL裡要讀取或更新幾萬行資料甚至更多，這種最好是想辦法減少一次讀寫的資料量；
- SQL查詢中沒有適當的索引可以用來完成條件過濾、排序（ORDER BY）、分組（GROUP BY）、資料聚合（MIN/MAX/COUNT/AVG等），新增索引或者進行SQL改寫吧；
- 瞬間突發有大量請求，這種一般只要能扛過峰值就好，保險起見還是要適當提高伺服器的配置，萬一峰值抗不過去就可能發生雪崩效應；
- 因為某些定時任務引起的負載升高，比如做資料統計分析和備份，這種對CPU、記憶體、磁碟I/O消耗都很大，最好放在獨立的slave伺服器上執行；
- 伺服器自身的節能策略發現負載較低時會讓CPU降頻，當發現負載升高時再自動升頻，但通常不是那麼及時，結果導致CPU效能不足，抗不過突發的請求；
- 使用raid卡的時候，通常配備BBU（cache模組的備用電池），早期一般採用鋰電池技術，需要定期充放電（DELL伺服器90天一次，IBM是30天），我們可以透過監控在下一次充放電的時間前在業務低谷時提前對其進行放電，不過新一代伺服器大多采用電容式電池，也就不存在這個問題了。
- 檔案系統採用ext4甚至ext3，而不是xfs，在高I/O壓力時，很可能導致%util已經跑到100%了，但iops卻無法再提升，換成xfs一般可獲得大幅提升；
- 核心的io scheduler策略採用cfq而非deadline或noop，可以線上直接調整，也可獲得大幅提升。
轉載至
https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653929453&idx=1&sn=acfdda3bbf165b2f094d0d9f021f0bec&scene=21#wechat_redirect