MySQL関連のパラメータ(主にInnoDB)について

MySQL

このエントリはMySQL Casual Advent Calendar 2015の10日目のエントリです。

先日のMySQL Casual Talks Vol8で@karupaneruraさんがパラメータの振り返りのような発表をされていたので、昨今あまり書かれなくなったMySQLに絡む設定パラメータについて書きます。それなりのメモリ(32GBとか)やSSDとか使ってる事を前提にしたような内容となります。
依存して変更した方が良いパラメータもあるので内容が前後に飛びますがご容赦下さい。またソースコードをがっつり読んだわけではなく、ベンチマーク中の挙動から推測している箇所が多分にあります。MyISAMのテーブルがサービス用データベースに同居する事を考慮していません。
結構突貫で書いているので後から微妙に修正する可能性があります。

InnoDBのパラメータ

  • innodb_buffer_pool_size
    • 挙げるまでも無く最近のバージョンのMySQLを使うなら必ず設定すべきパラメータ。データとINDEXが格納されるため、DB専用サーバとして使う場合は70%程度割り当てる事が推奨されます。後述する innodb_log_file_size やバイナリログはファイルシステムキャッシュにある程度載っていた方が良いのでギリギリまで攻める必要はないかと思います。
    • innodb_page_sizeをデフォルト(16k)より小さくした場合、例えば4kにした場合は16kの時と比較し多くのメモリを消費するため、innodb_page_sizeを16kで運用していたサーバを4kに変更した場合は設定値によりますがGB単位で下げないとスワップ使い始めるため注意が必要です。
  • innodb_log_file_size
    • redoログを書くファイルのサイズ。所謂ib_logfileのファイルサイズを指定します。redoログの合計サイズはinnodb_log_file_size x innodb_log_files_in_group となるため数GBとする場合はinnodb_log_file_sizeを512MBとか1GBに設定し、innodb_log_files_in_groupを調整するのが良いと思います。1ファイルを大きくしすぎるとファイルシステムキャッシュから溢れやすくなるためです。更新が多い環境、かつ5.6以上を利用している場合は合計4GB以上に設定するのも有りです。5.5では合計の最大値が4GBのため注意して下さい。
    • わざと小さいサイズにする事もありますが、適切にその他のパラメータやファイルシステムを選択出来るのであれば更新ヘビーな環境では大きい方がほとんどの場合、性能向上・安定する傾向があります。
    • 一般にib_logfileの合計サイズが大きくなるとcrash recoveryの時間が長くなります。
  • innodb_buffer_pool_instances
    • innodb_buffer_pool_sizeをいくつのインスタンスに分けるか指定します。デフォルトは8です。なおinnodb_buffer_pool_sizeが1G以下(未満？)の場合は1が設定されます)40GBとか大きい値をinnodb_buffer_pool_sizeに設定している場合は20とか設定することで同時実行性能が向上します。
  • innodb_flush_method
    • こちらも言わずもがな、なパラメータ。SANストレージや仮想環境の場合はデフォルトを使う事が多いようですが、BBU付きRAIDを使っていたりSSDの場合はO_DIRECTを推奨します。ファイルシステムがXFSの場合は並列書込みが可能(たぶん同一ファイルへの)となるようですのでext3やext4よりも性能が上がるケースが多いです。
  • innodb_purge_threads
    • あまり意識している人はいない気がするパラメータ。5.6ではデフォルト1、5.7ではデフォルト4となっています。更新が多く、show engine innodb status\Gを見た場合に History list length [数字] の数字が肥大化していっている場合は増やす事が推奨されます。但しMySQLの再起動が必要です。History list lengthが増えているということはundo領域(デフォルトではibdata1と同居)が肥大化する要因となるため気を配ったほうが良いです。またHistory list lengthが大きくなっていくと基本的には性能が劣化する傾向があります。
    • 何か時間の超絶かかるSELECTとかが実行されている場合もHistory list lengthが増える事があります。その場合は増やしてもたいした効果はないと考えられます(だってpurge出来ないし)
    • なお、過去の記憶ですが、無駄に増やしすぎるとコンテキストスイッチが増加して少ない設定の時よりも性能が下がるので程ほどに設定して下さい。5.7なら殆どのケースでデフォルトで十分だと思います。
  • innodb_io_capacity, innodb_io_capacity_max(max側は5.6から追加)
    • I/O アクティビティー (バッファープールからのページのフラッシュや挿入バッファーからのデータのマージなど) に上限を設定します(めんどいのでマニュアルより)。
    • 増やした方が良いケースとして知っているパターンを記載します。show engine innodb status\Gを実行し、INDIVIDUAL BUFFER POOL INFO以下の各バッファプールインスタンスのFree buffersが0で張り付いている場合は増やす事が推奨されます。少ないとバッファからのフラッシュ待ちが発生し性能劣化します。
    • これはioリクエスト数ではなく、innodb_page_sizeを1としているように見受けられます。ですのでinnodb_page_sizeが16kと4kでは異なる設定にする事が推奨されます。1000設定していてinnodb_page_sizeが16kなら 16k x 1000だけ書こうとするはずです。
    • このパラメータの挙動は5.5、5.6、5.7で全部微妙に違うはずなのでそれぞれの環境で調整する必要があります。5.7で改善され必要な状況では綺麗にpage/secで書込みを行うように見られました。5.7より前は設定値以上で書込みを行うケースが見受けられました。
    • なお、増やし過ぎても性能が下がるので様子を見ながら設定変更するのが良いです。
  • innodb_lru_scan_depth
    • ざっくり書くとInnoDBバッファプールを使い切るぐらいのデータ・INDEXが存在する環境において各バッファプールインスタンスのFree buffersをどこまで空けておくかを指定します。デフォルト1024です。よってinnodb_buffer_pool_instancesがデフォルトの8の場合は1024 x 8(page)だけMySQLは空きを確保しようとします。
    • innodb_lru_scan_depthの設定値を下回るとMySQLは空きを確保するために積極的にフラッシュしようとするようなので、innodb_io_capacityが少ないと待ち時間が長くなり性能劣化するようでした。
    • 一般にinnodb_io_capacityを増やしたらinnodb_lru_scan_depthも増やせ、と言われているようです。但し、先にも記載したとおりバッファプールインスタンス毎になるためinnodb_buffer_pool_instancesの設定値も意識して調整する必要があります。
  • innodb_checksum_algorithm
    • 最近のCPUならCRC32の計算を速く行ってくれるのでcrc32を設定することを推奨します。書込み性能向上しますが、体感できるほどのワークロードは滅多にないと思います。が気持ち的にcrc32に設定するのが良いと思います。
    • strict_crc32は使ったことが無いですが、マニュアルを読む限りサービス開始時からこのパラメータを設定出来る場合はstrict_crc32を使うのが良さそうです。
  • innodb_change_buffer_max_size
    • innodb_change_bufferingが有効であれば使われます。デフォルト25ですが木下さんのスライドによるとSSDのようにio性能高い場合は少なくすることが推奨のようです。セカンダリインデックスの更新が多くないなら下げて良いかなー、という感じです。私はベンチマーク取る時は多少下げて10にしています。
  • innodb_numa_interleave
    • 5.6.27, 5.7.9から利用可能になったパラメータ。NUMA環境においてInnoDBバッファプールをnumactl --interleave=allのようにして扱うようになります。Swap Insanity対策にも有効にしておくと良いと思います。
  • innodb_flush_neighbors, innodb_read_ahead_threshold
    • SSDやPCI-E SSDを使う場合は0にして余計なioを減らしたほうがほとんどのケースで性能向上が期待出来ます。HDD環境で0にすると涙目になるぐらい性能が劣化する事があるので注意して下さい。
  • innodb_read_io_threads, innodb_write_io_threads
    • 変更して厳密に測定した事は無いのですが、SSDを使う場合は12とか設定した方が速くなる事が多いようです。
  • innodb_adaptive_hash_index
    • KVSのようなハッシュインデックスをバッファ内に持つかの指定。デフォルトONで有効です。データ・INDEXが全てInnoDBバッファプールに収まる範囲では有効な方が速くなるケースがありますが、収まらない場合は当然ヒットしないことがあるのでオーバーヘッドが発生し性能が劣化する事があります。一応5.7.8からinnodb_adaptive_hash_index_parts(デフォルト8)というパラメータが増えていますが、バッファに収まらない環境ではやはりOFFの方が性能が出る傾向にあります。
    • 早いケースでも劇的に、という程ではないのでOFFで良いんじゃないかと最近は思っています。
    • 内部のmutex競合の問題もあるようです。中の人も問題認識はされているようですので5.8では改善されるかもしれません。

