MariaDB Galera Clusterのパラメータを変化させたときの書き込み性能について

こんにちは，S.T.です。今回はMariaDB Galera Clusterの性能に寄与するパラメータを調査し，その挙動と，最終的にデフォルト比でどの程度性能が改善できるのかを検証します。特に，パラメータ単独で変化させたときの性能を重点的に確認します。

1.MariaDB Galera Cluster

MariaDB Galera Clusterは，MariaDBで利用可能なマルチマスタのRDBクラスタです。Galera Cluster自体の解説は，当ブログで別の方が検証を行った記事がありますので，そちらを参照してください。
クラスタ復旧時の順番が大切　MariaDB Galera Clusterの障害復旧方法
Galera Cluster を無停止で新規ノードにデータを完全同期する

今回注目すべきGalera Clusterの特性は，

• クラスタ全体で同じトランザクションを実行することを保証できる
• 単一ノードのMariaDBよりも書き込み性能が低い

という2点です。Galera Clusterは，あるノードに対してトランザクションを実行したとき，実際にコミットする前に他のノードにそのトランザクションの情報を送り，全ノードで実行可能であることを確認してから（各ノードが個々に）コミット処理を行います。この挙動により，あるマスタノードに対して発行して成功したトランザクションは，必ずクラスタ全体で成功していることが保証されます（ノードごとのタイムラグは存在する）。後者の特性は，コミット処理に各ノードで合意を取る，というひと手間が挟まるためです。ネットワーク越しに通信して合意を取りにいくわけですから，書き込みにおいては無視できない性能の差があることが想定されます。

大前提として，更新処理を大量に行うようなワークロードにGalera Clusterを採用することが適切かというと，そうではないかもしれません。しかし，そうではないケースにおいて，更新性能が低くても良いということはありません。突発的なスパイクへの対応や短時間でのバッチ処理など，更新処理が高速になるメリットは多くあります。また，後述しますが参照はDBの設定よりもデータがメモリに載っているかどうかと，実行するクエリが索引をうまく使えるかが支配的です。そこで，今回は更新の性能を中心に検証します。

2.検証環境

今回はAWS上に以下のVMを建てて，3台構成のGalera Clusterを構築しました。EC2の設定や構築手順については，特別なことはせず一般的な手法を採ったため，省略します。

• AWS EC2
• r6i.large（2vCPU/16GB RAM/gp2 SSD）3台 同一AZ
• Amazon Linux 2
• MariaDB 10.7.3

クライアントには同じスペックのVMをもう一台準備し，そこからHA Proxyを介してクラスタに接続します。

負荷試験はmysqlslapを用いて行います。mysqlslapはMySQLオフィシャルで提供されている負荷エミュレーションクライアントであり，簡単に負荷試験を行うことができます。mysqlslapは以下の設定をベースに，テーブルスキャン（read），挿入（write），PK SELECT（key），テーブルスキャンと挿入の混合（mixed），更新（update）を実行します。ただし，更新についてはGalera Clusterの特性上HA Proxyで分散させるとDeadLockが発生して試験に失敗するため，updateを指定するときは単一ノードのみにクエリが流れるようにします。

• –auto-generate-sql-add-autoincrement
• number-int-cols=10
• number-char-cols=10
• iterations=10
• concurrency=50
• auto-generate-sql-write-number=5000
• number-of-queries=1000

この設定でmysqlslapを実行すると，数値カラム10個，文字列カラム10個，AUTO INCREMENTつきカラムありのテーブルに，5000レコード，1000クエリを50スレッドで負荷をかける，という操作を10回繰り返した結果を得ることができます。

3.個々のパラメータの検証

MariaDBには様々なパラメータが存在します。そして，パフォーマンスに大きな影響を持つパラメータのデフォルト値は，多くの場合十分な性能を引き出すのに不十分な値になっています。特に，本番環境で使用するような高性能なノードでは，そのリーソスを十分に使うことができません。まずは，パラメータを1つ1つ個別に変更して，デフォルトとの性能の差を検証します。

innodb_buffer_pool_size

このパラメータは，パッファプールのサイズを指定するパラメータです。バッファプールとは，テーブルや索引のデータを載せるためのメモリ領域です。InnoDBが使えるMySQL系のDBを触ったことがある方は，一度は目にしたことがあるド定番のパラメータではないでしょうか。このサイズが大きいと，多くのデータをメモリに載せることができ，ディスクアクセスが減るため，大幅にパフォーマンスが向上します。デフォルトは128MBで，推奨は搭載しているメモリの80%です。どんな環境でも数GBにはなるはずなので，必ず設定するべきパラメータです。

