ONAKA NO OWARI #1

cureseven · 2023-11-23T10:17:16Z

メンバー: @cureseven @mism-mism @pinkumohikan

アプリケーション

ユーザー名：test001
パスワード: test

変更してはいけないファイル

envcheck.serviceに関わるファイル
- /etc/systemd/system/envcheck.service
- /etc/systemd/system/multi-user.target.wants/envcheck.service
/opt/isucon-env-checker 内のファイル、バイナリファイル
- aws-env-isucon-subdomain-address.service に関わるファイル
- /etc/systemd/system/aws-env-isucon-subdomain-address.service
- /etc/systemd/system/multi-user.target.wants/aws-env-isucon-subdomain-address.service
- /opt/aws-env-isucon-subdomain-address.sh
isuadmin ユーザに関わるファイルおよびログイン情報
その他、主催者による追試を妨げる変更（例：サーバー上の isucon 以外のユーザに関する、ユーザ削除や既存の公開鍵の削除、サーバーの再起動の妨害）

競技終了時にポート閉じる

~~19999~~

mism-mism · 2023-11-25T00:28:48Z

mism-mism · 2023-11-25T00:59:19Z

チップ機能の高速化（話していた）

mism-mism · 2023-11-25T01:01:36Z

EC2 インスタンス （c5.large）x 3
EBS（gp3 40GB）x 3
EIP x 3
VPC x 1
VPC サブネット x 1
VPC ルートテーブル x 1
インターネットゲートウェイ x 1
セキュリティグループ x 1
Availability Zone 情報取得に利用する Lambda 関数 x 1
IAM ロール x 2
EC2 インスタンスプロファイル x 1

mism-mism · 2023-11-25T01:02:10Z

3. サーバーへの SSH 接続
スタックの作成完了後、数分程度で SSH 接続できるようになります。サーバーには選手が GitHub に登録している SSH 鍵を利用して、ユーザ名 isucon で SSH 接続ができます。

また、AWSマネージメントコンソールからセッションマネージャーによる接続も可能です。セッションマネージャーで接続した場合は ssm-user というユーザーになるため、sudo su - isucon で isucon ユーザーに切り替えてください。

サーバーの IP アドレスはポータルか、AWS マネジメントコンソールの EC2 インスタンスページから確認できます。

サーバーへ接続後、下記のコマンドを実行することで正しく起動しているか検証が行えます。

$ sudo /opt/isucon-env-checker/envcheck
サーバー起動後にポータルのサーバーリストに表示されない場合は、サーバーに　SSH(またはセッションマネージャ)ログインし、上記 envcheck コマンドを実行し、詳細を確認してください。

mism-mism · 2023-11-25T01:03:32Z

アプリケーションマニュアル要約してくれた

ISUPipeは、リアルタイムのライブ配信を支援するアプリケーションで、ユーザーは配信者（ストリーマー）と視聴者（ビューアー）のどちらにもなれます。特徴として、配信者ごとにカスタマイズ可能なドメイン、リアルタイムコメント、投げ銭機能、統計情報の提供などがあります。また、ユーザーはスパムコメントに対する報告やモデレーションを行うことができ、これにより配信の品質を保つことができます。ISUCOINという独自の通貨システムを使用し、ユーザーのアイコン画像やパスワード管理に関する特別な取り扱いがあります。ISUPipeは、HLS配信サーバーや特定のAPIを通じて運営され、Node.jsの初期実装に関するパッチも提供されています。
``

mism-mism · 2023-11-25T01:05:07Z

HLS配信サーバー (media.xiii.isucon.dev)
フロントエンドでの表示上必要なHLS配信機能が提供されます。
また、配信のサムネイルを取得することが可能です。
本サーバーについてはチューニング対象ではありません。

mism-mism · 2023-11-25T01:05:45Z

ISUPipeのサーバーはこのリクエストに対し、ユーザーのアイコン画像のSHA256値と送信された icon_hash　の値を比較して、一致する場合にはステータスコード 304 でレスポンスすることができます(MAY)。

条件付きGETリクエストでない場合、ステータスコード 304 をレスポンスしてはいけません(MUST NOT)。

アイコン画像が更新され、送信された icon_hash の値と一致しなくなった場合には、ステータスコード200でレスポンスし画像データを送信しなければなりません(MUST)。

APIで返却される icon_hash の値と GET /api/user/:username/icon で配信されるアイコン画像は、ユーザーのアイコン画像が更新された後、2秒以内に変更が反映されている必要があります(MUST)。

mism-mism · 2023-11-25T01:06:23Z

ISUPipeのサーバーでは、ユーザーのパスワードをbcryptでハッシュ化して保存しています。
パスワードのハッシュアルゴリズムを変更したり、bcryptのコストを変更してはいけません(MUST NOT)。

mism-mism · 2023-11-25T01:06:32Z

この「一般エラー」はベンチに問題となり、減点対象ではありません

pinkumohikan · 2023-11-25T01:08:45Z

インフラ構成

スペック

OS: "Ubuntu 22.04.3 LTS"
vCPU: 2

メモリ

               total        used        free      shared  buff/cache   available
Mem:           3.6Gi       743Mi       2.3Gi       0.0Ki       630Mi       2.7Gi
Swap:             0B          0B          0B

ミドルウェア

MySQL: /usr/sbin/mysqld Ver 8.0.35-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))
Nginx: nginx version: nginx/1.18.0 (Ubuntu)
Go: go version go1.21.2 linux/amd64

開いているポート

