このブログはURLが変更になりました

新しいブログはこちら→ https://matsuu.hatenablog.com/

isucon2に参加してきました＆主にSQL周りでやったこと

いんふらえんじにあーチームとして @netmarkjp 先生、 @ishikawa84g 先生の3人でISUCON2に参加したmatsuuです、こんにちは。

大まかにやったことは同じチームのnetmarkjp先生にツイートをまとめていただいたので省略。

で、ツイートした以外の補足とか。以下は当日やりながら考えた内容です。

最近購入されたチケットをどげんかせんといかん

トップページ、アーティストページ、チケットページの左側に表示される「最近購入されたチケット」はJOINが多く、また何度も呼ばれているため、これをキャッシュさせればいいんじゃないか？と考えました。

SELECT stock.seat_id, variation.name AS v_name, ticket.name AS t_name, artist.name AS a_name FROM stock
  JOIN variation ON stock.variation_id = variation.id
  JOIN ticket ON variation.ticket_id = ticket.id
  JOIN artist ON ticket.artist_id = artist.id
WHERE order_id IS NOT NULL
ORDER BY order_id DESC LIMIT 10

そこで、チケットを購入した際に最近購入されたチケットの以下の一行を生成し、フロントエンド側から返すように実装しました。

<tr>
  <td class="recent_variation"><: [$item.a_name, $item.t_name, $item.v_name].join(' ') :></td>
  <td class="recent_seat_id"><: $item.seat_id :></td>
</tr>

これを、isucon2本番の前日に初めて試したnginx + redis2-nginx-module + lua-nginx-moduleで生成して返せばいいんじゃないかと考えました。

この時点ではリバースプロキシの負荷が軽かったので、リバースプロキシでRedisで動かしてnginxと直結してます。

購入処理のSQLの後に以下のような内容を追加。ソースを残してなかったので若干うろ覚え。

  my $item = $self->dbh->select_row(
    'SELECT variation.name AS v_name, ticket.name AS t_name, artist.name AS a_name FROM variation
       JOIN ticket ON variation.ticket_id = ticket.id
       JOIN artist ON ticket.artist_id = artist.id
     WHERE variation_id = ?',
    $variation_id
  );
  require Redis::Client;
  my $redis = Redis::Client->new(host => 'rev13', port => 6379);
  use Time::HiRes qw(time);
  $redis->zadd(time => $c->tx->render('recent_sold.tx', { item => $item, order_id => $order_id }));

最初はRedisモジュールで実装しようとしたものの、zaddの方法がわからなかったのでRedis::Clientに変更。Redisへの登録は初めてでぶっつけ本番でしたがなんとかなりました。
nginx側でRedisからデータを吸い出してトップ、アーティスト、チケットを組み立てました。
しかし性能はあまり向上せず。その時の判断はSELECTを追加することで購入処理に余計に時間がかかってしまい、あまり意味がなかったとの判断でした。

購入処理のSQLをどげんかせんといかん

その後、各リクエストのレスポンスタイムを測ったところ、購入処理のSQLがやたら重いことが判明しました。

購入トランザクションは以下のようになっています。

BEGIN
INSERT INTO order_request (member_id) VALUES (?)
-- last_insert_idでorder_idを取得
UPDATE stock SET order_id = ? WHERE variation_id = ? AND order_id IS NULL ORDER BY RAND() LIMIT 1
-- 更新対象がなければここでROLLBACK(完売)
SELECT seat_id FROM stock WHERE order_id = ? LIMIT 1
COMMIT

また、並列して以下のようなSQLも実行されています。

アーティストページのstockに関するSQL

SELECT COUNT(*) FROM variation
  INNER JOIN stock ON stock.variation_id = variation.id
WHERE variation.ticket_id = ? AND stock.order_id IS NULL

チケットページのstockに関するSQL

SELECT seat_id, order_id FROM stock WHERE variation_id = ?
SELECT COUNT(*) FROM stock WHERE variation_id = ? AND order_id IS NULL

またトップ、アーティスト、チケットのそれぞれで最新の購入履歴を取得する以下のSQLも実行されています。

SELECT stock.seat_id, variation.name AS v_name, ticket.name AS t_name, artist.name AS a_name FROM stock
  JOIN variation ON stock.variation_id = variation.id
  JOIN ticket ON variation.ticket_id = ticket.id
  JOIN artist ON ticket.artist_id = artist.id
