ISUCONに参加しました - fushigu’s blog

はじめに

Twitter（現 X) で、ISUCONに参加する人が多く気になっていたことと、追加募集していたので応募して初めて参加しました。参加した際にISUCONの当日にどのようなことを行ったかを記録する意味で記事を書きました。

また、自分はWebバックエンドについては初心者であり、あんまりWeb技術がわからないけどISUCON参加して大丈夫かなという人の参考になればうれしいです。~~ソロで参加したのでチームプレイみたいなところは全く参考にならないけど。~~

自分のスペック

タイトルのWeb初心者というのは語弊があり、NuxtやNextといったフレームワークやJavaScript等を用いて、サイト作成は行ったことはあります。また、基本的なプログラミングをする技術、CSの知識はあるので、その辺は全くの初心者とは違うと思います。

逆に次のことは全くやったことがないです。 - SQLを記述すること、SQL-likeなDBを用いること - Nginxをいじること - Goを読み書きすること - AWSを触ること

当日まで

流石に何をすればいいかわからないまま当日を迎えるのはまずいので、ISUCON本を読むことにしました。当日まではデータベースを高速化する章までしか読めませんでしたが、いろいろな計測の方法と何を改善するか等がわかったので、かなり役に立ちました。

具体的には、次の知識が当日役に立ったと思います。

MySQLのslow queryのpt-digest-queryの使用の仕方
MySQLのindexについて
MySQLのexplainコマンドについて
Nginxのある程度の仕組み

また、過去のISUCONの解法を眺めてどういうことをやればいいんだということを把握見ていました。初心者だしDBのindexを張るのと、N+1（いまだに何かわかっていない）を改善すればよいだろう！という印象を持ちました。

当日

初期スコアを出すまで

AWSには、Cloud Formationというスクリプトを用いて自動でサーバやらなんやら設定してくれるサービスがあるらしいです。まず、それにyamlファイルを与えてサーバを立ててログインしました。事前のAWS環境確認でもCloud Formationを使っていたのですが、その時は記事を参考に何が何だかわからないままyamlファイルを入れてスタックを削除していました。

とりあえず3つサーバを立てた後ポータルを見ると、ベンチマークのjobを提出するために「サーバを選択してください」という文字があったので、よくわからないまま1つを選択し、とりあえず出しました。

そうしたら3,471というスコアがつきました。

システムの把握と改善点の把握

3つのサーバがあるのはいいが、「3つのサーバをどうやって使っているんだ？」となって、まずサーバの中を眺めることにしました。そうして3つのサーバのhomeを見るとすべてにwebapp、go、localの3つのフォルダがあることに気づきました。ここから、どうやら3つのサーバはすべて同じで、1つのサーバでベンチを回しているんだろうなと推測しました。

システムを把握した後、まずtopを用いてベンチを回している最中のCPUを使用率を見ると、MySQLのプロセスが高い使用率（9割ぐらい？）で推移し、かつCPU使用率が100%だったので、MySQLのクエリが悪さしているんだろうなということで、そこから改善することにしました。

SQLの改善

ISUCON本の通り、slow queryを吐き出させることにしました。 ISUCON本では、slow queryの閾値を0にすることを強くお勧めしていたのでそのように設定し、pt-digest-queryでlogを解析させました。

そうすると次のように出てきました。

# Profile
# Rank Query ID                     Response time  Calls  R/Call V/M   Ite
# ==== ============================ ============== ====== ====== ===== ===
#    1 0xF7144185D9A142A426A36DC... 282.0526 28.1%  11707 0.0241  0.01 SELECT livestream_tags
#    2 0x84B457C910C4A79FC9EBECB...  97.6360  9.7%  20885 0.0047  0.01 SELECT icons
#    3 0x42EF7D7D98FBCC9723BF896...  96.7173  9.6%  13677 0.0071  0.01 SELECT records
#    4 0xDA556F9115773A1A99AA016...  83.4807  8.3% 251972 0.0003  0.01 ADMIN PREPARE

一番遅かったlivestream_tagsのクエリは以下のものでした。

SELECT * FROM livestream_tags WHERE livestream_id = ?

