MariaDBのチューニング設定（2023年2月版）

投稿日：

slow_logを眺めていたら「遅いの多くね？」と思って調査したときの覚書。

環境： CentOS Stream 8, MariaDB 10.5.9

ほぼWordPress用のデータベース。
InnoDBエンジンしか使っていない（systemは除く）。


１．MySQLTunerを実行

MariaDBにも対応しているチューニング設定を確認してくれるスクリプトをインストールする。
参考： major/MySQLTuner-perl: MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.

# cd /opt/software/
# git clone https://github.com/major/MySQLTuner-perl.git
# cd MySQLTuner-perl/
# git tag
# git checkout v1.9.9
# perl mysqltuner.pl

[!!]になっているところを調べて修正していく。


２．設定変数を調べて理解していく

設定値のメモ。
phpMyAdminのVariablesで現在の値と説明を見るのが早い。


共通設定

  • skip-name-resolve: クライアントのIPアドレスを逆引きする機能を無効。
  • query_cache_type(default:OFF): MairaDB 10.1からはquery_cache_size値で自動設定されるので設定不要。
  • table_open_cache(default:2000): 同時に開かれるテーブル数。
  • table_definition_cache(default:400): テーブル定義をキャッシュする数。tableの総数が多い場合は引き上げた方がいい。
  • performance_schema(default:OFF): SQL実行パフォーマンス情報を取得する機能
  • join_buffer_size(default:256K): インデックスを使わない場合の最小メモリ領域。設定不要。
  • sort_buffer_size(default:2M): 全てのエンジンのソート処理に使用するメモリ領域。セッションごとに消費する。
  • query_cache_size(default:1M): クエリ結果をキャッシュするためのメモリ領域。
  • query_cache_limit(default:1M): キャッシュするクエリ結果の上限値。
  • query_cache_min_res_unit(default:4K): クエリ結果をキャッシュするブロックサイズ。設定不要。
  • tmp_table_size(default:16M): 一時テーブルのメモリ領域。
  • max_heap_table_size(default:16M): Memoryエンジンのメモリ領域。tmp_table_sizeと同じ値にする。


InnoDB用設定

  • innodb_buffer_pool_size(default:128M): InnoDBのメモリ領域。
  • innodb_log_buffer_size(default:16M): InnoDBのクエリ更新ログのためのメモリ領域。innodb_buffer_pool_sizeの4分の1。


MyISAMとAria用設定

  • key_buffer_size(default:128M): MyISAMで使用されるためInnoDBだけの場合は小さい値に設定。
  • myisam_sort_buffer_size(default:128M): MyISAMで使用されるためInnoDBだけの場合は小さい値に設定。
  • aria_sort_buffer_size(default:256M): Ariaで使用されるためInnoDBだけの場合は小さい値に設定。
  • aria_pagecache_buffer_size(default:128M): Ariaで使用されるためInnoDBだけの場合は小さい値に設定。
  • read_buffer_size(default:128K): MyISAMとAriaでインデックスを使わないシークエンススキャンで使用するメモリ領域。設定不要。
  • read_rnd_buffer_size(default:256K): MyISAMでインデックスのソート処理で使用するメモリ領域。設定不要。


３．設定configファイルのサンプル

メモリ4GBのサーバーでは下記のように設定した。
# less /etc/my.cnf.d/mariadb-server.cnf

[server]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
thread_handling = pool-of-threads
max_connections = 100

# Tuning
performance_schema = ON
skip-name-resolve

sort_buffer_size = 4M
query_cache_size = 256M
query_cache_limit = 4M

table_open_cache = 10K
table_definition_cache = 2K
tmp_table_size = 128M
max_heap_table_size = 128M

# InnoDB
innodb_buffer_pool_size = 512M
innodb_log_buffer_size = 128M
innodb_ft_min_token_size = 1  # Fulltext Index

# MyISAM/Aria
key_buffer_size = 256K
myisam_sort_buffer_size = 256K
aria_sort_buffer_size = 256K
aria_pagecache_buffer_size = 256K

# Slow Query
slow_query_log = ON
long_query_time = 1
log_output = TABLE


しばらくはslow_logをみながら調整する。
Googleアナリティクスのページ速度でも確認する。


【関連記事】