表1と図1に，デフォルトとinnodb_buffer_pool_sizeをメモリ（16GB）の80%である12.8GBに設定した場合の性能検証結果の比較を示します。defaultがデフォルト設定，tunedがチューニング済みの結果で，min，max，avgはそれぞれ最小，最大，平均のクエリ時間（秒）です。グラフでは，値が大きいreadを除いた結果の平均のみを比較しています。

	default avg	default min	default max	tuned avg	tuned min	tuned max
key	0.021	0.019	0.024	0.021	0.02	0.024
read	7.405	6.551	8.372	7.447	6.494	8.432
write	0.474	0.416	0.652	0.441	0.393	0.615
mixed	0.276	0.224	0.419	0.247	0.22	0.284
update	0.555	0.531	0.602	0.574	0.534	0.649

これは，今回の検証に使っているデータサイズが小さいため，デフォルトの128MBでメモリにおさまってしまうためです。特にテーブルスキャンとPK読み取りは，そのデータがメモリ載っているか否かが性能の鍵になります。今回はデータがすべてメモリ載っているため，テーブルスキャンはメモリ上のデータをスキャンしているだけになります。つまり，これ以上の高速化はのぞめません。また，PK読み取りはメモリ上のデータをPKで引いてくるという操作を行っているだけになるため，こちらも同じくこれ以上速くなることはないでしょう。

データ量を増やせば明確な差が出ることが確認できるはずですが，参照のパフォーマンスはメモリにデータが載っていること，索引を正しく使ってデータへのアクセスを減らすこと，の2点が支配的なのは明らかです。DBのチューニングよりもアプリケーションが実行するSQLが重要になってくるため，今回の検証では扱いません。

wsrep_slave_threads

このパラメータは，各ノードが同期をとるためのスレッド数を指定するものです。Galera ClusterはWrite setという単位で書き込みの同期をとっており，それらを並列して処理します。デフォルトは1です。並列度のパラメータは，一般的にCPUコア数と同じやその倍に設定すると良いパフォーマンスが出ると言われています。

今回は，2vCPUのインスタンスですので，2の場合，4の場合をそれぞれ検証しました。表2，図2にデフォルトとwsrep_slave_threadsが2，4の場合の性能検証結果の比較を示します。thread 2，thread 4が，設定値2，4に対応します。

	default avg	default min	default max	thread 4 avg	thread 4 min	thread 4 max	thread 2 avg	thread 2 min	thread 2 max
key	0.021	0.019	0.024	0.021	0.016	0.023	0.021	0.019	0.024
read	7.405	6.551	8.372	7.739	6.389	8.511	7.814	6.508	8.599
write	0.474	0.416	0.652	0.068	0.065	0.076	0.305	0.261	0.363
mixed	0.276	0.224	0.419	0.127	0.045	0.847	0.168	0.13	0.225
update	0.555	0.531	0.602	0.137	0.106	0.278	0.553	0.497	0.634

wsrep_slave_threadsが影響するのは書き込み同期処理なので，write，mixed，updateにおいて顕著な差が出ています。特に，writeはデフォルトの10倍以上の性能が出ています。並列処理が行われるのは，安全に処理できると判断された場合のみです。writeは単純なINSERTなので，常に高い並列度を維持できていると考えられます。更新処理がイミュータブルデータモデルを採用するアプリケーションでは，このパラメータにより非常に高速化できる可能性があります。

innodb_flush_log_at_trx_commit

このパラメータは，コミット処理を行った際のディスクの書き込みポリシーを設定するものです。デフォルトは1で，コミットをするたびにファイルに書き込みを行い，フラッシュします。RDBにおいて，ACIDコンプライアンスは非常に重要ですが，これを遵守できるのはinnodb_flush_log_at_trx_commitが1の場合のみです。

この値を0にすると，コミットをしても即時にファイルに書き込むことはせず，非同期で約1秒に1回ファイルに書き込み，フラッシュを行います。電源故障などでサーバが突然停止した場合，当然フラッシュしていないデータは消失します。つまり，最長で約1秒間のデータを失う可能性があります。反面，例えば秒間10回の書き込みを行うシステムであれば，書き込み回数が1/10になり，レスポンスタイムやスループットが向上します。

