Zen Cartで構築した予約システムの負荷対策(1)：DB読み出しクエリー数を削減して改善する

エンジニアの金田です。大量アクセスのある顧客サイトのサーバーの負荷削減について、弊社での対応の例を2章に分けて紹介したいと思います。

概要

弊社が初期にZen Cartをカスタマイズして構築し、運営サポートをしているサービス予約サイトで、毎年の繁忙期になるとDBがダウンしてしまいつながらなくなる不具合が頻発していました。
すでに負荷分散装置の設置をするなど対策を施しておりましたが不十分であったため、今季はシステムダウンやレスポンスの悪さを改善の手を打つべく、原因と対策を検討しました。
結論としてボトルネックが複数存在することが確認できました。サイトのトップページとなる部分のカレンダーの表示がスクリプト的にもDB的にも負荷が高かったこと。またAWS（Amazon Web Services）のインスタンスの性能がうまく発揮できていなかったことが判明しました。
まずは前者についてSQL文の発行を見直しました。よく似たSQL文を複数回発行している部分を特定し、類似のSQL文を何度も発行するのではなく、代わりに一度に大量の情報を取得し、それをキャッシュすることで一呼び出しあたりのDBアクセス数を減らしたところ、状況が改善しました。今回はこのSQL文見直しについて紹介します。

目的

繁忙期において、顧客のサーバーがダウンするなど運用に支障をきたしているため、原因を特定し改善を行います。

調査

まず、最適化する部分を特定する作業を行います。一般にアクセスが集中している部分がボトルネックとなっている可能性が高いのでアクセス数を調査したところ、予約のためのカレンダー表示画面が最もアクセスが多かったので、この部分を中心に調べることにしました。

    /calendar: 5054　←カレンダー部分
    /input_seat: 1780
    /input_checkin: 1042
    /seat_detail: 164
    /date: 133
    /redirect_index: 111
    /orders_export: 56
    /rental: 3
    login_as_customers: 2
    /booking_order_change: 1

ログ解析文は即席で以下のPerl文で生成しました


    open(my $fh, '<', 'access_log.txt');
    my %module;
    while(&lt;$fh&gt;){
        m{GET /xxx/yyyy/} or next;
        m{module=([^& "]+)};
        my $module = $1;
        next if $module eq '';
        $module =~ s{\%2f}{/}ig;
        $module{$module}++;
    }
    foreach my $key (reverse sort {$module{$a} <=> $module{$b}} keys %module){
        print "$key: $module{$key}\n";
    }

Googleアナリティクスでも調査しましたが、やはりカレンダー部分のアクセスが多いことが判明しました。

そこでAWS上にテスト環境を構築し、現象の再現をすることにしました。

負荷状況の調査はApache Jmeterを使用しました。

カレンダー部分をリバースエンジニアリングしたところ、カレンダーの日毎（31日分表示されていれば31回）にSQLクエリを発行している部分があり、ここがDBへの負荷要素になっていると仮定しました。カレンダーが3つ表示されていれば100回程度の呼び出しになるからです。1日について3つのSQL文が発行されていれば300回の呼び出しとなり、無視できない量になります。

ここで一旦SQLリクエストの呼び出し回数を計測してみます。すると1ページにつき800クエリほど発行していることが分かりました。低レベルな（PHPから呼んでいない）呼び出しも含まれていますが、少ない数ではありません。特に

get_date()
が192クエリ

get_monthly_data()
が同じく192クエリ、

合計384クエリ発行しているので、ここの呼び出しを2回にすれば約400クエリ、半分の削減となりかなりの効果が期待できます。冒頭でも書いた通り、1回の呼び出しで192クエリ分の情報を取得し、メモリ上にキャッシュを取る方法を試みることにしました。本稿では以降、この手法を使ったものを「キャッシュ版」と呼びます。

まず修正前にカレンダー部分の負荷状況を調べておきます。

 2.12
 1.90
 1.76
 2.04
 1.85
 1.77
 1.79
 1.80
 1.98
 2.13
 ---
 平均1.914（秒）

テスト環境ではフロントサーバが1台しかなく、DBに負荷が掛かる前にHTTPサーバが音を上げてしまうので、SQLクエリ悪玉仮説の裏付けがなかなか取れずもどかしい部分もありましたが、DBに対して負荷が減るのは間違いないので、結果を見てSQLクエリの結果のキャッシュ版を実装しました。

0.1秒ほど改善しています。ここだけみると大した変化ではありませんが、DBの負荷はどうでしょうか。DBサーバーの負荷もあわせて計測してみることにしました。

ご覧のように、HTTPサーバーの負荷（オレンジ色）はいずれも最大になっていますが、チューニング前はDBサーバーにそれなりの負荷が掛かっていたのに対し（青色左側）キャッシュ版はほとんどDBに負荷が掛かっていないことが分かります（青色右側）。問題になっていたのはDBサーバーの過負荷による遅延やサーバーダウンなので、大きな効果が期待できます。テスト環境で動作に問題ないことを確認後、本番環境に組み込むことにしました。

結果

キャッシュ版を本番環境に適用しました。クエリ呼び出しが700クエリ/秒から200クエリ/秒まで削減されました。速度も2秒前後掛かっていたのが0.7～0.8秒程度まで高速化しました。データベースに対する負荷は大幅に改善されましたが、キャッシュしているため1リクエスト当たりで使用するメモリ量は増えているので、今度はwwwサーバーの負荷が目立つようになりました。この点の改善については次章に譲ります。

結論

データベースの負荷が極端に高い場合、SQL問い合わせに重い部分がないか、あるいは大量に発行している部分がないかチェックした方がよい。つまり、スロークエリを探すことも重要だが発行回数が問題になることもあるということです。今回のDB負荷は後者によるものでした。

データベースへの問い合わせを変更する場合、結果に食い違いががないか確認することも重要になります。今回の場合も適用前と適用後で差分を取るなど厳密なチェックを行いましたが、「過去に対する問い合わせは発生しない」という前提で設計してしまったために管理画面から特定の条件の検索をすると結果がおかしくなるというバグが発生してしまいました。前提とする仕様を勝手に解釈せずにしっかりと洗い出し、施策が実際の仕様に合致しているか、そうでない場合は場合分けして処理を分けるなどの対策が必要だと感じました。

今回の施策で繁忙期のレスポンスはかなり向上しましたが、HTTPサーバー側に負荷が増える結果となったこと、依然としてDBにアクセスが殺到していることを鑑み、更に負荷削減対策を取ることにします。

次章に続きます。