Skip to content

Instantly share code, notes, and snippets.

@nipe0324

nipe0324/tuning.md

Created May 28, 2023 01:33
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save nipe0324/2ac094c30d17f7ebd5d1875918a24e35 to your computer and use it in GitHub Desktop.
Save nipe0324/2ac094c30d17f7ebd5d1875918a24e35 to your computer and use it in GitHub Desktop.
Download ZIP
ISUCON12のチューニングメモ（初期 1345 -> 最終スコア 86910)
Raw
tuning.md

ISUCON12のチューニングメモ

初期スコア

04:15:02.788004 Error 3 (Critical:3)
04:15:02.788010 PASSED: true
04:15:02.788013 SCORE: 1345 (+1921 -576(30%))

nginx.confとslow queryの設定追加

スコア

微減したぐらいで大きく変わらず

04:21:21.767880 Error 0 (Critical:0)
04:21:21.767888 PASSED: true
04:21:21.767892 SCORE: 1858 (+1858 0(0%))

測定

アクセスログ（rankingとplayerがリクエスト数多い、billingは時間かかっている）

+-------+--------+------------------------------------------------+-----+-----+-----+-----+----------+-------+-------+--------+--------+--------+--------+
| COUNT | METHOD |                      URI                       | 2XX | 3XX | 4XX | 5XX |   SUM    |  MIN  |  AVG  |  P90   |  P95   |  P99   |  MAX   |
+-------+--------+------------------------------------------------+-----+-----+-----+-----+----------+-------+-------+--------+--------+--------+--------+
| 432   | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 413 | 0   | 19  | 0   | 1024.672 | 0.004 | 2.372 | 5.668  | 6.076  | 7.644  | 7.928  |
| 503   | GET    | /api/player/player/[0-9a-z\-]+                 | 488 | 0   | 15  | 0   | 978.800  | 0.004 | 1.946 | 5.056  | 6.576  | 6.992  | 7.560  |
| 11    | GET    | /api/admin/tenants/billing                     | 9   | 0   | 2   | 0   | 102.356  | 3.108 | 9.305 | 15.988 | 21.612 | 21.612 | 21.612 |
| 36    | POST   | /api/organizer/competition/[0-9a-z\-]+/score   | 29  | 0   | 7   | 0   | 70.912   | 0.348 | 1.970 | 3.696  | 5.676  | 7.608  | 7.608  |
| 90    | GET    | /api/player/competitions                       | 85  | 0   | 5   | 0   | 27.980   | 0.008 | 0.311 | 1.344  | 1.388  | 2.908  | 2.908  |
| 9     | POST   | /api/organizer/players/add                     | 8   | 0   | 1   | 0   | 20.080   | 0.900 | 2.231 | 5.204  | 5.204  | 5.204  | 5.204  |
| 23    | POST   | /api/organizer/competition/[0-9a-z\-]+/finish  | 22  | 0   | 1   | 0   | 16.248   | 0.004 | 0.706 | 3.348  | 3.720  | 6.572  | 6.572  |
| 19    | GET    | /api/organizer/players                         | 19  | 0   | 0   | 0   | 12.968   | 0.004 | 0.683 | 2.580  | 3.356  | 3.356  | 3.356  |
| 16    | GET    | /api/organizer/billing                         | 16  | 0   | 0   | 0   | 10.844   | 0.056 | 0.678 | 2.044  | 2.664  | 2.664  | 2.664  |
| 26    | POST   | /api/organizer/competitions/add                | 25  | 0   | 1   | 0   | 4.736    | 0.008 | 0.182 | 0.264  | 0.532  | 2.136  | 2.136  |
| 1     | POST   | /initialize                                    | 1   | 0   | 0   | 0   | 2.780    | 2.780 | 2.780 | 2.780  | 2.780  | 2.780  | 2.780  |
| 11    | POST   | /api/admin/tenants/add                         | 6   | 0   | 5   | 0   | 0.456    | 0.004 | 0.041 | 0.060  | 0.152  | 0.152  | 0.152  |
| 7     | POST   | /api/organizer/player/[0-9a-z\-]+/disqualified | 6   | 0   | 1   | 0   | 0.072    | 0.004 | 0.010 | 0.020  | 0.020  | 0.020  | 0.020  |
| 1     | GET    | /css/app.83b4c321.css                          | 1   | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  | 0.000  |
| 1     | GET    | /js/app.3a4ec98c.js                            | 1   | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  | 0.000  |
| 1     | GET    | /index.html                                    | 1   | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  | 0.000  |
| 1     | GET    | /api/organizer/competitions                    | 1   | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  | 0.000  |
+-------+--------+------------------------------------------------+-----+-----+-----+-----+----------+-------+-------+--------+--------+--------+--------+

スロークエリ

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0xFCB05D4948FC2248 86.5559 62.3%  1321 0.0655  0.05 SELECT visit_history
#    2 0xA1FC19A5563AD0F5 47.1288 33.9%  7998 0.0059  0.00 REPLACE id_generator
# MISC 0xMISC              5.2702  3.8%  1997 0.0026   0.0 <9 ITEMS>

MySQLのCPU負荷が高い

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
  10778 mysql     20   0 1802284 558760  35456 S  99.7  14.7   0:31.40 mysqld
  11158 isucon    20   0  850600  60436   9484 S  17.6   1.6   0:05.16 bundle
  11160 isucon    20   0 1116716  54200   9444 S  14.6   1.4   0:05.11 bundle

やったこと

nginxのアクセスログとmysqlのsloq queryの設定反映コマンド

scp nginx/nginx.conf isu-app:nginx.conf
scp mysql/mysql.conf.d/mysqld.cnf isu-app:mysqld.cnf

ssh isu-app
sudo su - isucon

sudo chown root:root /home/ubuntu/nginx.conf
sudo mv /home/ubuntu/nginx.conf /etc/nginx/nginx.conf
sudo systemctl restart nginx.service

sudo chown root:root /home/ubuntu/mysqld.cnf
sudo mv /home/ubuntu/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf
sudo systemctl restart mysql.service

adminDB visit_history にINDEXをはる

スコア

少しさがる。他のところにネックがある？

04:38:36.145339 Error 0 (Critical:0)
04:38:36.145345 PASSED: true
04:38:36.145348 SCORE: 1665 (+1665 0(0%))

測定

該当のクエリはでなくなった

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0xA1FC19A5563AD0F5 41.3957 79.2%  7494 0.0055  0.00 REPLACE id_generator
#    2 0xFCB05D4948FC2248  6.5593 12.5%  2296 0.0029  0.02 SELECT visit_history
#    3 0x348974B3D332E575  2.4071  4.6%     1 2.4071  0.00 DELETE visit_history
# MISC 0xMISC              1.9149  3.7%  1746 0.0011   0.0 <8 ITEMS>

MySQLのCPU負荷は下がった

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
  10597 isucon    20   0 1122536  76132   9656 S  39.0   2.0   0:25.01 bundle
  10595 isucon    20   0  987780  72272   9828 S  29.3   1.9   0:18.02 bundle
  10208 mysql     20   0 1796328 524052  35208 S  23.0  13.8   0:21.56 mysqld

やったこと

INDEXをはる。created_atまでいれるとカバリングインデックスになるので高速になるらしい。

-- 300万レコードある
mysql> select count(*) from visit_history;
+----------+
| count(*) |
+----------+
|  3225256 |
+----------+
1 row in set (0.38 sec)

-- インデックスは tenant_id だけ
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

-- 実行計画
mysql> explain SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = '59' AND competition_id = '184b9b3e1' GROUP BY player_id;
+----+-------------+---------------+------------+------+---------------+---------------+---------+-------+-------+----------+-----------------------------------------------------+
| id | select_type | table         | partitions | type | possible_keys | key           | key_len | ref   | rows  | filtered | Extra                                               |
+----+-------------+---------------+------------+------+---------------+---------------+---------+-------+-------+----------+-----------------------------------------------------+
|  1 | SIMPLE      | visit_history | NULL       | ref  | tenant_id_idx | tenant_id_idx | 8       | const | 68070 |    10.00 | Using index condition; Using where; Using temporary |
+----+-------------+---------------+------------+------+---------------+---------------+---------+-------+-------+----------+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

-- インデックス追加
mysql> ALTER TABLE visit_history ADD INDEX idx_all_cover (tenant_id, competition_id, player_id, created_at);

-- 実行計画さいど
mysql> explain SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = '59' AND competition_id = '184b9b3e1' GROUP BY player_id;
+----+-------------+---------------+------------+------+-----------------------------+---------------+---------+-------------+------+----------+--------------------------+
| id | select_type | table         | partitions | type | possible_keys               | key           | key_len | ref         | rows | filtered | Extra                    |
+----+-------------+---------------+------------+------+-----------------------------+---------------+---------+-------------+------+----------+--------------------------+
|  1 | SIMPLE      | visit_history | NULL       | ref  | tenant_id_idx,idx_all_cover | idx_all_cover | 1030    | const,const | 2273 |   100.00 | Using where; Using index |
+----+-------------+---------------+------------+------+-----------------------------+---------------+---------+-------------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Ranking APIが重いのでひとまずループクエリをなくす

スコア

特にかわらず。flockとかで他のAPIがひっかかってそう

04:53:25.699101 Error 1 (Critical:1)
04:53:25.699108 PASSED: true
04:53:25.699115 SCORE: 1592 (+1768 -176(10%))

計測

rankingはCOUNTも少し増えて、MAXが少しだけ早くなったぐらいで平均とかは同じ

+-------+--------+------------------------------------------------+-----+-----+-----+-----+---------+-------+-------+-------+--------+--------+--------+
| COUNT | METHOD |                      URI                       | 2XX | 3XX | 4XX | 5XX |   SUM   |  MIN  |  AVG  |  P90  |  P95   |  P99   |  MAX   |
+-------+--------+------------------------------------------------+-----+-----+-----+-----+---------+-------+-------+-------+--------+--------+--------+
| 420   | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 394 | 0   | 26  | 0   | 928.300 | 0.004 | 2.210 | 5.248 | 7.152  | 7.764  | 12.808 |
# after
| 492   | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 443 | 0   | 49  | 0   | 1119.296 | 0.004 | 2.275 | 4.792 | 7.044 | 8.108  | 8.244  |