COMMAND   PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
nginx     628     root    6u  IPv4  17909      0t0  TCP *:http (LISTEN)
nginx     628     root    7u  IPv4  17910      0t0  TCP *:https (LISTEN)
nginx     629 www-data    6u  IPv4  17909      0t0  TCP *:http (LISTEN)
nginx     629 www-data    7u  IPv4  17910      0t0  TCP *:https (LISTEN)
nginx     629 www-data   11u  IPv4  22414      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:41890 (ESTABLISHED)
nginx     629 www-data   12u  IPv4  28353      0t0  TCP localhost:42966->localhost:http-alt (ESTABLISHED)
nginx     629 www-data   13u  IPv4  28374      0t0  TCP localhost:43014->localhost:http-alt (ESTABLISHED)
nginx     629 www-data   14u  IPv4  28128      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55042 (ESTABLISHED)
nginx     629 www-data   15u  IPv4  28299      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:45262 (ESTABLISHED)
nginx     630 www-data    6u  IPv4  17909      0t0  TCP *:http (LISTEN)
nginx     630 www-data    7u  IPv4  17910      0t0  TCP *:https (LISTEN)
nginx     630 www-data    8u  IPv4  28333      0t0  TCP localhost:42936->localhost:http-alt (ESTABLISHED)
nginx     630 www-data    9u  IPv4  24645      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:43502 (ESTABLISHED)
nginx     630 www-data   13u  IPv4  27992      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54854 (ESTABLISHED)
nginx     630 www-data   14u  IPv4  28357      0t0  TCP localhost:42988->localhost:http-alt (ESTABLISHED)
nginx     630 www-data   15u  IPv4  28354      0t0  TCP localhost:42980->localhost:http-alt (ESTABLISHED)
nginx     630 www-data   16u  IPv4  24671      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:43516 (ESTABLISHED)
nginx     630 www-data   17u  IPv4  28292      0t0  TCP localhost:42844->localhost:http-alt (ESTABLISHED)
nginx     630 www-data   18u  IPv4  28091      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54988 (ESTABLISHED)
nginx     630 www-data   19u  IPv4  27978      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54796 (ESTABLISHED)
nginx     630 www-data   20u  IPv4  21717      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:41522 (ESTABLISHED)
nginx     630 www-data   21u  IPv4  22072      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:41814 (ESTABLISHED)
nginx     630 www-data   22u  IPv4  28289      0t0  TCP localhost:42834->localhost:http-alt (ESTABLISHED)
nginx     630 www-data   23u  IPv4  27755      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54570 (ESTABLISHED)
nginx     630 www-data   24u  IPv4  25482      0t0  TCP localhost:26172->localhost:http-alt (ESTABLISHED)
nginx     630 www-data   25u  IPv4  28280      0t0  TCP localhost:42788->localhost:http-alt (ESTABLISHED)
nginx     630 www-data   26u  IPv4  29755      0t0  TCP localhost:43028->localhost:http-alt (ESTABLISHED)
nginx     630 www-data   27u  IPv4  28226      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55138 (ESTABLISHED)
nginx     630 www-data   28u  IPv4  25018      0t0  TCP localhost:29196->localhost:http-alt (ESTABLISHED)
nginx     630 www-data   29u  IPv4  27989      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54838 (ESTABLISHED)
nginx     630 www-data   30u  IPv4  28267      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:45222 (ESTABLISHED)
nginx     630 www-data   31u  IPv4  24756      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:43588 (ESTABLISHED)
nginx     630 www-data   32u  IPv4  28023      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54902 (ESTABLISHED)
nginx     630 www-data   33u  IPv4  28036      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54912 (ESTABLISHED)
nginx     630 www-data   34u  IPv4  28076      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54972 (ESTABLISHED)
nginx     630 www-data   35u  IPv4  28058      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54952 (ESTABLISHED)
nginx     630 www-data   36u  IPv4  28195      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55118 (ESTABLISHED)
nginx     630 www-data   37u  IPv4  28210      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55120 (ESTABLISHED)
nginx     630 www-data   38u  IPv4  28263      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55172 (ESTABLISHED)
nginx     630 www-data   39u  IPv4  28360      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:45304 (ESTABLISHED)
nginx     630 www-data   40u  IPv4  28178      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55072 (ESTABLISHED)
nginx     630 www-data   41u  IPv4  21940      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:41690 (ESTABLISHED)
nginx     630 www-data   42u  IPv4  25454      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54404 (ESTABLISHED)
nginx     630 www-data   43u  IPv4  28278      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:45238 (ESTABLISHED)
nginx     630 www-data   44u  IPv4  27994      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54864 (ESTABLISHED)
nginx     630 www-data   45u  IPv4  28329      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:45282 (ESTABLISHED)
nginx     630 www-data   46u  IPv4  28361      0t0  TCP localhost:42992->localhost:http-alt (ESTABLISHED)
nginx     630 www-data   47u  IPv4  25163      0t0  TCP localhost:30132->localhost:http-alt (ESTABLISHED)
nginx     630 www-data   48u  IPv4  28189      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55102 (ESTABLISHED)
nginx     630 www-data   49u  IPv4  28025      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54908 (ESTABLISHED)
nginx     630 www-data   50u  IPv4  28298      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:45250 (ESTABLISHED)
nginx     630 www-data   51u  IPv4  28004      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54878 (ESTABLISHED)
nginx     630 www-data   52u  IPv4  28364      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:45320 (ESTABLISHED)
nginx     630 www-data   53u  IPv4  27980      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54812 (ESTABLISHED)
nginx     630 www-data   54u  IPv4  19449      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:41386 (ESTABLISHED)
nginx     630 www-data   55u  IPv4  28383      0t0  TCP localhost:43026->localhost:http-alt (ESTABLISHED)
nginx     630 www-data   56u  IPv4  28104      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55000 (ESTABLISHED)
nginx     630 www-data   57u  IPv4  27949      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54766 (ESTABLISHED)
nginx     630 www-data   58u  IPv4  28269      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:45236 (ESTABLISHED)
nginx     630 www-data   59u  IPv4  27879      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54674 (ESTABLISHED)
nginx     630 www-data   60u  IPv4  29754      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:45334 (ESTABLISHED)
nginx     630 www-data   61u  IPv4  28062      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54954 (ESTABLISHED)
nginx     630 www-data   62u  IPv4  28212      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55132 (ESTABLISHED)
nginx     630 www-data   63u  IPv4  28229      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55148 (ESTABLISHED)
nginx     630 www-data   64u  IPv4  27982      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54826 (ESTABLISHED)
nginx     630 www-data   65u  IPv4  25352      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:44236 (ESTABLISHED)
nginx     630 www-data   66u  IPv4  28315      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:45264 (ESTABLISHED)
nginx     630 www-data   67u  IPv4  27782      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54596 (ESTABLISHED)
nginx     630 www-data   68u  IPv4  28045      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54918 (ESTABLISHED)
nginx     630 www-data   69u  IPv4  25477      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54414 (ESTABLISHED)
nginx     630 www-data   70u  IPv4  21858      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:41620 (ESTABLISHED)
nginx     630 www-data   71u  IPv4  20797      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:41548 (ESTABLISHED)
nginx     630 www-data   72u  IPv4  29756      0t0  TCP localhost:43032->localhost:http-alt (ESTABLISHED)
nginx     630 www-data   73u  IPv4  26006      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:43732 (ESTABLISHED)
nginx     630 www-data   75u  IPv4  27952      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54770 (ESTABLISHED)
nginx     630 www-data   76u  IPv4  28109      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55006 (ESTABLISHED)
nginx     630 www-data   77u  IPv4  28048      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54926 (ESTABLISHED)
nginx     630 www-data   78u  IPv4  21850      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:41614 (ESTABLISHED)
nginx     630 www-data   79u  IPv4  28338      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:45290 (ESTABLISHED)
nginx     630 www-data   80u  IPv4  28069      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54966 (ESTABLISHED)
nginx     630 www-data   81u  IPv4  27963      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54780 (ESTABLISHED)
nginx     630 www-data   82u  IPv4  28006      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54894 (ESTABLISHED)
nginx     630 www-data   84u  IPv4  28051      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54938 (ESTABLISHED)
nginx     630 www-data   85u  IPv4  28118      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55026 (ESTABLISHED)
nginx     630 www-data   86u  IPv4  28053      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:54940 (ESTABLISHED)
nginx     630 www-data   87u  IPv4  28167      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55056 (ESTABLISHED)
nginx     630 www-data   88u  IPv4  28113      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55014 (ESTABLISHED)
nginx     630 www-data   89u  IPv4  28232      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55160 (ESTABLISHED)
nginx     630 www-data   90u  IPv4  28182      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55084 (ESTABLISHED)
nginx     630 www-data   91u  IPv4  28325      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:45272 (ESTABLISHED)
nginx     630 www-data   92u  IPv4  28185      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:55098 (ESTABLISHED)
nginx     630 www-data   93u  IPv4  24820      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:43640 (ESTABLISHED)
nginx     630 www-data  103u  IPv4  24889      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:https->ec2-54-95-10-69.ap-northeast-1.compute.amazonaws.com:43756 (ESTABLISHED)
mysqld    834    mysql   32u  IPv4  18541      0t0  TCP localhost:mysql (LISTEN)
mysqld    834    mysql   35u  IPv4  20696      0t0  TCP localhost:mysql->localhost:64626 (ESTABLISHED)
mysqld    834    mysql   36u  IPv4  19809      0t0  TCP localhost:mysql->localhost:22112 (ESTABLISHED)
mysqld    834    mysql   37u  IPv4  18591      0t0  TCP localhost:mysql->localhost:gsidcap (ESTABLISHED)
mysqld    834    mysql   38u  IPv4  18600      0t0  TCP localhost:mysql->localhost:22144 (ESTABLISHED)
mysqld    834    mysql   39u  IPv4  19833      0t0  TCP localhost:mysql->localhost:22150 (ESTABLISHED)
mysqld    834    mysql   57u  IPv4  20581      0t0  TCP localhost:mysql->localhost:64418 (ESTABLISHED)
mysqld    834    mysql   59u  IPv4  20815      0t0  TCP localhost:mysql->localhost:64752 (ESTABLISHED)
mysqld    834    mysql   60u  IPv4  20628      0t0  TCP localhost:mysql->localhost:64472 (ESTABLISHED)
mysqld    834    mysql   61u  IPv4  20667      0t0  TCP localhost:mysql->localhost:64528 (ESTABLISHED)
mysqld    834    mysql   62u  IPv4  20816      0t0  TCP localhost:mysql->localhost:64764 (ESTABLISHED)
mysqld    834    mysql   63u  IPv4  20743      0t0  TCP localhost:mysql->localhost:64680 (ESTABLISHED)
mysqld    834    mysql   65u  IPv4  20796      0t0  TCP localhost:mysql->localhost:64728 (ESTABLISHED)
mysqld    834    mysql   67u  IPv4  20799      0t0  TCP localhost:mysql->localhost:64742 (ESTABLISHED)
mysqld    834    mysql   68u  IPv4  20806      0t0  TCP localhost:mysql->localhost:64750 (ESTABLISHED)
isupipe   880   isucon    3u  IPv6  28293      0t0  TCP localhost:http-alt->localhost:42844 (ESTABLISHED)
isupipe   880   isucon    7u  IPv6  19793      0t0  TCP *:http-alt (LISTEN)
isupipe   880   isucon    8u  IPv6  26516      0t0  TCP localhost:http-alt->localhost:30132 (ESTABLISHED)
isupipe   880   isucon    9u  IPv6  26855      0t0  TCP localhost:http-alt->localhost:26172 (ESTABLISHED)
isupipe   880   isucon   10u  IPv6  28290      0t0  TCP localhost:http-alt->localhost:42834 (ESTABLISHED)
isupipe   880   isucon   11u  IPv4  19397      0t0  TCP localhost:64418->localhost:mysql (ESTABLISHED)
isupipe   880   isucon   12u  IPv6  28334      0t0  TCP localhost:http-alt->localhost:42936 (ESTABLISHED)
isupipe   880   isucon   13u  IPv6  27634      0t0  TCP localhost:http-alt->localhost:42788 (ESTABLISHED)
isupipe   880   isucon   14u  IPv4  21610      0t0  TCP localhost:64626->localhost:mysql (ESTABLISHED)
isupipe   880   isucon   15u  IPv4  19419      0t0  TCP localhost:64472->localhost:mysql (ESTABLISHED)
isupipe   880   isucon   16u  IPv6  28355      0t0  TCP localhost:http-alt->localhost:42980 (ESTABLISHED)
isupipe   880   isucon   17u  IPv4  19453      0t0  TCP localhost:64528->localhost:mysql (ESTABLISHED)
isupipe   880   isucon   18u  IPv4  21730      0t0  TCP localhost:64728->localhost:mysql (ESTABLISHED)
isupipe   880   isucon   19u  IPv4  21696      0t0  TCP localhost:64680->localhost:mysql (ESTABLISHED)
isupipe   880   isucon   20u  IPv6  26270      0t0  TCP localhost:http-alt->localhost:29196 (ESTABLISHED)
isupipe   880   isucon   21u  IPv4  21733      0t0  TCP localhost:64742->localhost:mysql (ESTABLISHED)
isupipe   880   isucon   22u  IPv6  28358      0t0  TCP localhost:http-alt->localhost:42988 (ESTABLISHED)
isupipe   880   isucon   23u  IPv4  21746      0t0  TCP localhost:64750->localhost:mysql (ESTABLISHED)
isupipe   880   isucon   24u  IPv4  21793      0t0  TCP localhost:64752->localhost:mysql (ESTABLISHED)
isupipe   880   isucon   25u  IPv6  28375      0t0  TCP localhost:http-alt->localhost:43014 (ESTABLISHED)
isupipe   880   isucon   26u  IPv6  28362      0t0  TCP localhost:http-alt->localhost:42992 (ESTABLISHED)
isupipe   880   isucon   27u  IPv4  21801      0t0  TCP localhost:64764->localhost:mysql (ESTABLISHED)
isupipe   880   isucon   28u  IPv6  28714      0t0  TCP localhost:http-alt->localhost:43028 (ESTABLISHED)
isupipe   880   isucon   29u  IPv6  28384      0t0  TCP localhost:http-alt->localhost:43026 (ESTABLISHED)
isupipe   880   isucon   30u  IPv6  28716      0t0  TCP localhost:http-alt->localhost:43032 (ESTABLISHED)
isupipe   880   isucon   31u  IPv6  28705      0t0  TCP localhost:http-alt->localhost:42966 (ESTABLISHED)
pdns_serv 881     pdns   10u  IPv4  19807      0t0  TCP localhost:22112->localhost:mysql (ESTABLISHED)
pdns_serv 881     pdns   19u  IPv4  18590      0t0  TCP localhost:gsidcap->localhost:mysql (ESTABLISHED)
pdns_serv 881     pdns   20u  IPv4  18599      0t0  TCP localhost:22144->localhost:mysql (ESTABLISHED)
pdns_serv 881     pdns   21u  IPv4  19832      0t0  TCP localhost:22150->localhost:mysql (ESTABLISHED)
ssm-agent 900     root   11u  IPv4  26865      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:21846->99.77.58.10:https (ESTABLISHED)
ssm-agent 900     root   14u  IPv4  19917      0t0  TCP ip-192-168-0-13.ap-northeast-1.compute.internal:45070->99.77.58.55:https (ESTABLISHED)

