Hatena::Diary

フツーな日常

2007-01-06

[]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かも