- 更新日: 2014年7月21日
- MySQL & DB
my.cnfで設定するMySQLオプションで重要そうなのまとめ
MySQL のチューニングで、my.cnf のオプション設定をいじっていたのですが、パラメータが大量にあって奥が深い。「現場で使える MySQL」のパフォーマンス・チューニングの章(13章, 14章)を読みなおしたのですが、そこからの抜粋中心で大事そうなパラメータの概要をまとめました。
現場で使える MySQL (DB Magazine SELECTION)
スロークエリログ関連
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割程度大きい値にする。
具体的に理想の設定値については、各々環境で異なるでしょうけど、いずれまた別エントリーでまとめたいと思います。パラメータは他にもまだ大量にあります、参考リンク参照。
- MySQL & DB の関連記事
- MySQLでBLOB/TEXT型のカラムにはデフォルト値を設定できない
- SQLite3でDBテーブルをdumpバックアップ
- Warning: mysql_connect() PHPからMySQL接続でsocketエラー
- phpMyAdminでMySQLをバックアップ(エクスポート)
- MySQLが起動しないエラー(The server quit without updating PID file)
- phpMyAdminで#2002 Cannot log in to the MySQL serverエラー
- libaio.so.1, openssl-devel がなくて mysql-server, mysql-devel をインストールできないエラー
- MySQLオプション(/etc/my.cnf)の設定例
- my.cnf の innodb_data_file_path, innodb_log_file_size 設定で MySQL が起動しなくなる場合の対処
- MySQLのソケット場所変更、my.cnfでのsocket設定の注意点
Leave Your Message!