起動中のサービス

  UNIT                                           LOAD   ACTIVE SUB     DESCRIPTION
  acpid.service                                  loaded active running ACPI event daemon
  apparmor.service                               loaded active exited  Load AppArmor profiles
  apport.service                                 loaded active exited  LSB: automatic crash report generation
  aws-env-isucon-subdomain-address.service       loaded active exited  run aws-env-isucon-subdomain-address once
  blk-availability.service                       loaded active exited  Availability of block devices
  chrony.service                                 loaded active running chrony, an NTP client/server
  cloud-config.service                           loaded active exited  Apply the settings specified in cloud-config
  cloud-final.service                            loaded active exited  Execute cloud user/final scripts
  cloud-init-local.service                       loaded active exited  Initial cloud-init job (pre-networking)
  cloud-init.service                             loaded active exited  Initial cloud-init job (metadata service crawler)
  console-setup.service                          loaded active exited  Set console font and keymap
  cron.service                                   loaded active running Regular background program processing daemon
  dbus.service                                   loaded active running D-Bus System Message Bus
  envcheck.service                               loaded active exited  Check Environments for ISUCON13 Contestants
  finalrd.service                                loaded active exited  Create final runtime dir for shutdown pivot root
  [email protected]                             loaded active running Getty on tty1
  irqbalance.service                             loaded active running irqbalance daemon
  isupipe-go.service                             loaded active running isupipe-go
  keyboard-setup.service                         loaded active exited  Set the console keyboard layout
  kmod-static-nodes.service                      loaded active exited  Create List of Static Device Nodes
  lvm2-monitor.service                           loaded active exited  Monitoring of LVM2 mirrors, snapshots etc. using dmeventd or progress polling
  multipathd.service                             loaded active running Device-Mapper Multipath Device Controller
  mysql.service                                  loaded active running MySQL Community Server
  networkd-dispatcher.service                    loaded active running Dispatcher daemon for systemd-networkd
  nginx.service                                  loaded active running A high performance web server and a reverse proxy server
  pdns.service                                   loaded active running PowerDNS Authoritative Server
  plymouth-quit-wait.service                     loaded active exited  Hold until boot process finishes up
  plymouth-quit.service                          loaded active exited  Terminate Plymouth Boot Screen
  plymouth-read-write.service                    loaded active exited  Tell Plymouth To Write Out Runtime Data
  polkit.service                                 loaded active running Authorization Manager
  rsyslog.service                                loaded active running System Logging Service
  [email protected]                     loaded active running Serial Getty on ttyS0
  setvtrgb.service                               loaded active exited  Set console scheme
  snap.amazon-ssm-agent.amazon-ssm-agent.service loaded active running Service for snap application amazon-ssm-agent.amazon-ssm-agent
  snapd.apparmor.service                         loaded active exited  Load AppArmor profiles managed internally by snapd
  snapd.seeded.service                           loaded active exited  Wait until snapd is fully seeded
  snapd.service                                  loaded active running Snap Daemon
  ssh.service                                    loaded active running OpenBSD Secure Shell server
  systemd-binfmt.service                         loaded active exited  Set Up Additional Binary Formats
  systemd-fsck-root.service                      loaded active exited  File System Check on Root Device
  systemd-fsck@dev-disk-by\x2dlabel-UEFI.service loaded active exited  File System Check on /dev/disk/by-label/UEFI
  systemd-journal-flush.service                  loaded active exited  Flush Journal to Persistent Storage
  systemd-journald.service                       loaded active running Journal Service
  systemd-logind.service                         loaded active running User Login Management
  systemd-machine-id-commit.service              loaded active exited  Commit a transient machine-id on disk
  systemd-modules-load.service                   loaded active exited  Load Kernel Modules
  systemd-networkd-wait-online.service           loaded active exited  Wait for Network to be Configured
  systemd-networkd.service                       loaded active running Network Configuration
  systemd-random-seed.service                    loaded active exited  Load/Save Random Seed
  systemd-remount-fs.service                     loaded active exited  Remount Root and Kernel File Systems
  systemd-resolved.service                       loaded active running Network Name Resolution
  systemd-sysctl.service                         loaded active exited  Apply Kernel Variables
  systemd-sysusers.service                       loaded active exited  Create System Users
  systemd-tmpfiles-setup-dev.service             loaded active exited  Create Static Device Nodes in /dev
  systemd-tmpfiles-setup.service                 loaded active exited  Create Volatile Files and Directories
  systemd-udev-trigger.service                   loaded active exited  Coldplug All udev Devices
  systemd-udevd.service                          loaded active running Rule-based Manager for Device Events and Files
  systemd-update-utmp.service                    loaded active exited  Record System Boot/Shutdown in UTMP
  systemd-user-sessions.service                  loaded active exited  Permit User Sessions
  ufw.service                                    loaded active exited  Uncomplicated firewall
  unattended-upgrades.service                    loaded active running Unattended Upgrades Shutdown
  [email protected]                  loaded active exited  User Runtime Directory /run/user/1100
  [email protected]                              loaded active running User Manager for UID 1100

LOAD   = Reflects whether the unit definition was properly loaded.
ACTIVE = The high-level unit activation state, i.e. generalization of SUB.
SUB    = The low-level unit activation state, values depend on unit type.
63 loaded units listed.
To show all installed unit files use 'systemctl list-unit-files'.

mism-mism · 2023-11-25T01:09:17Z

DNS 水責め攻撃について
負荷走行中、DNSサーバに対していわゆる「DNS水責め攻撃」が行われます。

DNS水責め攻撃はランダムなサブドメインを生成し、大量のアクセスを行うことでDNSサーバの応答に影響を与えることを目的とする攻撃手法です。

ベンチマーカーはDNS水責め攻撃およびスクレイピングを行い、名前解決ができると HTTPS によりアクセスを試みる動作を行います。失敗(NXDOMAINや応答なし)では来ません。

pinkumohikan · 2023-11-25T01:10:18Z

初期スコア

 https://portal.isucon.net/contest/jobs/23/

2023-11-25T01:05:30.764Z	info	isupipe-benchmarker	SSL接続が有効になっています
2023-11-25T01:05:30.764Z	info	isupipe-benchmarker	静的ファイルチェックを行います
2023-11-25T01:05:30.764Z	info	isupipe-benchmarker	静的ファイルチェックが完了しました
2023-11-25T01:05:30.764Z	info	isupipe-benchmarker	webappの初期化を行います
2023-11-25T01:05:46.097Z	info	isupipe-benchmarker	ベンチマーク走行前のデータ整合性チェックを行います
2023-11-25T01:06:18.166Z	info	isupipe-benchmarker	整合性チェックが成功しました
2023-11-25T01:06:18.166Z	info	isupipe-benchmarker	ベンチマーク走行を開始します
2023-11-25T01:07:18.167Z	info	isupipe-benchmarker	ベンチマーク走行を停止します
2023-11-25T01:07:18.168Z	warn	isupipe-benchmarker	ライブコメントを配信に投稿できないため、視聴者が離脱します	{"viewer": "haruka191", "livestream_id": 7537, "error": "Post \"https://sayuriyamazaki0.u.isucon.dev:443/api/livestream/7537/livecomment\": context canceled: ベンチマーク走行が継続できないエラーが発生しました"}
2023-11-25T01:07:18.168Z	warn	isupipe-benchmarker	ライブコメントを配信に投稿できないため、視聴者が離脱します	{"viewer": "zsuzuki0", "livestream_id": 7529, "error": "Post \"https://shayashi0.u.isucon.dev:443/api/livestream/7529/livecomment\": context canceled: ベンチマーク走行が継続できないエラーが発生しました"}
2023-11-25T01:07:18.168Z	warn	isupipe-benchmarker	ライブコメントを配信に投稿できないため、視聴者が離脱します	{"viewer": "pyamamoto1", "livestream_id": 7550, "error": "Post \"https://yoichiendo1.u.isucon.dev:443/api/livestream/7550/livecomment\": context canceled: ベンチマーク走行が継続できないエラーが発生しました"}
2023-11-25T01:07:18.168Z	warn	isupipe-benchmarker	ライブコメントを配信に投稿できないため、視聴者が離脱します	{"viewer": "vshimizu1", "livestream_id": 7553, "error": "Post \"https://utakahashi0.u.isucon.dev:443/api/livestream/7553/livecomment\": context canceled: ベンチマーク走行が継続できないエラーが発生しました"}
2023-11-25T01:07:19.141Z	info	isupipe-benchmarker	ベンチマーク走行終了
2023-11-25T01:07:19.141Z	info	isupipe-benchmarker	最終チェックを実施します
2023-11-25T01:07:19.141Z	info	isupipe-benchmarker	最終チェックが成功しました
2023-11-25T01:07:19.141Z	info	isupipe-benchmarker	重複排除したログを以下に出力します
2023-11-25T01:07:19.141Z	info	isupipe-benchmarker	配信を最後まで視聴できた視聴者数	{"viewers": 13}