WHERE order_id IS NOT NULL
ORDER BY order_id DESC LIMIT 10

レスポンスタイムとクエリーログから推測して、stockテーブルに対してSELECTとUPDATEが排他となるので処理に時間がかかっているのだろうと判断しました。そこで、stockに対するSELECTを減らす目的で購入履歴を保存するテーブルrecent_soldを作成しました。

CREATE TABLE recent_sold (
  order_id INT(10) UNSIGNED NOT NULL,
  variation_id INT(10) UNSIGNED NOT NULL,
  seat_id VARCHAR(255) NOT NULL,
  INDEX (variation_id),
  PRIMARY KEY (order_id)
);

購入トランザクションは次のように変更。

BEGIN
INSERT INTO order_request (member_id) VALUES (?)
-- last_insert_idでorder_idを取得
UPDATE stock SET order_id = ? WHERE variation_id = ? AND order_id IS NULL LIMIT 1 -- ← ORDER BY RAND()はとっぱらってもエラーにならなかったので削除 
-- 更新対象がなければここでROLLBACK(完売)
COMMIT -- ←この時点で座席確保して確定しているのでCOMMITをここに移動
SELECT seat_id FROM stock WHERE order_id = ? LIMIT 1
INSERT INTO recent_stock (order_id, variation_id, seat_id) VALUES (?, ?, ?) -- ←厳密にする必要はないと判断したのでトランザクションの外に追加

アーティストページのstockに関するSQL

SELECT COUNT(order_id) FROM recent_sold WHERE variation_id = ? -- ←購入履歴から同じものを生成できる。処理の遅いIS NULLがなくなって高速化

チケットページでのstockに関するSQL

SELECT seat_id, order_id FROM recent_sold WHERE variation_id = ? -- ←埋まってるところだけ取得でも正しく座席表は生成できるので差し替え
SELECT COUNT(order_id) FROM recent_sold WHERE variation_id = ? -- ←IS NULLいらないぜﾋｬｯﾊｰ

最新の購入履歴を取得するSQL

-- stockをrecent_soldに差し替えても同じ結果を取得できる。IS NULLもなくて幸せ
SELECT recent_sold.seat_id, variation.name AS v_name, ticket.name AS t_name, artist.name AS a_name FROM recent_sold
  JOIN variation ON recent_sold.variation_id = variation.id
  JOIN ticket ON variation.ticket_id = ticket.id
  JOIN artist ON ticket.artist_id = artist.id
ORDER BY order_id DESC LIMIT 10

これで排他処理がrecent_soldに移ったこと、SELECTの条件が軽くなったことにより処理できるチケット数が大きく向上したようです。また上記のRedisへの追加を再び適用することでさらに処理できる数が向上しました。

この時点で負荷はDBからappに移ったことを確認しています。

appに負荷が移ったので

残り時間も少なかったので、revとdbにもappをインストールし、4台でぶん回す作戦に出ました。その結果、ベストスコアで19万弱にまで改善。ここでタイムアップ。残念ながら18万の壁は超えられませんでした。

まとめ

座席予約をIS NULLを条件にしたUPDATEで実装するってのはナンセンスだと思いました。座席マスタと予約トランザクションに分けるべきで、そうすればトランザクション処理的にも軽くなるんじゃないかと。そのようなテーブル構造に変えるところまでは時間がなかったです。無念。

上位チームのように短時間でまるっと再実装するほどのスキルは私にはないのですが、まだSQLであればなんとかわかるので自分のできる範囲での改善に終始しました。勝負には勝てませんでしたが、自分的には結構満足しています。いや、満足してはいけないのだと思うけど。

nginx + Redis + Luaの構成を今回初めて触りましたが、職業プログラマーではないインフラエンジニアの立場でも結構使えるんじゃないかという感触を得ました。これは今後の仕事にも活かしていこうと思います。

わからなかったこと

Plack(Starman)構成でDevel::NYTProfを実行方法がわからない

単体のPerlプログラムに対して

#!/usr/bin/perl

を

#!/usr/bin/perl -d:NYTProf

とすることでプロファイルをしたことはありますが、Plack構成でどのように出力すればいいのかが皆目検討がつきませんでした。こういう場合どうすればいいんでしょうね。あとで調べる。