やったこと

player_scoreを取得する部分をplayerもJOINして取得する

-- BEFORE
SELECT *
FROM player_score
WHERE tenant_id = ? AND competition_id = ?
ORDER BY row_num DESC

-- AFTER
SELECT player_score.*, player.display_name
FROM player_score
JOIN player ON player.id = player_score.player_id
WHERE player_score.tenant_id = ? AND competition_id = ?
ORDER BY row_num DESC;

Score APIの追加のループクエリをなくす

スコア

1000ぐらいあがった

05:50:03.354914 Error 1 (Critical:1)
05:50:03.354920 PASSED: true
05:50:03.354923 SCORE: 3342 (+3713 -371(10%))

測定

scoreのレスポンスタイムは遅くなっているが、rankingやplayerの数が増えている。flock？

# Before
+-------+--------+------------------------------------------------+-----+-----+-----+-----+----------+-------+-------+-------+--------+--------+--------+
| COUNT | METHOD |                      URI                       | 2XX | 3XX | 4XX | 5XX |   SUM    |  MIN  |  AVG  |  P90  |  P95   |  P99   |  MAX   |
+-------+--------+------------------------------------------------+-----+-----+-----+-----+----------+-------+-------+-------+--------+--------+--------+
| 471   | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 445 | 0   | 26  | 0   | 1050.712 | 0.004 | 2.231 | 5.036 | 5.472  | 7.092  | 8.740  |
| 389   | GET    | /api/player/player/[0-9a-z\-]+                 | 351 | 0   | 38  | 0   | 927.960  | 0.008 | 2.386 | 5.104 | 7.044  | 7.552  | 7.824  |
| 22    | GET    | /api/admin/tenants/billing                     | 20  | 0   | 2   | 0   | 85.208   | 0.080 | 3.873 | 8.568 | 10.044 | 10.584 | 10.584 |
| 34    | POST   | /api/organizer/competition/[0-9a-z\-]+/score   | 28  | 0   | 5   | 1   | 68.952   | 0.004 | 2.028 | 4.508 | 6.008  | 7.580  | 7.580  |
| 110   | GET    | /api/player/competitions                       | 106 | 0   | 4   | 0   | 63.568   | 0.008 | 0.578 | 2.224 | 4.288  | 4.864  | 4.872  |
| 8     | POST   | /api/organizer/players/add                     | 7   | 0   | 1   | 0   | 25.924   | 1.348 | 3.240 | 8.656 | 8.656  | 8.656  | 8.656  |
| 20    | GET    | /api/organizer/players                         | 19  | 0   | 1   | 0   | 19.884   | 0.004 | 0.994 | 2.148 | 3.064  | 6.240  | 6.240  |
| 20    | POST   | /api/organizer/competition/[0-9a-z\-]+/finish  | 17  | 0   | 2   | 1   | 16.976   | 0.004 | 0.849 | 3.200 | 4.884  | 5.500  | 5.500  |
| 11    | GET    | /api/organizer/billing                         | 11  | 0   | 0   | 0   | 6.204    | 0.004 | 0.564 | 1.252 | 3.764  | 3.764  | 3.764  |
| 25    | POST   | /api/organizer/competitions/add                | 24  | 0   | 1   | 0   | 3.708    | 0.004 | 0.148 | 0.424 | 0.476  | 1.384  | 1.384  |
| 12    | POST   | /api/admin/tenants/add                         | 7   | 0   | 5   | 0   | 2.764    | 0.004 | 0.230 | 0.092 | 2.284  | 2.284  | 2.284  |
| 1     | POST   | /initialize                                    | 1   | 0   | 0   | 0   | 2.692    | 2.692 | 2.692 | 2.692 | 2.692  | 2.692  | 2.692  |
| 6     | POST   | /api/organizer/player/[0-9a-z\-]+/disqualified | 5   | 0   | 1   | 0   | 0.092    | 0.004 | 0.015 | 0.052 | 0.052  | 0.052  | 0.052  |
| 1     | GET    | /api/organizer/competitions                    | 1   | 0   | 0   | 0   | 0.004    | 0.004 | 0.004 | 0.004 | 0.004  | 0.004  | 0.004  |
| 1     | GET    | /css/app.83b4c321.css                          | 1   | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  |
| 1     | GET    | /js/app.3a4ec98c.js                            | 1   | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  |
| 1     | GET    | /index.html                                    | 1   | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  |
+-------+--------+------------------------------------------------+-----+-----+-----+-----+----------+-------+-------+-------+--------+--------+--------+

# After
+-------+--------+------------------------------------------------+------+-----+-----+-----+---------+-------+-------+--------+--------+--------+--------+
| COUNT | METHOD |                      URI                       | 2XX  | 3XX | 4XX | 5XX |   SUM   |  MIN  |  AVG  |  P90   |  P95   |  P99   |  MAX   |
+-------+--------+------------------------------------------------+------+-----+-----+-----+---------+-------+-------+--------+--------+--------+--------+
| 1426  | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 1382 | 0   | 44  | 0   | 717.864 | 0.004 | 0.503 | 1.548  | 2.604  | 5.076  | 7.980  |
| 973   | GET    | /api/player/player/[0-9a-z\-]+                 | 917  | 0   | 56  | 0   | 673.308 | 0.004 | 0.692 | 1.612  | 2.720  | 5.172  | 12.916 |
| 431   | GET    | /api/player/competitions                       | 418  | 0   | 13  | 0   | 183.044 | 0.004 | 0.425 | 1.000  | 2.572  | 4.996  | 5.188  |
| 48    | POST   | /api/organizer/competition/[0-9a-z\-]+/score   | 41   | 0   | 7   | 0   | 134.180 | 0.004 | 2.795 | 8.776  | 11.056 | 18.144 | 18.144 |
| 31    | GET    | /api/admin/tenants/billing                     | 29   | 0   | 2   | 0   | 79.124  | 0.756 | 2.552 | 5.224  | 9.792  | 15.428 | 15.428 |
| 9     | POST   | /api/organizer/players/add                     | 8    | 0   | 0   | 1   | 38.712  | 1.320 | 4.301 | 12.684 | 12.684 | 12.684 | 12.684 |
| 18    | GET    | /api/organizer/billing                         | 16   | 0   | 2   | 0   | 28.260  | 0.004 | 1.570 | 3.136  | 19.536 | 19.536 | 19.536 |
| 30    | POST   | /api/organizer/competition/[0-9a-z\-]+/finish  | 29   | 0   | 1   | 0   | 15.296  | 0.004 | 0.510 | 1.376  | 2.660  | 5.184  | 5.184  |
| 26    | GET    | /api/organizer/players                         | 26   | 0   | 0   | 0   | 8.676   | 0.004 | 0.334 | 1.716  | 1.800  | 1.888  | 1.888  |
| 35    | POST   | /api/organizer/competitions/add                | 34   | 0   | 1   | 0   | 6.596   | 0.008 | 0.188 | 0.636  | 1.416  | 1.500  | 1.500  |
| 1     | POST   | /initialize                                    | 1    | 0   | 0   | 0   | 2.776   | 2.776 | 2.776 | 2.776  | 2.776  | 2.776  | 2.776  |
| 10    | POST   | /api/organizer/player/[0-9a-z\-]+/disqualified | 9    | 0   | 1   | 0   | 1.800   | 0.004 | 0.180 | 0.148  | 1.560  | 1.560  | 1.560  |
| 12    | POST   | /api/admin/tenants/add                         | 7    | 0   | 5   | 0   | 0.520   | 0.004 | 0.043 | 0.112  | 0.132  | 0.132  | 0.132  |
| 1     | GET    | /css/app.83b4c321.css                          | 1    | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  | 0.000  |
| 1     | GET    | /js/app.3a4ec98c.js                            | 1    | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  | 0.000  |
| 1     | GET    | /index.html                                    | 1    | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  | 0.000  |
| 1     | GET    | /api/organizer/competitions                    | 1    | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  | 0.000  |
+-------+--------+------------------------------------------------+------+-----+-----+-----+---------+-------+-------+--------+--------+--------+--------+

やったこと

  • プレイヤーがみつからないを1クエリに変更
  • INSERTもBulk INSERTに変更

pumaのworkerを32、スレッドを1にする

スコア

スコア大きくあがる。ベンチマーカーの並列度が高いらしいからサーバーも並列数をあげるとスコアがあがるらしい

06:02:45.092143 Error 1 (Critical:1)
06:02:45.092149 PASSED: true
06:02:45.092155 SCORE: 6013 (+6681 -668(10%))

測定

リクエストは大きくさばけるようになっている

