MySQLのパラメータ調整

先日和訳したTipsの元のドキュメントを見に行ったら項目毎に整理されていた。パラメータの項目については直接記述するのではなく外部のドキュメントを参照するようになっていたので、そちらも翻訳した。
http://docs.cellblue.nl/easy_mysql_performance_tweaks/
訳を貼っておいて言うのもなんですが、そんなに目を惹くような事は書いていなかったです。内容はごくごく基本的なことだけ。



以下の項目は影響度の大きい順に並んでいます。

Key Buffer

Key bufferはテーブルのインデックスを保持します。多くのメモリとkey bufferはそれだけ速いルックアップを実現できます。必要に応じて調整してください。大容量はもちろんいいことですが、スワップはなんとしても避けて下さい。経験則としてシステムメモリの1/4を割り当てるのが良いとされています。

key_buffer = 128M

Query Cache

魔法か何かでしょうか。いいえ、違います単純なキャッシュです。後から書き込みなどによって無効になるまでクエリの結果をメモリに保持することによって劇的にパフォーマンスを向上させるのです。query_cache_sizeは名前の通りクエリキャッシュのサイズです。query_cache_limitは1回のクエリについてキャッシュすることが出来る最大値を指定します。この値を大きくし過ぎると小さいクエリをキャッシュし難くなります。小さくし過ぎると大きなクエリは全くキャッシュされなくなり、小さなクエリでキャッシュが一杯になるでしょう。これはリソースの無駄使いというものです。必要とメモリ容量に応じて調整して下さい。

query_cache_size = 128MB
query_cache_limit = 4MB

Table Cache

多くのテーブルにアクセスするアプリケーションでは重要な値です。1スレッドが同時に保持することが出来るテーブルの数です。512ぐらいにしておくのが無難でしょう。

table_cache = 512

Sort Buffers

sort_buffer_size(以前は sort_bufferと呼ばれていた)はグルーピングやソートなどの処理に使うスレッド毎のバッファです。もしバッファ上に入り切らないようなソートの場合、処理はディスク上で行われます。各スレッドがソートをするときや多くのソートが発生するときなどに大きすぎるバッファサイズにしておくと、メモリを大量消費しますので注意して下さい。

sort_buffer_size = 32M
myisam_sort_buffer_size = 32M

The InnoDB Engine

多くの人がInnoDBを使っておらず、代りにMyISAMを使っています。MySQLがこのエンジンを予備扱いにしているため、使わないなら切ってしまいましょう。InnoDBが必要なら、MySQLの公式ドキュメントにもっと情報が記載されています、そちらを参照して下さい。


my.cnfに 'skip-innodb'と追記することでこのエンジンを無効にできます。

Binary Logging

MySQLは幾つも強力な機能があります。データの変更を2台目のサーバに複製(レプリケーション)する機能です。MySQLはこの機能のためにデータの変更をログに保存しています。レプリケーションを使わない場合やファイルをインクリメンタルバックアップする場合はこの機能を無効にできます。これによってデータ操作毎に発生する高コストなディスクアクセス動作をせずにすみます。頻繁にデータの更新をするようなアプリケーションならば、これは非常にパフォーマンスを向上させます。公式ドキュメントによればこれは一般的に言えば1%程度の向上に過ぎないそうです。しかし、ログが不要ならば簡単に得られるのです。もっとバイナリログについて知りたい方ははここを参照下さい。無効にするには、以下の行をコメントアウトする。


log-bin = /var/log/mysql/mysql-bin.log

Temporary Tables

テンポラリテーブルはソートやグルーピングに使います。このバッファは必要に応じて作られます、大きすぎる値にしないよう注意して下さい。データが入り切らない場合、一時ファイルはディスクを使います。

tmp_table_size = 64MB

Delayed Writing

この設定はデータの更新を大幅に向上させます。ディスクへのデータの反映を行う代りに、MySQLは書き込みをキューへ入れクエリから直ちに返ります。電源故障やクラッシュによってデータを失うことを意味しますので、十分に注意して下さい。クラッシュによって数行のデータを失うことを気にしないのならば、これをログに使うのは有りでしょう。

delay_key_write = 1

Connection Timeout

これは休眠中のコネクションを切るかを決めるごく小さい調整です。デフォルトの1時間はほとんどの場合において長すぎます。私は大抵1分(60)にしています。

wait_timeout = 60



これらの設定でmysql全般的にちょっとだけ速くなったでしょう。データベース自体を最適化すればさらに劇的に速くなります。