explainで説明させるとたくさんのrowを見ていたので、これにindexを張るといいのかなということで張りました。 livestream_tagsのテーブルでは、ほかに

SELECT * FROM livestream_tags WHERE tag_id IN (?) ORDER BY livestream_id DESC

というクエリが走っていたので、indexを張る際には、

alter table `livestream_tags` add index `idx_tag_id_livestream_tags`(`livestream_tags` desc, `tag_id`);

としました。

explainで参照するrow数が減ったことを確認して、ベンチを回すと4,760というスコアがつきました。

SQLの改善2

引き続きtopを見るとMySQLプロセスが高いCPU使用率で依然CPU使用率がほぼ100%だったので、再びpt-digest-queryでlogを解析させました。

# Profile
# Rank Query ID                     Response time Calls  R/Call V/M   Item
# ==== ============================ ============= ====== ====== ===== ====
#    1 0x84B457C910C4A79FC9EBECB... 70.7090 16.6%  13069 0.0054  0.01 SELECT icons
#    2 0xDA556F9115773A1A99AA016... 57.1001 13.4% 157650 0.0004  0.01 ADMIN PREPARE
#    3 0x42EF7D7D98FBCC9723BF896... 47.3000 11.1%   6723 0.0070  0.00 SELECT records

今度はまた別のクエリがトップに来ており、goファイルを見るとアイコンを取ってくる関数が問題になっていました。

SELECT image FROM icons WHERE user_id = ?

DBに画像ファイルを載せるのはあんまり良くないと思い、何とかnginxに静的に配信させることを考えました。

Goを読むのは慣れていませんでしたが、func postIconHandlerとfunc getIconHandlerでそれぞれ画像を更新、配信していることを見つけ、そこを何とかしようということになりました。通常、更新がなければ簡単なんですが、そうではないのでここで苦慮しました。

nginxで配信はあきらめ、次にmemcachedを使うのはどうかということで入れてみましたが、これもそんなに効果はなく4,830とか4,382ぐらいのスコアでした。

結局、先ほどのクエリをexplainで見てみると、idで検索しているのにも関わらず数row分見ていることに気づき、user_idでindexを張りました。これが当たり、スコアが6,196ぐらいまで伸びました。

SQLの改善3

pt-query-digestで確かめながら遅いクエリから順次indexを張っていくことにしました。

1つ目はdnsのクエリです。

SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and name='pipe.u.isucon.dev' and domain_id=2;

このクエリに対して、disabled、name、domain_idにindexを張りました。スコアはこれで5,300ぐらいまで下がったんですが、名前解決数が2,200程度から7,700程度まで上がりました。

次にこれです。

SELECT IFNULL(SUM(l2.tip), 0) FROM users u
        INNER JOIN livestreams l ON l.user_id = u.id    
        INNER JOIN livecomments l2 ON l2.livestream_id = l.id
        WHERE u.id = 113;

このクエリを改善する必要があるのですが、INNER JOINという初めて見るものを使っているので少し時間がかかりました。このクエリを各ユーザごとに回しているので、始めはbulk selectみたいなことができないのかなと思いました。でも、よく見るとusersテーブルを参照する必要がないので、以下のように変えました。

SELECT IFNULL(SUM(l2.tip), 0) FROM livestreams l
        INNER JOIN livecomments l2 ON l2.livestream_id = l.id
        WHERE l.user_id = 145;

これで、スコアは5,566ぐらいで変わらないのですが、pt-query-digestの結果が変わったので良かったかなと思います。

最後にthemesテーブルが一番遅いと出ました。これは単純にuser_idにindexを張りました。

SELECT * FROM themes WHERE user_id = 1049

これでスコアが6,100ぐらいまで伸びました。

最終的に何回か提出してよかったものを最終スコアとし、6,400ぐらいでした。

さいごに

最後には、CPU使用率が100%に届いていなかったので、GoのMySQLとのconnection数を増やしてもよかったかもしれません。

また、結局時間がなくマルチサーバに対応することをできませんでした。なんとなくDBの環境変数を書き換えればできるのかな？と思っていますが、実際にどうなのかはわからないままです。

付け加えると、DBにindexを張っていただけなので、もう少し別の高速化（N+1とかbulk insertとか）について今後理解できたらいいなと思います。