MariaDB(MySQL)の主なシステム変数（チューニング）
MariaDB(MySQL)の設定ファイル（my.cnf 又は my.ini）のデフォルトの位置は以下にあるようです。
- Linuxは以下の何れか
-
/etc/my.cnf
/etc/mysql/my.cnf
~/.my.cnf - Windowsは以下の何れかのディレクトリ
-
C:\WINDOWS
C:\
C:\Program Files\MySQL\MySQL Server xx.x
C:\Program Files\MySQL\MySQL Server xx.x\data
C:\Program Files\MariaDB xx.x
C:\Program Files\MariaDB xx.x\data
システム変数をMySQLコマンドライン クライアントから確認するには以下のコマンドを使用します。
-- 全システム変数を表示 mysql> SHOW GLOBAL VARIABLES;
上記だと大量に表示されるので特定のシステム変数だけを確認したい場合は LIKE 文を使用します。
-- システム変数 version を表示 mysql> SHOW GLOBAL VARIABLES LIKE 'version'; -- 又は以下 mysql> SELECT @@version; -- versionから始まるシステム変数を表示 mysql> SHOW GLOBAL VARIABLES LIKE 'version%';
主なシステム変数
sort_buffer_size
インデックスがないカラムの ORDER BY,GROUP BY操作を高速化するために使われるバッファサイズ
128K～1M程度がおすすめ
[mysqld]
sort_buffer_size = 512K
join_buffer_size
インデックス無しjoin(結合)、インデックスを付与するとき等で使われる最小バッファサイズ
256K前後がおすすめ
[mysqld]
join_buffer_size = 256K
max_connections
サーバが許容可能なコネクションの数
デフォルト(設定なし)の場合は151
通常デフォルト値で問題ないが接続数が多い場合はそれに応じて増やす
[mysqld]
max_connections = 151
thread_cache_size
スレッドをキャッシュする数
応答時間が長い場合に大きくすると改善する可能性があるが程々の値に設定します
デフォルトは -1(自動) = 8+(max_connections÷100) = 8+(151÷100)= 9
上限は100
[mysqld]
thread_cache_size = 30
query_cache_type（MySQLは8.0以降で廃止）
クエリキャッシュのタイプ
(0:off, 1:ON SELECT SQL_NO_CACHE以外, 2:DEMAND SELECT SQL_CACHEのみ)
同一クエリーを発行されない場合は無効にするのもあり
[mysqld]
query_cache_type = 1
query_cache_size（MySQLは8.0以降で廃止）
query_cache_typeが0以外の場合に有効な変数で、クエリー結果をキャッシュするメモリー量
同一クエリーを発行される場合に有効
デフォルト 1M(1048576Byte)
1024(1K)の倍数単位を設定する
[mysqld]
query_cache_size = 128M
query_cache_limit（MySQLは8.0以降で廃止）
この数より大きいクエリー結果をキャッシュしません。
デフォルト値は1M
[mysqld]
query_cache_limit = 8M
innodb_buffer_pool_size
データベースやInnoDBテーブルのインデックスのバッファサイズ
大きくするとインデックス処理を改善できる可能性がある
デフォルト128M、最大でマシン合計メモリーの50%～80%程度を割り当てるのが一般的。
大きくし過ぎるとOSのページングにより余計に遅くなる。
用途にもよりますがデータベース専用サーバーであれば目安として
合計メモリ4GB搭載なら 1.6GB 程度
合計メモリ6GB搭載なら 3GB 程度
合計メモリ8GB搭載なら 5GB 程度
合計メモリ16GB搭載なら 12GB 程度
を割り当てるのが妥当かもしれません。
[mysqld]
innodb_buffer_pool_size = 1536M
innodb_log_file_size
InnoDBのロググループ内の各ログファイルのサイズ
サーバーの合計メモリの25%以下で innodb_buffer_pool_size の25%程度
デフォルト5M程度
[mysqld]
innodb_log_file_size = 384M
innodb_log_writer_threads（MySQL 8.0以降専用）
専用ログライタスレッドを使用するかしないか設定
CPUコアを32個以上搭載している場合はONを推奨
デフォルト ON
[mysqld]
innodb_log_writer_threads = OFF
read_buffer_size
MyISAMテーブルの順次スキャンで使用されるバッファサイズ
4Kの倍数を設定する
128K～1M程度がおすすめ
[mysqld]
read_buffer_size = 128K
read_rnd_buffer_size
MyISAM テーブルの読取、ストレージエンジン、複数範囲読み取りの最適化のために使用
大きい値に割り当てると、ORDER BYパフォーマンスを大きく改善できる可能性がある
デフォルトは256K
[mysqld]
read_rnd_buffer_size = 2M
key_buffer_size
MyISAMテーブルのインデックスのバッファサイズ
大きくするとインデックス処理を改善できる可能性がある
デフォルト8M、最大でマシン合計メモリーの25%を割り当て可能
大きくし過ぎるとOSのページングにより余計に遅くなる
[mysqld]
key_buffer_size = 256M
innodb_ft_min_token_size
フルテキスト検索インデックスのインデックスする最小の単語文字数を指定する。
デフォルトは3文字(つまり2文字の単語はインデックスされない)。
日本語の場合は1を設定する必要がある。
[mysqld]
innodb_ft_min_token_size = 1
innodb_ft_cache_size
フルテキスト検索インデックスキャッシュに割り当てられたテーブルごとのメモリー (バイト単位)。
デフォルト値 8,000,000
最小値 1,600,000
最大値 80,000,000
[mysqld]
innodb_ft_cache_size = 8000000
innodb_ft_total_cache_size
フルテキスト検索インデックスキャッシュに割り当てられた合計メモリー (バイト単位)。
デフォルト値 640,000,000
最小値 32,000,000
最大値 1,600,000,000
[mysqld]
innodb_ft_total_cache_size = 640000000
innodb_ft_server_stopword_table
フルテキスト検索で指定したテーブルに登録された単語をインデックスしないようにする
データベース名/テーブル名 で設定する
デフォルトで「INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD」テーブルに単語が登録されている
[mysqld]
innodb_ft_server_stopword_table = database_name/table_name