私が歌川です

@utgwkk が書いている

社内ISUCONでISUCON11予選問題を解いて840,504点を記録した

技術 ISUCON

こんにちは。社内ISUCONで優勝しました。ネタバレ防止のために「続きを読む」を挟みます。

最終的なサーバー構成

  • サーバー1 (role: web)
    • アプリケーション (Go)
    • nginx
  • サーバー2 (role: db)
    • DB (MariaDB)
  • サーバー3 (role: web-writer)
    • アプリケーション (Go)

roleについては後述します。

やったこと

デプロイスクリプトを書く

コマンド1つでデプロイできるように手順を整理しました。git pullしてアプリケーションをビルド+再起動する、ログをローテートする、nginxやDBの設定をコピーして再起動する、といったシンプルなスクリプトです。これがあるだけでだいぶ作業が楽になります。

tmux-csshをインストールする

複数サーバーで同時にデプロイしたいのでtmux-csshをインストールしました。 brew install tmux-cssh で入るかと思ったら意外と入らなかったのでリポジトリから直接取ってきました。

LIMIT 1 をつける

とりあえずpt-query-digestの実行結果からやばそうなクエリを見ていくと、GET /api/trend のクエリが目につきます。なんとかしたいところですが、よく見ると最新1件のメトリックしか使っていないことが分かります。ここはSQLに LIMIT 1 を足すだけでよかろう、ということで足しました。

isu_condition テーブルにインデックスを貼る

isu_condition テーブルに対しては、 jia_isu_uuid カラム (と timestamp カラム) で絞り込んだのち timestamp カラムでソートする、というクエリがよく発行されているけどインデックスがないので、複合インデックスを足しました。後述するように、このインデックスがそのまま主キーになります。

コンディションレベルの値をDBに持たせてSQLで絞り込めるようにする

getIsuConditionFromDB 関数では、isu_condition テーブルの非正規化された condition カラムの値をパースしてコンディションレベルを計算し、ユーザーが指定したコンディションレベルのメトリックだけを返す実装になっていました。

最後に limit 件までに切り詰めていることからも、指定された期間の全メトリックを取ってくるのは明らかに無駄だと分かりますが、このままではLIMIT句を足せません。逆に言えば、コンディションレベルの値がDBに入ってさえいれば condition_level IN ('info', 'warn') のような条件式を足すことでSQLだけで絞り込めるようになります。

以下のstackoverflowにある手法を使って =true の数を数えて分岐することでコンディションレベルを再現し、DBに生成カラムとして持たせることにしました。メトリック投稿時に値を計算して書き込んでもよかったかもしれませんが、生成カラムなら初期データにも後から簡単に足すことができて、取り除くのも簡単です。スキーマ定義に生成カラムを足すと初期データの投入に失敗するようになったので、投入後に改めて ALTER TABLE 文を発行するようにしました。

stackoverflow.com

ALTER TABLE `isu_condition` ADD COLUMN `condition_level` VARCHAR(32) AS (
  CASE ROUND(
    (
        LENGTH(`condition`)
        - LENGTH(REPLACE(`condition`, '=true', ''))
    ) / LENGTH('=true')
  )
  WHEN 0 THEN "info"
  WHEN 1 THEN "warning"
  WHEN 2 THEN "warning"
  WHEN 3 THEN "critical"
  ELSE ""
  END
) STORED AFTER `condition`;

これでSQLにLIMIT句をつけられるようになったのでかなりマシになったと思います。

POST /api/condition/:jia_isu_uuid でbulk insertする

メトリックを投稿するときに1件ずつINSERT文を発行しているけど、これは明らかにbulk insertにするのがよかろう、ということで書き換えていきます。 sqlxの NamedExec 関数を使うと簡単にbulk insertできました。

// エラー処理は省略
var args []IsuCondition
for _, cond := range req {
    timestamp := time.Unix(cond.Timestamp, 0)
    args = append(args, IsuCondition{
        JIAIsuUUID: jiaIsuUUID,
        Timestamp:  timestamp,
        IsSitting:  cond.IsSitting,
        Condition:  cond.Condition,
        Message:    cond.Message,
    })
}
_, err = tx.NamedExec(
    "INSERT INTO `isu_condition`"+
        "    (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `message`)"+
        "    VALUES (:jia_isu_uuid, :timestamp, :is_sitting, :condition, :message)",
    args)

複合降順インデックス用のカラムを足す

MySQL 5.7では複合降順インデックスがうまく効かないので、値を降順にしたカラムに対して複合インデックスを貼ることにしました。

ALTER TABLE `isu_condition` ADD COLUMN `timestamp_desc` INT AS (-UNIX_TIMESTAMP(`timestamp`)) AFTER `timestamp`;
CREATE INDEX `jia_isu_uuid_timestamp_desc` ON `isu_condition` (`jia_isu_uuid`, `timestamp_desc`);

このようにした上で ORDER BY timestamp DESC を ORDER BY timestamp_desc に書き換えることで、ISUの最新のメトリックを取得するクエリがちょっとだけ速くなります。

DBを別サーバーに分ける

さすがに1台構成のままではサーバーのCPU使用率が高いままなので、DBを分離することにしました。別サーバーからクエリを発行できるユーザーを作るのに手間取りましたがなんとかなりました。普段あまりGRANT文を実行しないので……。

role ファイルにサーバーのロールを書いて、web ロールのときアプリケーションとnginxの設定だけデプロイする、db ロールのときDBの設定だけデプロイする、と分岐できるようにデプロイスクリプトを改造しました。

