sysbench的lua小改動導致的性能差異（上）

| 導語

最近在配合某同事做一項性能壓測，發現相同數據量、相同資料庫參數、相同sysbench壓力、相同資料庫版本和sysbench版本、相同伺服器硬體環境下，我和同事的壓測結果天差地別：一個小時壓測結束後，我的壓測結果中出現了高頻率周期性阻塞(tps,qps為0)，而同事的壓測結果中未出現阻塞(tps,qps從頭到尾都比較穩定)。正常情況下，在環境完全相同時，不可能會出現如此巨大的性能差異。但這次，不可能發生的事情它的確發生了。經過複測與排查，終於發現了其中的奧妙。

伺服器硬體信息

• 資料庫主機

  * CPU：72 process

  * memory：128G
  * 磁碟：某存儲 100G
  * 網卡：intel 萬兆網卡
• 資料庫版本：MySQL 5.7.21
• sysbench版本：1.0.9
• sysbench主機

  * CPU：20 process

  * memory：128G
  * 磁碟：本地SAS 50G
  * 網卡：intel 萬兆網卡

| 複測結果數據

先來看看壓測結果數據，如下圖所示（註：此處只截取了前幾十秒的數據進行對比）：

• 我的壓測結果（下圖可見，明顯的周期性tps，qps為0的情況）

• 同事的壓測結果（下圖可見，tps,qps較為穩定且並未出現阻塞情況）

| 抓取等待事件

看到上文第1節中的結果，如果根據以往的經驗、常識來快速判斷...估計會懵圈！！當得知同事的測試結果很穩定時，我本能地想：要把等待事件信息拉出來瞧瞧！！下面是我與同事各自在複測時截取的等待事件信息（複測時間為3分鐘）（不想仔細看等待事件內容的親可直接跳至第3節）

operation操作時間統計（每秒查詢一次，查詢數十次截取時間最長的5次）

# 我的mysql> select sys.format_time(sum(timer_wait)),operation from performance_schema.events_waits_history_long where timer_wait > 1000000000 group by operation order by sum(timer_wait) desc;+----------------------------------+-----------+| sys.format_time(sum(timer_wait)) | operation |+----------------------------------+-----------+| 1.20 s | idle || 219.20 ms | write |+----------------------------------+-----------+2 rows in set (0.01 sec)mysql> select sys.format_time(sum(timer_wait)),operation from performance_schema.events_waits_history_long where timer_wait > 1000000000 group by operation order by sum(timer_wait) desc;+----------------------------------+-----------+| sys.format_time(sum(timer_wait)) | operation |+----------------------------------+-----------+| 1.16 s | idle || 182.44 ms | write |+----------------------------------+-----------+2 rows in set (0.01 sec)mysql> select sys.format_time(sum(timer_wait)),operation from performance_schema.events_waits_history_long where timer_wait > 1000000000 group by operation order by sum(timer_wait) desc;+----------------------------------+-----------+| sys.format_time(sum(timer_wait)) | operation |+----------------------------------+-----------+| 1.17 s | idle || 164.27 ms | write |+----------------------------------+-----------+2 rows in set (0.01 sec)mysql> select sys.format_time(sum(timer_wait)),operation from performance_schema.events_waits_history_long where timer_wait > 1000000000 group by operation order by sum(timer_wait) desc;+----------------------------------+-----------+| sys.format_time(sum(timer_wait)) | operation |+----------------------------------+-----------+| 1.21 s | idle || 268.65 ms | write |+----------------------------------+-----------+2 rows in set (0.01 sec)mysql> select sys.format_time(sum(timer_wait)),operation from performance_schema.events_waits_history_long where timer_wait > 1000000000 group by operation order by sum(timer_wait) desc;+----------------------------------+-----------+| sys.format_time(sum(timer_wait)) | operation |+----------------------------------+-----------+| 11.78 s | fetch || 1.61 s | idle |+----------------------------------+-----------+2 rows in set (0.02 sec)# 同事的mysql> select sys.format_time(sum(timer_wait)),operation from performance_schema.events_waits_history_long where timer_wait > 1000000000 group by operation order by sum(timer_wait) desc;+----------------------------------+-----------+| sys.format_time(sum(timer_wait)) | operation |+----------------------------------+-----------+| 21.17 ms | idle || 1.58 ms | fetch |+----------------------------------+-----------+2 rows in set (0.02 sec)mysql> select sys.format_time(sum(timer_wait)),operation from performance_schema.events_waits_history_long where timer_wait > 1000000000 group by operation order by sum(timer_wait) desc;+----------------------------------+-----------+| sys.format_time(sum(timer_wait)) | operation |+----------------------------------+-----------+| 185.44 ms | idle |+----------------------------------+-----------+1 row in set (0.02 sec)mysql> select sys.format_time(sum(timer_wait)),operation from performance_schema.events_waits_history_long where timer_wait > 1000000000 group by operation order by sum(timer_wait) desc;+----------------------------------+-----------+| sys.format_time(sum(timer_wait)) | operation |+----------------------------------+-----------+| 141.82 ms | lock || 110.90 ms | fetch || 3.30 ms | idle |+----------------------------------+-----------+3 rows in set (0.01 sec)mysql> select sys.format_time(sum(timer_wait)),operation from performance_schema.events_waits_history_long where timer_wait > 1000000000 group by operation order by sum(timer_wait) desc;+----------------------------------+-----------+| sys.format_time(sum(timer_wait)) | operation |+----------------------------------+-----------+| 2.91 s | idle |+----------------------------------+-----------+1 row in set (0.00 sec)mysql> select sys.format_time(sum(timer_wait)),operation from performance_schema.events_waits_history_long where timer_wait > 1000000000 group by operation order by sum(timer_wait) desc;+----------------------------------+-----------+| sys.format_time(sum(timer_wait)) | operation |+----------------------------------+-----------+| 1.20 s | idle |+----------------------------------+-----------+1 row in set (0.01 sec)mysql> select sys.format_time(sum(timer_wait)),operation from performance_schema.events_waits_history_long where timer_wait > 1000000000 group by operation order by sum(timer_wait) desc;+----------------------------------+-----------+| sys.format_time(sum(timer_wait)) | operation |+----------------------------------+-----------+| 215.34 ms | idle |+----------------------------------+-----------+1 row in set (0.01 sec)

限於篇幅，本文將分期進行推送，下一篇"sysbench的lua小改動導致的性能差異（下）"，精彩內容不容錯過！

| 作者簡介

羅小波·沃趣科技高級資料庫技術專家

IT從業多年，歷任運維工程師、高級運維工程師、運維經理、資料庫工程師，曾參與版本發布系統、輕量級監控系統、運維管理平台、資料庫管理平台的設計與編寫，熟悉MySQL體系結構，Innodb存儲引擎，喜好專研開源技術，追求完美。