MySQL のチューニング (ボトルネックの検出)

こんにちは！ onk です。

SAPさんが各社とも「ソーシャルアプリは負荷対策が大事」って言っていますね。 弊社でも mixi アプリ(PC)，mixi アプリモバイルをリリースしたときはお祭り状態だったので， ふりかえりも兼ねて MySQL のボトルネックを調べる方法を書いてみました。 (幸い，モバゲーオープンゲームのリリース時はこれらの経験が役に立ったので何ともなかったです)

といっても 9 割方

• そもそもサーバの設定がおかしい
• 更新が多いテーブルなのに MyISAM エンジン
• for 文の中でクエリを発行
• INDEX 張ってない
• データ量がえらいことになってる

辺りなんですけどねー。

基本は下から

まず，ボトルネックを調べるときは下の層から上がっていくのが基本です。たぶん。

なので ssh でサーバに入って (LoadAverage 300 ぐらいまでならなんとか入れますね) 以下のコマンドをよく叩きます。

top

現在稼動しているプロセスの一覧と，システム情報の概要が表示されるプログラム。

見るのは主に

• Load Average
• CPU 使用率
• メモリ使用量
• swap 使用量

と，これらが異常なプロセス。

たとえば swap だと，正常なときは

Swap:  7879872k total,      764k used,  7879108k free,  3589852k cached

ですが，異常に減っているときは

Swap:  7879872k total,  2529080k used,  5350792k free,  1574320k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
26945 mysql     20   0 7681m 6.0g 5080 S   37 77.2  17214:10 /usr/local/mysql/libexec/mysqld

のようになります。明らかに mysql がメモリ食い潰してますね。 free や ps でも確認出来ますが，どのプロセスが悪いのか見渡しやすいので，僕は top が好きです。

vmstat

これもシステム情報を表示するプログラムです。

• swap が発生しているかどうか
• CPU 使用率が異常な値じゃないか
• CPU IO Wait が発生しているかどうか
• Disk IO が異常値じゃないか
• Context Switch が異常に頻繁だったりしないか

あたりを確認します。

たとえば CPU IO wait が異常なときは

procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 0 11  20092 281480     12 7671592    0    0 27820    42 1967 2557  3  3 41 52
 0 12  20092 284492     12 7668020    0    0 26512     1 1812 2222  2  3 47 48
 0 13  20092 281188     12 7671080    0    0 33144     0 1890 2258  2  2 40 56
 0 13  20092 277208     12 7674300    0    0 24612     0 1722 2000  2  1 38 59
 0 24  20092 283364     12 7665688    0    0 24304     0 1871 2287  2  2 30 66
 2 21  20092 284912     12 7661960    0    0 31688    30 1714 2130  3  3 35 60

となります。

大抵の場合，以上の 2 コマンドで原因となっているプロセスを特定できるので，新機能のリリース時は 2 画面開いて

• top -d 5
• vmstat 5

として見張っています。

ありがちな例

CPU Usage が高い

大概，インデックスの張り忘れです。インデックスさえ張っておけば一瞬で終わるはずの WHERE 句や ORDER 句を，必死に CPU が計算しています。

最初はデータ量が少なくて気づかないかもしれないけど，次第に CPU 使用率が 100% に限りなく近づいて……。

varchar カラムに対して int を検索条件にする場合は INDEX が使われないので注意してください。 MySQL は文字列の型に対して where hoge = 1 と絞り込むとき，'01' や '1.00' の行もマッチします。つまり INDEX は使えず，全件走査となります。

ソーシャルアプリでは opensocial_owner_id が文字列なのでコレやりがちなんですよね。さすがにすぐ気づくとは思いますが(笑)

CPU IO Wait が高い

データ量に対して，メモリの割り当てが少ない場合とか。

show table status;

したときの Data_length + Index_length と key_buffer_size や innodb_buffer_pool_size を見直してみてください。 せめて Index だけでもメモリに載せてあげたいので。

ディスクの読み書きはメモリの読み書きの 10 万倍遅いです。ディスクアクセスは 1 回につき 5ms 程度かかるので，秒間 200 回しかアクセスできません。

1 回のクエリで 4 回ディスクへのアクセスが発生し，1 ページ表示するのに 5 回クエリを投げるとすると秒間 10 PV しか耐えられないことになります。 1 ユーザあたり 150 PV/day，ピーク時に平均の 2 倍になる場合で 2,500 UU/day が限界な感じですね。これが全てメモリに載っていれば 5 億人までいけます。

実行中のクエリを確認

MySQL の設定ではなくクエリが原因だと判断したら，実行中のクエリを確認します。

• innotop
• show processlist
• mprofile

の 3 パターンかな。

innotop

InnoDB を使ってるときはぜひ。 innotop - Project Hosting on Google Code

僕が注意しているのは

• InnoDB Buffers
  • Hit Rate
• Command Summary
  • Com_select
  • Com_show_status
  • Com_begin
  • Com_commit
  • Com_update
  • Com_insert
• InnoDB I/O Info
  • Reads/Sec
  • Writes/Sec
• Query List

ですね。

トランザクションが重い場合，Query List には「COMMIT;」しか表示されなかったりするので注意してください。

show processlist

innotop や mytop で実行中のクエリのリストが見えるんですが，これらを入れていない場合は show processlist; で確認します。

クエリを全て Info カラムに出力したいときは

show full processlist;

mprofile

Kazuho@Cybozu Labs: MySQL のボトルネックを統計的に監視・解析する方法

show processlist を 1,000 回実行し，どのクエリが何回表示されたかの統計を取ってくれます。何から潰すべきなのかの判断に迷ったらとりあえず統計取りましょう。

奥さんも書いてますが，定期的に実行してログを残しておくと，ユーザの動向と DB の使われ方の変遷が見えてきてサーバ増強計画を作りやすいですね。

……思ってたより長くなったので次回 EXPLAIN 編に続きます。乞うご期待！