この作業が完了した時点では以下のようなサーバー構成になりました。

  • サーバー1 (role: web)
    • アプリケーション (Go)
    • nginx
  • サーバー2 (role: db)
    • DB (MariaDB)

POST /api/condition/:jia_isu_uuid を受ける用のアプリケーションを別サーバーに分ける

まだアプリケーションサーバーのCPU使用率が高いので、ISUのメトリックを投稿する部分を別サーバーに切り出すことにしました。

ログローテートの処理にバグがあって*1nginxの設定がうまく反映されずに手こずりました。nginxの設定だけでPOSTリクエストのみを別サーバーに送るのがうまくできずに GET /api/condition/:jia_isu_uuid へのリクエストも飛ぶようになっていましたが、よく見るとメトリック投稿先のURLを指定することができましたね……。とはいえ負荷はだいぶマシになったのでよかったのではないでしょうか。

この作業が完了した時点では以下のようなサーバー構成になりました。

  • サーバー1 (role: web)
    • アプリケーション (Go)
    • nginx
  • サーバー2 (role: db)
    • DB (MariaDB)
  • サーバー3 (role: web-writer)
    • アプリケーション (Go)

この時点で162,875点を記録しており、予選通過ラインを超えていました。

isu_condition テーブルの主キーを変える

isu_condition テーブルにはAUTO INCREMENTな主キーが設定されているけど、jia_isu_uuid と timestamp で一意だからそちらを主キーにしてもよいのでは、ということをチームメンバーから聞いたのでやりました。

主キーを変えられることは分かりましたが、以下のように1つのALTER TABLE文の中で実行しないとエラーが出るのでちょっとハマりました。

ALTER TABLE `isu_condition`
  -- idカラムの主キーを落とす
  DROP PRIMARY KEY,
  -- idカラムを落とす
  DROP COLUMN `id`,
  -- (`jia_isu_uuid`, `timestamp`) を新たな主キーにする
  ADD PRIMARY KEY (`jia_isu_uuid`, `timestamp`);

GET /api/trend の前段のN+1クエリを解消する

isu テーブルから character を取得して、character の値ごとに isu をSELECTしていますが、つまり全ての isu をSELECTするのと変わらないのでそのようにクエリを書き換えました。実際にはもうちょっとクエリを最適化する余地があったと思います。

GET /api/trend をVarnishでキャッシュ + gzip圧縮する

もうちょっとクエリをましにする余地はあったと思いますが、レギュレーションによれば GET /api/trend のレスポンスをキャッシュしても問題ない、しかもユーザーごとに出し分ける要素がないからキャッシュしやすい、ということでキャッシュを導入することにしました。

vcl 4.0;

import directors;

backend isucondition1 {
    .host = "127.0.0.1";
    .port = "3000";
}

sub vcl_init {
    new bar = directors.round_robin();
    bar.add_backend(isucondition1);
}

sub vcl_recv {
    if (req.url ~ "^/api/trend") {
        unset req.http.cookie;
    }
}

sub vcl_backend_response {
    # /api/trend を2秒近くキャッシュしておく
    if (bereq.url ~ "^/api/trend") {
        set beresp.grace = 0.2s;
        set beresp.ttl = 1.8s;
    }
    set beresp.do_gzip = true;
}

これで GET /api/trend の負荷に引きずられてアプリケーションが落ちることもなくなったと思います。ここで280,935点を記録していました。

そういえば Cache-Control レスポンスヘッダを設定するのを忘れていそうですね。

静的ファイルの配信を全てnginxに寄せる

静的に返せるところは返したらよかろう、ということで静的ファイルの配信をnginxに寄せました。シングルページアプリケーションに対応するnginxの設定は毎回ググりながら書いています。

全てのメトリックを記録するようにする

このあたりで dropProbability に応じてメトリックを落とすのをやめるようにしました。さよなら最後の dropProbability ……。

ここで442,325点を記録していました。

SELECT * FROM isu をやめて必要なカラムだけ取得する

isu テーブルにはISUの画像データがカラムに突っ込まれています。これを取得しないようにクエリを修正してまわりました。理論的には効くはずだけど、ここがボトルネックになってはなかったので気休めだったかも?

感想

基本に忠実にプロファイリングして、ボトルネックを順番に潰すことで予選通過ラインをじゅうぶんに上回る得点を記録できました。ほかにもDBのコネクション周りのパラメータを調整したり、ログを止めて回ったりしています。うまくリソースを活用できるように調整して回った結果の点数なので、アプリケーションコードだけでなくパラメータにも注目する必要があると思います。このあたりはチームメンバーにISUCON本の記述を教えてもらいつつ作業していました。

もうちょっとよくする余地があるとしたら、GET /api/trend のクエリをチューニングするとか、メトリックをRedisに入れるとかはできそうですが、ユーザーを増やさない戦略が最強という説もあって悩ましいですね。メトリック投稿を取りこぼさないようにしたら加点要素が多くなるはずなので、ある程度まとめてからDBにINSERTするように書き換えることもできたかも? いずれにせよユーザーストーリーを読み解いて、どういう戦略を取るとちょうどよい点数に落ち着くのか、をちゃんと分析できるようになりたいです。

感想戦では、アイコン画像の配信をなんとかしているチームが多かったような気がするけど、そういえばアイコン画像については何もやってないですね。アクセスログは何度か見ているけど上位に上がってこなかったのでずっと無視していた気がします。

さくっとレスポンスキャッシュしたいならVarnishは便利ですね。とはいえまだ導入するのにちょっと手こずっているのでもう少し練習が必要かも?

*1:アクセスログがないときにnginxの再起動が行われていなかった