|
| 作者 王文安，騰訊CSIG數(shù)據(jù)庫專項的數(shù)據(jù)庫工程師，主要負責(zé)騰訊云數(shù)據(jù)庫 MySQL 的相關(guān)的工作，熱愛技術(shù)，歡迎留言進行交流。 在日常工作中，有時候會發(fā)現(xiàn) MySQL 的狀態(tài)不太對勁，這時候就會看看監(jiān)控指標(biāo)，可能會發(fā)現(xiàn)：寫入 QPS 開始出現(xiàn)毛刺，或者 IO 的指標(biāo)很高。這時候該怎么辦呢？本文會從 Linux 層面入手，根據(jù)不同的 IO 特點來分析 MySQL 數(shù)據(jù)庫可能遇到的問題，并給出一些可參考的優(yōu)化/緩解思路。一、怎么看懂 IO 指標(biāo)？檢查 IO 的問題會使用iostat這個命令，這里展示一下命令的效果（iostat -x 1 -m，debian 10.2）：
iostat avg-cpu 自然就是 CPU 相關(guān)的指標(biāo)，判斷 IO 問題時可以關(guān)注 %iowait，其他指標(biāo)的意義如下： ·r/s 和 w/s：合并過后的讀請求和寫請求的每秒請求數(shù)，可以當(dāng)做 IOPS 來理解。 ·rMB/s 和 wMB/s：磁盤的讀寫吞吐量。 ·rrqm/s 和 wrqm/s：每秒合并的讀請求和寫請求數(shù)量。 ·%rrqm 和 %wrqm：合并的讀請求和寫請求百分比。 ·r_await 和 w_await：讀請求和寫請求的平均響應(yīng)時間，包含真正的處理時間和隊列中的等待時間（ms）。 ·aqu-sz：平均隊列深度。 ·rareq_sz 和 wareq_sz：一個讀請求和寫請求的平均物理大?。↘B）。 ·scvtm：計算出來的平均 IO 響應(yīng)時間，目前已經(jīng)不準(zhǔn)確，不用再關(guān)注。 ·%util：如果使用了 RAID 或者 SSD，則忽略這個指標(biāo)，僅在單塊機械盤上準(zhǔn)確。 一般來說，評價一塊 IO 設(shè)備（忽略機械盤的情況，沒有評價的意義）是否達到了高負載情況，可以看這幾個指標(biāo)：r/s，w/s，rMB/s，wMB/s，r_await，w_await，aqu-sz。 二、MySQL 與 IO由于 MySQL 涉及到 IO 相關(guān)的參數(shù)會比較多，因此這里僅一部分經(jīng)常用到的參數(shù)以及在測試&模擬中使用默認設(shè)置： 參數(shù) 設(shè)置 備注 innodb_io_capacity 16000 定義了后臺任務(wù)可用的 IOPS 量 innodb_io_capacity_max 32000 定義了后臺任務(wù)可用的最大 IOPS 量 innodb_flush_log_at_trx_commit 1 控制事務(wù)的提交策略，具體信息請參考官方文檔 sync_binlog 1 控制 binlog 落盤的頻率，具體信息請參考官方文檔 innodb_io_capacity 和 innodb_io_capacity_max 是最直接限制 IOPS 的指標(biāo)，大多數(shù)時候，SSD 可以設(shè)置成 16000 或者更高的數(shù)值，如果是云主機或者其他的共享存儲設(shè)備，則需要了解一下詳細的 IOPS 上限再具體調(diào)整。trx_commit 和 sync_binlog 這兩個參數(shù)也放進來的原因是不同的參數(shù)組合對 IO 的壓力也會有區(qū)別。通常的用法是雙 1 或者 20（二零），參考官方文檔的描述，雙 1 在每次提交事務(wù)的時候都會刷盤，對 IO 的壓力要高不少；20 則是滯后刷盤，對 IO 的壓力會較小，因此寫入 QPS 會高一些。 另外，可以關(guān)注到一個細節(jié)，innodb_io_capacity 的描述對象是：后臺任務(wù)。這代表著 MySQL 后臺的 flush，purge 操作會受到這個參數(shù)設(shè)置的限制。 三、測試環(huán)境本次測試使用騰訊云服務(wù)器的高 IO 型 IT3 實例，自帶了 3TB 的本地 NVME。由于騰訊云平臺限制了系統(tǒng)版本（debian 9），因此 iostat 在輸出內(nèi)容上稍有差異，但是不影響分析，簡單用 fio 跑了一下 16k（innodb_page_size 的默認配置） 的 IO 性能： 類型 IOPS 吞吐量（MB） 隨機讀 121959 1905 隨機寫 98326 1536 隨機讀寫（讀部分） 47129 750 隨機讀寫（寫部分） 47152 754 那么，為什么測試環(huán)境要用一個完全不會有 IO 瓶頸的呢？這是為了方便展示調(diào)整 MySQL 之后的效果。如果整套系統(tǒng)的 IO 設(shè)備負載長期處于高水位的話，最佳優(yōu)化策略是升級 IO 設(shè)備，而不是調(diào)整 MySQL。因此所有的分析和應(yīng)對的場景都屬于中、短時間內(nèi)的高 IO 負載。 四、IO 分析1. 純寫入先看一種比較純粹，但是較少出現(xiàn)的 IO 負載場景：
iostat_wo 這種類型的指標(biāo)有一個明顯的特點：IO 負載中沒有，或者幾乎沒有讀取相關(guān)的壓力。這種負載的特征一般是緩存足夠放下所有的數(shù)據(jù)，因此不需要從磁盤上讀數(shù)據(jù)，壓力全部在寫入上。 首先能想到的，顯然是trx_commit 和 sync_binlog 這兩個參數(shù)，把雙 1 改成 20 的配置，產(chǎn)生 QPS 變化的原因也比較好理解：原本一個事務(wù)需要刷一次磁盤，變成多個事務(wù)刷盤操作合并到了一起，就像是提高了每個 IOPS 的“事務(wù)處理效率”，比如從 1 事務(wù)/IOPS 變成了 N 事務(wù)/IOPS。 除了提高“每個 IOPS 的事務(wù)處理效率”以外，其實還會有另外一種思路：適當(dāng)限制后臺任務(wù)的 IOPS。實際上 MySQL 的寫入會涉及到非常多的 buffer，log，并產(chǎn)生后臺任務(wù)相關(guān)的數(shù)據(jù)，出現(xiàn)中等時間的高寫入場景時，后臺任務(wù)一般會慢慢堆積需要 flush 和 purge 的數(shù)據(jù)，如果 innodb_io_capacity 和 innodb_io_capacity_max 的參數(shù)設(shè)置得比較高，可能會讓后臺任務(wù)消耗過多的 IO 資源，這時候適當(dāng)調(diào)低一些可以在一段時間內(nèi)穩(wěn)住寫入 QPS，等高寫入的壓力過去之后再回滾設(shè)置。 另外，如果有更加精細化的調(diào)整方式，應(yīng)該會有更好的效果，目前只能靠這個參數(shù)一刀切，不過不要改得太低，因為當(dāng)后臺任務(wù)堆積的數(shù)據(jù)過多，觸發(fā)強制刷臟/checkpoint 等機制時，會大幅度的侵占 IO 資源，導(dǎo)致非常劇烈的寫入 QPS 波動，這一點需要注意。 這里給出“反向調(diào)整”的效果，日志數(shù)據(jù)取自于某一個 sysbench 客戶端，在 2050s 左右的時候大幅度調(diào)高了 io_capacity： 2. 純讀取另外一種比較純粹的場景，自然就是純讀取了，例如：
iostat_ro 純讀取的 IO 特征說明緩存不夠大，需要從磁盤讀取熱數(shù)據(jù)。那么增加內(nèi)存和調(diào)高 innodb_buffer_pool_size，把更多的數(shù)據(jù)放到內(nèi)存中就是最好的解決方案。至于需要加多少內(nèi)存，可以結(jié)合實際業(yè)務(wù) SQL 的響應(yīng)時間（做好索引優(yōu)化之后）和 buffer_pool 的命中率，從經(jīng)驗值來看，命中率（show engine innodb status里面）高于 99.5% 是比較理想的，如果實際 SQL 的響應(yīng)時間不滿足業(yè)務(wù)的需求，那么就可以根據(jù)實際命中率來估算需要的內(nèi)存大小。 由于從 5.7 開始，MySQL 支持動態(tài)調(diào)整 innodb_buffer_pool_size 這個參數(shù)了，因此變更帶來的影響相對小了很多，不過調(diào)整還是有代價的，盡量在業(yè)務(wù)低峰期操作。 3. 讀寫混合最常見的肯定是讀寫混合的場景，比如像這樣子的：
iostat_rw 分析起來會相對復(fù)雜一點，但是結(jié)合純讀取和純寫入的分析之后，可以比較容易想到如下的可能性： 場景一：讀寫混合的場景。 場景二：純寫入的場景，但是內(nèi)存放不下所有的數(shù)據(jù)，需要從磁盤讀取之后再修改。 先看比較簡單的場景2，本質(zhì)上還是類似于純寫入場景，但是由于內(nèi)存不夠大，因此在排查 MySQL 的讀寫 SQL 比例（global status 中的 com_xxx 系列數(shù)據(jù)）之后，可以參考純寫入這個章節(jié)的內(nèi)容進行分析處理。 雖然場景 1 會復(fù)雜一些，但是結(jié)合純寫和純讀的內(nèi)容，分析的思路就有了，比如依次思考如下問題： 業(yè)務(wù)讀寫比例大概是多少？ IO 系統(tǒng)的讀性能問題比較大還是寫性能問題比較大？ 如果說： 業(yè)務(wù)讀的比例高（例如 >4:1），IO 系統(tǒng)讀的性能問題比較大：那么參考純讀取的內(nèi)容，調(diào)高 buffer_pool_size 。 業(yè)務(wù)讀的比例高（例如 >4:1），IO 系統(tǒng)寫的性能問題比較大：那么參考純寫入的內(nèi)容，調(diào)整事務(wù)提交策略或者 io_capacity。另外，此類場景可能是因為在大批量變更數(shù)據(jù)，也可以考慮一下優(yōu)化這種業(yè)務(wù)行為。 業(yè)務(wù)寫的比例高（例如<4:1），IO 系統(tǒng)讀的性能問題比較大：那么參考純讀取的內(nèi)容。 業(yè)務(wù)寫的比例高（例如<4:1），IO 系統(tǒng)寫的性能問題比較大：那么參考純寫入的內(nèi)容。 業(yè)務(wù)的讀寫比例沒有什么明顯的特點，IO 系統(tǒng)讀寫的性能問題都比較嚴(yán)重：考慮以上所有的方法，包括升級硬件。 4.一些tips：吞吐量，IOPS 和一些分散讀寫壓力的手段吞吐量和 IOPS ，一般情況下衡量 IO 系統(tǒng)性能最直觀的指標(biāo)，并沒有特別的提及，主要原因還是判斷起來很簡單：如果iostat的指標(biāo)已經(jīng)達到或者接近了實際硬件的指標(biāo)（比如達到了 75%），那么根據(jù)業(yè)務(wù)量增長的情況及早規(guī)劃硬件升級或者其他的手段來分散讀寫壓力。 常規(guī)的手段，可以簡單的遵循以下場景來酌情使用：讀多寫少讀寫分離，寫多讀少拆庫拆表加緩存。 判斷 MySQL IO 情況的指標(biāo)如果 MySQL 在 IO 方面出現(xiàn)了阻塞的現(xiàn)象，那么可以觀察以下幾個指標(biāo)： 參數(shù)名 意義 備注 Innodb_data_pending_fsyncs 當(dāng)前阻塞的 fsync 操作 一般為 0，比較高的話，看一下 innodb_flush_method 的設(shè)置 Innodb_data_pending_reads 當(dāng)前阻塞的 read 操作 一般為 0，如果指標(biāo)較高且影響業(yè)務(wù)的話，參考讀壓力的應(yīng)對方式 Innodb_data_pending_writes 當(dāng)前阻塞的 write 操作 一般為 0，如果指標(biāo)較高且影響業(yè)務(wù)的話，參考寫壓力的應(yīng)對方式 Innodb_os_log_pending_fsyncs 寫 redo log 時，當(dāng)前阻塞的 fsync 操作 一般為 0，如果大于 0 的話，通常就是 IO 設(shè)備的瓶頸，考慮把 redo log 遷移到 SSD 或者做 IO 隔離，獨占 IO 設(shè)備的性能 Innodb_os_log_pending_writes 寫 redo log 時，當(dāng)前阻塞的 write 操作 一般為 0，如果指標(biāo)較高且影響業(yè)務(wù)的話，參考寫壓力的應(yīng)對方式 InnoDB 還有很多其他的 read 和 write 的指標(biāo)，通過show global status like '%innodb%read%'之類的操作都可以看到，但是這類指標(biāo)一般是累計值，需要對比上一個取值時間的差值才能有比較實際的作用，通常也是用來判斷 MySQL 的讀寫比例用，結(jié)合上表的 pending 數(shù)據(jù)和其他的系統(tǒng)指標(biāo)來綜合判斷 IO 系統(tǒng)的負載。這些指標(biāo)也是建議監(jiān)控起來的。 五、總結(jié)解決 IO 問題的手段是多樣化的：最省事的升級硬件；最快捷的調(diào)整 MySQL（本文主要內(nèi)容）；比較常用的架構(gòu)調(diào)整手段（讀寫分離，拆庫拆表）；結(jié)合實際情況來優(yōu)化業(yè)務(wù)的行為（合并單行操作的 DML，拆分單個大量更新數(shù)據(jù)的 DML 語句等）。 雖然不能對上述手段進行全面的介紹，但是iostat提供的信息在分析 MySQL 瓶頸時還是非常有用的，本文僅從硬件的負載特點出發(fā)，簡述了調(diào)整 MySQL 的一些思路。實際上需要多種手段結(jié)合起來才能比較好的應(yīng)對 IO 方面的問題。
|
|
|