[一般エラー] POST /api/livestream/7528/livecomment/1034/report へのリクエストに対して、期待されたHTTPステータスコードが確認できませんでした (expected:201, actual:404)
[一般エラー] POST /api/livestream/7528/livecomment/1035/report へのリクエストに対して、期待されたHTTPステータスコードが確認できませんでした (expected:201, actual:404)
[一般エラー] POST /api/livestream/7531/livecomment/1060/report へのリクエストに対して、期待されたHTTPステータスコードが確認できませんでした (expected:201, actual:404)
canceled
[リクエストタイムアウト] GET /api/user/sayuriyamazaki0/statistics: Get "https://pipe.u.isucon.dev:443/api/user/sayuriyamazaki0/statistics": context deadline exceeded (Client.Timeout exceeded while awaiting headers): タイムアウトによりリクエスト失敗
[リクエストタイムアウト] GET /api/user/yoichiendo1/statistics: Get "https://pipe.u.isucon.dev:443/api/user/yoichiendo1/statistics": context deadline exceeded (Client.Timeout exceeded while awaiting headers): タイムアウトによりリクエスト失敗
[リクエストタイムアウト] GET /api/user/kobayashihanako0/statistics: Get "https://pipe.u.isucon.dev:443/api/user/kobayashihanako0/statistics": context deadline exceeded (Client.Timeout exceeded while awaiting headers): タイムアウトによりリクエスト失敗
[リクエストタイムアウト] POST /api/livestream/reservation: Post "https://jtakahashi0.u.isucon.dev:443/api/livestream/reservation": read udp 10.0.15.11:41344->52.199.67.72:53: i/o timeout: タイムアウトによりリクエスト失敗
名前解決成功数 2291
売上: 3466

mism-mism · 2023-11-25T01:21:53Z

isudns

isupipe

mism-mism · 2023-11-25T01:23:15Z

スロークエリとnginxのログ設定した点数

mism-mism · 2023-11-25T01:24:23Z

テーブルのレコード（ベンチ前）

TABLE_NAME	TABLE_ROWS	total_storage_mb	avg_storage_b	total_data_mb	total_index_mb
icons	186	14	80951	14	0
ng_words	14526	3	218	1	1
livestreams	7442	2	354	2	0
livecomments	1228	0	146	0	0
users	1147	0	442	0	0
themes	1194	0	68	0	0
tags	103	0	318	0	0
reservation_slots	8593	0	59	0	0
reactions	1199	0	95	0	0
livestream_viewers_history	9	0	1820	0	0
livestream_tags	10972	0	49	0	0
comments	0	0	null	0	0
livecomment_reports	45	0	364	0	0
tsigkeys	0	0	null	0	0
supermasters	0	0	null	0	0
records	1474	0	166	0	0
domains	1	0	16384	0	0
domainmetadata	0	0	null	0	0
cryptokeys	0	0	null	0	0

pinkumohikan · 2023-11-25T01:28:36Z

リソース使用状況 (s1)

pinkumohikan · 2023-11-25T02:00:05Z

サーバ分離

スコア

https://portal.isucon.net/contest/jobs/1710/

現在のサーバ構成

s1: app, dns, dnsのdb
s2: free
s3: アプリのdb

s1

s3

mism-mism · 2023-11-25T02:00:22Z

kataribe

Top 20 Sort By Total
Count    Total     Mean   Stddev     Min   P50.0   P90.0   P95.0   P99.0     Max   2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
 2454  174.162   0.0710   0.0925   0.001   0.035   0.178   0.257   0.436   0.922  2451    0    3    0   141429076          0      57632     164992  GET /api/user/*/icon
  300  118.029   0.3934   0.3114   0.001   0.323   0.837   0.978   1.314   1.481   297    0    3    0     3616306          0      12054      39216  GET /api/livestream/<num>/livecomment
  331  110.479   0.3338   0.3199   0.001   0.243   0.816   0.963   1.203   1.399   330    0    1    0     3149906          0       9516      37506  GET /api/livestream/<num>/reaction
   30   85.966   2.8655   1.8767   0.287   1.911   5.441   7.142   9.112   9.112    29    0    1    0         667          0         22         23  POST /api/livestream/<num>/moderate
    6   85.682  14.2803   8.0895   2.838  20.000  20.001  20.001  20.001  20.001     2    0    4    0         272          0         45        139  GET /api/user/*/statistics
   43   78.599   1.8279   0.4955   0.182   1.919   2.326   2.349   2.637   2.637    42    0    1    0     2442522          0      56802      63780  GET /api/livestream/search?limit=<num> HTTP/1.1
  273   56.434   0.2067   0.1472   0.000   0.164   0.381   0.513   0.792   1.131   271    0    1    1      126981         67        465        504  POST /api/register HTTP/1.1
  330   50.286   0.1524   0.0960   0.005   0.125   0.263   0.329   0.541   0.746   324    0    6    0      631073          0       1912       2725  POST /api/livestream/<num>/livecomment
  135   43.899   0.3252   0.2105   0.000   0.363   0.545   0.666   0.840   0.925   131    0    4    0      149718          0       1109       1456  POST /api/livestream/reservation HTTP/1.1
  282   30.607   0.1085   0.1056   0.005   0.081   0.209   0.303   0.493   1.132   281    0    1    0      515488          0       1827       2199  POST /api/livestream/<num>/reaction
  274   29.246   0.1067   0.1328   0.006   0.059   0.244   0.351   0.776   0.997   274    0    0    0        4276         14         15         16  POST /api/icon HTTP/1.1
   14   24.149   1.7249   1.7663   0.364   0.491   4.690   4.865   4.865   4.865    14    0    0    0     1605640      95356     114688     130979  GET /api/livestream/search?tag=<tag>
  279   19.065   0.0683   0.1113   0.001   0.029   0.193   0.273   0.731   0.772   276    0    3    0         128          0          0         64  POST /api/login HTTP/1.1
    2   13.340   6.6700   0.0130   6.657   6.683   6.683   6.683   6.683   6.683     2    0    0    0         202        100        101        102  GET /api/livestream/<num>/statistics
  114    9.671   0.0848   0.1044   0.001   0.052   0.218   0.267   0.525   0.633   114    0    0    0      694590       6089       6092       6095  GET /api/tag HTTP/1.1
   73    7.375   0.1010   0.1188   0.005   0.075   0.224   0.324   0.573   0.573    72    0    1    0      128390          0       1758      22501  GET /api/livestream HTTP/1.1
   33    5.766   0.1747   0.1310   0.008   0.134   0.322   0.538   0.636   0.636    33    0    0    0       85133       2309       2579       2682  POST /api/livestream/<num>/livecomment/<num>/report
   48    5.367   0.1118   0.2164   0.001   0.031   0.389   0.517   1.037   1.037    48    0    0    0       87814          3       1829      41032  GET /api/livestream/<num>/report
   32    3.399   0.1062   0.1824   0.000   0.052   0.254   0.458   0.959   0.959    32    0    0    0           0          0          0          0  POST /api/livestream/<num>/enter
   36    2.942   0.0817   0.0702   0.005   0.063   0.159   0.189   0.408   0.408    36    0    0    0        1061          5         29        256  GET /api/livestream/<num>/ngwords

mism-mism · 2023-11-25T02:00:49Z

CURD

OBJECT_NAME	COUNT_FETCH	COUNT_INSERT	COUNT_UPDATE	COUNT_DELETE
livestream_tags	89663043	11543	0	0
livecomments	20614150	1312	0	5
reactions	20561396	1282	0	0
themes	13923826	1271	0	0
reservation_slots	12832066	8759	1201	0
livestreams	10877114	7626	0	0
ng_words	5423842	14367	0	0
icons	1776910	274	0	11
tags	43086	103	0	0
users	30887	1272	0	0
records	7370	0	0	0
livecomment_reports	873	48	0	0
livestream_viewers_history	208	32	0	22

mism-mism · 2023-11-25T02:02:16Z

S1

top-slow.log

S3

top-slow-3.log

mism-mism · 2023-11-25T02:06:24Z

語り部のトップの直し方方針

当日マニュアルにこうある

アイコン画像配信についての特記事項
ISUPipeのクライアント(ベンチマーカーを含む)は、/api/user/:username/iconというURLに対してGETリクエストを行い、ユーザーのアイコン画像を取得します。

このリクエストでは、他のAPIで返されるユーザー情報に含まれる icon_hash という値を利用し、HTTPリクエストヘッダを次のように付与して条件付きGETリクエストを行うことがあります。

GET /api/user/:username/icon HTTP/1.1
Host: pipe.u.isucon.dev
If-None-Match: "{icon_hashの値}"
(他のヘッダは省略)
ISUPipeのサーバーはこのリクエストに対し、ユーザーのアイコン画像のSHA256値と送信された icon_hash　の値を比較して、一致する場合にはステータスコード 304 でレスポンスすることができます(MAY)。

条件付きGETリクエストでない場合、ステータスコード 304 をレスポンスしてはいけません(MUST NOT)。

アイコン画像が更新され、送信された icon_hash の値と一致しなくなった場合には、ステータスコード200でレスポンスし画像データを送信しなければなりません(MUST)。

