MariaDBの性能対策設定(システムチューニング)を説明します。
概要
MariaDBのパフォーマンスに関するチューニングは、大きく分けて以下の通りです。
- SQLチューニング
- テーブルチューニング
- システムチューニング
本ページでは、上記のうちMariaDB自体の動作に関するシステムチューニングについて説明します。
システムチューニングの肝は、以下の図中ファイルシステムに存在するファイルへのI/Oを出来る限り減らすことです。高速にアクセスできるメモリ領域を増やし、ディスクへのアクセス回数を減らすことで、パフォーマンスの向上を目指します。
システムチューニングで変更するパラメータは、/etc/my.cnf.d/mariadb-server.cnfの[mysqld]セクションで指定します。
設定パラメータの確認
動作中のシステムの設定パラメータを確認するには、DBに接続した状態でSHOW GLOBAL VARIABLES;を実行します。末尾にLIKE '<文字列>'を付与することで、任意の文字列を含む設定に限定してパラメータを確認できます。文字列中の%は、ワイルドカードを示します。
以下は、innodb_logを先頭に含む設定パラメータの情報を表示しています。
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'innodb_log%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_optimize_ddl | ON |
| innodb_log_write_ahead_size | 8192 |
+-----------------------------+----------+
8 rows in set (0.001 sec)
設定項目
接続スレッド関連
設定概要
MariaDBは、クライアントからの接続要求ごとに、同要求を処理するための接続スレッドを作成します。
DBサーバへ接続するクライアント数が多い場合は、最大接続数を増やします。
なお、スレッドが増えるごとにメモリ消費量も増えていくので、接続数があまりにも多い場合は、スレッドを増やすのではなく読み取り専用のレプリケーションサーバを用意して負荷分散することも検討する必要があります。
パラメータ
| パラメータ名 | 概要 | デフォルト値 | 推奨値 |
|---|---|---|---|
| max_connections | クライアントの最大接続数。接続スレッドの最大数でもある。スレッドの数だけメモリを消費するため、メモリを潤沢に使用できない場合は値を減らす。 | 151 | 50 |
| thread_cache_size | 接続スレッドプールのキャッシュサイズ。本パラメータの範囲内で、接続スレッドを使い回すことができる。 | 151 | 50 |
バイナリログ関連
設定概要
バイナリログは、ロールフォワードバックアップやレプリケーションに使用される更新ログです。未コミット状態の更新データはバイナリログキャッシュに保管され、コミットされるとバイナリログに書き込まれます。
バイナリログキャッシュは接続スレッド単位で領域が作成されます。あまりにも接続数が多いと、メモリ領域を食い潰すことになるので、注意が必要です。また、容量が足りなくなると、一時ファイルに内容を書き出すのでディスクI/Oが発生する点も注意が必要です。
パラメータ
| パラメータ名 | 概要 | デフォルト値 | 推奨値 |
|---|---|---|---|
| binlog_cache_size | バイナリログキャッシュ領域のサイズ(単位: KB)。コミット前のトランザクションを保管する。1回のトランザクションで扱うデータが大きい場合は、値を増やす。 | 32768 | 32M |
テーブルスペース関連
設定概要
MariaDBのストレージエンジンにInnoDBを使用している場合、DBデータに対するCRUD処理(追加/参照/更新/削除)は直接ディスク上のテーブルスペースには行われず、メモリ上のInnoDBバッファプールが使用されます。
データの書込み時は、InnoDBバッファプールとInnoDBログにデータ内容を反映した後、非同期にテーブルスペースにデータが書き込まれます。InnoDBバッファプールの容量が不足すると、データの読み書きともにテーブルスペースへのデータ退避が頻発し、I/O負荷が上昇する可能性があります。InnoDBバッファプールは可能な限り大きくすべきです。
InnoDBは標準でテーブルスペースへの二重書き込み(doublewrite)が有効化されています。これは、テーブルスペースへのデータ書き込み中に障害が発生した場合、クラッシュリカバリ時にデータを復旧するために使われます。二重書き込みはパフォーマンスを犠牲にするので、障害発生時に自動的にフェイルオーバするレプリケーション構成の場合は本処理は不要なので無効化します。
パラメータ
| パラメータ名 | 概要 | デフォルト値 | 推奨値 |
|---|---|---|---|
| innodb_buffer_pool_size | InnoDBテーブルのデータとインデックスのバッファ領域(単位: byte)。クライアントからのクエリに対してデータの読み書きを提供する。また、非同期にテーブルスペースに自身の内容を反映させる。 | 134217728 | DBの容量以上。難しい場合は、物理メモリの75%程度。 |
| skip-innodb_doublewrite | 耐障害性を高めるためのテーブルスペースへの二重書き込み処理を無効化する。 | 指定無し | シングル構成の場合は指定なし、レプリケーション構成の場合は指定あり(パラメータ名のみ記載) |
InnoDBログ関連
設定概要
データ書き込み時のトランザクションログは、InnoDBログバッファを介してInnoDBログ(ib_logfileN)に書き込まれます。InnoDBログは、mysqldプロセスが強制終了した場合に次回再起動時のクラッシュリカバリに使用される重要なログです。
InnoDBログに書き込まれたトランザクションの内容は、innodb_flush_log_at_trx_commitで指定した間隔でバッファプールからテーブルスペース(DBの実データ)に書き込まれます。また、ダーティページ(まだテーブルスペースに書き込まれていないInnoDBログ)の割合がinnodb_max_dirty_pages_pctで指定した値に達した場合も、チェックポイント処理が実行されバッファプールからテーブルスペース(DBの実データ)にデータが書き込まれます。
テーブルスペースへのデータ書き込みはランダムアクセスのため、ディスクI/O負荷がかかります。そのため、なるべくテーブルスペースへの書き込み頻度を減らすことがInnoDBログに関するチューニングのポイントです。
パラメータ
| パラメータ名 | 概要 | デフォルト値 | 推奨値 |
|---|---|---|---|
| innodb_flush_log_at_trx_commit | InnoDBログバッファ→InnoDBログ、およびバッファプール→テーブルスペースのフラッシュタイミング。
(<値>: <InnoDBログバッファ→InnoDBログ>、<バッファプール→テーブルスペース>) |
1 | 通常はデフォルト値。厳格なトランザクション管理が不要な場合は2(障害発生時に約1秒分の書き込みデータ消失リスクあり) |
| innodb_max_dirty_pages_pct | バッファプール→テーブルスペースのフラッシュが行われるダーティページ(まだテーブルスペースに書き込まれていないInnoDBログ)の割合(単位: %)。 | 75.000000 | 90 |
| innodb_log_buffer_size | InnoDBログのメモリ上のバッファ領域(単位: byte)。innodb_flush_log_at_trx_commitのフラッシュタイミング以前は、InnoDBログは本領域に保管される。1回のトランザクションで扱うデータ数が多い場合は値を増やす。 |
16777216 | 32M |
| innodb_log_files_in_group | InnoDBログのファイル数(単位: 個)。指定した数だけib_logfileN(Nは0以上の数)ファイルが作成され、そのファイルグループの中で出力先をスイッチしながらログが書き込まれる。 | 2 | デフォルト値。 |
| innodb_log_file_size | InnoDBログのファイルサイズ(単位: byte)。 | 50331648 | innodb_buffer_pool_size / innodb_log_files_in_group |
名前解決関連
設定概要
デフォルト設定では、クライアントのIPアドレスをもとにホスト名をDNSサーバに問い合わせる逆引き名前解決が行われるようになっています。DBユーザの接続元設定をホスト名で管理したい場合を除いて、名前解決処理は負荷が掛かるので無効化します。
パラメータ
| パラメータ名 | 概要 | デフォルト値 | 推奨値 |
|---|---|---|---|
| skip-name-resolve | 接続元クライアントの逆引き名前解決を無効化する。 | 指定無し | 指定あり(パラメータ名のみ記載) |
設定ファイルの記載例
物理メモリ8GBのサーバを想定した/etc/my.cnf.d/mariadb-server.cnfの記載例です。
[mysqld]
(略)
# スレッド関連
max_connections=50
thread_cache_size=50
# バイナリログ関連
log-bin=mysql-bin
binlog_cache_size=32M
# テーブルスペース関連
innodb_buffer_pool_size=6G
skip-innodb_doublewrite
# InnoDBログ関連
innodb_flush_log_at_trx_commit=2
innodb_max_dirty_pages_pct=90
innodb_log_buffer_size=32M
innodb_log_files_in_group=2
innodb_log_file_size=3G
# 名前解決関連
skip-name-resolve