【背景】

之前我們碰到一些MySQL的效能問題，比如伺服器日誌備份時可能會導致慢查詢增多，一句簡單的select或insert語句可能執行幾秒，IO負載較高的伺服器更容易出現併發執行緒數升高，CPU上升等問題。

最近學習了MySQL InnoDB IO相關的部分核心原理，可以幫我們瞭解伺服器IO瓶頸對MySQL效能的影響，下面以MySQL5.7.23的原始碼為例

【原理】

1、InnoDB實現了同步IO和非同步IO兩種檔案讀寫方式

(1、)對於讀操作，通常使用者執行緒觸發的資料請求都是同步讀，其他後臺執行緒觸發的是非同步讀。

(2、)對於寫操作，InnoDB是WAL模式，先寫日誌，延遲寫資料頁；

redo log的寫操作大部分是非同步寫，主要在下面場景下觸發

<1、>redo log buffer空間不足時；

<2、>當引數innodb_flush_log_at_trx_commit設定為1時，每次事務提交都會做一次fsync，相當於是同步寫；

<3、>master執行緒每秒做一次redo fsync；

<4、>checkpoint

<5、>例項shutdown時

<.6、>binlog切換時

Page cleaner執行緒負責髒頁的重新整理操作，其中double write buffer的寫磁碟是同步寫，資料檔案的寫入是非同步寫。

2、同步讀寫操作通常由使用者執行緒來完成，下面先分析同步讀

當使用者執行緒執行一句SQL時，如果請求的資料頁不在buffer pool中，就需要將檔案中的資料頁載入到buffer pool中，

從函式buf_read_page可以看到這裡是同步讀操作，如果IO有瓶頸，響應延遲，那麼該執行緒就會被阻塞。

從函式buf_page_init_for_read可以看到，在讀資料頁時會加X鎖

這時如果有其他使用者執行緒請求相同的資料頁時，從函式buf_wait_for_read看到，嘗試獲取X鎖，就會處於阻塞狀態。

當伺服器IO成為瓶頸，發生上面的問題時，就會出現SQL執行變慢

問題進一步惡化，大量慢查詢，執行中的執行緒處於等待狀態，佔用了Innodb執行緒（innodb_thread_concurrency我們的配置大部分是0或64，實際上通常是CPU的邏輯核數40）

對於併發較高的系統，會導致其他大量的執行緒處於等待佇列中，併發執行緒過高又會導致上下文切換頻繁，CPU上升。

3、一個同步寫的例子

前面做過一個測試，執行500W條insert語句

用source執行insert指令碼，TPS大約在每秒700，後面並行同時執行3個insert指令碼，TPS達到每秒2000左右，IO %util已經接近100%

由於此時引數innodb_flush_log_at_trx_commit設定為1時，每次事務提交都會做一次fsync，相當於是同步寫，IO已達到瓶頸，TPS處理能力無法提高。

當將引數innodb_flush_log_at_trx_commit臨時調整為2，改為後臺程式進行非同步寫，並行執行8個insert指令碼，TPS達到每秒約1W左右，IO %util約在8%。

實現邏輯可以關注log_write_up_to函式

【應用場景】

1、當伺服器IO出現瓶頸，會導致MySQL效能大幅下降，因此建議儘可能的利用伺服器記憶體資源，將例項的innodb_buffer_pool_size設定為實體記憶體的70%左右；

2、合理的拆分，儘可能的讓一個例項的熱點資料都可以快取在innodb buffer pool中

3、對於某些場景下執行指令碼，或初始化資料時，可以將innodb_flush_log_at_trx_commit臨時設定為2，能大幅提升匯入效能。

參考資料：

伺服器IO瓶頸對MySQL效能的影響

相關文章