InnoDBを使うときのパフォーマンスチューニング

ストレージエンジンとしてInnoDBを使うときはMyISAMのときと触るべきポイントが違うので注意。
http://www.mysqlperformanceblog.com/files/presentations/OSCON2004-MySQL-Innodb-Performance-Optimization.pdf
を読みながら取ったメモ。状況としては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に。

glibcの制限からこれ以上大きくできない*2



    もうちょっと詳細に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倍以上速くなった。

*1:TPC-Cを模したベンチマーク

*2:64bit archにすればもっと行ける？

*3:そりゃLinuxだけだと思うが

*4:通常のカーネルの場合ユーザ空間3G+カーネル空間1G。hugememの場合ユーザ空間4Gと別にカーネルが使う4Gを保持する。4G一杯までユーザ空間で使いたいときや16GB以上の主記憶のときに選択する。

*5:最近だとCFQなんで触らなくてもOKかも

1	SIMPLE	new_order	index	NULL	PRIMARY	12	NULL	1543785	Using where; Using index