APIで返却される icon_hash の値と GET /api/user/:username/icon で配信されるアイコン画像は、ユーザーのアイコン画像が更新された後、2秒以内に変更が反映されている必要があります(MUST)。

Iconのハッシュを見て、304を返すようにする
アイコンが更新されたらハッシュ値を変更するようにする

pinkumohikan · 2023-11-25T02:16:44Z

DNSのslow queryチューニング

mysql> explain SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and name='puqlyoeaae'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: records
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> ALTER TABLE records ADD INDEX (disabled, name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='jguugda6etvoxuyoc820.u.isucon.dev'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: records
   partitions: NULL
         type: ref
possible_keys: disabled
          key: disabled
      key_len: 260
          ref: const,const
         rows: 1
     filtered: 25.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> ALTER TABLE records ADD INDEX (disabled, type, name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

https://portal.isucon.net/contest/jobs/2427/

pinkumohikan · 2023-11-25T02:19:25Z

DNSはクエリチューニングでどうこう出来る状態じゃ無くなってきた

# 50.3s user time, 240ms system time, 38.17M rss, 46.06M vsz
# Current date: Sat Nov 25 11:17:59 2023
# Hostname: tuna.pinkumohikan.com
# Files: mysql-slow.log
# Overall: 481.36k total, 20 unique, 3.49k QPS, 0.85x concurrency ________
# Time range: 2023-11-25T02:12:57 to 2023-11-25T02:15:15
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           117s     1us    22ms   243us   690us   399us   144us
# Lock time          619ms       0     5ms     1us     1us    19us     1us
# Rows sent         28.96k       0       9    0.06    0.99    0.24       0
# Rows examine     401.35k       0   1.51k    0.85    0.99   32.67       0
# Query size        37.75M       5     286   82.24  151.03   48.91  112.70

# Profile
# Rank Query ID                            Response time Calls  R/Call V/M
# ==== =================================== ============= ====== ====== ===
#    1 0x22279D81D51006139E0C76405B54C261  35.2390 30.1% 105972 0.0003  0.00 SELECT domains domainmetadata
#    2 0x42EF7D7D98FBCC9723BF896EBFC51D24  31.8640 27.2%  82528 0.0004  0.00 SELECT records
#    3 0x8F7679D452333ED3C7D60D22131CEFD4  23.6460 20.2% 230566 0.0001  0.00 ADMIN RESET STMT
#    4 0x3D83BC87F3B3A00D571FFC8104A6E50C  23.2827 19.9%  54958 0.0004  0.00 SELECT records
# MISC 0xMISC                               3.1837  2.7%   7331 0.0004   0.0 <16 ITEMS>

# Query 1: 1.15k QPS, 0.38x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 86533302
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T02:13:42 to 2023-11-25T02:15:14
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         22  105972
# Exec time     30     35s    79us    18ms   332us   799us   395us   214us
# Lock time     44   276ms       0     4ms     2us     1us    24us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    30  11.52M     114     114     114     114       0     114
# String:
# Databases    isudns
# Hosts        127.0.0.1
# Users        isudns
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  ##
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isudns` LIKE 'domains'\G
#    SHOW CREATE TABLE `isudns`.`domains`\G
#    SHOW TABLE STATUS FROM `isudns` LIKE 'domainmetadata'\G
#    SHOW CREATE TABLE `isudns`.`domainmetadata`\G
# EXPLAIN /*!50100 PARTITIONS*/
select kind,content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='u.isucon.dev'\G

# Query 2: 897.04 QPS, 0.35x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 111788234
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T02:13:42 to 2023-11-25T02:15:14
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         17   82528
# Exec time     27     32s    94us    17ms   386us   881us   416us   247us
# Lock time     27   173ms       0   937us     2us     1us    14us     1us
# Rows sent      6   1.82k       0       1    0.02       0    0.15       0
# Rows examine   0   1.82k       0       1    0.02       0    0.15       0
# Query size    29  11.28M     129     223  143.31  158.58   12.62  143.84
# String:
# Databases    isudns
# Hosts        127.0.0.1
# Users        isudns
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  ##
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isudns` LIKE 'records'\G
#    SHOW CREATE TABLE `isudns`.`records`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and name='k1uk8nse0rwsni55f8dz0.u.isucon.dev' and domain_id=2\G

# Query 3: 1.68k QPS, 0.17x concurrency, ID 0x8F7679D452333ED3C7D60D22131CEFD4 at byte 32174949
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T02:12:57 to 2023-11-25T02:15:14
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         47  230566
# Exec time     20     24s     9us    22ms   102us   316us   218us    49us
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    19   7.26M      33      33      33      33       0      33
# String:
# Databases    isudns
# Hosts        127.0.0.1
# Users        isudns
# Query_time distribution
#   1us  #
#  10us  ################################################################
# 100us  ######################
#   1ms  #
#  10ms  #
# 100ms
#    1s
#  10s+
administrator command: Reset stmt\G

# Query 4: 597.37 QPS, 0.25x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 17807280
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T02:13:42 to 2023-11-25T02:15:14
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         11   54958
# Exec time     19     23s    93us    14ms   423us   972us   468us   287us
# Lock time     25   155ms       0     5ms     2us     1us    31us     1us
# Rows sent     91  26.59k       0       1    0.50    0.99    0.50       0
# Rows examine   6  27.50k       0       1    0.51    0.99    0.50    0.99
# Query size    19   7.21M     126     222  137.52  151.03   12.12  130.47
# String:
# Databases    isudns
# Hosts        127.0.0.1
# Users        isudns
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  ###
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isudns` LIKE 'records'\G
#    SHOW CREATE TABLE `isudns`.`records`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='kq8ttpq1h4if0.u.isucon.dev'\G

mism-mism · 2023-11-25T02:22:14Z

SELECT * FROM livestream_tags WHERE livestream_id = 7526

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	livestream_tags	null	ALL	null	null	null	null	11714	10	Using where

create index livestream_tags_livestream_id_index
on livestream_tags (livestream_id);

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	livestream_tags	null	ref	livestream_tags_livestream_id_index	livestream_tags_livestream_id_index	8	const	5	100	null

SELECT * FROM themes WHERE user_id = 493

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	themes	null	ALL	null	null	null	null	1111	10	Using where

create index themes_user_id_index
on themes (user_id);

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	themes	null	ref	themes_user_id_index	themes_user_id_index	8	const	1	100	null

SELECT image FROM icons WHERE user_id = 1015

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	icons	null	ALL	null	null	null	null	259	10	Using where

create index icons_user_id_index
on icons (user_id);

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	icons	null	ref	icons_user_id_index	icons_user_id_index	8	const	1	100	null

mism-mism · 2023-11-25T02:28:25Z

index貼った結果

pinkumohikan · 2023-11-25T02:43:42Z

PowerDNSのチューニングマニュアル
https://doc.powerdns.com/authoritative/performance.html

mism-mism · 2023-11-25T02:55:00Z

pprofで見る感じアイコンが重いので、修正していきます

cureseven · 2023-11-25T03:09:38Z

#2 で10,178点
#3 で12,320点

今のkataribe

Top 20 Sort By Total
Count    Total     Mean   Stddev     Min   P50.0   P90.0   P95.0   P99.0     Max   2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
   13  215.670  16.5900   6.2031   2.619  20.000  20.001  20.001  20.001  20.001     2    0   11    0         273          0         21        140  GET /api/user/*/statistics
   37  108.458   2.9313   1.3866   0.171   2.428   4.889   5.239   7.111   7.111    34    0    3    0         782          0         21         23  POST /api/livestream/<num>/moderate
  718  102.421   0.1426   0.1099   0.001   0.122   0.304   0.349   0.404   0.486   716    0    2    0     9148566          0      12741      48943  GET /api/livestream/<num>/livecomment
  753   97.341   0.1293   0.1080   0.000   0.110   0.286   0.339   0.410   0.511   752    0    1    0     8602955          0      11424      47430  GET /api/livestream/<num>/reaction
  484   94.976   0.1962   0.0587   0.000   0.194   0.276   0.290   0.326   0.358   480    0    3    1      225479          0        465        522  POST /api/register HTTP/1.1
   94   64.232   0.6833   0.1591   0.039   0.706   0.843   0.873   0.897   0.897    93    0    1    0     5745981          0      61127      63991  GET /api/livestream/search?limit=<num> HTTP/1.1
  205   53.673   0.2618   0.1612   0.000   0.259   0.486   0.558   0.646   0.716   201    0    4    0      232908          0       1136       1450  POST /api/livestream/reservation HTTP/1.1
 5961   52.665   0.0088   0.0063   0.000   0.007   0.017   0.022   0.031   0.082  5961    0    0    0   357698606       6969      60006     171848  GET /api/user/*/icon
    5   50.014  10.0028   4.1550   5.162  11.923  15.617  15.617  15.617  15.617     2    0    3    0         202          0         40        102  GET /api/livestream/<num>/statistics
  697   43.453   0.0623   0.0167   0.005   0.062   0.083   0.090   0.104   0.127   693    0    4    0     1342532          0       1926       2731  POST /api/livestream/<num>/livecomment
  665   18.321   0.0276   0.0132   0.001   0.026   0.044   0.053   0.068   0.080   665    0    0    0     1234272       1552       1856       2190  POST /api/livestream/<num>/reaction
   20   15.232   0.7616   0.6024   0.097   1.121   1.505   1.568   1.568   1.568    20    0    0    0     2284581      94141     114229     142254  GET /api/livestream/search?tag=<tag>
  495    9.639   0.0195   0.0098   0.004   0.018   0.032   0.037   0.058   0.075   488    0    0    7        8779         14         17        154  POST /api/icon HTTP/1.1
   88    4.416   0.0502   0.0306   0.007   0.047   0.097   0.113   0.134   0.134    88    0    0    0      186183          3       2115      22401  GET /api/livestream HTTP/1.1
  488    3.917   0.0080   0.0056   0.001   0.007   0.015   0.019   0.029   0.047   486    0    2    0         128          0          0         64  POST /api/login HTTP/1.1
   81    2.662   0.0329   0.0122   0.005   0.029   0.050   0.058   0.064   0.064    81    0    0    0        1814          5         22        268  GET /api/livestream/<num>/ngwords
  190    1.832   0.0096   0.0069   0.000   0.007   0.020   0.025   0.030   0.035   190    0    0    0     1157978       6089       6094       6095  GET /api/tag HTTP/1.1
    1    1.819   1.8190   0.0000   1.819   1.819   1.819   1.819   1.819   1.819     1    0    0    0          27         27         27         27  POST /api/initialize HTTP/1.1
   50    1.805   0.0361   0.0209   0.003   0.038   0.068   0.072   0.082   0.082    42    0    8    0      109523          0       2190       2719  POST /api/livestream/<num>/livecomment/<num>/report
  129    1.634   0.0127   0.0167   0.001   0.008   0.023   0.041   0.084   0.119   128    0    0    1       48346          3        374      11402  GET /api/livestream/<num>/report

cureseven · 2023-11-25T03:40:55Z

#5 マージで
今6い
14,927てん

Top 20 Sort By Total
Count    Total    Mean  Stddev     Min   P50.0   P90.0   P95.0   P99.0     Max   2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
  855  163.850  0.1916  0.1568   0.001   0.163   0.418   0.491   0.634   0.775   852    0    3    0    12045443          0      14088      63662  GET /api/livestream/<num>/livecomment
  889  160.444  0.1805  0.1550   0.000   0.147   0.410   0.467   0.612   0.770   886    0    3    0    11363390          0      12782      61321  GET /api/livestream/<num>/reaction
   38  120.007  3.1581  1.4511   0.174   3.067   3.571   6.421   9.679   9.679    37    0    1    0         851          0         22         23  POST /api/livestream/<num>/moderate
  410   84.996  0.2073  0.0613   0.000   0.209   0.278   0.305   0.348   0.401   407    0    2    1      191361          0        466        516  POST /api/register HTTP/1.1
 7233   83.169  0.0115  0.0077   0.000   0.010   0.022   0.027   0.038   0.074  7231    0    2    0   408891056          0      56531     171847  GET /api/user/*/icon
   35   69.836  1.9953  5.5834   0.000   0.001   5.248  20.000  20.000  20.000     2    0   33    0        2482          0         70        102  GET /api/livestream/<num>/statistics
   77   59.497  0.7727  0.2512   0.021   0.823   1.010   1.038   1.115   1.115    74    0    3    0     4428601          0      57514      63662  GET /api/livestream/search?limit=<num> HTTP/1.1
  807   49.848  0.0618  0.0162   0.005   0.062   0.082   0.088   0.101   0.121   805    0    2    0     1557104         57       1929       2668  POST /api/livestream/<num>/livecomment
  259   45.062  0.1740  0.0873   0.001   0.177   0.281   0.314   0.387   0.465   256    0    3    0      297353         62       1148       1475  POST /api/livestream/reservation HTTP/1.1
  788   27.349  0.0347  0.0143   0.002   0.033   0.053   0.060   0.071   0.106   788    0    0    0     1461768       1552       1855       2221  POST /api/livestream/<num>/reaction
   22   20.780  0.9445  0.7038   0.106   1.179   1.686   1.712   1.729   1.729    22    0    0    0     2670893     108441     121404     142098  GET /api/livestream/search?tag=<tag>
  424    9.531  0.0225  0.0099   0.007   0.021   0.036   0.042   0.054   0.064   417    0    0    7        7648         14         18        154  POST /api/icon HTTP/1.1
  415    4.145  0.0100  0.0061   0.001   0.009   0.018   0.022   0.029   0.036   413    0    2    0         128          0          0         64  POST /api/login HTTP/1.1
  247    3.018  0.0122  0.0082   0.001   0.010   0.023   0.026   0.043   0.056   247    0    0    0     1505459       6089       6094       6095  GET /api/tag HTTP/1.1
   76    2.926  0.0385  0.0373   0.005   0.026   0.084   0.122   0.186   0.186    76    0    0    0      161918          3       2130      22624  GET /api/livestream HTTP/1.1
   49    2.246  0.0458  0.0237   0.003   0.046   0.081   0.088   0.099   0.099    42    0    7    0      108498         57       2214       2659  POST /api/livestream/<num>/livecomment/<num>/report
    1    1.942  1.9420  0.0000   1.942   1.942   1.942   1.942   1.942   1.942     1    0    0    0          27         27         27         27  POST /api/initialize HTTP/1.1
   87    1.839  0.0211  0.0307   0.001   0.012   0.038   0.087   0.192   0.192    87    0    0    0       55049          3        632       8209  GET /api/livestream/<num>/report
   14    1.637  0.1169  0.0447   0.026   0.124   0.175   0.180   0.180   0.180    14    0    0    0        1957        133        139        147  GET /api/user/*/statistics
   59    1.501  0.0254  0.0108   0.005   0.024   0.042   0.054   0.056   0.056    59    0    0    0         295          5          5          5  GET /api/livestream/<num>/ngwords

cureseven · 2023-11-25T04:02:40Z

最新ではタイムアウトのログは出なくなっている

cureseven · 2023-11-25T04:58:12Z

pprof.samples.1215.9ecb37d0.pb.gz

mism-mism · 2023-11-25T05:03:10Z

pinkumohikan · 2023-11-25T07:25:09Z

PowerDNSのバックエンドをMySQLからLMDBへ変更

references

https://doc.powerdns.com/authoritative/backends/lmdb.html
https://speakerdeck.com/kazeburo/dns-water-torture-attack-monitoring-and-slo

before

after

score

https://portal.isucon.net/contest/jobs/24763/

DNSとしてさばける量は増えたが、スコアとしては上がっていない

cureseven · 2023-11-25T07:28:19Z

Query 2
16,300

mysql> explain SELECT slot FROM reservation_slots WHERE start_at = 1702728000 AND end_at = 1702731600;
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | reservation_slots | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 8593 |     1.00 | Using where |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

CREATE INDEX idx_reservation_slots_start_at_end_at ON reservation_slots (start_at, end_at);

mysql> explain SELECT slot FROM reservation_slots WHERE start_at = 1702728000 AND end_at = 1702731600;
+----+-------------+-------------------+------------+------+---------------------------------------+---------------------------------------+---------+-------------+------+----------+-------+
| id | select_type | table             | partitions | type | possible_keys                         | key                                   | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------------------------------+---------------------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | reservation_slots | NULL       | ref  | idx_reservation_slots_start_at_end_at | idx_reservation_slots_start_at_end_at | 16      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------------------+------------+------+---------------------------------------+---------------------------------------+---------+-------------+------+----------+-------+

Top 20 Sort By Total
Count    Total     Mean   Stddev     Min   P50.0   P90.0   P95.0   P99.0     Max   2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
  972  168.193   0.1730   0.1376   0.001   0.145   0.371   0.429   0.592   0.685   968    0    4    0    13131143          0      13509      62813  GET /api/livestream/<num>/livecomment
 1004  161.117   0.1605   0.1381   0.000   0.136   0.359   0.417   0.553   0.718  1001    0    3    0    11931475          0      11883      60272  GET /api/livestream/<num>/reaction
  201  156.844   0.7803   0.1769   0.040   0.791   0.983   1.016   1.093   1.105   198    0    3    0    12356331          0      61474      63851  GET /api/livestream/search?limit=<num> HTTP/1.1
 8066   80.210   0.0099   0.0060   0.000   0.009   0.018   0.021   0.030   0.112  8065    0    1    0   458359498          0      56826     103175  GET /api/user/*/icon
    5   70.677  14.1354   7.1822   5.339  19.999  20.000  20.000  20.000  20.000     2    0    3    0         202          0         40        102  GET /api/livestream/<num>/statistics
 1362   68.076   0.0500   0.0156   0.001   0.048   0.070   0.078   0.095   0.237  1360    0    1    1      639920         67        469        522  POST /api/register HTTP/1.1
 1075   67.621   0.0629   0.0158   0.004   0.062   0.082   0.088   0.102   0.157  1071    0    4    0     2070269          0       1925       2650  POST /api/livestream/<num>/livecomment
  468   50.683   0.1083   0.0357   0.001   0.112   0.149   0.162   0.181   0.192   464    0    4    0      543975          0       1162       1460  POST /api/livestream/reservation HTTP/1.1
  898   28.514   0.0318   0.0115   0.002   0.031   0.046   0.050   0.064   0.075   898    0    0    0     1662662       1552       1851       2203  POST /api/livestream/<num>/reaction
 1395   27.514   0.0197   0.0096   0.006   0.018   0.030   0.035   0.054   0.174  1322    0    0   73       32670         14         23        154  POST /api/icon HTTP/1.1
   23   18.870   0.8204   0.5825   0.096   1.146   1.476   1.490   1.567   1.567    23    0    0    0     2634962      89798     114563     137689  GET /api/livestream/search?tag=<tag>
  259   12.720   0.0491   0.0294   0.007   0.043   0.094   0.113   0.137   0.148   259    0    0    0      326901          3       1262      22591  GET /api/livestream HTTP/1.1
 1368   10.976   0.0080   0.0043   0.001   0.007   0.013   0.016   0.022   0.044  1366    0    2    0         128          0          0         64  POST /api/login HTTP/1.1
  183    4.740   0.0259   0.0099   0.005   0.024   0.039   0.045   0.054   0.057   183    0    0    0        6517          5         35        414  GET /api/livestream/<num>/ngwords
  456    4.519   0.0099   0.0057   0.000   0.009   0.017   0.020   0.029   0.047   456    0    0    0     2779050       6089       6094       6095  GET /api/tag HTTP/1.1
  246    3.883   0.0158   0.0205   0.001   0.010   0.029   0.056   0.110   0.139   243    0    0    3      105477          3        428      11000  GET /api/livestream/<num>/report
  127    3.058   0.0241   0.0157   0.002   0.020   0.048   0.059   0.076   0.082   127    0    0    0        2921         23         23         23  POST /api/livestream/<num>/moderate
   48    2.076   0.0433   0.0191   0.003   0.045   0.061   0.067   0.096   0.096    45    0    3    0      116801         57       2433       2717  POST /api/livestream/<num>/livecomment/<num>/report
   15    2.072   0.1381   0.0735   0.028   0.120   0.230   0.326   0.326   0.326    15    0    0    0        2108        133        140        154  GET /api/user/*/statistics
    1    1.667   1.6670   0.0000   1.667   1.667   1.667   1.667   1.667   1.667     1    0    0    0          27         27         27         27  POST /api/initialize HTTP/1.1

cureseven · 2023-11-25T07:35:43Z

Query 5
17,475

mysql> explain SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = 1024 AND livestream_id = 7529;

+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | ng_words | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 14337 |     1.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+

CREATE INDEX idx_ng_words_user_id_livestream_id ON ng_words (user_id, livestream_id);

mysql> explain SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = 1024 AND livestream_id = 7529;
+----+-------------+----------+------------+------+------------------------------------+------------------------------------+---------+-------------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys                      | key                                | key_len | ref         | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------------------------+------------------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | ng_words | NULL       | ref  | idx_ng_words_user_id_livestream_id | idx_ng_words_user_id_livestream_id | 16      | const,const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------------------------+------------------------------------+---------+-------------+------+----------+-------+

Top 20 Sort By Total
Count    Total     Mean   Stddev     Min   P50.0   P90.0   P95.0   P99.0     Max   2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
 1028  175.190   0.1704   0.1421   0.001   0.138   0.385   0.444   0.574   0.753  1024    0    4    0    13277737          0      12916      63938  GET /api/livestream/<num>/livecomment
  203  167.361   0.8244   0.1747   0.039   0.838   1.016   1.062   1.115   1.120   201    0    2    0    12583781          0      61989      63942  GET /api/livestream/search?limit=<num> HTTP/1.1
 1058  163.861   0.1549   0.1394   0.000   0.121   0.369   0.438   0.527   0.712  1057    0    1    0    11906920          0      11254      61529  GET /api/livestream/<num>/reaction
 8305   83.560   0.0101   0.0060   0.000   0.009   0.018   0.021   0.030   0.083  8304    0    1    0   470598324          0      56664     103175  GET /api/user/*/icon
    6   71.781  11.9635   8.1429   1.303  20.000  20.000  20.000  20.000  20.000     2    0    4    0         202          0         33        102  GET /api/livestream/<num>/statistics
 1335   69.501   0.0521   0.0170   0.001   0.049   0.073   0.084   0.104   0.189  1331    0    3    1      626073          0        468        522  POST /api/register HTTP/1.1
  502   50.156   0.0999   0.0313   0.001   0.104   0.134   0.143   0.161   0.175   498    0    4    0      584782          0       1164       1474  POST /api/livestream/reservation HTTP/1.1
 1123   40.981   0.0365   0.0121   0.001   0.036   0.052   0.058   0.071   0.102  1118    0    5    0     2178083          0       1939       2711  POST /api/livestream/<num>/livecomment
  945   29.787   0.0315   0.0111   0.001   0.031   0.044   0.051   0.064   0.089   945    0    0    0     1749640       1552       1851       2217  POST /api/livestream/<num>/reaction
 1371   26.023   0.0190   0.0081   0.005   0.017   0.028   0.033   0.049   0.074  1295    0    0   76       32685         14         23        154  POST /api/icon HTTP/1.1
   23   22.563   0.9810   0.7110   0.107   1.290   1.750   1.820   1.824   1.824    23    0    0    0     2851146     101477     123962     156606  GET /api/livestream/search?tag=<tag>
 1339   11.300   0.0084   0.0052   0.000   0.007   0.014   0.017   0.025   0.091  1337    0    2    0         128          0          0         64  POST /api/login HTTP/1.1
  222    9.778   0.0440   0.0300   0.001   0.036   0.082   0.103   0.120   0.248   221    0    1    0      327719          0       1476      22623  GET /api/livestream HTTP/1.1
  490    4.672   0.0095   0.0055   0.000   0.008   0.017   0.020   0.025   0.040   490    0    0    0     2986310       6089       6094       6095  GET /api/tag HTTP/1.1
  249    2.906   0.0117   0.0129   0.001   0.008   0.020   0.031   0.084   0.109   247    0    0    2       47561          3        191      10966  GET /api/livestream/<num>/report
  127    2.547   0.0201   0.0120   0.002   0.016   0.037   0.045   0.059   0.063   127    0    0    0        2921         23         23         23  POST /api/livestream/<num>/moderate
   16    1.883   0.1177   0.0648   0.026   0.110   0.247   0.263   0.263   0.263    16    0    0    0        2253        133        140        162  GET /api/user/*/statistics
    1    1.822   1.8220   0.0000   1.822   1.822   1.822   1.822   1.822   1.822     1    0    0    0          27         27         27         27  POST /api/initialize HTTP/1.1
  186    1.704   0.0092   0.0071   0.001   0.008   0.017   0.021   0.027   0.075   186    0    0    0        6495          5         34        277  GET /api/livestream/<num>/ngwords
   96    0.857   0.0089   0.0055   0.001   0.008   0.015   0.021   0.031   0.031    96    0    0    0           0          0          0          0  POST /api/livestream/<num>/enter

mism-mism · 2023-11-25T07:50:31Z

Top 20 Sort By Total
Count    Total     Mean   Stddev     Min   P50.0   P90.0   P95.0   P99.0     Max   2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
 1058  174.754   0.1652   0.1312   0.001   0.147   0.346   0.407   0.535   0.789  1056    0    2    0    14061324          0      13290      59602  GET /api/livestream/<num>/livecomment
  209  167.674   0.8023   0.1716   0.032   0.819   0.986   1.014   1.063   1.105   206    0    3    0    12882332          0      61637      63867  GET /api/livestream/search?limit=<num> HTTP/1.1
 1094  164.596   0.1505   0.1279   0.000   0.125   0.337   0.392   0.507   0.751  1088    0    6    0    12875200          0      11768      53412  GET /api/livestream/<num>/reaction
 8651   82.258   0.0095   0.0060   0.000   0.008   0.017   0.021   0.030   0.058  8651    0    0    0   521009842       6969      60225     171848  GET /api/user/*/icon
    6   71.439  11.9065   8.2149   1.020  20.000  20.005  20.005  20.005  20.005     2    0    4    0         202          0         33        102  GET /api/livestream/<num>/statistics
 1420   69.221   0.0487   0.0139   0.000   0.047   0.068   0.074   0.091   0.126  1418    0    1    1      666942         67        469        522  POST /api/register HTTP/1.1
  543   49.007   0.0903   0.0285   0.001   0.092   0.123   0.133   0.146   0.160   540    0    3    0      633717         62       1167       1447  POST /api/livestream/reservation HTTP/1.1
 1112   41.960   0.0377   0.0134   0.001   0.036   0.054   0.060   0.078   0.127  1111    0    1    0     2140589         87       1924       2619  POST /api/livestream/<num>/livecomment
  975   30.002   0.0308   0.0113   0.001   0.030   0.045   0.050   0.061   0.072   974    0    1    0     1803043          0       1849       2187  POST /api/livestream/<num>/reaction
 1462   23.744   0.0162   0.0075   0.002   0.015   0.026   0.030   0.040   0.067  1439    0    0   23       26919         14         18        154  POST /api/icon HTTP/1.1
   23   21.203   0.9219   0.6704   0.086   1.278   1.623   1.634   1.638   1.638    23    0    0    0     2746013     103772     119391     148086  GET /api/livestream/search?tag=<tag>
 1426   13.442   0.0094   0.0050   0.001   0.008   0.016   0.019   0.027   0.059  1423    0    3    0         128          0          0         64  POST /api/login HTTP/1.1
  218    8.639   0.0396   0.0244   0.006   0.034   0.073   0.088   0.132   0.137   218    0    0    0      330902          3       1517      22453  GET /api/livestream HTTP/1.1
  531    5.708   0.0107   0.0065   0.000   0.010   0.019   0.023   0.029   0.070   531    0    0    0     3236229       6089       6094       6095  GET /api/tag HTTP/1.1
  256    3.847   0.0150   0.0175   0.001   0.010   0.027   0.042   0.118   0.146   255    0    0    1       77506          3        302      10733  GET /api/livestream/<num>/report
  129    2.845   0.0221   0.0136   0.002   0.017   0.044   0.047   0.068   0.074   129    0    0    0        2967         23         23         23  POST /api/livestream/<num>/moderate
   55    2.535   0.0461   0.0171   0.004   0.048   0.064   0.071   0.086   0.086    53    0    2    0      136782         57       2486       2719  POST /api/livestream/<num>/livecomment/<num>/report
  191    1.861   0.0097   0.0064   0.001   0.008   0.017   0.021   0.041   0.043   191    0    0    0        5099          5         26        515  GET /api/livestream/<num>/ngwords
    1    1.743   1.7430   0.0000   1.743   1.743   1.743   1.743   1.743   1.743     1    0    0    0          27         27         27         27  POST /api/initialize HTTP/1.1
   16    1.547   0.0967   0.0349   0.028   0.099   0.142   0.145   0.145   0.145    16    0    0    0        2184        133        136        154  GET /api/user/*/statistics

mism-mism · 2023-11-25T07:51:28Z

top-slow-1.log

top-slow.log

mism-mism · 2023-11-25T08:01:23Z

#9

pinkumohikan · 2023-11-25T08:06:25Z

サーバ全台活用

サーバ構成

s1: dns, app (ユーザ登録, initializeのみ)
s2: app
s3: db

s1

s2

s3

スコア

https://portal.isucon.net/contest/jobs/28947/

pinkumohikan · 2023-11-25T08:07:26Z

kataribe

Top 20 Sort By Total
Count    Total     Mean   Stddev     Min   P50.0   P90.0   P95.0   P99.0     Max    2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
 1405  191.792   0.1365   0.1179   0.001   0.111   0.299   0.369   0.491   0.653   1403    0    2    0    24127777          0      17172      77033  GET /api/livestream/<num>/livecomment
 1439  187.068   0.1300   0.1197   0.000   0.102   0.298   0.370   0.515   0.732   1435    0    4    0    22482692          0      15623      74542  GET /api/livestream/<num>/reaction
   46  130.717   2.8417   1.1879   0.173   2.770   3.685   5.526   7.310   7.310     43    0    3    0         989          0         21         23  POST /api/livestream/<num>/moderate
    7  102.992  14.7131   6.5247   5.144  20.000  20.001  20.001  20.001  20.001      2    0    5    0         202          0         28        102  GET /api/livestream/<num>/statistics
  389   54.744   0.1407   0.0482   0.000   0.150   0.190   0.200   0.217   0.224    385    0    4    0      450984          0       1159       1442  POST /api/livestream/reservation HTTP/1.1
 2312   54.375   0.0235   0.0052   0.000   0.023   0.030   0.033   0.039   0.064   2309    0    2    1     1086026          0        469        522  POST /api/register HTTP/1.1
   95   46.755   0.4922   0.1311   0.039   0.530   0.595   0.616   0.635   0.635     95    0    0    0     5813111      49389      61190      63898  GET /api/livestream/search?limit=<num> HTTP/1.1
12272   37.811   0.0031   0.0034   0.000   0.002   0.006   0.008   0.014   0.187  12271    0    1    0   728448310          0      59358     164992  GET /api/user/*/icon
 2339   31.699   0.0136   0.0112   0.004   0.009   0.028   0.036   0.051   0.233   2145    0    0  194       65338         14         27        154  POST /api/icon HTTP/1.1
 1395   19.512   0.0140   0.0078   0.001   0.012   0.025   0.029   0.039   0.060   1393    0    2    0     2695352         57       1932       2695  POST /api/livestream/<num>/livecomment
 1290   17.516   0.0136   0.0081   0.001   0.012   0.024   0.028   0.037   0.131   1290    0    0    0     2390572       1552       1853       2182  POST /api/livestream/<num>/reaction
   27   16.235   0.6013   0.3754   0.092   0.760   0.980   1.087   1.089   1.089     26    0    1    0     2924265          0     108306     135137  GET /api/livestream/search?tag=<tag>
 2317    8.543   0.0037   0.0041   0.000   0.003   0.006   0.008   0.014   0.154   2315    0    2    0         128          0          0         64  POST /api/login HTTP/1.1
  151    7.663   0.0507   0.0243   0.005   0.052   0.082   0.093   0.110   0.127    151    0    0    0      176732          3       1170      22403  GET /api/livestream HTTP/1.1
   19    5.555   0.2924   0.1210   0.027   0.308   0.444   0.474   0.474   0.474     19    0    0    0        2614        133        137        154  GET /api/user/*/statistics
    1    1.708   1.7080   0.0000   1.708   1.708   1.708   1.708   1.708   1.708      1    0    0    0          27         27         27         27  POST /api/initialize HTTP/1.1
  381    1.096   0.0029   0.0024   0.000   0.002   0.005   0.008   0.013   0.019    381    0    0    0     2321031       6089       6091       6095  GET /api/tag HTTP/1.1
   46    0.915   0.0199   0.0121   0.002   0.019   0.036   0.045   0.047   0.047     44    0    2    0      114260         57       2483       2726  POST /api/livestream/<num>/livecomment/<num>/report
  116    0.787   0.0068   0.0101   0.000   0.003   0.020   0.034   0.047   0.049    116    0    0    0       74480          3        642       5665  GET /api/livestream/<num>/report
  129    0.347   0.0027   0.0024   0.000   0.002   0.005   0.006   0.012   0.019    129    0    0    0           0          0          0          0  POST /api/livestream/<num>/enter

cureseven · 2023-11-25T08:30:28Z

main
top-slow.log

cureseven · 2023-11-25T08:36:08Z

25,081

mysql> explain SELECT * FROM livestreams WHERE user_id = 1024;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | livestreams | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 7531 |    10.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+

CREATE INDEX idx_livestreams_user_id ON livestreams (user_id);

mysql> explain SELECT * FROM livestreams WHERE user_id = 1024;
+----+-------------+-------------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | livestreams | NULL       | ref  | idx_livestreams_user_id | idx_livestreams_user_id | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

pinkumohikan · 2023-11-25T10:17:45Z

三島さんのicons書き出しによって約30k
ログ停止で約37k

pinkumohikan · 2023-11-27T03:23:42Z

657チーム中45位
https://isucon.net/archives/57995340.html

cureseven mentioned this issue Nov 25, 2023

動的プレースホルダを使ってmysql負荷を下げる #2

Merged

cureseven mentioned this issue Nov 25, 2023

pool #3

Merged

mism-mism mentioned this issue Nov 25, 2023

pprof #4

Merged

cureseven mentioned this issue Nov 25, 2023

feat:getUserStatisticsHandlerのN+1削除 #5

Merged

cureseven mentioned this issue Nov 25, 2023

fillLivestreamResponse N+1解消 #8

Open

mism-mism mentioned this issue Nov 25, 2023

fillReactionsResponseを作ってN+1解消 #9

Merged

cureseven mentioned this issue Nov 25, 2023

feat:livestreamを先に持っておいて、それを使い回す #12

Open

pinkumohikan closed this as completed Nov 27, 2023

cureseven mentioned this issue Dec 10, 2023

ONAKA NO HAJIMARI #16

Closed

ONAKA NO OWARI #1

ONAKA NO OWARI #1

Comments

cureseven commented Nov 23, 2023 • edited by pinkumohikan Loading

競技終了時にポート閉じる

mism-mism commented Nov 25, 2023

mism-mism commented Nov 25, 2023

mism-mism commented Nov 25, 2023

mism-mism commented Nov 25, 2023

mism-mism commented Nov 25, 2023 • edited Loading

mism-mism commented Nov 25, 2023

mism-mism commented Nov 25, 2023 • edited Loading

mism-mism commented Nov 25, 2023

mism-mism commented Nov 25, 2023

pinkumohikan commented Nov 25, 2023

インフラ構成

スペック

メモリ

ミドルウェア

開いているポート

起動中のサービス

mism-mism commented Nov 25, 2023

pinkumohikan commented Nov 25, 2023 • edited Loading

mism-mism commented Nov 25, 2023

isudns

isupipe

mism-mism commented Nov 25, 2023

mism-mism commented Nov 25, 2023

テーブルのレコード（ベンチ前）

pinkumohikan commented Nov 25, 2023

リソース使用状況 (s1)

pinkumohikan commented Nov 25, 2023 • edited Loading

サーバ分離

スコア

現在のサーバ構成

s1

s3

mism-mism commented Nov 25, 2023

mism-mism commented Nov 25, 2023

mism-mism commented Nov 25, 2023 • edited Loading

S1

S3

mism-mism commented Nov 25, 2023

語り部のトップの直し方方針

pinkumohikan commented Nov 25, 2023

DNSのslow queryチューニング

pinkumohikan commented Nov 25, 2023

mism-mism commented Nov 25, 2023 • edited Loading

SELECT * FROM livestream_tags WHERE livestream_id = 7526

SELECT * FROM themes WHERE user_id = 493

SELECT image FROM icons WHERE user_id = 1015

mism-mism commented Nov 25, 2023

pinkumohikan commented Nov 25, 2023

mism-mism commented Nov 25, 2023 • edited Loading

cureseven commented Nov 25, 2023 • edited Loading

cureseven commented Nov 25, 2023

cureseven commented Nov 25, 2023

cureseven commented Nov 25, 2023

mism-mism commented Nov 25, 2023

pinkumohikan commented Nov 25, 2023 • edited Loading

PowerDNSのバックエンドをMySQLからLMDBへ変更

references

before

after

score

cureseven commented Nov 25, 2023 • edited Loading

cureseven commented Nov 25, 2023

mism-mism commented Nov 25, 2023

mism-mism commented Nov 25, 2023

mism-mism commented Nov 25, 2023 • edited Loading

pinkumohikan commented Nov 25, 2023 • edited Loading

サーバ全台活用

サーバ構成

s1

s2

s3

スコア

pinkumohikan commented Nov 25, 2023

kataribe

cureseven commented Nov 25, 2023

cureseven commented Nov 23, 2023 •

edited by pinkumohikan

Loading

mism-mism commented Nov 25, 2023 •

edited

Loading

mism-mism commented Nov 25, 2023 •

edited

Loading

pinkumohikan commented Nov 25, 2023 •

edited

Loading

pinkumohikan commented Nov 25, 2023 •

edited

Loading

mism-mism commented Nov 25, 2023 •

edited

Loading

mism-mism commented Nov 25, 2023 •

edited

Loading

mism-mism commented Nov 25, 2023 •

edited

Loading

cureseven commented Nov 25, 2023 •

edited

Loading

pinkumohikan commented Nov 25, 2023 •

edited

Loading

cureseven commented Nov 25, 2023 •

edited

Loading

mism-mism commented Nov 25, 2023 •

edited

Loading

pinkumohikan commented Nov 25, 2023 •

edited

Loading