その他のパラメータ

  • thread_cache_size
    • 都度アプリケーションからMySQLへ接続/切断を行うような環境では128とかもっと大きい値を設定しておいた方が接続が速くなります。MySQLは接続ごとにスレッドを生成しますが、それをキャッシュしておくことによりスレッド生成の待ち分、短縮されるためとなります。
    • show global statusのThreads_createdが増え続ける傾向にある場合には適当に増やして様子を見るのが良いです。このパラメータはset globalで動的変更可能です。
  • back_log
    • 増やしておくと何らかのタイミングでアクセスが急増し、MySQLへの接続が一気に発生した場合の取りこぼしが減ります。MySQLのスレッド生成が追い付かない、またはTCP接続の段階でエラーとなってぶった切られた場合はLost Connectionがエラーとしてアプリケーションのログに記録される事があります。
    • この値を増やす場合は以降に記載するnet.core.somaxconnやnet.core.netdev_max_backlogも合わせて増やす事が推奨されます。

OS側色々

  • vm.swappiness
    • CentOS6系(たしか6.3あたりのKernelから)ならvm.swappiness=1(sysctl.confに書いてOS再起動しても永続化)にしておく事でスワップを使う可能性を抑制出来ます。CentOS5系なら0です。ただこの設定をするのは怖い、という人もいるようです。自分の経験ではinnodb_buffer_pool_sizeとかを適切に設定していれば問題になった事はありませんが、もちろん絶対問題無いという保障はしません。
  • net.core.somaxconn、net.core.netdev_max_backlogも多数の接続/切断が行われる環境では増やす事が推奨されます。
  • ファイルシステム
    • 更新が多い環境であればxfsの方が速い傾向にあります。ext4だと同一ファイルへの複数プロセス(スレッド)からの書込みが行えないためだと考えられます。
    • ext4, xfsではIOバリアがデフォルト有効のため、性能を求めるのであればマウントオプションにbarrier=0(またはnobarrier)と書いて無効にする事もあります。凄く速いSSDだと有効でも無効でも、MySQLから使う分には差が全然出ない事もありました。

まとめ

色々と書いてみましたがどうでしょうか。一気に書き上げたので変な記載もあるかもしれませんが参考になれば幸いです。
個人的にはその他パラメータだとinnodb_adaptive_flushing_lwmを動かした場合の挙動をもうちょっとみたいと思っています。

他にもこの辺設定しろよ、とかあればMySQL CasualのSlackなんかでご連絡いただけると喜びます。