「パフォーマンス チューニングを行いたい」
MySQLのパフォーマンスを少しでも上げたい。
どのようにチューニングすればよいか
回答
以下参考に
my.cnf (linux)
又は
my.ini (windows)
の設定を行いましょう。
ソース
■MySQLのパフォーマンスに関するシステム変数
・sort_buffer_size
インデックスがないカラムの ORDER BY,GROUP BY操作を高速化するために使われるバッファサイズ
128K～1M程度がおすすめ
sort_buffer_size = 512K
・read_buffer_size
MyISAMテーブルの順次スキャンで使用されるバッファサイズ
4Kの倍数を設定する
128K～1M程度がおすすめ
read_buffer_size = 128K
・join_buffer_size
インデックス無しjoin(結合)、インデックスを付与するとき等で使われる最小バッファサイズ
256K前後がおすすめ
join_buffer_size = 256K
・read_rnd_buffer_size
MyISAM テーブルの読取、ストレージエンジン、複数範囲読み取りの最適化のために使用
大きい値に割り当てると、ORDER BYパフォーマンスを大きく改善できる可能性がある
デフォルトは256K
read_rnd_buffer_size = 2M
・max_connections
サーバが許容可能なコネクションの数
デフォルト(設定なし)の場合は151
通常デフォルト値で問題ないが接続数が多い場合はそれに応じて増やす
・thread_cache_size
スレッドをキャッシュする数
応答時間が長い場合に大きくすると改善する可能性があるが程々に
デフォルトは-1[ auto = 8+(max_connections÷100) ]、上限は100
thread_cache_size=30
・query_cache_type
クエリキャッシュのタイプ
(0:off,1:ON SELECT SQL_NO_CACHE以外,2:DEMAND SELECT SQL_CACHEのみ)
同一クエリーを発行されない場合は無効にするのもあり
query_cache_type = 1
・query_cache_size
クエリー結果をキャッシュするメモリー量
query_cache_typeが0以外の場合に有効
同一クエリーを発行される場合に有効
デフォルト1M
1024(1K)の倍数単位を設定する
query_cache_size = 128M
・query_cache_limit
この数より大きいクエリー結果をキャッシュしません。
デフォルト値は1M
query_cache_limit = 8M
・key_buffer_size
MyISAMテーブルのインデックスのバッファサイズ
大きくするとインデックス処理を改善できる可能性がある
デフォルト8M、最大でマシン合計メモリーの25%を割り当て可能
大きくし過ぎるとOSのページングにより余計に遅くなる
key_buffer_size = 256M
・innodb_buffer_pool_size
InnoDBのテーブルとインデックスのバッファサイズ
（テーブルとインデックスをメモリにキャッシュするサイズ）
大きくするとディスクI/Oを減らし、処理を改善できる可能性がある
デフォルト128M、最大でマシン合計メモリーの80%を割り当てることもあります
大きくし過ぎるとOSのページングにより余計に遅くなる為、
搭載メモリ量やOSの他プロセスのメモリ消費量を配慮して設定する必要がある
（参考：MyISAMのテーブル[データ]キャッシュはOSキャッシュに依存する）
innodb_buffer_pool_size = 256M
・innodb_log_file_size
ロググループ内の各ログファイルのサイズ
innodb_buffer_pool_sizeの25%程度に設定する
デフォルト5M程度
innodb_log_file_size = 64M
■MySQLのその他システム変数
・max_allowed_packet
クライアントからサーバーに送信できるパケットの最大長
デフォルトは1M、又は4M
大きい BLOB カラムを使用している場合はそのサイズ以上に設定する必要がある
max_allowed_packet = 8M
・max_heap_table_size
MEMORYテーブルの最大サイズ
この値を超えるとディスク上のMyISAMテーブルになる
デフォルト1.5M
max_heap_table_size = 4M
・tmp_table_size
一時テーブルの最大サイズ
ユーザーが作成した MEMORY テーブルには適用されません
この値を超えるとディスク上のMyISAMテーブルになる
tmp_table_sizeとmax_heap_table_sizeの最小値で決定されます
・innodb_flush_log_at_trx_commit
デフォルト 1 (0,1,2の値を設定可能)
1以外の値を設定するとクラッシュ時にトランザクションが最大1秒間失われる
・innodb_lock_wait_timeout
行ロックが解除されるまで InnoDBトランザクションが待機する時間の長さ (秒単位)
デフォルト50
この値以上経過すると Lock wait timeout exceededが発生する
InnoDBではテーブルロックは発生しない(InnoDBでデッドロックが発生してもすぐにロールバックされる)
・lock_wait_timeout
メタデータロックを取得するための試行のタイムアウトを秒単位で指定
デフォルトは31536000(1年)
・wait_timeout
非対話型の接続で接続を閉じる前に待機する秒数
デフォルト28800(8時間)
・interactive_timeout
対話型の接続で接続を閉じる前に待機する秒数
デフォルト28800(8時間)
■MySQLのシステム変数の確認
-- 全システム変数を表示
mysql> SHOW VARIABLES;
-- 特定のシステム変数を表示
mysql> SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size';