MySQLのチューニングやること

参考リンク

nippondanji.blogspot.jp

thinkit.co.jp

yakst.com

基本指針

具体的な方法

1. バッファサイズを適切に設定する

  • innodb_buffer_pool_size・・・InnoDBだけを利用する場合は空きメモリの7〜8割程度を割り当てる最も重要なバッファである。余談だが、実際にはここで割り当てた値の5〜10%ぐらいを多めにメモリを使うので注意が必要だ。

MySQLの推奨の70%で設定してみる。

  • key_buffer_size・・・MyISAMだけを利用する場合は、空きメモリの3割程度を割り当てるといい。残りはファイルシステムのキャッシュ用に残しておこう。
  • sort_buffer_size・・・ソート処理に利用するバッファである。OLTPでは256K〜1Mぐらいを割り当てると良い。これがあまり大きすぎると、メモリの割り当てのオーバーヘッドが大きくなるので注意しよう。DWH系の処理などで大きなソートが必要な場合、セッションごとに動的に調整すると良い。
  • read_buffer_size・・・全件スキャンをするときに利用するバッファ。OLTPでは128K〜512Kぐらいを割り当てると良い。
  • read_rnd_buffer_size・・・ソート処理でインデックスを利用する場合に利用するバッファ。OLTPでは256K〜1MぐらいをDWH系の処理などで大きなソートが必要な場合、セッションごとに動的に調整すると良い。 http://nippondanji.blogspot.jp/2009/02/mysql10.html

https://yakst.com/ja/posts/61

パラメータの数値は以下を参考にしました。

http://sawara.me/mysql/1428/

/etc/my.cnf/

innodb_buffer_pool_size = 512M
innodb_log_file_size=64M

innodb_additional_mem_pool_size = 5M

key_buffer_size = 64M
join_buffer_size = 256K
read_buffer_size = 256K
max_allowed_packet = 2M
read_rnd_buffer_size = 1M
sort_buffer_size = 1M
query_cache_limit = 4M
query_cache_size = 128M
query_cache_type = 1
max_connections = 100 # default
max_heap_table_size = 4M
tmp_table_size = 4M

2. indexを適切に貼る

“適切に"というところも大事。全てにindexを貼ってしまうと、

3. テーブルの最適化

できるだけカラムサイズが小さくなるデータタイプを選択すること

ex. 数値を保存するときにVARCHARなどは使わないように!!INTやBIGINTの方がデータサイズはずっと小さくなる!

その他

メモリの消費量が大きいなと思ったとき

table_definition_cache を400くらいに設定しておくとかなりメモリの消費量を抑えることができます。

設定していたときのEC2サーバーで、ここまでのチューニングでMEM%が30%前後だったのが、20%前後で落ち着くようになりました。

blog.ybbo.net

MySQL :: MySQL 5.6 Reference Manual :: 5.1.5 Server System Variables