この値を1にすると，コミットしたタイミングでファイルに書き込みのみを行い，フラッシュを非同期で約1秒に1回行います。つまり，最長で1秒は実際にディスクには書き込まれず，OSのバッファの中で待つことになります。0の場合と同様，性能向上の代償に最長で約1秒間のデータを失う可能性があります。

単一ノードのデータベースでは，データロストの可能性を考えるとinnodb_flush_log_at_trx_commitを積極的に1以外にするのは避けたいところです。ここで注目したいのは，Galera Clusterの「クラスタ全体で同じトランザクションを実行することを保証できる」という特性です。例えば，innodb_flush_log_at_trx_commitを2に設定していて，ある書き込みトランザクションを実行直後に1ノードクラッシュしたとしても，1秒以内に他のノードがクラッシュしなければ，トランザクションの結果は正しくディスクにフラッシュされ，データはロストせずに済みます。これを生かして，Gelera Clusterではinnodb_flush_log_at_trx_commitを2に設定し，書き込みパフォーマンスを大幅に向上することができます。

表3，図3に，デフォルトとinnodb_flush_log_at_trx_commitを2に設定したときの性能検証結果の比較を示します。

	default avg	default min	default max	tuned avg	tuned min	tuned max
key	0.021	0.019	0.024	0.02	0.019	0.021
read	7.405	6.551	8.372	6.645	6.491	6.869
write	0.474	0.416	0.652	0.064	0.054	0.136
mixed	0.276	0.224	0.419	0.045	0.038	0.067
update	0.555	0.531	0.602	0.135	0.107	0.297

writeとupdateが非常に高速になっています。とくに，writeはデフォルト設定に対して8倍程度の性能が出ています。これだけの差が出ると，他のパラメータを細かく調整しても，結局innodb_flush_log_at_trx_commitが支配的になる予感がします。注意点としては，もしOSとMariaDBにクリティカルなバグが存在し，あるトランザクションを流して一斉にOSごとクラッシュした場合，残念ながらデータは助かりません。サーバの物理故障ががフラッシュの間隔内で発生する可能性は非常に低いですが，他の要因でもデータロストにつながる事象は発生し得る，ということは考えておく必要があります。

innodb_autoinc_lock_mode

このパラメータは，AUTO INCREMENTのロックに関する設定です。AUTO INCREMENTの値を挿入順に割り当てるために，発番用カウンタをロックする挙動を制御できます。0，1，2を設定することができ，デフォルトは1です。Gelera Clusterでは2を設定することが推奨されています。

0は互換性のために用意されているパラメータで，カウンタを毎回ロックします。現在では，積極的にこれを使用する理由はありません。

デフォルトの1は連続ロックモードで，通常のINSERTではロックを取らずにアプリケーション内部の排他制御で処理を行い，一括INSERTのように挿入される行数が事前にわからない場合にのみロックを取得します。性能を確保しつつ，ステートメントベースでのレプリケーションで一括INSERTしても発番された値の順番の整合性を保つことができます。

2はインタリーブロックモードで，すべての場合でロックをとりません。また複数の挿入を同時に実行できるようにするため，AUTO INCREMENTの値をインタリーブして（ある程度の範囲で区切って割り当てて）います。一括INSERTを行うと，挿入順とAUCTO INCREMENTの順番の整合性が崩れることがあり，ステートメントベースでレプリケーションを行うと，プライマリとレプリカで値に差が発生する可能性があります。一方，行ベースレプリケーションやGelera Clusterでは順番は崩れるもののノード間の整合性は崩れません。

表4，図4に，デフォルトとinnodb_autoinc_lock_modeを2に設定したときの性能検証結果の比較を示します。

	default avg	default min	default max	tuned avg	tuned min	tuned max
key	0.021	0.019	0.024	0.021	0.017	0.024
read	7.405	6.551	8.372	7.78	6.695	8.701
write	0.474	0.416	0.652	0.47	0.426	0.562
mixed	0.276	0.224	0.419	0.292	0.229	0.483
update	0.555	0.531	0.602	0.586	0.566	0.672

mysqlslapは一括INSERTは行わないため，全体的に大きな性能の変化はありません。

innodb_io_capacity

このパラメータは，InnoDBストレージエンジンが使用するIOの量を指定するもので，デフォルトは100です。ドキュメントによると，1秒間に実行可能なIOの回数を指定するのが良いようです。今回の検証環境は3000IOPS（バースト時）まで発揮可能です。

