ようへいの日々精進XP

よかろうもん

MySQL をチューニングする時のポイント一部始終

要件

  • 「MySQL をチューニングしたい」というフワッとした要望に対して、どこからアプローチするべきなのかを自分なりに整理
  • 今更次郎的な内容だが、個人的にはちゃんと整理して抑えておきたい！

ポイント

slow log

slow log を取って遅いクエリを監視することから始める

long_query_time=${n} # ${n}には秒数が入る
log-slow-queries=/path/to/log

遅いクエリを眺める

クエリの頭に EXPLAIN を付ける

EXPLAIN SELECT * FROM hoge;

パラメータ

確認

いつも綴りを間違えてしまう...

SHOW VARIABLES;
各種変数
  • key_buffer_size
    • インデックスを保存するバッファのサイズ。
    • MyISAMならばOSキャッシュも使うので 256MB を推奨。
  • innodb_buffer_pool_size
    • InnoDBのバッファのプールサイズ。
    • InnoDBだけを利用する場合は空きメモリの 70 から 80% 程度を割り当てる。
    • 最も重要なバッファ
  • table_open_cache
    • テーブルを開くときに使われるキャッシュ数。
    • I/Oタイムを減らすのに有効。
    • レコードの数が数百程度のテーブルなら1024がベスト。
  • thread_cache_size
    • スレッドのキャッシュに使われるキャッシュ数。
    • 16 を推奨。
  • innodb_log_file_size
    • ログファイルのサイズ。増やすとパフォーマンスが良くなるが、復旧に時間を要する。
    • 64 から 512MB くらいで調整すると良い。
  • innodb_log_buffer_size
    • ログのバッファサイズ。
    • デフォルトを推奨。
  • innodb_flush_log_at_trx_commit
    • 設定は必須？
    • flush logをディスクにとるかメモリかとらないかを選択する。
    • 1がディスク、2がメモリ、0がとらない。
  • max_allowed_packet
    • 入力データ保持のための最大バッファサイズ。
    • 大きなファイルを入出力する際に必須。
    • 1MB を推奨。
  • sort_buffer_size
    • ORDER BY や GROUP BY のクエリ速度を向上させる。
  • join_buffer_size
    • join用のバッファサイズを指定する。
    • 1MB 程度が推奨。
  • query_cache
    • クエリのキャッシュ量。
    • 増やしすぎるのも良くない。
    • データベースのサイズに合わせて調整すること。（32 から 512M 位で調整する）
注意点
  • バッファは増やせば増やすほどいいというわけではない！
    • メモリの割り当てがオーバーヘッドになるので、無駄に大きくし過ぎることは禁物。
    • バッファを増やしすぎたためにスワップ発生には要注意。

まとめ

  • ぶっちゃげ、これまでは各種変数等についてはざっくりとしか理解していなかったが、上記のように整理することが出来てまずは良かった。

参考