MySQLチューニング 
メールサーバと格闘中です。姜子牙です。
涙目です。
さて今回はMySQLのチューニングについて書きたいと思います。
チューニングと言ってもMySQLはコンフィグファイル(my.cnf)を変更する方法とは別に再構築（コンパイルし直す）という方法があります。不要なオプションを削ったり、逆に組み込んだりすることで機能を追加したり、処理速度を上げることができるのです。が、まだ再構築の方についてはお世辞にもここに書けるほど把握できてないのでここではmy.cnfの変更でできることでチューニングをしてみたいと思います。
まず、以下が現在DBサーバに施してあるチューニングに関わる設定部分です。
#For MyISAM Table Global Buffer set-variable = key_buffer_size=256M #For InnoDB Table Global Buffer set-variable = innodb_buffer_pool_size=256M set-variable = query_cache_size=64M set-variable = table_cache=128
key_buffer_sizeとinnodb_buffer_pool_sizeはそれぞれMyISAMとInnoDBでのアクセスの際に使用されるキャッシュメモリになります。
現在DBにはそれぞれのDBエンジンを使用したDBが稼働しているため、両方にそれぞれキャッシュメモリ256MBを割り当てています。
もし、MyISAMやInnoDBどちらかだけであれば、設定はどちらかだけでOKです。
またクエリのキャッシュに64MB、テーブル(テーブル単位でのキャッシュ)に128個分のキャッシュを割り当てています。
テーブルのキャッシュはテーブル数単位で、明確に何MBなどは指定できないようです・・・。
今回はこれらの設定のみとしていますが、もしDBが参照系(Select文)が多いとか、逆に更新系(Insert文・Update文など)が多いなど
特徴がある場合には、read_buffer_sizeやsort_buffer_sizeなどにキャッシュを割り当てるとさらに速度向上が見込めます。
しかし、上記の設定は1クエリごとにいくつのメモリを割り当てるかの設定なので
平均のクエリの発生頻度などを考慮しないとメモリを食いつぶしてしまうのでご注意ください。
(key_buffer_sizeなどは全クエリ共通の領域として設定されるため、上記設定だと全体共通で256MBを使用するという設定です)
また、key_buffer_sizeとinnodb_buffer_pool_sizeについてはキャッシュのHIT率を測る方法があるため、それを今回は
チューニング効果の指標として使用しました。
MySQLのStatusで取得できるKey_readsとKey_read_requestsをKey_reads/Key_read_requests で求めるとキャッシュミス率が出ます。
弊社のDBサーバはチューニング前はHIT率が99.3%でしたが、チューニング後からは99.7%～99.8%になっています。
どこかのブログでは99.7以上が目安と書かれていたため、効果はまずまず・・・と言ったところでしょうか。
まだまだ設定できる項目は多く、その中をよく調査するともっと処理性能を向上させることは可能かもしれませんが今回はまずこのくらいで留めています。
3部に渡って書いたチューニング編ですが、サーバ側だけでできるチューニングには限界があります。
ApacheにはPerlやPHPのコード、MySQLにはSQL文によっても負荷が左右されます。以前にも少し書いたとおり、サーバ担当ですべて把握できれば良いのですが、規模が大きいほどそれは難しくなり少なからず開発のPGやSEと協力して行うことが重要になります。
自分の作業範囲で掘り下げられる部分は掘り下げ、範囲の外になってしまう部分はその範囲の担当に協力を仰ぎましょう。
サーバは乗っかるアプリ全部に影響を与えるため、担当者間調整だったり、コミュニケーションが重要になってきます。学生の卒業年度にやった性格診断で社交性が最低レベル(5段階評価の1)を記録した私がここまで出来ているためその重要性を意識していれば、コミュニケーション苦手っていう人でも苦手克服くらいはできるんじゃないかなと思います。
[かーつん](#ﾟДﾟ) < 「何キレイに〆てんだよ」
[姜子牙](*’▽’) < 「たまにはいいじゃないー」
[SELLCA](￣ o￣) < 「でも結構ダークですよね」
[姜子牙](；’-') < 「う、痛いところを・・・。」
ダークではないですよ。