表5，図5に，デフォルトとinnodb_io_capacityを3000に設定したときの性能検証結果の比較を示します。

	default avg	default min	default max	tuned avg	tuned min	tuned max
key	0.021	0.019	0.024	0.02	0.017	0.023
read	7.405	6.551	8.372	7.86	6.619	8.824
write	0.474	0.416	0.652	0.465	0.42	0.512
mixed	0.276	0.224	0.419	0.261	0.223	0.322
update	0.555	0.531	0.602	0.577	0.532	0.64

こちらのパラメータも，read，writeともに大きな変化は見られませんでした。今回はAWS EC2上のgp2 SSDボリュームであることも1つの要因である可能性があります。オンプレミス環境で非常に高速なストレージを使用している場合，このパラメータを引き上げることで性能を改善できる可能性もあります。

4.フルチューン

複数のパラメータを個別に調整し，デフォルトとの性能を比較してきました。次に，今回紹介したパラメータを全て適用した「フルチューン」の環境で性能試験を行います。ある程度数値の幅があるパラメータもあるため，以下の2パターンのパラメータをデフォルトと比較します。

• （tuned A）今回紹介したもの全部入り
• innodb_buffer_pool_sizeを12.8GBに設定
• wsrep_slave_threadsを4に設定
• innodb_flush_log_at_trx_commitを2に設定
• innodb_autoinc_lock_modeを2に設定
• innodb_io_capacityを3000に設定
• （tuned B）上の設定から以下を変更したもの
• wsrep_slave_threadsを２に設定
• innodb_io_capacityを1000に設定

表6，図6に，デフォルトと上記のチューニングを施したものの性能検証結果の比較を示します。

	default avg	default min	default max	tuned A avg	tuned A min	tuned A max	tuned B avg	tuned B min	tuned B max
key	0.021	0.019	0.024	0.023	0.019	0.023	0.02	0.019	0.024
read	7.405	6.551	8.372	6.641	6.536	6.81	6.655	6.582	6.761
write	0.474	0.416	0.652	0.082	0.057	0.172	0.069	0.057	0.133
mixed	0.276	0.224	0.419	0.044	0.041	0.059	0.052	0.04	0.124
update	0.555	0.531	0.602	0.111	0.103	0.14	0.116	0.102	0.146

デフォルト比で相当に性能が改善しています。wsrep_slave_threadsは2と4で性能差がありましたが，この値が異なるtuned A，tuned Bは，大きな差がありませんでした。やはり，innodb_flush_log_at_trx_commitの影響が大きいようです。気になるのは，すべて適用した場合のwrite性能がinnodb_flush_log_at_trx_commitを単体で適用した場合の性能よりも少し低いということです。パラメータ単体でよかったものを適用すれば良い，というわけではなく，その組み合わせで結果が変わってきます。実際の環境では，ワークロードに合わせて様々なパラメータを検証してチューニングする必要がありそうです。

5.まとめ

Galera Clusterの性能に寄与するパラメータを個別に調整し，デフォルト設定よりも大幅に性能向上できることを確認できました。

パラメータは複数ありますが，書き込み性能においては特にinnodb_flush_log_at_trx_commitが支配的であることがわかりました。また，wsrep_slave_threadの調整も効果的です。読み込み性能については，細かなパラメータよりもメモリに載っているかどうか，PK（または索引）を使えているかどうかが重要です。innodb_flush_log_at_trx_commitを2に設定するのはGalera Clusterならではです。他にもパラメータもGalera Clusterの特性を生かしてシングルノードとは異なる考え方が必要な場合もあり，アーキテクチャの理解が重要です。

最後に

次世代システム研究室では，データサイエンティスト/機械学習エンジニアを募集しています。ビッグデータの解析業務など次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら，ぜひ募集職種一覧からご応募をお願いします。皆さんのご応募をお待ちしています。

参考

Tuning a MariaDB Galera Cluster for Performancehttps://thoughtsimproved.wordpress.com/2020/01/07/tuning-a-mariadb-galera-cluster-for-performance/
MySQL道普請便り 第49回　MySQLのAUTO_INCREMENTについてhttps://gihyo.jp/dev/serial/01/mysql-road-construction-news/0049
MariaDB InnoDB System Variableshttps://mariadb.com/kb/en/innodb-system-variables/

※記事中で参照しているWeb上の文献は記事公開日時点の情報です。