Gマイナー志向

とくに意味はありません

ISUCON13にチーム「ウー馬場ーイー222」で参加して最終スコアは 49,344 でした

TL;DR

2023年11月25日に開催されたISUCON13に参加しました。最終スコアは49,344でした。実装言語はGoです。今回のチーム名の由来は申し込み時のチームIDが222だったためです。

追記：30位でギリギリTOP30チームに入りました。やったね。

今回スコアが思ったほど奮わなかったのですが、敗因はN+1を甘く見ていたことだと思っています。MySQLのスロークエリーログ（トータル実行時間順）を見ているだけではだめで、実行回数をもっと重視する必要があるなと思いました。うん、そうだね。

今回はDNSが課題に組み込まれており、インフラエンジニア3人態勢の我々のチームの得意分野とする領域のはずだったのですが、DBのボトルネックを解消できずDNS周りに着手できませんでした。くやしー。

運営の皆様、ことしもよい問題をありがとうございました。来年こそはリベンジして上位入賞を目指したいと思います。がんばるぞー！！！

リポジトリは競技終了後に公開しました。

当日の様子は動画にしてYouTubeにアップしています。8時間ノー編集です。

体制

あいこん	なまえ	やくわり	ペアプロ
	matsuu	バリバリ実装する前衛	ドライバー
	netmarkjp	司令塔+ベンチ実行+結果解析	ナビゲーター
	ishikawa84g	セキュリティ+情報官+動作確認 AppArmor、マニュアルや公式アナウンスの把握、ブラウザでの挙動確認	ナビゲーター

3人はそれぞれリモートで、Discordでボイスチャット＋画面共有で進めました。

使用したツールなど

ツール	用途
sshrc	ssh接続時に手元の環境を持ち込むため
tmux	ターミナル分割など
vim	エディタ
top	リソース使用状況確認
dstat	リソース使用状況確認
MySQLTuner	MySQLパラメータ確認
kataribe	アクセスログ解析
go-mysql-query-digest	MySQLスロークエリー解析

昨年から代わり映えしないですね。昨年はVimではなくNeovimを使ったんですが、Neovimを直前に整備していた際にLSP周りの挙動が怪しかったのでVimに切り替えました。

最終構成

サーバ	構成要素	備考
1台目	nginx + app	appはGo実装
2台目	mysql(isupipe)
3台目	powerdns + mysql(isudns)

スコア推移

ISUCON13のスコア推移

中盤で伸び悩んでますね。後半の大きな伸びは1台構成から3台構成に変更したためです。

やったこと

「時間スコア概要」でまとめていきます。

10:49 3386 初回ベンチ

一通りログ解析などができる体制をセットアップした後での初回ベンチです。各種スロークエリーログ出力を仕込んだうえで、MySQLのバイナリログを無効化してます。

# /etc/mysql/mysql.conf.d/mysqld.cnf
disable-log-bin

11:19 7,888 インデックス追加など

取り急ぎMySQLが高負荷だったのでインデックスの追加などをしました。

Goで定番の設定 interpolateParams=true を追加
Goのアプリログ出力を抑制
インデックス追加

use isupipe;
ALTER TABLE icons ADD INDEX (user_id);
ALTER TABLE themes ADD INDEX (user_id);
ALTER TABLE livestreams ADD INDEX (user_id);
ALTER TABLE reservation_slots ADD INDEX (start_at, end_at);
ALTER TABLE livestream_tags ADD INDEX (livestream_id);
ALTER TABLE livecomments ADD INDEX (livestream_id);
ALTER TABLE reactions ADD INDEX (livestream_id);

use isudns;
ALTER TABLE records ADD INDEX (name);

12:02 8,460 NGワード該当チェックをアプリ側で実施するよう変更

インデックス追加の結果、MySQLのスロークエリーの上位にきているのが以下のようなSQLでした。

DELETE FROM livecomments
            WHERE
            id = 66 AND
            livestream_id = 7526 AND
            (SELECT COUNT(*)
            FROM
            (SELECT 'あなたの私物や日常の詳細を知りたいな〜。どこで買い物してるのかな？教えて！' AS text) AS texts
            INNER JOIN
            (SELECT CONCAT('%', '生体情報解析', '%')    AS pattern) AS patterns
            ON texts.text LIKE patterns.pattern) >= 1\G

何やらややこしい実装になっているものの、コメントにNGワードが含まれているかをチェックしてるだけだったので、まずはアプリ側でチェックを行うよう変更しました。スコアは下がったものの、クエリーは高速になったのでとりあえず良しとしました。

12:21 9,220 NGワードの周りのN+1を解消

NGワードが追加されるたびに過去のNGワードも再度コメントチェックをしているのは無駄と判断し、追加されたNGワードについてのみ削除するよう実装変更しました。

   if _, err := tx.ExecContext(ctx, "DELETE FROM livecomments WHERE livestream_id = ? AND comment LIKE ?", livestreamID, fmt.Sprintf("%%%s%%", req.NGWord)); err != nil {
        return echo.NewHTTPError(http.StatusInternalServerError, "failed to delete old livecomments that hit spams: "+err.Error())
    }