+-------+--------+------------------------------------------------+------+-----+-----+-----+---------+-------+-------+-------+--------+--------+--------+
| COUNT | METHOD |                      URI                       | 2XX  | 3XX | 4XX | 5XX |   SUM   |  MIN  |  AVG  |  P90  |  P95   |  P99   |  MAX   |
+-------+--------+------------------------------------------------+------+-----+-----+-----+---------+-------+-------+-------+--------+--------+--------+
| 2240  | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 2192 | 0   | 48  | 0   | 531.428 | 0.016 | 0.237 | 0.192 | 0.628  | 6.332  | 15.980 |
| 2008  | GET    | /api/player/player/[0-9a-z\-]+                 | 1933 | 0   | 75  | 0   | 450.064 | 0.004 | 0.224 | 0.564 | 0.956  | 3.188  | 9.704  |
| 73    | POST   | /api/organizer/competition/[0-9a-z\-]+/score   | 67   | 0   | 6   | 0   | 137.272 | 0.004 | 1.880 | 4.208 | 5.000  | 19.144 | 19.144 |
| 36    | GET    | /api/admin/tenants/billing                     | 34   | 0   | 2   | 0   | 84.852  | 0.004 | 2.357 | 4.732 | 11.032 | 12.816 | 12.816 |
| 10    | POST   | /api/organizer/players/add                     | 8    | 0   | 1   | 1   | 36.200  | 1.448 | 3.620 | 5.380 | 6.204  | 6.204  | 6.204  |
| 628   | GET    | /api/player/competitions                       | 611  | 0   | 17  | 0   | 23.900  | 0.004 | 0.038 | 0.100 | 0.132  | 0.808  | 0.944  |
| 33    | GET    | /api/organizer/billing                         | 33   | 0   | 0   | 0   | 13.352  | 0.004 | 0.405 | 0.296 | 0.332  | 10.684 | 10.684 |
| 1     | POST   | /initialize                                    | 1    | 0   | 0   | 0   | 4.532   | 4.532 | 4.532 | 4.532 | 4.532  | 4.532  | 4.532  |
| 56    | POST   | /api/organizer/competitions/add                | 55   | 0   | 1   | 0   | 2.184   | 0.008 | 0.039 | 0.088 | 0.100  | 0.216  | 0.216  |
| 40    | GET    | /api/organizer/players                         | 40   | 0   | 0   | 0   | 1.380   | 0.004 | 0.035 | 0.072 | 0.120  | 0.368  | 0.368  |
| 50    | POST   | /api/organizer/competition/[0-9a-z\-]+/finish  | 49   | 0   | 1   | 0   | 1.368   | 0.004 | 0.027 | 0.076 | 0.100  | 0.152  | 0.152  |
| 12    | POST   | /api/admin/tenants/add                         | 7    | 0   | 5   | 0   | 0.836   | 0.004 | 0.070 | 0.120 | 0.192  | 0.192  | 0.192  |
| 14    | POST   | /api/organizer/player/[0-9a-z\-]+/disqualified | 13   | 0   | 1   | 0   | 0.568   | 0.008 | 0.041 | 0.080 | 0.252  | 0.252  | 0.252  |
| 1     | GET    | /api/organizer/competitions                    | 1    | 0   | 0   | 0   | 0.004   | 0.004 | 0.004 | 0.004 | 0.004  | 0.004  | 0.004  |
| 1     | GET    | /css/app.83b4c321.css                          | 1    | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  |
| 1     | GET    | /js/app.3a4ec98c.js                            | 1    | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  |
| 1     | GET    | /index.html                                    | 1    | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  |
+-------+--------+------------------------------------------------+------+-----+-----+-----+---------+-------+-------+-------+--------+--------+--------+

そろそろ id_generator 対応したほうがよさげ

# Profile
# Rank Query ID           Response time  Calls R/Call V/M   Item
# ==== ================== ============== ===== ====== ===== ==============
#    1 0xA1FC19A5563AD0F5 131.5128 82.6% 12519 0.0105  0.00 REPLACE id_generator
#    2 0xFCB05D4948FC2248  12.7047  8.0%  4215 0.0030  0.03 SELECT visit_history
#    3 0x2A1217500A0400FE  11.1864  7.0%  2200 0.0051  0.00 INSERT visit_history
# MISC 0xMISC               3.8896  2.4%  7323 0.0005   0.0 <8 ITEMS>

やったこと

最初にやっておけばよかった

# Before
exec bundle exec puma -e production -p ${SERVER_APP_PORT:-3000} -w 2

# After
exec bundle exec puma -e production -p ${SERVER_APP_PORT:-3000} -w 32 -t 1:1

アトミック書き込みのためのflockをトランザクションに変更する

スコア

スコアは7500(+1500)ぐらいあがった。実行によっては6900とかもあったのでベンチマークの挙動やタイミングは一定ありそう

06:09:27.277699 Error 0 (Critical:0)
06:09:27.277706 PASSED: true
06:09:27.277713 SCORE: 7483 (+7483 0(0%))

測定

P99、MAXをみると遅いAPIがきもち短くなった。API間の競合が減ったから？

# Before
+-------+--------+------------------------------------------------+------+-----+-----+-----+---------+-------+-------+-------+--------+--------+--------+
| COUNT | METHOD |                      URI                       | 2XX  | 3XX | 4XX | 5XX |   SUM   |  MIN  |  AVG  |  P90  |  P95   |  P99   |  MAX   |
+-------+--------+------------------------------------------------+------+-----+-----+-----+---------+-------+-------+-------+--------+--------+--------+
| 2240  | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 2192 | 0   | 48  | 0   | 531.428 | 0.016 | 0.237 | 0.192 | 0.628  | 6.332  | 15.980 |
| 2008  | GET    | /api/player/player/[0-9a-z\-]+                 | 1933 | 0   | 75  | 0   | 450.064 | 0.004 | 0.224 | 0.564 | 0.956  | 3.188  | 9.704  |
| 73    | POST   | /api/organizer/competition/[0-9a-z\-]+/score   | 67   | 0   | 6   | 0   | 137.272 | 0.004 | 1.880 | 4.208 | 5.000  | 19.144 | 19.144 |
| 36    | GET    | /api/admin/tenants/billing                     | 34   | 0   | 2   | 0   | 84.852  | 0.004 | 2.357 | 4.732 | 11.032 | 12.816 | 12.816 |
| 10    | POST   | /api/organizer/players/add                     | 8    | 0   | 1   | 1   | 36.200  | 1.448 | 3.620 | 5.380 | 6.204  | 6.204  | 6.204  |
| 628   | GET    | /api/player/competitions                       | 611  | 0   | 17  | 0   | 23.900  | 0.004 | 0.038 | 0.100 | 0.132  | 0.808  | 0.944  |
| 33    | GET    | /api/organizer/billing                         | 33   | 0   | 0   | 0   | 13.352  | 0.004 | 0.405 | 0.296 | 0.332  | 10.684 | 10.684 |
| 1     | POST   | /initialize                                    | 1    | 0   | 0   | 0   | 4.532   | 4.532 | 4.532 | 4.532 | 4.532  | 4.532  | 4.532  |
| 56    | POST   | /api/organizer/competitions/add                | 55   | 0   | 1   | 0   | 2.184   | 0.008 | 0.039 | 0.088 | 0.100  | 0.216  | 0.216  |
| 40    | GET    | /api/organizer/players                         | 40   | 0   | 0   | 0   | 1.380   | 0.004 | 0.035 | 0.072 | 0.120  | 0.368  | 0.368  |
| 50    | POST   | /api/organizer/competition/[0-9a-z\-]+/finish  | 49   | 0   | 1   | 0   | 1.368   | 0.004 | 0.027 | 0.076 | 0.100  | 0.152  | 0.152  |
| 12    | POST   | /api/admin/tenants/add                         | 7    | 0   | 5   | 0   | 0.836   | 0.004 | 0.070 | 0.120 | 0.192  | 0.192  | 0.192  |
| 14    | POST   | /api/organizer/player/[0-9a-z\-]+/disqualified | 13   | 0   | 1   | 0   | 0.568   | 0.008 | 0.041 | 0.080 | 0.252  | 0.252  | 0.252  |
| 1     | GET    | /api/organizer/competitions                    | 1    | 0   | 0   | 0   | 0.004   | 0.004 | 0.004 | 0.004 | 0.004  | 0.004  | 0.004  |
| 1     | GET    | /css/app.83b4c321.css                          | 1    | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  |
| 1     | GET    | /js/app.3a4ec98c.js                            | 1    | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  |
| 1     | GET    | /index.html                                    | 1    | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  |
+-------+--------+------------------------------------------------+------+-----+-----+-----+---------+-------+-------+-------+--------+--------+--------+

# After
+-------+--------+------------------------------------------------+------+-----+-----+-----+---------+-------+-------+-------+-------+--------+--------+
| COUNT | METHOD |                      URI                       | 2XX  | 3XX | 4XX | 5XX |   SUM   |  MIN  |  AVG  |  P90  |  P95  |  P99   |  MAX   |
+-------+--------+------------------------------------------------+------+-----+-----+-----+---------+-------+-------+-------+-------+--------+--------+
| 2378  | GET    | /api/player/player/[0-9a-z\-]+                 | 2352 | 0   | 26  | 0   | 384.072 | 0.004 | 0.162 | 0.372 | 0.572 | 1.868  | 3.308  |
| 2484  | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 2463 | 0   | 21  | 0   | 313.812 | 0.004 | 0.126 | 0.228 | 0.356 | 3.072  | 4.284  |
| 72    | POST   | /api/organizer/competition/[0-9a-z\-]+/score   | 65   | 0   | 7   | 0   | 132.436 | 0.004 | 1.839 | 4.036 | 5.276 | 11.572 | 11.572 |
| 32    | GET    | /api/admin/tenants/billing                     | 30   | 0   | 2   | 0   | 91.908  | 2.640 | 2.872 | 5.452 | 8.144 | 10.100 | 10.100 |
| 10    | POST   | /api/organizer/players/add                     | 10   | 0   | 0   | 0   | 32.244  | 1.304 | 3.224 | 4.060 | 7.428 | 7.428  | 7.428  |
| 36    | GET    | /api/organizer/billing                         | 35   | 0   | 1   | 0   | 27.192  | 0.008 | 0.755 | 0.376 | 0.704 | 23.340 | 23.340 |
| 709   | GET    | /api/player/competitions                       | 699  | 0   | 10  | 0   | 17.472  | 0.008 | 0.025 | 0.048 | 0.108 | 0.272  | 0.408  |
| 1     | POST   | /initialize                                    | 1    | 0   | 0   | 0   | 3.596   | 3.596 | 3.596 | 3.596 | 3.596 | 3.596  | 3.596  |
| 51    | POST   | /api/organizer/competition/[0-9a-z\-]+/finish  | 50   | 0   | 1   | 0   | 1.984   | 0.004 | 0.039 | 0.104 | 0.136 | 0.160  | 0.160  |
| 57    | POST   | /api/organizer/competitions/add                | 56   | 0   | 1   | 0   | 1.672   | 0.008 | 0.029 | 0.040 | 0.104 | 0.196  | 0.196  |
| 41    | GET    | /api/organizer/players                         | 41   | 0   | 0   | 0   | 1.368   | 0.004 | 0.033 | 0.060 | 0.136 | 0.248  | 0.248  |
| 12    | POST   | /api/admin/tenants/add                         | 7    | 0   | 5   | 0   | 0.652   | 0.004 | 0.054 | 0.132 | 0.172 | 0.172  | 0.172  |
| 12    | POST   | /api/organizer/player/[0-9a-z\-]+/disqualified | 11   | 0   | 1   | 0   | 0.180   | 0.008 | 0.015 | 0.024 | 0.032 | 0.032  | 0.032  |
| 1     | GET    | /css/app.83b4c321.css                          | 1    | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  |
| 1     | GET    | /js/app.3a4ec98c.js                            | 1    | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  |
| 1     | GET    | /index.html                                    | 1    | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  |
| 1     | GET    | /api/organizer/competitions                    | 1    | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  |
| 1     | GET    | /                                              | 0    | 0   | 1   | 0   | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  |
+-------+--------+------------------------------------------------+------+-----+-----+-----+---------+-------+-------+-------+-------+--------+--------+

