MySQL5.6と5.7のちょっとした違いとinnodb_thread_concurrencyの影響

MySQL

この記事はMySQL Casual Advent Calendar 2016の22日目です。

innodb_thread_concurrencyを最近のデフォルトである0と論理CPUコア数の2倍の48に設定した場合に観測出来た小ネタです。ベンチマークのtpsを載せていますが、1回しか取得してないので、割と誤差があると考えられるため、目安程度に見てください。

  • 環境
    • CentOS 6.6(2.6.32-504.12.2.el6.x86_64)
    • Xeon E5-2643 v2 3.5GHz x 2(2P12C24T)
    • Memory 64GB (8GB x 8, DDR3 1866MHz)
    • HDD SAS 300GB x 2 10k rpm(RAID1 BBU付き)
    • FileSystem ext4
    • ベンチマークのデータ量はinnodb_buffer_pool_sizeに収まる量
      • メモリで殴るような環境を想定
  • ベンチマークツール
    • sysbench 0.5 (oltp.lua)
    • 実行スレッド数(8, 16, 32, 64, ... , 2048, 4096)
  • ベンチマーク実行コマンド(以下をスレッド数1から512まで8個同時実行)
    • 1ケース実行毎に150秒間のSleepを入れています。
      LD_PRELOAD=/usr/lib64/libjemalloc.so.1 \
      /usr/local/sysbench-0.5/bin/sysbench \
        --test=/usr/local/sysbench-0.5/lua/oltp.lua \
        --rand-init=on --db-driver=mysql --oltp-read-only=off \
        --rand-type=uniform \
        --oltp-tables-count=18 --oltp-table-size=3000000 \
        --mysql-socket=/var/lib/mysql/mysql.sock \
        --mysql-db=sbtest5 \
        --mysql-user=sbtest --mysql-password=sbtest-pw \
        --max-time=300 --max-requests=0 \
        --num-threads=$thread \
        run

上記条件でベンチマークを行なった際のグラフは以下のようになります(モニタリング間隔15秒)。
左から5.7のinnodb_thread_concurrency 48 / 0, 5.6のinnodb_thread_concurrency 48 / 0の結果です。


5.7側のDMLがジグサグしていて安定感がありません。また、(自分の観測範囲で)あまり使われてない以下を設定しています。

innodb_max_purge_lag_delay=1000000
innodb_max_purge_lag=100000

こちらの設定をざっくり書くと、SHOW ENGINE INNODB STATUS\G を実行した際に確認できるHistory list lengthをinnodb_max_purge_lagの設定値(100000)に収まるようにしようとします。

その結果、innodb_thread_concurrencyが48の場合は収まるような結果となっていますが、innodb_thread_concurrencyが0の場合は収まらずに伸び続けています。History list lengthは多くても5000以下程度に収まるのが望ましい、と考えています。一定以上の大きさになると割と性能が下がる傾向にあるためです。よって、innodb_max_purge_lagの制御が行われる状態はあまり良くありません。
ただ、今回の結果を見ると全部メモリに載る状態だと性能への影響が軽微なのかもしれません。LinkBenchでメモリに載り切らない場合では10000超えたあたりで結構性能低下した事があるのでその他の要因と関連がありそうです。

またcheckpoint ageが5.6は150秒間のスリープの間に減少していますが、5.7ではinnodb_io_capacityの設定値に準じて書き出しを行うため減少しきらない状態となっています(5.6はinnodb_io_capacityの制御が割と雑なため)。
　※完全に同じ条件じゃない、と言われると実際そうです

とはいえディスクI/Oがあまり強くない環境で大量の接続・更新が行われる場合、innodb_thread_concurrencyを48にすることでHistory list lengthの伸びを抑制出来るためベンチマーク後のibdata1のサイズは結構な差が出ます。

以下ベンチマーク後のibdata1のファイルサイズです。

5.7 ccr 48 5.7 ccr0 5.6 ccr48 5.6 ccr0
268MB 780MB 204MB 460MB

5.7の方がibdata1のサイズが大きいのは、単純に5.6よりtpsが高く、処理量が多いためです。
以下sysbenchのtpsです。

スレッド数 5.7 ccr48 5.7 ccr0 5.6 ccr48 5.6 ccr0
8 2133.73 2176.16 2085.15 2130.29
16 3454.79 3454.86 3378.4 3433.78
32 3860.71 3920.18 3637.29 3665.24
64 4077.97 4300.74 3659.85 3731.46
128 4529.18 4623.69 3669.78 3755.15
256 4669.99 4714.38 3648.23 3655.07
512 4416.23 4466.67 3614.36 3729.47
1024 4683.41 4634.53 3520.22 3585.23
2048 4460.89 4590.36 3407.69 3499.41
4096 4253.65 4435.6 3049.28 3343.77

ということでジグザグなグラフの割に5.7の方がトータルで見ると性能が出ていることになります。しかし、あるタイミングを切り取れば5.6より性能が出ていない状態を表しています。

では5.7で安定させるためには何をしたら良いでしょうか。性能が下がるタイミングではcheckpont ageが張り付いていて、ibdファイルへのフラッシュが動作していると考えられます。例えばib_logfile系をtmpfsに置くとか、I/O強いにすれば良いのはそれはそうなんですが、そう出来る環境とは限りません。今回のケースは5.6の設定をほぼそのまま5.7で使用している事が悪いので、パラメータ調整を行います。
流用するようなケースはバージョンアップが多いかと思いますので、そのタイミングで調整が行える可能性が高いかと思います。

という事でフラッシュをもっとゆっくりさせれば良いのでシンプルにib_logfileを増やします。

innodb_log_files_in_group = 12

これでib_logfile0からib_logfile11までの合計12GBになります。この状態で5.7のみベンチマークを再取得したグラフとtpsは以下のようになります。取得順を間違えてて、今回は左がinnodb_thread_concurrency 0, 右がinnodb_thread_concurrency 48となります。




スレッド数 5.7 ccr48 5.7 ccr0
8 2262.15 2289.72
16 4214.03 4231.89
32 5476.94 5492.78
64 5757.04 5772.37
128 5805.44 5817.16
256 5732.35 5751.25
512 5810.34 5830.87
1024 5930.8 5744.61
2048 5785.43 5656.04
4096 5523.51 5536.83

安定した結果、tpsは更に上昇しcheckpoint ageが張り付くこともありません。とはいえinnodb_thread_concurrencyが0の場合、相変わらずHistory list lengthは伸びます。この辺りまで来るとH/Wの変更が無理だとDurability下げるしかないのかなー、と思います(doublewrite止めるとか)。

適切にib_logfile(REDO)増やしたりinnodb_thread_concurrency, innodb_max_purge_lagを設定する事で延命出来る可能性があります。メモリ大量に積んでて「バッファにデータもINDEXも載るから処理は余裕だぜー」と油断してると5.7の環境でib_logfileが少ない場合、気付かないタイミングでスローダウンしてる可能性があるのでご注意ください。特にモニタリング間隔が5分とかだと埋もれる可能性が高いです。

なお、単純にundo領域が肥大化するのをどうにかしたいだけであれば innodb_undo_directory, innodb_undo_tablespaces, innodb_undo_log_truncate とか設定しておく方が特に5.7においては良いと思います。

という感じです。5.6と5.7の違い、innodb_thread_concurrencyを0以外にした方が良いケースについて知っておいて頂ければ幸いです。

明日は@yoku0825さんによる「COUNTを速くする（？）SQL1本ノック その2」です。