pinkumohikan commented 2 years ago

ISUCON 12 予選
- まとめ
- 競技時間: 10:00 - 18:00 (JST)
牡蠣食えば金が無くなりリボ払いチーム ( @mism-mism @cureseven @pinkumohikan )
- タイムテーブル
  - https://docs.google.com/spreadsheets/d/17ALeV4WxWVZSC_5Tcp3falJsHYIYi8P3W2a5yBNUpd0/edit#gid=1226747339

サーバー構成

app1 : nginx + app
app2 : mysql
app3: 遊んでる

資料

予選レギュレーション
- https://isucon.net/archives/56671734.html
当日マニュアル
- https://gist.github.com/mackee/4320c18919c8f6f1867849378a17e651

mism-mism commented 2 years ago

初期スコア

2704

mism-mism commented 2 years ago

app1

CPU

2 processers = 1 socket x 1 core x 2 threads

Memory

Memory Total : 3774.1MB SWAP Total : 0MB

Disk

Disk : 20G

Nginx

nginx version: nginx/1.18.0 (Ubuntu)

Mysql

mysql 8.0.29

app2

CPU

2 processers = 1 socket x 1 core x 2 threads

Memory

Memory Total : 3802.78MB SWAP Total : 0MB

Disk

Disk : 20G

Nginx

nginx version: nginx/1.18.0 (Ubuntu)

Mysql

mysql 8.0.29

app3

CPU

2 processers = 1 socket x 1 core x 2 threads

Memory

Memory Total : 3802.78MB SWAP Total : 0MB

Disk

Disk : 20G

Nginx

nginx version: nginx/1.18.0 (Ubuntu)

Mysql

mysql 8.0.29

pinkumohikan commented 2 years ago

general log吐く設定をした

スコア: 3134 https://portal.isucon.net/contestant/benchmark_jobs/622

cureseven commented 2 years ago

Makefile作成おわり

cureseven commented 2 years ago

mysql

mysql> show create table tenant\G;
*************************** 1. row ***************************
       Table: tenant
Create Table: CREATE TABLE `tenant` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `display_name` varchar(255) NOT NULL,
  `created_at` bigint NOT NULL,
  `updated_at` bigint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=179 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show create table visit_history\G;
*************************** 1. row ***************************
       Table: visit_history