やったこと

  • Score APIの flock_by_tenant_id でトランザクションをはるように変更
  • それ以外の flock_by_tenant_id は削除（トランザクションにより必要なくなった）
  • 補足：各flock_by_tenant_idの利用箇所で「player_scoreを読んでいるときに更新が走ると不整合が起こるのでロックを取得する」と書かれているのでヒントになっており、コードを照らし合わせながらみるとScore APIのために追加していたことがわかりそう

dispenseIDでMySQLを使うのをやめる

スコア

数100点さがる。（ベンチマーク実行すると1000点ぐらい上下あるので、ベンチマークのスコア安定しないなー）

06:42:57.641892 Error 1 (Critical:1)
06:42:57.641899 PASSED: true
06:42:57.641903 SCORE: 6311 (+7012 -701(10%))

測定

visit_historyがネックになったので狙い通り

-- Before
# Profile
# Rank Query ID           Response time  Calls R/Call V/M   Item
# ==== ================== ============== ===== ====== ===== ==============
#    1 0xA1FC19A5563AD0F5 133.0688 82.4% 14719 0.0090  0.00 REPLACE id_generator
#    2 0xFCB05D4948FC2248  13.7378  8.5%  3905 0.0035  0.04 SELECT visit_history
#    3 0x2A1217500A0400FE  10.7843  6.7%  2473 0.0044  0.00 INSERT visit_history
# MISC 0xMISC               3.8956  2.4%  8368 0.0005   0.0 <8 ITEMS>

-- After
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0xFCB05D4948FC2248  7.5496 43.1%  2477 0.0030  0.04 SELECT visit_history
#    2 0x2A1217500A0400FE  6.3568 36.3%  1473 0.0043  0.00 INSERT visit_history
#    3 0x348974B3D332E575  2.4870 14.2%     1 2.4870  0.00 DELETE visit_history
#    4 0x3635BEFFE5F2117E  0.8383  4.8%  4241 0.0002  0.01 SELECT tenant
# MISC 0xMISC              0.2937  1.7%  1502 0.0002   0.0 <6 ITEMS>

やったこと

遅かったdispense_idの生成をSecureRandom.uuidに変更

# システム全体で一意なIDを生成する
def dispense_id
  SecureRandom.uuid
  # last_exception = nil
  # 100.times do |i|
  #   begin
  #     admin_db.xquery('REPLACE INTO id_generator (stub) VALUES (?)', 'a')
  #   rescue Mysql2::Error => e
  #     if e.error_number == 1213 # deadlock
  #       last_exception = e
  #       next
  #     else
  #       raise e
  #     end
  #   end
  #   return admin_db.last_id.to_s(16)
  # end
  # raise last_exception
end

adminDB visit_historyの初期データをコンパクトにする

スコア

ベンチマーカーのスコアいまいち安定しないなー

07:01:08.411610 Error 6 (Critical:2)
07:01:08.411614 PASSED: true
07:01:08.411618 SCORE: 5044 (+6636 -1592(24%))

測定

SELECTはほぼR/Callが0.0005なのでほぼ大丈夫。INSERTはちょっと早くしたい。mysqlのbinいじる

-- Before
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0xFCB05D4948FC2248  7.5496 43.1%  2477 0.0030  0.04 SELECT visit_history
#    2 0x2A1217500A0400FE  6.3568 36.3%  1473 0.0043  0.00 INSERT visit_history
#    3 0x348974B3D332E575  2.4870 14.2%     1 2.4870  0.00 DELETE visit_history
#    4 0x3635BEFFE5F2117E  0.8383  4.8%  4241 0.0002  0.01 SELECT tenant
# MISC 0xMISC              0.2937  1.7%  1502 0.0002   0.0 <6 ITEMS>

-- After
# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x2A1217500A0400FE  9.9352 77.0%  1923 0.0052  0.02 INSERT visit_history
#    2 0xFCB05D4948FC2248  1.5512 12.0%  3270 0.0005  0.00 SELECT visit_history
#    3 0x3635BEFFE5F2117E  0.9550  7.4%  5858 0.0002  0.00 SELECT tenant
# MISC 0xMISC              0.4618  3.6%  1962 0.0002   0.0 <7 ITEMS>

INSERT INTO visit_history (player_id, tenant_id, competition_id, created_at, updated_at) VALUES ('69dce6e7-6b2b-456e-8490-7b950aa41652', '2', 'ffd5a0eb-2220-4228-807a-6a9f9f88f80e', '1684565370', '1684565370')\G

やったこと

visit_historyデータを圧縮（322万行->20万行)

MySQLに入っているvisit_historyデータは100テナントで322万行の巨大データですが、「アクセスをしたかどうか」だけがわかれば大丈夫というアプリケーションの作りになっているため、全件を保持しておく必要はありません。 初期データ加工としてテナントID、大会ID、プレイヤーIDでGROUP BYして min(created_at) / min(updated_at)の値を1行とってきて入れ直すようにしてやれば20万行程度に減ります。私の場合は visit_historyと同じスキーマでvisit_history_tmpテーブルを作って一旦全データをそちらに移し、visit_historyテーブルに入れ直しました。これはアプリの中ではなくインフラ作業として行います。

