MySQLで最初に見るべきチューニング設定項目

どうも、俺＠仕事中です。
わが社ではMySQL、PostgreSQLを良く使っている（というかほとんど）のですが、よくアクセス過多によるサーバチューニングをやっています。
今回はMySQLサーバで負荷対策を行う際の「まず最初にココの設定だけは要Check It Now」というのをメモします。

# vim /etc/my.cnf
または
# mysqladmin variables
または
mysql> show variables;

で、現在の設定の確認ができます。

-- key_buffer_size

mysql> show variables like '%key_buffer%';

MyISAMエンジンテーブルのインデックスを保存するメモリバッファのサイズ。
Key_read/Key_read_requestsでキーバッファミス率を算出し、0.01以下になるように。

-- max_allowed_packet

mysql> show variables like '%max_allowed%';

通信時の1パケット最大サイズ。画像データなど大容量のデータをMySQLへ保存する際には大きくする。

-- table_cache

mysql> show variables like '%table_cache%';

一度開いたMySQLのデータファイルポインタを保存しておくためのキャッシュ。二度目以降のテーブルへのアクセスを高速にする。つまりはサーバのディスクI/Oを減らせる。

mysql> show global status like '%Open_tables%'

の値が大きい場合はテーブルキャッシュの値を増やした方が良い場合がある。
※バージョン5.1.xからはtable_open_sizeに変わってるかも！？

-- sort_buffer_size

mysql> show variables like '%sort_buffer%';

ソートに利用されるメモリサイズ。Order ByとかGroup Byとかで使われる。もしsort_buffer_sizeが1Mでmax_connectionsが1000だった場合、メモリは最低でも1GB必要という事ですよ。

-- read_buffer_size

mysql> show variables like '%read_buffer%';

テーブル読み込み時に利用されるメモリサイズ。INDEXを使わずフルスキャンされる場合に利用。そもそもINDEXを見直すべきでは？！

-- read_rnd_buffer_size

mysql> show variables like '%read_rnd_buffer%';

テーブルをランダムに読み込む場合に使われるメモリサイズ。使用可能メモリより大きなテーブルでGroup By/Order Byをする際は大きめにしておく。ソート後のレコード読み取り速度を上げるかも。要はディスクI/Oの軽減。

-- myisam_sort_buffer_size

mysql> show variables like '%myisam_sort_buffer%';

REPAIR TABLE / CREATE INDEX / ALTER INDEX 時に利用されるメモリサイズ。かなり頻度は低い。

-- thread_cache_size

mysql> show variables like '%thread_cache%';

MySQLコネクションスレッドをプールする数。スレッドを破棄＆生成するコストを下げる。

-- tmp_table_size

mysql> show variables like '%tmp_table_size%';

サブクエリなどで一時的に実行結果をテンポラリテーブルに保存されたときのメモリサイズ。このサイズを超えた場合、MySQLはディスクへ一時テーブルへ保存しディスクI/Oが発生してしまう。このサイズを指定した場合はmax_heap_table_sizeも同じ値に設定する。

-- query_cache_size

mysql> show variables like '%query_cache%';

一度発行したクエリ実行結果をキャッシュしておき、二回目以降はクエリを発行することなくキャッシュから結果を返す。もちろん更新クエリなどが走った後はキャッシュはクリアされる。ちなみに

mysql> FLUSH QUERY CACHE;

でキャッシュを強制的にクリアできる。

-- skip-name-resolve
これは有名すぎますがDNS逆引きをしない。この設定をした場合、GRANT構文などでユーザを登録する際にもIP指定にする必要がある。

my.cnfの設定は基本的に上記のものを見直します。
もちろん細かい設定をする場合は、もっと詳細な設定を見直す必要があるのであくまで参考程度に。

あとは、スローログを取得させる。

slow_query_log=mysql-slow　// スローログを取得
logn_query_time=1　// 1秒以上かかったクエリを取得

OPTIMIZE TABLE [table_name]を定期的にかける。

mysql> OPTIMIZE TABLE table_name;

更新や削除などが頻繁に行われているテーブルに対してやると効果絶大。デフラグを解消させる。postgreSQLで言うところのvacuumみたいなもの。


あとは、サーバのメモリを増やすだとかサーバそのもののスペックを上げるだとか、アプリケーション側でのチューニングは必ず必要です。
特にスローログに出ているクエリは見直す余地がかなりあると思うので、そちらのチューニングも大切に！


以上でぇぇす。