Create Table: CREATE TABLE `visit_history` (
  `player_id` varchar(255) NOT NULL,
  `tenant_id` bigint unsigned NOT NULL,
  `competition_id` varchar(255) NOT NULL,
  `created_at` bigint NOT NULL,
  `updated_at` bigint NOT NULL,
  KEY `tenant_id_idx` (`tenant_id`),
  KEY `tenant_id` (`tenant_id`,`competition_id`,`player_id`,`created_at`),
  KEY `tenant_id_2` (`tenant_id`,`competition_id`,`player_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

mysql> select count(*) from id_generator;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from tenant;
+----------+
| count(*) |
+----------+
|      106 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from visit_history;
+----------+
| count(*) |
+----------+
|  3225441 |
+----------+
1 row in set (1.31 sec)

sqlite

CREATE TABLE competition (
  id VARCHAR(255) NOT NULL PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  title TEXT NOT NULL,
  finished_at BIGINT NULL,
  created_at BIGINT NOT NULL,
  updated_at BIGINT NOT NULL
);

CREATE TABLE player (
  id VARCHAR(255) NOT NULL PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  display_name TEXT NOT NULL,
  is_disqualified BOOLEAN NOT NULL,
  created_at BIGINT NOT NULL,
  updated_at BIGINT NOT NULL
);

CREATE TABLE player_score (
  id VARCHAR(255) NOT NULL PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  player_id VARCHAR(255) NOT NULL,
  competition_id VARCHAR(255) NOT NULL,
  score BIGINT NOT NULL,
  row_num BIGINT NOT NULL,
  created_at BIGINT NOT NULL,
  updated_at BIGINT NOT NULL
);

pinkumohikan commented 2 years ago

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
  615  1005.771   1.6354   2.6239   0.001   0.049   5.300   5.703  12.158  12.762  568    0   47    0     5939931          0       9658      14590  GET /api/player/competition/[a-z0-9]+/ranking
  631   864.613   1.3702   2.0972   0.001   0.247   4.368   5.274  11.723  12.488  586    0   45    0      593895          0        941       2506  GET /api/player/player/[a-z0-9]+
   61   125.584   2.0588   1.9499   0.001   1.508   4.341   5.283  11.816  11.816   54    0    7    0        3111          0         51         62  POST /api/organizer/competition/[a-z0-9]+/score
    8   116.087  14.5109   7.4245   4.474  13.742  28.219  28.219  28.219  28.219    7    0    1    0       10625          0       1328       1530  GET /api/admin/tenants/billing?before=[0-9]+
    7    22.090   3.1557   1.4522   1.488   3.921   4.961   4.961   4.961   4.961    7    0    0    0      131239      12150      18748      26940  POST /api/organizer/players/add HTTP/2.0
   25    19.511   0.7804   2.1171   0.002   0.030   3.968   6.200   8.546   8.546   24    0    1    0       46082          0       1843       6075  GET /api/organizer/billing HTTP/2.0
    1     3.194   3.1940   0.0000   3.194   3.194   3.194   3.194   3.194   3.194    1    0    0    0        1560       1560       1560       1560  GET /api/admin/tenants/billing HTTP/2.0
    1     2.638   2.6380   0.0000   2.638   2.638   2.638   2.638   2.638   2.638    1    0    0    0          55         55         55         55  POST /initialize HTTP/2.0
  122     1.640   0.0134   0.0116   0.001   0.012   0.027   0.031   0.043   0.077  110    0   12    0      147685         39       1210       2967  GET /api/player/competitions HTTP/2.0
   43     0.897   0.0209   0.0113   0.003   0.018   0.036   0.038   0.060   0.060   42    0    1    0        6930         39        161        195  POST /api/organizer/competitions/add HTTP/2.0
   39     0.383   0.0098   0.0052   0.002   0.008   0.020   0.024   0.026   0.026   38    0    1    0         837         21         21         39  POST /api/organizer/competition/[a-z0-9]+/finish
   11     0.363   0.0330   0.0335   0.001   0.032   0.069   0.105   0.105   0.105    6    0    5    0        1357         39        123        225  POST /api/admin/tenants/add HTTP/2.0
   27     0.171   0.0063   0.0038   0.002   0.005   0.010   0.015   0.020   0.020   27    0    0    0      529207       5798      19600      61812  GET /api/organizer/players HTTP/2.0
   15     0.141   0.0094   0.0039   0.003   0.009   0.014   0.019   0.019   0.019   14    0    1    0        2234         39        148        163  POST /api/organizer/player/[a-z0-9]+/disqualified
    1     0.001   0.0010   0.0000   0.001   0.001   0.001   0.001   0.001   0.001    1    0    0    0         177        177        177        177  GET /api/organizer/competitions HTTP/2.0
    1     0.000   0.0000   0.0000   0.000   0.000   0.000   0.000   0.000   0.000    1    0    0    0         479        479        479        479  GET /index.html HTTP/2.0
    1     0.000   0.0000   0.0000   0.000   0.000   0.000   0.000   0.000   0.000    1    0    0    0       33294      33294      33294      33294  GET /js/app.3a4ec98c.js HTTP/2.0
    1     0.000   0.0000   0.0000   0.000   0.000   0.000   0.000   0.000   0.000    1    0    0    0        4868       4868       4868       4868  GET /css/app.83b4c321.css HTTP/2.0

pinkumohikan commented 2 years ago

pt-query-digest

# 3.1s user time, 40ms system time, 32.82M rss, 33.15G vsz
# Current date: Sat Jul 23 10:41:03 2022
# Hostname: salmon.local
# Files: mysql-slow.log
# Overall: 39.15k total, 21 unique, 387.66 QPS, 2.14x concurrency ________
# Time range: 2022-07-23T01:39:16 to 2022-07-23T01:40:57
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           216s     1us      2s     6ms    16ms    29ms    66us
# Lock time            33s       0    64ms   837us     7ms     3ms       0
# Rows sent        103.63k       0     199    2.71    0.99   17.16       0
# Rows examine      26.30M       0   3.08M  704.28    0.99  16.19k       0
# Query size         1.52M      11     186   40.76   56.92   23.30   31.70

# Profile
# Rank Query ID                            Response time  Calls R/Call V/M
# ==== =================================== ============== ===== ====== ===
#    1 0x676347F321DB8BC7FCB05D4948FC2248  127.9152 59.3%  1233 0.1037  0.10 SELECT visit_history
#    2 0x94A9E43DFAAFA029A1FC19A5563AD0F5   77.3798 35.9%  8874 0.0087  0.00 REPLACE id_generator
# MISC 0xMISC                               10.4524  4.8% 29047 0.0004   0.0 <19 ITEMS>

# Query 1: 16.22 QPS, 1.68x concurrency, ID 0x676347F321DB8BC7FCB05D4948FC2248 at byte 5276415
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.10
# Time range: 2022-07-23T01:39:36 to 2022-07-23T01:40:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3    1233
# Exec time     59    128s   146us   597ms   104ms   323ms   102ms    68ms
# Lock time      0     2ms     1us    61us     1us     2us     2us     1us
# Rows sent     97 100.55k       0     199   83.51  174.84   53.46   72.65
# Rows examine  88  23.22M       0  49.19k  19.28k  44.45k  13.52k  14.47k
# Query size    11 172.18k     142     144  143.00  136.99    0.75  136.99
# String:
# Databases    isuports
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  #####
#   1ms  ###########
#  10ms  ################################################################
# 100ms  ###############################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuports` LIKE 'visit_history'\G
#    SHOW CREATE TABLE `isuports`.`visit_history`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = 45 AND competition_id = '4d1b6fea5' GROUP BY player_id\G

# Query 2: 124.99 QPS, 1.09x concurrency, ID 0x94A9E43DFAAFA029A1FC19A5563AD0F5 at byte 778820
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-07-23T01:39:36 to 2022-07-23T01:40:47
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         22    8874
# Exec time     35     77s     2ms   135ms     9ms    19ms     6ms     7ms
# Lock time     99     33s       0    64ms     4ms    12ms     5ms     2ms
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    25 389.97k      45      45      45      45       0      45
# String:
# Databases    isuports
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  ##############################
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuports` LIKE 'id_generator'\G
#    SHOW CREATE TABLE `isuports`.`id_generator`\G
REPLACE INTO id_generator (stub) VALUES ('a')\G

mism-mism commented 2 years ago

mysql重い

app2へ移動

pinkumohikan commented 2 years ago

visit_historyに対するindex追加 #2 マージで3,700点ぐらい

cureseven commented 2 years ago

3 スコア下がったけどマージした

Marked At: 2022-07-23 10:57:38.171

Score: 3568

Score Breakdown: base=3568, deduction=0

pinkumohikan commented 2 years ago

pt-query-digest


# 2s user time, 30ms system time, 33.25M rss, 33.15G vsz
# Current date: Sat Jul 23 11:02:42 2022
# Hostname: salmon.local
# Files: mysql-slow.log
# Overall: 21.17k total, 12 unique, 225.26 QPS, 1.73x concurrency ________
# Time range: 2022-07-23T01:56:26 to 2022-07-23T01:58:00
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           163s     1us      2s     8ms    21ms    19ms     5ms
# Lock time            81s       0   101ms     4ms    15ms     6ms     1us
# Rows sent        624.36k       0   4.88k   30.19  130.47  237.16       0
# Rows examine      12.74M       0   3.08M  630.98   2.06k  21.63k       0
# Query size         1.36M      27     159   67.42  136.99   40.34   44.60

# Profile
# Rank Query ID                            Response time  Calls R/Call V/M
# ==== =================================== ============== ===== ====== ===
#    1 0x94A9E43DFAAFA029A1FC19A5563AD0F5  139.0454 85.3% 12248 0.0114  0.00 REPLACE id_generator
#    2 0x676347F321DB8BC7FCB05D4948FC2248   15.8792  9.7%  3968 0.0040  0.04 SELECT visit_history
# MISC 0xMISC                                8.0908  5.0%  4958 0.0016   0.0 <10 ITEMS>

# Query 1: 170.11 QPS, 1.93x concurrency, ID 0x94A9E43DFAAFA029A1FC19A5563AD0F5 at byte 2706272
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-07-23T01:56:29 to 2022-07-23T01:57:41
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         57   12248
# Exec time     85    139s   965us   107ms    11ms    23ms     7ms    10ms
# Lock time     99     81s       0   101ms     7ms    18ms     6ms     5ms
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    38 538.24k      45      45      45      45       0      45
# String:
# Databases    isuports
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  #
#   1ms  #############################################################
#  10ms  ################################################################
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuports` LIKE 'id_generator'\G
#    SHOW CREATE TABLE `isuports`.`id_generator`\G
REPLACE INTO id_generator (stub) VALUES ('a')\G

# Query 2: 43.60 QPS, 0.17x concurrency, ID 0x676347F321DB8BC7FCB05D4948FC2248 at byte 1421912
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.04
# Time range: 2022-07-23T01:56:29 to 2022-07-23T01:58:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         18    3968
# Exec time      9     16s   148us   176ms     4ms     6ms    13ms     2ms
# Lock time      0     7ms       0   329us     1us     1us     6us     1us
# Rows sent     98 617.77k       0   4.88k  159.42  183.58  529.71   88.31
# Rows examine  75   9.66M       0  78.96k   2.49k   2.89k   8.33k   1.39k
# Query size    39 553.72k     141     144  142.89  136.99    0.59  136.99
# String:
# Databases    isuports
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  ###############
#   1ms  ################################################################
#  10ms  #
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuports` LIKE 'visit_history'\G
#    SHOW CREATE TABLE `isuports`.`visit_history`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = 1 AND competition_id = '2b9509304' GROUP BY player_id\G

pinkumohikan commented 2 years ago

REPLACE INTO id_generator (stub) VALUES ('a')\G

https://github.com/pinkumohikan/isucon12-qualify/blob/8d863da8970f0cc83133da191e22baae1f9bacdc/go/isuports.go#L102-L126

DBでID発番しているアプリ側でUUID v4を使って発番するように変える

mism-mism commented 2 years ago

DB分離結果

cureseven commented 2 years ago

5 ちょっと落ちたけどマージした

Marked At: 2022-07-23 11:13:40.752

Score: 4411

Score Breakdown: base=4411, deduction=0

cureseven commented 2 years ago

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
 1770  1176.328  0.6646  1.1068   0.001   0.211   1.728   2.083   6.510   8.320  1683    0   85    2     1997363          0       1128       2153  GET /api/player/player/[a-z0-9]+
   13   112.140  8.6262  2.9655   3.896   8.114  12.383  13.244  13.244  13.244    12    0    1    0       18047          0       1388       1529  GET /api/admin/tenants/billing?before=[0-9]+
   22    99.084  4.5038  2.8180   0.380   6.709   6.957   6.998   7.678   7.678    22    0    0    0      338983        195      15408      16133  GET /api/player/competition/c2977d2a-ed59-40d0-9dee-113e356396a0/ranking HTTP/2.0
   24    81.450  3.3937  3.1265   0.047   1.404   7.728   7.769   7.864   7.864    24    0    0    0      231448        210       9643      16382  GET /api/player/competition/2a111f64-101b-4d2c-9499-b96ce6e3ebbf/ranking HTTP/2.0
   34    78.480  2.3082  2.8525   0.016   1.933   7.627   8.999   9.893   9.893    34    0    0    0      534054      15707      15707      15708  GET /api/player/competition/b75e6806-4952-443b-b438-1085aff8416c/ranking HTTP/2.0
   25    73.590  2.9436  2.2042   0.104   2.347   5.302   5.331   6.185   6.185    25    0    0    0      402891      16115      16115      16116  GET /api/player/competition/a7ca2c76-45d4-42b2-8f03-1c8fd80bebbc/ranking HTTP/2.0
   28    68.794  2.4569  2.4349   0.025   3.501   7.341   7.645   7.718   7.718    28    0    0    0      450759      16098      16098      16099  GET /api/player/competition/01fcf63f-57f7-4832-9fdf-e290b5437272/ranking HTTP/2.0
   41    63.523  1.5493  2.2038   0.009   0.933   4.021   7.657   8.093   8.093    41    0    0    0      539001      13146      13146      13147  GET /api/player/competition/f445a545-8c3f-4c0a-b67d-16e93e6265a4/ranking HTTP/2.0
   62    53.022  0.8552  0.7651   0.010   0.845   1.742   1.878   2.508   2.508    59    0    3    0      969976          0      15644      16441  GET /api/player/competition/34f56fb6-5580-4cc5-a7dc-349333bb2b49/ranking HTTP/2.0
   57    39.208  0.6879  0.6875   0.010   0.548   1.478   1.732   2.162   2.162    54    0    3    0      885670          0      15538      16402  GET /api/player/competition/79c342ad-e3c6-4d05-bfbc-b81f6ef6e337/ranking HTTP/2.0
   49    37.642  0.7682  0.7353   0.011   0.603   1.880   2.079   2.291   2.291    48    0    1    0      789760          0      16117      16454  GET /api/player/competition/3cdc2c47-9b22-4f51-afd5-1ba9f7936f7e/ranking HTTP/2.0
   36    32.888  0.9136  0.8646   0.009   0.529   1.923   2.163   2.170   2.170    36    0    0    0      592648      16462      16462      16463  GET /api/player/competition/4b4f354d-2ebb-4cb5-922f-97b3679c1612/ranking HTTP/2.0
  199    31.973  0.1607  0.4472   0.001   0.011   0.552   1.487   1.811   2.036   183    0   16    0      301112         39       1513       2626  GET /api/player/competitions HTTP/2.0
   10    31.493  3.1493  3.1345   0.001   1.496   9.237   9.237   9.237   9.237     9    0    1    0       90762         39       9076      12184  GET /api/player/competition/[a-z0-9]+/ranking
   39    29.862  0.7657  0.6617   0.013   0.903   1.472   1.840   1.862   1.862    37    0    2    0      606186          0      15543      16384  GET /api/player/competition/cb649810-61aa-4f10-8836-66e69b914b28/ranking HTTP/2.0
   27    29.227  1.0825  0.8319   0.011   1.156   2.091   2.097   2.318   2.318    26    0    1    0      428027          0      15852      16463  GET /api/player/competition/41df49cf-2c77-4c07-8288-f50ed5c4ebd5/ranking HTTP/2.0
   37    28.829  0.7792  0.7336   0.010   0.867   1.743   1.761   1.792   1.792    36    0    1    0      576589          0      15583      16469  GET /api/player/competition/1930d7fb-1213-46a6-8b1f-cbbb7cf3365e/ranking HTTP/2.0
   32    28.252  0.8829  0.7508   0.010   0.875   1.759   1.769   2.112   2.112    32    0    0    0      524496      16390      16390      16391  GET /api/player/competition/12db3a56-fa7a-40ca-9144-d7236782bb47/ranking HTTP/2.0
   41    27.284  0.6655  0.7455   0.009   0.055   1.754   1.787   2.362   2.362    41    0    0    0      670200      16346      16346      16347  GET /api/player/competition/4fadd28e-a539-453b-b778-6696f7ab1907/ranking HTTP/2.0
   30    26.984  0.8995  0.9443   0.009   0.617   2.171   2.177   2.215   2.215    30    0    0    0      492675      16422      16422      16423  GET /api/player/competition/85fbb8dc-e570-4dde-b695-1b90f92d946c/ranking HTTP/2.0

pinkumohikan commented 2 years ago

pt-query-digest


# 860ms user time, 20ms system time, 32.74M rss, 33.15G vsz
# Current date: Sat Jul 23 11:19:54 2022
# Hostname: salmon.local
# Files: mysql-slow.log
# Overall: 8.56k total, 11 unique, 57.04 QPS, 0.81x concurrency __________
# Time range: 2022-07-23T02:15:11 to 2022-07-23T02:17:41
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           122s     2us      2s    14ms    82ms    39ms   138us
# Lock time           13ms       0    93us     1us     2us     1us     1us
# Rows sent        168.65k       0     199   20.18  130.47   41.57    0.99
# Rows examine      39.67M       0   3.08M   4.75k  27.29k  34.85k    0.99
# Query size       800.65k      27     205   95.82  202.40   61.32   51.63

# Profile
# Rank Query ID                            Response time  Calls R/Call V/M
# ==== =================================== ============== ===== ====== ===
#    1 0x676347F321DB8BC7FCB05D4948FC2248  114.7691 94.4%  2081 0.0552  0.04 SELECT visit_history
#    2 0x2E69352DE16B15042A1217500A0400FE    3.8099  3.1%  1379 0.0028  0.00 INSERT visit_history
# MISC 0xMISC                                2.9731  2.4%  5096 0.0006   0.0 <9 ITEMS>

# Query 1: 29.73 QPS, 1.64x concurrency, ID 0x676347F321DB8BC7FCB05D4948FC2248 at byte 267875
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.04
# Time range: 2022-07-23T02:15:14 to 2022-07-23T02:16:24
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         24    2081
# Exec time     94    115s   136us   402ms    55ms   134ms    45ms    46ms
# Lock time     26     3ms       0    19us     1us     2us       0     1us
# Rows sent     96 162.27k       0     199   79.85  174.84   50.63   69.19
# Rows examine  92  36.58M       0  49.19k  18.00k  40.32k  12.49k  14.47k
# Query size    37 297.98k     141     171  146.63  166.51    9.95  136.99
# String:
# Databases    isuports
# Hosts        isuports-1.t.isucon.dev
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  ###
#   1ms  ##########
#  10ms  ################################################################
# 100ms  #################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuports` LIKE 'visit_history'\G
#    SHOW CREATE TABLE `isuports`.`visit_history`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = 45 AND competition_id = '1588887fd' GROUP BY player_id\G

# Query 2: 20.58 QPS, 0.06x concurrency, ID 0x2E69352DE16B15042A1217500A0400FE at byte 2167316
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-07-23T02:15:15 to 2022-07-23T02:16:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         16    1379
# Exec time      3      4s   595us    13ms     3ms     5ms     1ms     2ms
# Lock time     12     2ms       0    16us     1us     1us       0     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    33 270.94k     150     205  201.19  202.40    9.49  202.40
# String:
# Databases    isuports
# Hosts        isuports-1.t.isucon.dev
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  #
#   1ms  ################################################################
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuports` LIKE 'visit_history'\G
#    SHOW CREATE TABLE `isuports`.`visit_history`\G
INSERT INTO visit_history (player_id, tenant_id, competition_id, created_at, updated_at) VALUES ('fc8a78ae-41aa-414f-88ad-a14d747311f0', 127, '79c342ad-e3c6-4d05-bfbc-b81f6ef6e337', 1658542571, 1658542571)\G

pinkumohikan commented 2 years ago

ユニークIDの発番にDBを使わずアプリ側でUUID v4を使うように変更 #6 マージで5,400点

pinkumohikan commented 2 years ago

app1: nginx, app

app2: db

app3: まだ使ってないので省略

pinkumohikan commented 2 years ago

visit_historyテーブルに対するI/Oが重いのでなんとかする @pinkumohikan

// SaaS管理者用API // テナントごとの課金レポートを最大10件、テナントのid降順で取得する // GET /api/admin/tenants/billing

https://github.com/pinkumohikan/isucon12-qualify/blob/cb8f8938470ae7fb672fde46e7be33c12568f48f/go/isuports.go#L607

cureseven commented 2 years ago

uuid混ぜた kataribe結果は得点出るようになったらお願いします

cureseven commented 2 years ago

@cureseven // GET /api/player/competition/:competition_id/ranking みます

"SELECT * FROM player_score WHERE tenant_id = ? AND competition_id = ? ORDER BY row_num DESC", の結果でforしてプレイヤースコアのselectをしているので直します

cureseven commented 2 years ago

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
 1485  1143.125  0.7698  1.2891   0.001   0.196   2.159   2.565   7.161   9.190  1447    0   35    3     1551573          0       1044       2256  GET /api/player/player/{small-uuid}
 1251  1080.786  0.8639  1.6392   0.001   0.026   2.331   4.366   8.164   9.062  1215    0   35    1    15404245          0      12313      17290  GET /api/player/competition/{uuid}/ranking
   14   112.545  8.0389  3.2421   0.177   8.818  11.511  13.065  13.065  13.065    13    0    1    0       19602          0       1400       1542  GET /api/admin/tenants/billing?before=[0-9]+
   81    91.417  1.1286  1.3699   0.001   0.679   2.103   2.659   8.514   8.514    73    0    8    0        4133          0         51         62  POST /api/organizer/competition/{uuid}/score
  191    39.355  0.2060  0.5346   0.001   0.009   0.881   1.675   2.169   2.386   175    0   16    0      265231         39       1388       2596  GET /api/player/competitions HTTP/2.0
   10    12.370  1.2370  0.3738   0.754   1.318   1.784   1.784   1.784   1.784    10    0    0    0      240199      14950      24019      30860  POST /api/organizer/players/add HTTP/2.0
   37     7.913  0.2139  0.4141   0.001   0.023   1.014   1.170   1.797   1.797    35    0    2    0       86960          0       2350       4813  GET /api/organizer/billing HTTP/2.0
    1     3.037  3.0370  0.0000   3.037   3.037   3.037   3.037   3.037   3.037     1    0    0    0        1573       1573       1573       1573  GET /api/admin/tenants/billing HTTP/2.0
    1     2.531  2.5310  0.0000   2.531   2.531   2.531   2.531   2.531   2.531     1    0    0    0          55         55         55         55  POST /initialize HTTP/2.0
   60     0.613  0.0102  0.0061   0.001   0.009   0.017   0.019   0.046   0.046    59    0    1    0       11307         39        188        217  POST /api/organizer/competitions/add HTTP/2.0
   11     0.545  0.0495  0.0700   0.000   0.041   0.064   0.254   0.254   0.254     6    0    5    0        1334         39        121        198  POST /api/admin/tenants/add HTTP/2.0
   55     0.535  0.0097  0.0075   0.001   0.008   0.013   0.018   0.058   0.058    54    0    1    0        1173         21         21         39  POST /api/organizer/competition/{uuid}/finish
   41     0.175  0.0043  0.0031   0.001   0.003   0.007   0.009   0.015   0.015    41    0    0    0     1222402       5026      29814     148987  GET /api/organizer/players HTTP/2.0
   18     0.175  0.0097  0.0062   0.001   0.008   0.023   0.028   0.028   0.028    17    0    1    0        2761         39        153        185  POST /api/organizer/player/{uuid}/disqualified
    1     0.006  0.0060  0.0000   0.006   0.006   0.006   0.006   0.006   0.006     1    0    0    0         205        205        205        205  GET /api/organizer/competitions HTTP/2.0
    1     0.000  0.0000  0.0000   0.000   0.000   0.000   0.000   0.000   0.000     1    0    0    0       33294      33294      33294      33294  GET /js/app.3a4ec98c.js HTTP/2.0
    1     0.000  0.0000  0.0000   0.000   0.000   0.000   0.000   0.000   0.000     1    0    0    0         479        479        479        479  GET /index.html HTTP/2.0
    1     0.000  0.0000  0.0000   0.000   0.000   0.000   0.000   0.000   0.000     1    0    0    0        4868       4868       4868       4868  GET /css/app.83b4c321.css HTTP/2.0

cureseven commented 2 years ago

リーダーボードはこの画面 ranking取得のAPIを叩いている

さらに読み込むボタンを押した時以下のAPIが叩かれる https://isucon.t.isucon.dev/api/player/competition/6396ba682/ranking?rank_after=100

mism-mism commented 2 years ago

go分離

cureseven commented 2 years ago

8 マージで

Marked At: 2022-07-23 12:46:08.820

Score: 6049

Score Breakdown: base=6301, deduction=252

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
 1877  1688.903  0.8998  1.9026   0.001   0.145   2.155   2.480   8.583  30.001  1816    0   60    1     1797785          0        957       2092  GET /api/player/player/{small-uuid}
 1522  1550.228  1.0185  1.8590   0.001   0.061   2.551   2.793   8.181  30.001  1462    0   60    0    18604103          0      12223      17290  GET /api/player/competition/{uuid}/ranking
   95   121.549  1.2795  1.3780   0.001   0.753   2.628   3.439   8.167   8.167    88    0    7    0        4931          0         51         62  POST /api/organizer/competition/{uuid}/score
   37    77.895  2.1053  4.1730   0.009   0.994   3.147  11.447  24.127  24.127    36    0    1    0       52836          0       1428       1551  GET /api/admin/tenants/billing?before=[0-9]+
  256    33.858  0.1323  0.4647   0.000   0.013   0.056   0.857   2.407   2.669   241    0   15    0      339990         39       1328       2817  GET /api/player/competitions HTTP/2.0
   44    17.887  0.4065  1.9333   0.001   0.019   0.180   0.532  12.684  12.684    44    0    0    0      104534        351       2375       4810  GET /api/organizer/billing HTTP/2.0
   11    12.349  1.1226  0.4159   0.624   1.115   1.658   1.839   1.839   1.839    10    0    1    0      235159          0      21378      32820  POST /api/organizer/players/add HTTP/2.0
    1     2.559  2.5590  0.0000   2.559   2.559   2.559   2.559   2.559   2.559     1    0    0    0          55         55         55         55  POST /initialize HTTP/2.0
   74     1.654  0.0224  0.0523   0.002   0.010   0.036   0.044   0.355   0.355    73    0    1    0       13979         39        188        217  POST /api/organizer/competitions/add HTTP/2.0
    2     1.301  0.6505  0.2165   0.434   0.867   0.867   0.867   0.867   0.867     2    0    0    0        3159       1567       1579       1592  GET /api/admin/tenants/billing HTTP/2.0
   12     0.912  0.0760  0.1118   0.000   0.040   0.315   0.320   0.320   0.320     7    0    5    0        1497         39        124        202  POST /api/admin/tenants/add HTTP/2.0
   68     0.886  0.0130  0.0089   0.001   0.009   0.026   0.032   0.051   0.051    67    0    1    0        1446         21         21         39  POST /api/organizer/competition/{uuid}/finish
   55     0.470  0.0085  0.0172   0.001   0.004   0.014   0.024   0.123   0.123    55    0    0    0     2495958       2942      45381     602067  GET /api/organizer/players HTTP/2.0
   18     0.167  0.0093  0.0038   0.001   0.009   0.015   0.019   0.019   0.019    17    0    1    0        2765         39        153        185  POST /api/organizer/player/{uuid}/disqualified
    1     0.001  0.0010  0.0000   0.001   0.001   0.001   0.001   0.001   0.001     1    0    0    0         205        205        205        205  GET /api/organizer/competitions HTTP/2.0
    1     0.000  0.0000  0.0000   0.000   0.000   0.000   0.000   0.000   0.000     1    0    0    0         479        479        479        479  GET /index.html HTTP/2.0
    1     0.000  0.0000  0.0000   0.000   0.000   0.000   0.000   0.000   0.000     1    0    0    0        4868       4868       4868       4868  GET /css/app.83b4c321.css HTTP/2.0
    1     0.000  0.0000  0.0000   0.000   0.000   0.000   0.000   0.000   0.000     1    0    0    0       33294      33294      33294      33294  GET /js/app.3a4ec98c.js HTTP/2.0

ﾔｯﾀｰ！画面上から100人ずつ切れそうと分かった．アプリケーションで切っているのでシーク法に置き換えるのがよさそう

cureseven commented 2 years ago

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
 1877  1688.903  0.8998  1.9026   0.001   0.145   2.155   2.480   8.583  30.001  1816    0   60    1     1797785          0        957       2092  GET /api/player/player/{small-uuid}

は，タイトルとスコアを表示する部分にN+1が2つ登場テナントの持つ大会の数だけ回している．そんなに多くないはず

メモリに持っておけないかな？

cureseven commented 2 years ago

Top 20 Sort By Totalで3位が

// テナント管理者向けAPI
// POST /api/organizer/competition/:competition_id/score
// 大会のスコアをCSVでアップロードする

CSVの内容チェックがN+1 DBアクセスして存在しないplayerじゃないかを確認しているところを改善できそう

該当大会のplayer_scoreをいったん消してCSVの内容をforでINSERTしている

csvの内容

playerID, scoreStr := row[0], row[1]

cureseven commented 2 years ago

@cureseven POST /api/organizer/competition/:competition_id/score のロックの中でN+1でplayerをselectしているのを直します

cureseven commented 2 years ago

9 マージで

Marked At: 2022-07-23 15:11:12.567

Score: 6264

Score Breakdown: base=6959, deduction=695

pinkumohikan commented 2 years ago

ランキングの集計を1クエリでやる #11 マージで変わらず6,200ちょい

pinkumohikan commented 2 years ago

ランキングにアクセスしたプレイヤーを持つテーブルを用意する #12 マージで6,700ぐらい

pinkumohikan commented 2 years ago

pprof

$ go tool pprof -http=0.0.0.0:6061 pprof.isuports.samples.cpu.004.pb.gz Serving web UI on http://0.0.0.0:6061

cureseven commented 2 years ago

13 マージで

Marked At: 2022-07-23 16:55:29.301

Score: 6836

Score Breakdown: base=7195, deduction=359

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
 2396  2071.696  0.8646  1.9572   0.001   0.172   2.067   2.557   7.601  30.001  2316    0   80    0     2499397          0       1043       2360  GET /api/player/player/{small-uuid}
 1749  1725.043  0.9863  2.1354   0.001   0.031   2.304   2.918  10.097  30.000  1704    0   45    0    22871440          0      13076      17314  GET /api/player/competition/{uuid}/ranking
   98   143.105  1.4603  2.1571   0.001   0.745   2.344   3.739  17.828  17.828    91    0    7    0        5098          0         52         62  POST /api/organizer/competition/{uuid}/score
  271   103.776  0.3829  0.7534   0.001   0.015   1.865   2.168   2.613   2.649   256    0   15    0      371149         39       1369       2837  GET /api/player/competitions HTTP/2.0
   39    69.825  1.7904  7.9247   0.001   0.379   1.057   2.541  50.570  50.570    38    0    1    0       55932          0       1434       1551  GET /api/admin/tenants/billing?before=[0-9]+
   47    17.368  0.3695  1.5515   0.001   0.016   0.301   0.383   9.189   9.189    46    0    1    0      113402          0       2412       5014  GET /api/organizer/billing HTTP/2.0
   10    12.760  1.2760  0.4326   0.681   1.554   1.897   1.897   1.897   1.897    10    0    0    0      248039      14950      24803      32820  POST /api/organizer/players/add HTTP/2.0
    1     6.273  6.2730  0.0000   6.273   6.273   6.273   6.273   6.273   6.273     1    0    0    0          55         55         55         55  POST /initialize HTTP/2.0
   78     0.940  0.0121  0.0106   0.001   0.009   0.019   0.029   0.087   0.087    77    0    1    0       14925         39        191        222  POST /api/organizer/competitions/add HTTP/2.0
   71     0.677  0.0095  0.0043   0.001   0.008   0.014   0.019   0.026   0.026    70    0    1    0        1509         21         21         39  POST /api/organizer/competition/{uuid}/finish
   59     0.575  0.0097  0.0190   0.002   0.005   0.019   0.032   0.137   0.137    59    0    0    0     2701873       4624      45794     602067  GET /api/organizer/players HTTP/2.0
    2     0.302  0.1510  0.0250   0.126   0.176   0.176   0.176   0.176   0.176     2    0    0    0        3114       1547       1557       1567  GET /api/admin/tenants/billing HTTP/2.0
   12     0.256  0.0213  0.0177   0.001   0.029   0.044   0.047   0.047   0.047     7    0    5    0        1502         39        125        205  POST /api/admin/tenants/add HTTP/2.0
   17     0.136  0.0080  0.0027   0.001   0.008   0.012   0.014   0.014   0.014    16    0    1    0        2595         39        152        185  POST /api/organizer/player/{uuid}/disqualified
    1     0.001  0.0010  0.0000   0.001   0.001   0.001   0.001   0.001   0.001     1    0    0    0         205        205        205        205  GET /api/organizer/competitions HTTP/2.0
    1     0.000  0.0000  0.0000   0.000   0.000   0.000   0.000   0.000   0.000     1    0    0    0       33294      33294      33294      33294  GET /js/app.3a4ec98c.js HTTP/2.0
    1     0.000  0.0000  0.0000   0.000   0.000   0.000   0.000   0.000   0.000     1    0    0    0        4868       4868       4868       4868  GET /css/app.83b4c321.css HTTP/2.0
    1     0.000  0.0000  0.0000   0.000   0.000   0.000   0.000   0.000   0.000     1    0    0    0         479        479        479        479  GET /index.html HTTP/2.0

billingReportByCompetition ちょっと改善HAPPY

pinkumohikan commented 2 years ago

プレイヤーAPI内でのN+1を軽減 #14 マージで6,900ぐらい

pinkumohikan commented 2 years ago

app 1 memory足りないのでswap 2G足しとく

$ free -m
               total        used        free      shared  buff/cache   available
Mem:            3685        1147         112           1        2425        2258
Swap:              0           0           0

追加:

$ sudo su -l
root@ip-192-168-0-11:~# dd if=/dev/zero of=/swap.extended bs=1M count=2048
chmod 0600 /swap.extended
mkswap /swap.extended
swapon /swap.extended
echo '/swap.extended          swap                    swap    defaults        0 0' >> /etc/fstab
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB, 2.0 GiB) copied, 12.1438 s, 177 MB/s
Setting up swapspace version 1, size = 2 GiB (2147479552 bytes)
no label, UUID=41bfe873-0a73-4fe0-9855-e14a7f556709

cureseven commented 2 years ago

15 マージで

Marked At: 2022-07-23 17:38:39.355

Score: 8939

Score Breakdown: base=9311, deduction=372

pinkumohikan commented 2 years ago

↑ ちょっと混ざってる

SQLiteへindex追加 #16 マージで9,000点ぐらい

cureseven commented 2 years ago

app1 sudo systemctl disable —now netdata した

oystersjp / isucon12-qualify-revolving

牡蠣食えば金が無くなりリボ払いチーム (ISUCON 12 予選) #1

サーバー構成

資料

初期スコア

app1

CPU

Memory

Disk

Nginx

Mysql

app2

CPU

Memory

Disk

Nginx

Mysql

app3

CPU

Memory

Disk

Nginx

Mysql

general log吐く設定をした

mysql

sqlite

kataribe

pt-query-digest

3 スコア下がったけどマージした

pt-query-digest

5 ちょっと落ちたけどマージした

pt-query-digest

8 マージで

9 マージで

pprof

13 マージで

15 マージで