CREATE TABLE `visit_history_tmp` (
  `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,
  INDEX `tenant_id_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;

insert into visit_history_tmp select * from visit_history;
truncate visit_history;
insert into visit_history select player_id, tenant_id, competition_id,
  min(created_at) as created_at, min(updated_at) as updated_at
  from visit_history_tmp
  group by player_id, tenant_id, competition_id;

select count(*) from visit_history_tmp; -- 3226593
select count(*) from visit_history; -- 201156

※ベンチマーカーのファイルディスクリプタの上限を追加

$ ulimit -n
1024

# /etc/security/limits.conf
isucon hard nofile 10000
isucon soft nofile 10000

$ ulimit -n
10000

ログのフラッシュを1秒ごとにする & バイナリログ無効化

スコア

いまいちあがらない

07:09:58.599684 Error 3 (Critical:2)
07:09:58.599689 PASSED: true
07:09:58.599693 SCORE: 5038 (+6377 -1339(21%))

計測

INSERT visit_historyもSELECTと同じぐらいになったのでSQLの改善はほぼOKそう

-- Before
# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x2A1217500A0400FE  9.9352 77.0%  1923 0.0052  0.02 INSERT visit_history
#    2 0xFCB05D4948FC2248  1.5512 12.0%  3270 0.0005  0.00 SELECT visit_history
#    3 0x3635BEFFE5F2117E  0.9550  7.4%  5858 0.0002  0.00 SELECT tenant
# MISC 0xMISC              0.4618  3.6%  1962 0.0002   0.0 <7 ITEMS>

-- After
# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0xFCB05D4948FC2248  1.2296 46.2%  2896 0.0004  0.00 SELECT visit_history
#    2 0x3635BEFFE5F2117E  0.6797 25.5%  4233 0.0002  0.00 SELECT tenant
#    3 0x2A1217500A0400FE  0.3376 12.7%  1499 0.0002  0.00 INSERT visit_history
#    4 0x348974B3D332E575  0.2031  7.6%     1 0.2031  0.00 DELETE visit_history
#    5 0xA0719EAF93CC4D19  0.1901  7.1%  1499 0.0001  0.00 SELECT tenant
# MISC 0xMISC              0.0224  0.8%    34 0.0007   0.0 <5 ITEMS>

AVGは1秒以下だが、MAXが長いものがある。あと、billingが遅い

+-------+--------+------------------------------------------------+------+-----+-----+-----+----------+-------+-------+--------+--------+--------+--------+
| COUNT | METHOD |                      URI                       | 2XX  | 3XX | 4XX | 5XX |   SUM    |  MIN  |  AVG  |  P90   |  P95   |  P99   |  MAX   |
+-------+--------+------------------------------------------------+------+-----+-----+-----+----------+-------+-------+--------+--------+--------+--------+
| 2445  | GET    | /api/player/player/[0-9a-z\-]+                 | 2376 | 0   | 69  | 0   | 2362.856 | 0.004 | 0.966 | 1.800  | 2.040  | 2.708  | 25.892 |
| 1610  | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 1575 | 0   | 35  | 0   | 1073.304 | 0.004 | 0.667 | 1.424  | 1.692  | 4.516  | 27.812 |
| 324   | GET    | /api/player/competitions                       | 316  | 0   | 8   | 0   | 98.672   | 0.004 | 0.305 | 1.024  | 1.116  | 1.976  | 2.240  |
| 26    | GET    | /api/admin/tenants/billing                     | 24   | 0   | 2   | 0   | 96.136   | 0.004 | 3.698 | 10.436 | 11.124 | 14.872 | 14.872 |
| 67    | POST   | /api/organizer/competition/[0-9a-z\-]+/score   | 61   | 0   | 6   | 0   | 35.304   | 0.008 | 0.527 | 1.416  | 1.476  | 8.064  | 8.064  |
| 55    | POST   | /api/organizer/competitions/add                | 52   | 0   | 2   | 1   | 21.148   | 0.004 | 0.385 | 0.820  | 1.484  | 7.612  | 7.612  |
| 36    | GET    | /api/organizer/billing                         | 35   | 0   | 1   | 0   | 21.000   | 0.004 | 0.583 | 2.188  | 2.764  | 3.048  | 3.048  |
| 49    | POST   | /api/organizer/competition/[0-9a-z\-]+/finish  | 47   | 0   | 1   | 1   | 20.748   | 0.004 | 0.423 | 1.048  | 1.112  | 7.232  | 7.232  |
| 46    | GET    | /api/organizer/players                         | 45   | 0   | 1   | 0   | 17.716   | 0.004 | 0.385 | 1.176  | 1.236  | 1.916  | 1.916  |
| 14    | POST   | /api/organizer/players/add                     | 14   | 0   | 0   | 0   | 17.000   | 0.648 | 1.214 | 1.932  | 1.976  | 1.976  | 1.976  |
| 11    | POST   | /api/organizer/player/[0-9a-z\-]+/disqualified | 10   | 0   | 1   | 0   | 2.216    | 0.008 | 0.201 | 0.596  | 1.332  | 1.332  | 1.332  |
| 12    | POST   | /api/admin/tenants/add                         | 7    | 0   | 5   | 0   | 0.732    | 0.004 | 0.061 | 0.128  | 0.184  | 0.184  | 0.184  |
| 1     | POST   | /initialize                                    | 1    | 0   | 0   | 0   | 0.488    | 0.488 | 0.488 | 0.488  | 0.488  | 0.488  | 0.488  |
| 1     | GET    | /api/organizer/competitions                    | 1    | 0   | 0   | 0   | 0.012    | 0.012 | 0.012 | 0.012  | 0.012  | 0.012  | 0.012  |
| 1     | GET    | /css/app.83b4c321.css                          | 1    | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  | 0.000  |
| 1     | GET    | /js/app.3a4ec98c.js                            | 1    | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  | 0.000  |
| 1     | GET    | /index.html                                    | 1    | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000  | 0.000  | 0.000  | 0.000  |
+-------+--------+------------------------------------------------+------+-----+-----+-----+----------+-------+-------+--------+--------+--------+--------+

やったこと

以下をmysqld.cnfに追加

# コミットごとに更新データをログに書き、1秒ごとにログをフラッシュ
innodb_flush_log_at_trx_commit = 2
# バイナリログを無効化する
disable-log-bin = 1

ファイルを更新

scp mysql/mysql.conf.d/mysqld.cnf isu-app:mysqld.cnf

ssh isu-app
sudo su - isucon

sudo chown root:root /home/ubuntu/mysqld.cnf
sudo mv /home/ubuntu/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf
sudo systemctl restart mysql.service

Finish APIでBillingReportを生成する

スコア

スコアは大きくあがった。

これがきっちり炸裂すると、スコアがいきなり激増します。わたしが事前解答でやったときは開発中のベンチマークスコアが5倍になりました。

でも3倍ぐらいなのでなにかたりないのかも

08:38:27.148288 Error 5 (Critical:0)
08:38:27.148294 PASSED: true
08:38:27.148298 SCORE: 14002 (+14738 -736(5%))

計測

Billing APIの平均はかなり早くなった。上位のAPIの平均遅いな。特にMAXかかりすぎだけどなぜ

+-------+--------+------------------------------------------------+------+-----+-----+-----+----------+-------+-------+-------+-------+--------+--------+
| COUNT | METHOD |                      URI                       | 2XX  | 3XX | 4XX | 5XX |   SUM    |  MIN  |  AVG  |  P90  |  P95  |  P99   |  MAX   |
+-------+--------+------------------------------------------------+------+-----+-----+-----+----------+-------+-------+-------+-------+--------+--------+
| 5503  | GET    | /api/player/player/[0-9a-z\-]+                 | 5332 | 0   | 168 | 3   | 7847.077 | 0.004 | 1.426 | 2.068 | 2.624 | 3.428  | 30.000 |
| 3609  | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 3532 | 0   | 77  | 0   | 4839.259 | 0.004 | 1.341 | 2.108 | 2.740 | 3.564  | 8.309  |
| 839   | GET    | /api/player/competitions                       | 833  | 0   | 6   | 0   | 672.098  | 0.004 | 0.801 | 1.540 | 1.776 | 2.148  | 3.109  |
| 139   | POST   | /api/organizer/competition/[0-9a-z\-]+/score   | 132  | 0   | 7   | 0   | 168.598  | 0.004 | 1.213 | 2.016 | 2.212 | 7.533  | 7.956  |
| 123   | POST   | /api/organizer/competition/[0-9a-z\-]+/finish  | 112  | 0   | 11  | 0   | 146.953  | 0.004 | 1.195 | 1.936 | 2.264 | 3.432  | 6.692  |
| 125   | GET    | /api/organizer/players                         | 123  | 0   | 2   | 0   | 107.280  | 0.004 | 0.858 | 1.524 | 1.768 | 3.048  | 3.344  |
| 103   | GET    | /api/organizer/billing                         | 100  | 0   | 3   | 0   | 99.988   | 0.032 | 0.971 | 1.668 | 1.740 | 2.492  | 3.388  |
| 130   | POST   | /api/organizer/competitions/add                | 128  | 0   | 2   | 0   | 99.896   | 0.012 | 0.768 | 1.444 | 1.492 | 1.756  | 3.188  |
| 27    | POST   | /api/organizer/players/add                     | 27   | 0   | 0   | 0   | 77.870   | 0.784 | 2.884 | 3.584 | 3.716 | 18.260 | 18.260 |
| 219   | GET    | /api/admin/tenants/billing                     | 218  | 0   | 1   | 0   | 62.941   | 0.008 | 0.287 | 1.204 | 1.512 | 1.920  | 3.356  |
| 27    | POST   | /api/admin/tenants/add                         | 22   | 0   | 5   | 0   | 6.968    | 0.004 | 0.258 | 0.692 | 1.304 | 1.548  | 1.548  |
| 7     | POST   | /api/organizer/player/[0-9a-z\-]+/disqualified | 6    | 0   | 1   | 0   | 2.912    | 0.008 | 0.416 | 1.372 | 1.372 | 1.372  | 1.372  |
| 1     | POST   | /initialize                                    | 1    | 0   | 0   | 0   | 1.636    | 1.636 | 1.636 | 1.636 | 1.636 | 1.636  | 1.636  |
| 1     | GET    | /api/organizer/competitions                    | 1    | 0   | 0   | 0   | 0.004    | 0.004 | 0.004 | 0.004 | 0.004 | 0.004  | 0.004  |
| 1     | GET    | /css/app.83b4c321.css                          | 1    | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  |
| 1     | GET    | /js/app.3a4ec98c.js                            | 1    | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  |
| 1     | GET    | /index.html                                    | 1    | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  |
+-------+--------+------------------------------------------------+------+-----+-----+-----+----------+-------+-------+-------+-------+--------+--------+

十分速いが、tenantの存在確認SQLが40%をしめている。MySQLのネックはかなり少ないので劣後

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x3635BEFFE5F2117E  1.5705 40.2% 10379 0.0002  0.00 SELECT tenant
#    2 0x2A1217500A0400FE  1.0481 26.9%  3546 0.0003  0.01 INSERT visit_history
#    3 0xA0719EAF93CC4D19  0.4607 11.8%  3546 0.0001  0.00 SELECT tenant
#    4 0x25873E529CD44A70  0.3083  7.9%  1959 0.0002  0.00 SELECT billing_report
#    5 0x348974B3D332E575  0.2017  5.2%     1 0.2017  0.00 DELETE visit_history
#    6 0xCE0293B4FFBDE858  0.0661  1.7%   218 0.0003  0.00 SELECT tenant
#    7 0xFCB05D4948FC2248  0.0464  1.2%   112 0.0004  0.00 SELECT visit_history
#    8 0x1CE7B47AF729810C  0.0446  1.1%     1 0.0446  0.00 TRUNCATE
# MISC 0xMISC              0.1562  4.0%   265 0.0006   0.0 <32 ITEMS>

SELECT * FROM tenant WHERE name = 'badrequest-tenantid'\G

やったこと

billing_report_by_competitionメソッドの戻り値の構造をそのままテーブルスキーマにする

CREATE TABLE `billing_report` (
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `competition_id` VARCHAR(255) NOT NULL,
  `competition_title` VARCHAR(255) NOT NULL,
  `player_count` BIGINT NOT NULL,
  `visitor_count` BIGINT NOT NULL,
  `billing_player_yen` BIGINT NOT NULL,
  `billing_visitor_yen` BIGINT NOT NULL,
  `billing_yen` BIGINT NOT NULL,
  PRIMARY KEY(`tenant_id`, `competition_id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;

finish APIで billing_reportを作成する

# TODO: ノンブロッキングで非同期に処理したい
billing_report = billing_report_by_competition(tenant_db, v.tenant_id, id)
sql = <<~SQL
  INSERT INTO billing_report (tenant_id, competition_id, competition_title, player_count, visitor_count, billing_player_yen, billing_visitor_yen, billing_yen)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?)
SQL
admin_db.xquery(sql, v.tenant_id, billing_report.competition_id, billing_report.competition_title, billing_report.player_count, billing_report.visitor_count, billing_report.billing_player_yen, billing_report.billing_visitor_yen, billing_report.billing_yen)

POST /initialize時に既存データのbilling_reportを作成してダンプして初期化プロセスにいれる

# app.rbのPOST /initalize APIでbilling_reportを作成するように実装する
admin_db.xquery('SELECT * FROM tenant').each do |row|
  tenant_id = row.fetch(:id)
  connect_to_tenant_db(tenant_id) do |tenant_db|
    tenant_db.execute('SELECT * FROM competition').each do |comp_row|
      comp_id = comp_row.fetch('id')

      # TODO: ノンブロッキングで非同期に処理したい
      billing_report = billing_report_by_competition(tenant_db, tenant_id, comp_id)
      sql = <<~SQL
        INSERT INTO billing_report (tenant_id, competition_id, competition_title, player_count, visitor_count, billing_player_yen, billing_visitor_yen, billing_yen)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
      SQL
      admin_db.xquery(sql, tenant_id, billing_report.competition_id, billing_report.competition_title, billing_report.player_count, billing_report.visitor_count, billing_report.billing_player_yen, billing_report.billing_visitor_yen, billing_report.billing_yen)
    end
  end
end

# curlでデータつくる
curl -X POST http://127.0.0.1:3000/initialize

# データができていることを確認する
select count(*) from billing_report; -- 1196

# ダンプする
cd /home/isucon/webapp/sql

ISUCON_DB_HOST=${ISUCON_DB_HOST:-127.0.0.1}
ISUCON_DB_PORT=${ISUCON_DB_PORT:-3306}
ISUCON_DB_USER=${ISUCON_DB_USER:-isucon}
ISUCON_DB_PASSWORD=${ISUCON_DB_PASSWORD:-isucon}
ISUCON_DB_NAME=${ISUCON_DB_NAME:-isuports}

mysqldump -u "$ISUCON_DB_USER" \
  -p"$ISUCON_DB_PASSWORD" \
  --host "$ISUCON_DB_HOST" \
  --port "$ISUCON_DB_PORT" \
  "$ISUCON_DB_NAME" \
  billing_report > billing_report_dump.sql

# POST /initialize時にtruncateとdumpからrestoreするためにinit.shに追記
mysql -u"$ISUCON_DB_USER" \
  -p"$ISUCON_DB_PASSWORD" \
  --host "$ISUCON_DB_HOST" \
  --port "$ISUCON_DB_PORT" \
  "$ISUCON_DB_NAME" -e "truncate billing_report;"

mysql -u"$ISUCON_DB_USER" \
  -p"$ISUCON_DB_PASSWORD" \
  --host "$ISUCON_DB_HOST" \
  --port "$ISUCON_DB_PORT" \
  "$ISUCON_DB_NAME" < billing_report_dump.sql

# initializeの実装はコメントアウトしておく

AdminのBilling APIでbilling_reportテーブルを使って請求金額をだす

billing_yen = 0
admin_db.xquery('SELECT sum(billing_yen) as billing_yen FROM billing_report WHERE tenant_id = ?', t.id).each do |billing_row|
  billing_yen += billing_row.fetch(:billing_yen).to_i
end

OrganizerのBilling APIでもbilling_reportテーブルを使って請求金額をだす。レコードがない場合は

# 力ざわでゴリゴリ
tenant_db.execute('SELECT * FROM competition WHERE tenant_id=? ORDER BY created_at DESC', [v.tenant_id]) do |row|
  comp_ids << row['id']
  comp_title_map[row['id']] ||= row['title']
  # comp = CompetitionRow.new(row)
  # reports.push(billing_report_by_competition(tenant_db, v.tenant_id, comp.id).to_h)
  # puts "-" * 100
  # puts "before: tenant_id: #{v.tenant_id}, comp_id: #{row['id']}, report_row: #{reports.last}"
  # puts "after: tenant_id: #{v.tenant_id}, comp_id: #{row['id']}, report_row: #{billing_report_by_competition(tenant_db, v.tenant_id, row['id']).to_h}"
end

comp_id_map = {}
comp_id_cond = comp_ids.map { "'#{_1}'" }.join(',')
admin_db.xquery("SELECT * FROM billing_report WHERE tenant_id = #{v.tenant_id} AND competition_id IN (#{comp_id_cond})").each do |billing_row|
  comp_id_map[billing_row[:competition_id]] = billing_row
end

comp_ids.each do |comp_id|
  billing_row = comp_id_map[comp_id]
  if billing_row
    reports.push({
      competition_id: billing_row.fetch(:competition_id),
      competition_title: billing_row.fetch(:competition_title),
      player_count: billing_row.fetch(:player_count),
      visitor_count: billing_row.fetch(:visitor_count),
      billing_player_yen: billing_row.fetch(:billing_player_yen),
      billing_visitor_yen: billing_row.fetch(:billing_visitor_yen),
      billing_yen: billing_row.fetch(:billing_yen)
    })
  else
    reports.push({
      competition_id: comp_id,
      competition_title: comp_title_map[comp_id],
      player_count: 0,
      visitor_count: 0,
      billing_player_yen: 0,
      billing_visitor_yen: 0,
      billing_yen: 0,
    })
  end
end

※1番目の競技用マシンのファイルディスクリプタの上限を追加

$ ulimit -n
1024

# /etc/security/limits.conf
isucon hard nofile 10000
isucon soft nofile 10000

$ ulimit -n
10000

tenantDB player_scoreにINDEXをはる

スコア

エラーでスコアは変わらず。でもエラーなければ2万点。ちょっとエラーがでやすくなってきた（RankingやPlayer Addでタイムアウトエラーおきているぽい、MAXが長いからそこぽい）

08:53:16.187810 Error 6 (Critical:3)
08:53:16.187817 PASSED: true
08:53:16.187821 SCORE: 14038 (+20952 -6914(33%))

測定

全体的にリクエストをよりさばけるようになった。レスポンスタイム伸びてないからよくわからん

# Before
+-------+--------+------------------------------------------------+------+-----+-----+-----+----------+-------+-------+-------+-------+--------+--------+
| COUNT | METHOD |                      URI                       | 2XX  | 3XX | 4XX | 5XX |   SUM    |  MIN  |  AVG  |  P90  |  P95  |  P99   |  MAX   |
+-------+--------+------------------------------------------------+------+-----+-----+-----+----------+-------+-------+-------+-------+--------+--------+
| 5503  | GET    | /api/player/player/[0-9a-z\-]+                 | 5332 | 0   | 168 | 3   | 7847.077 | 0.004 | 1.426 | 2.068 | 2.624 | 3.428  | 30.000 |
| 3609  | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 3532 | 0   | 77  | 0   | 4839.259 | 0.004 | 1.341 | 2.108 | 2.740 | 3.564  | 8.309  |
| 839   | GET    | /api/player/competitions                       | 833  | 0   | 6   | 0   | 672.098  | 0.004 | 0.801 | 1.540 | 1.776 | 2.148  | 3.109  |
| 139   | POST   | /api/organizer/competition/[0-9a-z\-]+/score   | 132  | 0   | 7   | 0   | 168.598  | 0.004 | 1.213 | 2.016 | 2.212 | 7.533  | 7.956  |
| 123   | POST   | /api/organizer/competition/[0-9a-z\-]+/finish  | 112  | 0   | 11  | 0   | 146.953  | 0.004 | 1.195 | 1.936 | 2.264 | 3.432  | 6.692  |
| 125   | GET    | /api/organizer/players                         | 123  | 0   | 2   | 0   | 107.280  | 0.004 | 0.858 | 1.524 | 1.768 | 3.048  | 3.344  |
| 103   | GET    | /api/organizer/billing                         | 100  | 0   | 3   | 0   | 99.988   | 0.032 | 0.971 | 1.668 | 1.740 | 2.492  | 3.388  |
| 130   | POST   | /api/organizer/competitions/add                | 128  | 0   | 2   | 0   | 99.896   | 0.012 | 0.768 | 1.444 | 1.492 | 1.756  | 3.188  |
| 27    | POST   | /api/organizer/players/add                     | 27   | 0   | 0   | 0   | 77.870   | 0.784 | 2.884 | 3.584 | 3.716 | 18.260 | 18.260 |
| 219   | GET    | /api/admin/tenants/billing                     | 218  | 0   | 1   | 0   | 62.941   | 0.008 | 0.287 | 1.204 | 1.512 | 1.920  | 3.356  |
| 27    | POST   | /api/admin/tenants/add                         | 22   | 0   | 5   | 0   | 6.968    | 0.004 | 0.258 | 0.692 | 1.304 | 1.548  | 1.548  |
| 7     | POST   | /api/organizer/player/[0-9a-z\-]+/disqualified | 6    | 0   | 1   | 0   | 2.912    | 0.008 | 0.416 | 1.372 | 1.372 | 1.372  | 1.372  |
| 1     | POST   | /initialize                                    | 1    | 0   | 0   | 0   | 1.636    | 1.636 | 1.636 | 1.636 | 1.636 | 1.636  | 1.636  |
| 1     | GET    | /api/organizer/competitions                    | 1    | 0   | 0   | 0   | 0.004    | 0.004 | 0.004 | 0.004 | 0.004 | 0.004  | 0.004  |
| 1     | GET    | /css/app.83b4c321.css                          | 1    | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  |
| 1     | GET    | /js/app.3a4ec98c.js                            | 1    | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  |
| 1     | GET    | /index.html                                    | 1    | 0   | 0   | 0   | 0.000    | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  |
+-------+--------+------------------------------------------------+------+-----+-----+-----+----------+-------+-------+-------+-------+--------+--------+

# After
+-------+--------+------------------------------------------------+------+-----+-----+-----+-----------+-------+-------+-------+-------+-------+--------+
| COUNT | METHOD |                      URI                       | 2XX  | 3XX | 4XX | 5XX |    SUM    |  MIN  |  AVG  |  P90  |  P95  |  P99  |  MAX   |
+-------+--------+------------------------------------------------+------+-----+-----+-----+-----------+-------+-------+-------+-------+-------+--------+
| 7914  | GET    | /api/player/player/[0-9a-z\-]+                 | 7719 | 0   | 195 | 0   | 11415.400 | 0.016 | 1.442 | 2.120 | 2.272 | 2.388 | 6.360  |
| 5049  | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 4918 | 0   | 131 | 0   | 6958.272  | 0.008 | 1.378 | 2.224 | 2.388 | 2.552 | 30.012 |
| 1161  | GET    | /api/player/competitions                       | 1150 | 0   | 11  | 0   | 1054.104  | 0.004 | 0.908 | 1.932 | 2.064 | 2.352 | 2.420  |
| 189   | POST   | /api/organizer/competition/[0-9a-z\-]+/score   | 183  | 0   | 6   | 0   | 238.612   | 0.008 | 1.262 | 2.272 | 2.352 | 2.728 | 3.884  |
| 184   | POST   | /api/organizer/competitions/add                | 177  | 0   | 4   | 3   | 194.392   | 0.004 | 1.056 | 1.980 | 2.096 | 7.876 | 8.472  |
| 168   | POST   | /api/organizer/competition/[0-9a-z\-]+/finish  | 158  | 0   | 10  | 0   | 189.312   | 0.008 | 1.127 | 2.116 | 2.172 | 2.344 | 2.396  |
| 181   | GET    | /api/organizer/players                         | 176  | 0   | 5   | 0   | 186.980   | 0.004 | 1.033 | 2.036 | 2.188 | 3.652 | 3.956  |
| 148   | GET    | /api/organizer/billing                         | 146  | 0   | 2   | 0   | 159.712   | 0.016 | 1.079 | 2.044 | 2.116 | 2.344 | 2.344  |
| 36    | POST   | /api/organizer/players/add                     | 35   | 0   | 1   | 0   | 119.904   | 0.788 | 3.331 | 4.824 | 5.296 | 5.360 | 5.360  |
| 341   | GET    | /api/admin/tenants/billing                     | 340  | 0   | 1   | 0   | 63.760    | 0.004 | 0.187 | 0.688 | 1.516 | 2.016 | 2.364  |
| 35    | POST   | /api/admin/tenants/add                         | 30   | 0   | 5   | 0   | 7.132     | 0.004 | 0.204 | 0.408 | 0.800 | 1.568 | 1.568  |
| 1     | POST   | /initialize                                    | 1    | 0   | 0   | 0   | 4.472     | 4.472 | 4.472 | 4.472 | 4.472 | 4.472 | 4.472  |
| 8     | POST   | /api/organizer/player/[0-9a-z\-]+/disqualified | 7    | 0   | 1   | 0   | 3.672     | 0.004 | 0.459 | 1.932 | 1.932 | 1.932 | 1.932  |
| 1     | GET    | /api/organizer/competitions                    | 1    | 0   | 0   | 0   | 0.004     | 0.004 | 0.004 | 0.004 | 0.004 | 0.004 | 0.004  |
| 1     | GET    | /css/app.83b4c321.css                          | 1    | 0   | 0   | 0   | 0.000     | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  |
| 1     | GET    | /js/app.3a4ec98c.js                            | 1    | 0   | 0   | 0   | 0.000     | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  |
| 1     | GET    | /index.html                                    | 1    | 0   | 0   | 0   | 0.000     | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  |
+-------+--------+------------------------------------------------+------+-----+-----+-----+-----------+-------+-------+-------+-------+-------+--------+

やったこと

初期のtenant_dbのテーブルにINDEXをはる（init.shでinital_data/*.dbからコピーしている）

cp -rp initial_data initial_data_backup
cd initial_data
for db in *.db; do echo "create index idx_score on player_score (tenant_id, competition_id, player_id);" | sqlite3 $db; done

新規テナント作成時にインデックスを追加する

-- /home/isucon/webapp/sql/tenant/10_schema.sql
create index idx_score on player_score (tenant_id, competition_id, player_id);

Ranking APIでランキング集計するのをやめる

スコア

エラーめっちゃでてる。scoreでエラーからのrankingでエラー？。playerが多いかも

08:44:57.088485 Error 46 (Critical:1)
08:44:57.088491 PASSED: true
08:44:57.088494 SCORE: 12061 (+26802 -14741(55%))

エラー

08:44:57.088030 ERROR[0] load: load-validation: POST /api/organizer/competition/bc7f815f-bc23-443b-8ed9-9e0ef62ece5e/score : expected([200]) != actual(500) tenant:zpf-y-1684658639 role:organizer playerID:organizer competitionID:bc7f815f-bc23-443b-8ed9-9e0ef62ece5e  CSV length:13017bytes
08:44:57.088048 ERROR[1] load: load-validation: Get "https://tzesx-s-1684658637.t.isucon.dev/api/player/player/c8b8603e-195a-45eb-811d-419ae48bdc7b": read tcp 192.168.0.21:47254->54.168.225.188:443: read: connection reset by peer tenant:tzesx-s-1684658637 role:player playerID:f207cb25-2b3d-46c7-af3a-3557748b6bcf playerID:c8b8603e-195a-45eb-811d-419ae48bdc7b
08:44:57.088058 ERROR[2] load: load-validation: Get "https://ymhy-xsp-1684658637.t.isucon.dev/api/player/player/ad205c68-d01a-439a-977f-3d0c1c3074a5": read tcp 192.168.0.21:47268->54.168.225.188:443: read: connection reset by peer tenant:ymhy-xsp-1684658637 role:player playerID:06c2e73a-e1b2-4f84-bd4c-c2be56f7bd8b playerID:ad205c68-d01a-439a-977f-3d0c1c3074a5
08:44:57.088064 ERROR[3] load: load-validation: Get "https://zpf-y-1684658639.t.isucon.dev/api/player/player/0933e0d6-3826-48a8-80e7-9f0dad2336e2": read tcp 192.168.0.21:47300->54.168.225.188:443: read: connection reset by peer tenant:zpf-y-1684658639 role:player playerID:a0871883-bfcf-497c-ba33-9918b36d9d75 playerID:0933e0d6-3826-48a8-80e7-9f0dad2336e2
08:44:57.088070 ERROR[4] load: load-validation: Get "https://c-xsr-1684658651.t.isucon.dev/api/player/player/04f86688-6364-4dfc-9e79-b9626eb57d69": read tcp 192.168.0.21:47316->54.168.225.188:443: read: connection reset by peer tenant:c-xsr-1684658651 role:player playerID:f6c4a170-c9fa-48b7-a12e-750d3cf9b46e playerID:04f86688-6364-4dfc-9e79-b9626eb57d69

測定

MAXはかなり短くなった

+-------+--------+------------------------------------------------+------+-----+-----+-----+-----------+-------+-------+-------+-------+-------+--------+
| COUNT | METHOD |                      URI                       | 2XX  | 3XX | 4XX | 5XX |    SUM    |  MIN  |  AVG  |  P90  |  P95  |  P99  |  MAX   |
+-------+--------+------------------------------------------------+------+-----+-----+-----+-----------+-------+-------+-------+-------+-------+--------+
| 5049  | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 4918 | 0   | 131 | 0   | 6958.272  | 0.008 | 1.378 | 2.224 | 2.388 | 2.552 | 30.012 |
# after
| 6625  | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 6410  | 0   | 214 | 1   | 7049.736  | 0.024 | 1.064 | 2.108 | 2.164 | 2.280 | 3.340 |

やったこと

Ranking APIは5000回、Score APIは200回と10~20倍の差がある。rankingはscore入稿時にしか変わらないので、score入校時にrankingの情報を生成しておく。

MySQLにランキング用のテーブルを追加

CREATE TABLE ranking (
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `competition_id` VARCHAR(255) NOT NULL,
  `ranking` INT NOT NULL,
  `score` BIGINT NOT NULL,
  `player_id` VARCHAR(255) NOT NULL,
  `player_display_name` TEXT NOT NULL,
  PRIMARY KEY (`tenant_id`, `competition_id`, `ranking`)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;

score入稿時にrankingレコードを作成する

upsert_rainkign(tenant_db, v.tenant_id, competitiotn_id)

初期化データのrankingレコードもを作成して、dumpをとって、initialize時に読み込む

# post '/initialize'に追加
admin_db.xquery('SELECT * FROM tenant').each do |row|
  tenant_id = row.fetch(:id)
  connect_to_tenant_db(tenant_id) do |tenant_db|
    tenant_db.execute('SELECT * FROM competition').each do |comp_row|
      competition_id = comp_row.fetch('id')

      upsert_ranking(tenant_db, tenant_id, competition_id)
    end
  end
end

# mysql
truncate ranking;
select count(*) from ranking; -- 0

# 初期データ作成
curl -X POST http://127.0.0.1:3000/initialize

# mysql
select count(*) from ranking; -- 180525

cd /home/isucon/webapp/sql

ISUCON_DB_HOST=${ISUCON_DB_HOST:-127.0.0.1}
ISUCON_DB_PORT=${ISUCON_DB_PORT:-3306}
ISUCON_DB_USER=${ISUCON_DB_USER:-isucon}
ISUCON_DB_PASSWORD=${ISUCON_DB_PASSWORD:-isucon}
ISUCON_DB_NAME=${ISUCON_DB_NAME:-isuports}

mysqldump -u "$ISUCON_DB_USER" \
  -p"$ISUCON_DB_PASSWORD" \
  --host "$ISUCON_DB_HOST" \
  --port "$ISUCON_DB_PORT" \
  "$ISUCON_DB_NAME" \
  ranking > ranking_dump.sql

# POST /initialize時にtruncateとdumpからrestoreするためにinit.shに追記
mysql -u"$ISUCON_DB_USER" \
  -p"$ISUCON_DB_PASSWORD" \
  --host "$ISUCON_DB_HOST" \
  --port "$ISUCON_DB_PORT" \
  "$ISUCON_DB_NAME" -e "truncate ranking;"

mysql -u"$ISUCON_DB_USER" \
  -p"$ISUCON_DB_PASSWORD" \
  --host "$ISUCON_DB_HOST" \
  --port "$ISUCON_DB_PORT" \
  "$ISUCON_DB_NAME" < ranking_dump.sql

# initailizeのコードはコメントアウトしておく

Ranking APIでrankingテーブルを利用する

paged_ranks = []
admin_db.xquery('SELECT * FROM ranking WHERE tenant_id = ? AND competition_id = ? AND ranking > ? ORDER BY ranking ASC LIMIT 100', v.tenant_id, competition_id, rank_after).each do |row|
  paged_ranks << {
    rank: row.fetch(:ranking),
    score: row.fetch(:score),
    player_id: row.fetch(:player_id),
    player_display_name: row.fetch(:player_display_name),
  }
end

Nginxワーカーコネクション数を3倍にする

スコア

エラーがなくなってスコアが大きくあがる

ここまでくると場合によってはめちゃめちゃ並列数が上がっていて、nginxが色々悲鳴をあげることがあります。nginxが悲鳴をあげると、ベンチマーカー側でread: connection reset by peer とか EOF とかが出るようになります(私も夜中にこれをみて悲鳴をあげました)。

らしく、たしかにベンチマーカーで read: connection reset by peer や EOF はでていた

09:06:45.215100 Error 0 (Critical:0)
09:06:45.215105 PASSED: true
09:06:45.215108 SCORE: 29124 (+29124 0(0%))

測定

AVGもほぼ1秒以下になった。players/addは遅いけどリクエスト少ないので余力あればかな

+-------+--------+------------------------------------------------+-------+-----+-----+-----+-----------+-------+-------+-------+-------+-------+-------+
| COUNT | METHOD |                      URI                       |  2XX  | 3XX | 4XX | 5XX |    SUM    |  MIN  |  AVG  |  P90  |  P95  |  P99  |  MAX  |
+-------+--------+------------------------------------------------+-------+-----+-----+-----+-----------+-------+-------+-------+-------+-------+-------+
| 11965 | GET    | /api/player/player/[0-9a-z\-]+                 | 11616 | 0   | 349 | 0   | 13969.102 | 0.004 | 1.167 | 1.668 | 1.692 | 1.744 | 3.316 |
| 7212  | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 6975  | 0   | 237 | 0   | 7056.076  | 0.004 | 0.978 | 1.640 | 1.660 | 1.708 | 2.968 |
| 1726  | GET    | /api/player/competitions                       | 1675  | 0   | 51  | 0   | 1194.040  | 0.008 | 0.692 | 1.556 | 1.628 | 1.676 | 1.744 |
| 232   | POST   | /api/organizer/competition/[0-9a-z\-]+/score   | 216   | 0   | 16  | 0   | 320.152   | 0.004 | 1.380 | 2.272 | 2.348 | 3.208 | 3.924 |
| 214   | GET    | /api/organizer/players                         | 213   | 0   | 1   | 0   | 188.504   | 0.004 | 0.881 | 1.656 | 1.720 | 3.268 | 4.052 |
| 221   | POST   | /api/organizer/competitions/add                | 220   | 0   | 1   | 0   | 175.844   | 0.004 | 0.796 | 1.600 | 1.640 | 1.672 | 1.688 |
| 193   | POST   | /api/organizer/competition/[0-9a-z\-]+/finish  | 190   | 0   | 3   | 0   | 169.996   | 0.004 | 0.881 | 1.628 | 1.684 | 1.776 | 1.836 |
| 179   | GET    | /api/organizer/billing                         | 177   | 0   | 2   | 0   | 153.164   | 0.004 | 0.856 | 1.572 | 1.612 | 1.668 | 1.680 |
| 41    | POST   | /api/organizer/players/add                     | 41    | 0   | 0   | 0   | 146.317   | 0.668 | 3.569 | 5.033 | 5.129 | 5.621 | 5.621 |
| 389   | GET    | /api/admin/tenants/billing                     | 387   | 0   | 2   | 0   | 63.393    | 0.008 | 0.163 | 0.640 | 1.192 | 1.640 | 1.692 |
| 38    | POST   | /api/admin/tenants/add                         | 33    | 0   | 5   | 0   | 6.404     | 0.004 | 0.169 | 0.320 | 0.844 | 1.284 | 1.284 |
| 2     | POST   | /initialize                                    | 1     | 0   | 0   | 1   | 6.116     | 6.116 | 3.058 | 6.116 | 6.116 | 6.116 | 6.116 |
| 8     | POST   | /api/organizer/player/[0-9a-z\-]+/disqualified | 7     | 0   | 1   | 0   | 2.276     | 0.012 | 0.284 | 1.184 | 1.184 | 1.184 | 1.184 |
| 1     | GET    | /api/organizer/competitions                    | 1     | 0   | 0   | 0   | 0.012     | 0.012 | 0.012 | 0.012 | 0.012 | 0.012 | 0.012 |
| 1     | GET    | /css/app.83b4c321.css                          | 1     | 0   | 0   | 0   | 0.000     | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 1     | GET    | /js/app.3a4ec98c.js                            | 1     | 0   | 0   | 0   | 0.000     | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 1     | GET    | /index.html                                    | 1     | 0   | 0   | 0   | 0.000     | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 1     | GET    | /                                              | 0     | 0   | 1   | 0   | 0.000     | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
+-------+--------+------------------------------------------------+-------+-----+-----+-----+-----------+-------+-------+-------+-------+-------+-------+

やったこと

worker_connectionsの値を3倍にする

# /etc/nginx/nginx.conf

- worker_connections 768;
+ worker_connections 2304;

※ついでに、コネクションあたりの設定を修正

# /etc/nginx/sites-available/isuports.conf

  location ~* /(api|initialize) {
    proxy_set_header Host $host;
+   proxy_set_header Connection "";
    proxy_read_timeout    480;
    proxy_pass http://127.0.0.1:3000;
+   proxy_http_version 1.1;
+   keepalive_requests 10000;
  }

1台から3台構成にする

スコア

15:34:17.802700 Error 2 (Critical:2)
15:34:17.802707 PASSED: true
15:34:17.802712 SCORE: 62227 (+77783 -15556(20%))

測定

# 1台目
+-------+--------+------------------------------------------------+-------+-----+------+-----+-----------+-------+-------+-------+-------+--------+--------+
| COUNT | METHOD |                      URI                       |  2XX  | 3XX | 4XX  | 5XX |    SUM    |  MIN  |  AVG  |  P90  |  P95  |  P99   |  MAX   |
+-------+--------+------------------------------------------------+-------+-----+------+-----+-----------+-------+-------+-------+-------+--------+--------+
| 29113 | GET    | /api/player/player/[0-9a-z\-]+                 | 27995 | 0   | 1118 | 0   | 41675.116 | 0.004 | 1.431 | 2.208 | 2.252 | 3.132  | 5.208  |
| 18206 | GET    | /api/player/competition/[0-9a-z\-]+/ranking    | 17532 | 0   | 674  | 0   | 23811.420 | 0.004 | 1.308 | 2.204 | 2.244 | 3.136  | 5.208  |
| 4159  | GET    | /api/player/competitions                       | 4030  | 0   | 129  | 0   | 4558.500  | 0.004 | 1.096 | 2.164 | 2.216 | 3.160  | 8.456  |
| 645   | POST   | /api/organizer/competition/[0-9a-z\-]+/score   | 613   | 0   | 30   | 2   | 1066.800  | 0.004 | 1.654 | 2.824 | 2.940 | 3.680  | 3.932  |
| 172   | POST   | /api/organizer/players/add                     | 170   | 0   | 2    | 0   | 855.215   | 0.376 | 4.972 | 8.656 | 9.920 | 10.732 | 10.868 |
| 661   | GET    | /api/organizer/players                         | 645   | 0   | 16   | 0   | 787.797   | 0.004 | 1.192 | 2.208 | 2.276 | 2.464  | 3.332  |
| 652   | POST   | /api/organizer/competitions/add                | 633   | 0   | 19   | 0   | 752.808   | 0.004 | 1.155 | 2.172 | 2.224 | 3.084  | 5.012  |
| 560   | POST   | /api/organizer/competition/[0-9a-z\-]+/finish  | 538   | 0   | 22   | 0   | 707.804   | 0.008 | 1.264 | 2.176 | 2.272 | 3.176  | 3.696  |
| 525   | GET    | /api/organizer/billing                         | 507   | 0   | 18   | 0   | 648.488   | 0.004 | 1.235 | 2.140 | 2.208 | 2.368  | 3.248  |
| 2758  | GET    | /api/admin/tenants/billing                     | 2757  | 0   | 1    | 0   | 53.629    | 0.004 | 0.019 | 0.040 | 0.064 | 0.204  | 1.820  |
| 1     | POST   | /initialize                                    | 1     | 0   | 0    | 0   | 4.084     | 4.084 | 4.084 | 4.084 | 4.084 | 4.084  | 4.084  |
| 8     | POST   | /api/organizer/player/[0-9a-z\-]+/disqualified | 7     | 0   | 1    | 0   | 2.412     | 0.008 | 0.301 | 1.792 | 1.792 | 1.792  | 1.792  |
| 154   | POST   | /api/admin/tenants/add                         | 149   | 0   | 5    | 0   | 1.616     | 0.004 | 0.010 | 0.020 | 0.060 | 0.136  | 0.156  |
| 1     | GET    | /css/app.83b4c321.css                          | 1     | 0   | 0    | 0   | 0.000     | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  |
| 1     | GET    | /js/app.3a4ec98c.js                            | 1     | 0   | 0    | 0   | 0.000     | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  |
| 1     | GET    | /index.html                                    | 1     | 0   | 0    | 0   | 0.000     | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  |
| 1     | GET    | /api/organizer/competitions                    | 1     | 0   | 0    | 0   | 0.000     | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000  |
+-------+--------+------------------------------------------------+-------+-----+------+-----+-----------+-------+-------+-------+-------+--------+--------+
# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x2A1217500A0400FE 16.7082 30.1% 17574 0.0010  0.01 INSERT visit_history
#    2 0xF52EF45E61C2A8A1 12.5924 22.7% 17574 0.0007  0.01 SELECT ranking
#    3 0x3635BEFFE5F2117E  9.0378 16.3% 52824 0.0002  0.00 SELECT tenant
#    4 0x8A73F9A79805E499  4.8532  8.7%   622 0.0078  0.02 INSERT ranking
#    5 0x25873E529CD44A70  4.6689  8.4% 25504 0.0002  0.00 SELECT billing_report
#    6 0xA0719EAF93CC4D19  2.6446  4.8% 17574 0.0002  0.00 SELECT tenant
#    7 0x600DF43F6D410819  1.4798  2.7%    11 0.1345  0.03 INSERT ranking
#    8 0xCE0293B4FFBDE858  1.1114  2.0%  2757 0.0004  0.00 SELECT tenant
# MISC 0xMISC              2.4329  4.4%  3661 0.0007   0.0 <42 ITEMS>

やったこと

  • add tenant APIでtenant dbを作らない、代わりに、connect_to_dbでファイルがなければつくる
  • nginxの+worker_rlimit_nofileを12000に設定。socket() failed (24: Too many open files) while connecting to upstream がでていたから
  • 1台目のnginxでapi配下を2台目と3台目にホストの長さに応じて負荷分散する ※tenant dbがsqlite3なので単純なロードバランシングはできず
  • 2,3台目から1台目のMySQLに接続するようにする。また、1台目のMySQLはローカルのみしかアクセスできないのでフルオープンする

最終スコア

若干安定しないこともあるが

15:56:04.587202 Error 0 (Critical:0)
15:56:04.587208 PASSED: true
15:56:04.587214 SCORE: 86910 (+86910 0(0%))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment