2007-01-06
■[mysql]InnoDBを使うときのパフォーマンスチューニング
ストレージエンジンとしてInnoDBを使うときはMyISAMのときと触るべきポイントが違うので注意。
を読みながら取ったメモ。状況としてはRedHat AS3.0で動かしたときのDBT2*1のパフォーマンスを改善していくというもの。MySQL
デフォルト状態での分析
Handler_read_nextが多い、つまりrange scanかindex scanが多すぎる
slow query logで何が悪いかを引っかける
例では2秒以上処理にかかったqeuryを記録するようにしている
結果を分析
update文が遅かったけど、update文そのままではexplainできないので、同じwhere句のselectに変えて分析
単に高負荷で引っかかる奴も居るので注意
EXPLAIN SELECT no_o_id FROM new_order WHERE no_w_id = 3 AND no_d_id = 1
というEXPLAINで以下のような結果となった。
| 1 | SIMPLE | new_order | index | NULL | PRIMARY | 12 | NULL | 1543785 | Using where; Using index
(no_w_id,no_d_id)について複合インデックスをはることで、'index'(=インデックスのフルスキャン)を'ref'(=定数によるインデックス参照)に改善することができる。
いきなり遅いクエリを見付けるのは難しい
遅いクエリを大量に流して、最適化して、再度負荷をかけて検討
show processlistで一般的なクエリを捕まえるのもよい
チューニングの結果を確認
Handler_read_nextが低く抑えられている
innodb のステータスを確認
show innodb statusでinnodb特有のステータスが得られる
FILE IOの項目
- readが多すぎ!
innodb_buffer_poolをdefaultの8Mから1800Mに。
もうちょっと詳細にinnodbのステータスを調査
BUFFER POOL AND MEMORYの項目
読み込みの6倍も書き込みがある
それほど書き込みをするワークロードでもないのに
defaultのinnodb_log_file_size=5MBが小さすぎる
dirty pageのflushが多すぎる。これはもっと遅延できるはず
ヒット率から言ってbuffer poolの量は悪くない
innodb_log_file_sizeを増やす
innodb_log_file_size=512Mに
ちょっとコツがいる操作が必要。
- 一度mysqlをshutdownする
- innodbのログを別の場所に退避させる
- innodb_log_file_sizeの値を変える
- 再度mysqldを上げ直す。ログは無ければ勝手に作られる。
ログのサイズを大きくするとリカバリーに時間がかかるようになる。
- 小さくしたからって必ず短かくなるものでもない
より詳細な分析
open_tablesが多い
- table_cacheを増やす
innodb_thread_concurrencyを32に
- 目安は(num_disks+num_cpus)*2ぐらい
log I/Oが多いように見受けられる
- innodb_log_buffer_size=8M
スキーマに手を入れる
- new_ordery テーブルについて
先に最適化の為に追加したkeyと今まで有った主キーとは順序が違うだけ
初期状態の主キーの並びを使うようなクエリが無いし、いっそ追加したキーの並びを主キーとする
insertが多いテーブルにはキーが少ないことが重要
- stockテーブルについて
(s_w_id,s_i_id)で一意なので、s_quantityを主keyに含める必要はない
主キーのカラムのデータを触るのは非常にコスト高
テーブルの再構成
フラグメントを直したり主キーから引き易くしてくれる。
alter tableやoptimize tableでやってくれる。
その他の使えるヒント
主キーにshort integerを使う
ランダムな主キーを使わない
プレフィックスキーを使う
- キーを対象カラムの先頭数文字だけにすること
- インデックスが小さくできる
- innodbはキー圧縮が無いので
InnoDBのIO方式
innodb_flush_methodで指定する
Linuxの場合O_DIRECTを使うとOSとMySQLで二重のバッファしなくて効率的
ちょっと危険な方法
fsync(2)は信用できないことが多い*3
innodb_flush_log_at_trx_commit=0にするとcommit時にflushをしなくなる。
ただし、毎秒の1回のflushは実行される
カーネルのパラメータチューニング
hugememはメモリ空間を分割して管理するため*4そのオーバーヘッドがある
最近のkernelは直ってきているが、それでも普通のsmpよりは遅い
2.6カーネルにする
I/Oパフォーマンスが向上する
- CPU boundな負荷だと向上しない
I/Oスケジューラはelevator=deadlineがいい*5
swapを無効にする
2.6.7からswapの悪影響は無くなった
2.4系列なら切ると速くなる
2.6.7でswapを切っているとOOMが動く
DirectI/Oのパフォーマンス
kernelのバッファを使わなくなるのでhugememの悪影響を軽減できる
RAID10のブロックサイズ
大きな処理のときにはブロックサイズが大きい方が速い
RAIDのlevel
RAID5は当然のことながら遅い
RAID0は速いけど普通使わない
RAID10がやっぱり良いのでは
まとめ
- 全部で100倍以上速くなった。
*2:64bit archにすればもっと行ける?
*3:そりゃLinuxだけだと思うが
*4:通常のカーネルの場合ユーザ空間3G+カーネル空間1G。hugememの場合ユーザ空間4Gと別にカーネルが使う4Gを保持する。4G一杯までユーザ空間で使いたいときや16GB以上の主記憶のときに選択する。
*5:最近だとCFQなんで触らなくてもOKかも