12:33 - 昼休憩

12:50 8,012 DNSレコードにTTLを設定

各種DNSレコードにTTLを設定すればリクエスト頻度が下がるのではないか？と考えてTTLを設定しました。しかしスコアは改善しませんでした。悪影響もなさそうなのでとりあえず変更したまま進めます。

13:37 8,968 Deadlock対策

iconsテーブルで発生しているDeadlockのエラーを解消するため、DELETEしてからINSERTしている処理をREPLACEに置き換えました。また、次の布石としてiconsテーブルにカラムを追加してiconのハッシュ値を格納しています。

15:01 10,331 いろいろ変更

この時間までは運営側のデプロイが繰り返し行われておりベンチマークを回せなかったのでまとめていろいろ改修しています。

PowerDNSの負荷抑制のためログ出力を抑制
アプリ側のDBへの最大同時接続数を10から128に引き上げ
getUserStatisticsにおけるランク算出のN+1を解消
If-None-Match リクエストヘッダーに対して304応答を返すよう実装（できてない）

15:15 10,687 tagsのN+1を解消

まだまだMySQLの負荷がボトルネックなのでスロークエリーログの対処をしています。tagsでN+1になっていた処理を解消しました。

15:22 11,540 iconの304応答を実装

If-None-Match リクエストヘッダーは " で括られて届いていることを考慮できてなかったので、これに対処しました。

16:10 14,666 スコアをテーブルに保存

getUserStatisticsのランク算出がスロークエリーの上位だったため、事後集計を行うのではなくreactionとtipを投げた際にスコアを積算するようにしました。scoresテーブルを追加しています。初期状態でも正しいランクを出力できるようinitialize時にscoresテーブルのレコードも追加するように対処しています。

CREATE TABLE `scores` (
  `user_id` BIGINT NOT NULL PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `score` INT NOT NULL,
  INDEX(score, name)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

INSERT INTO scores (user_id, name, score) SELECT u.id, u.name AS name, (SELECT count(*) from livestreams l inner join reactions r on r.livestream_id = l.id WHERE u.id = l.user_id) + (SELECT IFNULL(SUM(l2.tip), 0) FROM livestreams l INNER JOIN livecomments l2 ON l2.livestream_id = l.id WHERE u.id = l.user_id) AS score FROM users u;

16:36 17,337 iconのハッシュ値をアプリ側でキャッシュ

iconのハッシュ値をDBに格納したことでDBへの問い合わせ頻度が多くなっていたため、ハッシュ値はアプリ側でキャッシュするようにしました。アプリ側のキャッシュは sync.Map を利用しています。長年のISUCON経験から、アプリが再起動した場合やinitializeが実行された場合の対処もぬかりなく実施しています。

16:53 21,153 themeをアプリ側でキャッシュ

ハッシュ値と同様にthemeを問い合わせるクエリーを削減すべくthemeをアプリ側でキャッシュしました。こちらも sync.Map で実装しています。

17:07 42,477 MySQLを2台目サーバに移設

まだこのタイミングでもMySQLがボトルネックだったのですが、残り1時間ということもあり複数台構成へと変更し始めました。まずはMySQLを2台目サーバに変更しています。

17:18 42,631 PowerDNSのMySQLを3台目サーバに移設

PowerDNS全体を切り替えることは最初つまづいたので、まずはPowerDNSは1台目サーバで稼働したまま、PowerDNSのバックエンドMySQLだけ3台目サーバに切り替えました。

17:27 46,797 PowerDNSを3台目サーバで稼働するように変更

PowerDNSのレコード初期化とレコード登録の仕組みを丹念に調べながら、PowerDNSを3台目サーバで稼働するように変更しました。レコード初期化とレコード登録は強引ではあるもののSSH経由で実行しています。

-if out, err := exec.Command("pdnsutil", "add-record", "u.isucon.dev", req.Name, "A", "60", powerDNSSubdomainAddress).CombinedOutput(); err != nil {
+if out, err := exec.Command("ssh", "192.168.0.13", "pdnsutil", "add-record", "u.isucon.dev", req.Name, "A", "60", powerDNSSubdomainAddress).CombinedOutput(); err != nil {

-bash ../pdns/init_zone.sh
+ssh 192.168.0.13 bash webapp/pdns/init_zone.sh

17:48 - PowerDNSのバックエンドをLMDBに切り替えようとするも断念

PowerDNSをMySQLからLMDBに切り替えれば高速になるとの情報から、切り替えを試みるものの時間が足りず断念しました。あとちょっとだったけど時間が足りなかった。くやしい。

17:53 49344 スローログなどを無効にして最終ベンチ

スロークエリーログやアクセスログの出力を抑制して最終ベンチを回して最高スコアをたたき出してフィニッシュしました。その他、再起動エラー対策のsystemd設定変更も併せて実施しています。