更新日: 2014年7月21日
MySQL & DB

my.cnfで設定するMySQLオプションで重要そうなのまとめ

MySQL のチューニングで、my.cnf のオプション設定をいじっていたのですが、パラメータが大量にあって奥が深い。「現場で使える MySQL」のパフォーマンス・チューニングの章（13章, 14章）を読みなおしたのですが、そこからの抜粋中心で大事そうなパラメータの概要をまとめました。

現場で使える MySQL (DB Magazine SELECTION)

【お知らせ】英単語を画像イメージで楽に暗記できる辞書サイトを作りました。英語学習中の方は、ぜひご利用ください！

画像付き英語辞書 Imagict | 英単語をイメージで暗記
【開発記録】
英単語を画像イメージで暗記できる英語辞書サービスを作って公開しました

スロークエリログ関連

log-queries-not-using-indexes
これを指定すると、インデックスを使わないような処理（テーブルのフルスキャンなど）を行った全てのSQL文をスロークエリログに出力する。

slow_query_log
スロークエリログを有効にするかどうかを指定。slow_query_log = 1 で有効になる。

logn_query_time
指定時間より長く実行時間がかかったSQL文をスロークエリログに出力。

slow_query_log_file
スロークエリログのファイルの場所を指定。

キャッシュ・接続関連

query_cache_size
SELECT文の実行結果をメモリ内にキャッシュ。

query_cache_type
query_cache_type = 2 と指定すると、明示したSELET文のみをキャッシュ。SELECT SQL_CACHE 列名, … とSELECT文を書くとキャッシュ対象になる。

max_connections（デフォルト100）
MySQLインスタンスに同時に接続可能なセッションの上限値。大きくても100〜200にする。

thread_cache_size
切断したコネクションをキャッシュして、次回以降の接続負担を軽減。max_connections の3分の1程度を推奨。

InnoDB 関連

skip-innodb-doublewrite
Innodbは耐障害性を高めるために、Innodbデータファイル内の2箇所に書き込みを行う実装になっている。この二重書き込みを停止する場合に指定する。

innodb_buffer_pool_size
Innodbに対する各種操作、レコードデータなどをキャッシュする領域（容量）。

innodb_max_dirty_pages_pct
InnoDBバッファプールを占めるダーティな領域（更新された領域）が90％を超えると、強制的に（他の処理をブロックして）チェックポイントが行われる。このチェックポイントが行われる％値を指定する。innodb_max_dirty_pages_pct = 97 とすると、97%を超えるとチェックポイントが行われ、タイミングを多少遅延できる。

チェックポイントとは、InnoDBバッファプール内のダーティな領域（更新された領域）がInnoDBデータファイルに書き込まれる処理のこと。特定の条件を満たした時点で、このチェックポイントが行われる。チェックポイントは一般的にディスクの書き込み負荷が高いので、可能であれば回避するように各種チューニングを施す。

innodb_log_files_in_group
ロググループのログファイル数を指定。

innodb_log_file_size
Innodbロググループの各ログファイルのサイズ。1MBから innodb_buffer_pool_size/innodb_log_files_in_group の範囲の値。

innodb_log_files_in_group と innodb_log_file_size の積のおよそ7割程度を超えた時点で、強制的にチェックポイントが行われる。このため、innodb_buffer_pool_size を大きくする場合は、これらの値も大きくしておかないと意味がない。目安はこの積の値が、innodb_buffer_pool_size と同じか、1〜2割程度大きい値にする。

具体的に理想の設定値については、各々環境で異なるでしょうけど、いずれまた別エントリーでまとめたいと思います。パラメータは他にもまだ大量にあります、参考リンク参照。