ISUCON本実践 - Githubissues

手元PCからのベンチ実行とアプリサーバーからのベンチ実行の両方で最初自分は計測してしまっているが、基準がブレるので基本はどっちかに合わせる。

実際に、手元PCからのベンチ実行は5000点以降なかなか得点が伸びなかった。

nginxのログ形式変更 364c9afe095a7e4c8e51ed36043ae2760c592025

nginxのログをalpで解析

isucon@xxxxx:~/private_isu/webapp/log$ alp --sort sum -r json --file 20230909151928_access.log --matching-groups="/posts/[0-9]+,/image/\d+,/@\w+"
+-------+-----+------+-----+-----+-----+--------+--------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-------------+---------------+------------+
| COUNT | 1XX | 2XX  | 3XX | 4XX | 5XX | METHOD |        URI         |  MIN  |  MAX  |   SUM   |  AVG  |  P90  |  P95  |  P99  | STDDEV | MIN(BODY) |  MAX(BODY)  |   SUM(BODY)   | AVG(BODY)  |
+-------+-----+------+-----+-----+-----+--------+--------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-------------+---------------+------------+
| 66    | 0   | 66   | 0   | 0   | 0   | GET    | /                  | 2.288 | 8.444 | 402.044 | 6.092 | 8.260 | 8.320 | 8.444 | 1.939  | 5170.000  | 6047.000    | 366821.000    | 5557.894   |
| 23    | 0   | 23   | 0   | 0   | 0   | GET    | /@\w+              | 0.520 | 6.324 | 69.484  | 3.021 | 5.364 | 5.628 | 6.324 | 1.526  | 1790.000  | 4729.000    | 75935.000     | 3301.522   |
| 8     | 0   | 8    | 0   | 0   | 0   | GET    | /posts             | 3.880 | 7.716 | 48.820  | 6.103 | 7.716 | 7.716 | 7.716 | 1.279  | 4965.000  | 5115.000    | 40395.000     | 5049.375   |
| 113   | 0   | 113  | 0   | 0   | 0   | GET    | /posts/[0-9]+      | 0.108 | 0.472 | 35.712  | 0.316 | 0.412 | 0.424 | 0.460 | 0.089  | 766.000   | 1782.000    | 144013.000    | 1274.451   |
| 1084  | 0   | 1084 | 0   | 0   | 0   | GET    | /image/\d+         | 0.000 | 0.080 | 3.928   | 0.004 | 0.008 | 0.012 | 0.024 | 0.006  | 32373.000 | 1057904.000 | 213479223.000 | 196936.553 |
| 47    | 0   | 0    | 47  | 0   | 0   | POST   | /login             | 0.020 | 0.136 | 1.464   | 0.031 | 0.064 | 0.100 | 0.136 | 0.028  | 0.000     | 0.000       | 0.000         | 0.000      |
| 12    | 0   | 0    | 6   | 6   | 0   | POST   | /                  | 0.004 | 0.116 | 0.324   | 0.027 | 0.096 | 0.116 | 0.116 | 0.037  | 0.000     | 0.000       | 0.000         | 0.000      |
| 6     | 0   | 0    | 6   | 0   | 0   | POST   | /register          | 0.020 | 0.096 | 0.244   | 0.041 | 0.096 | 0.096 | 0.096 | 0.025  | 0.000     | 0.000       | 0.000         | 0.000      |
| 148   | 0   | 148  | 0   | 0   | 0   | GET    | /favicon.ico       | 0.000 | 0.016 | 0.132   | 0.001 | 0.004 | 0.004 | 0.012 | 0.002  | 43.000    | 43.000      | 6364.000      | 43.000     |
| 148   | 0   | 148  | 0   | 0   | 0   | GET    | /css/style.css     | 0.000 | 0.016 | 0.128   | 0.001 | 0.004 | 0.004 | 0.008 | 0.002  | 1549.000  | 1549.000    | 229252.000    | 1549.000   |
| 148   | 0   | 148  | 0   | 0   | 0   | GET    | /js/timeago.min.js | 0.000 | 0.008 | 0.100   | 0.001 | 0.004 | 0.004 | 0.004 | 0.002  | 1915.000  | 1915.000    | 283420.000    | 1915.000   |
| 148   | 0   | 148  | 0   | 0   | 0   | GET    | /js/main.js        | 0.000 | 0.008 | 0.076   | 0.001 | 0.004 | 0.004 | 0.004 | 0.001  | 1824.000  | 1824.000    | 269952.000    | 1824.000   |
| 1     | 0   | 1    | 0   | 0   | 0   | GET    | /initialize        | 0.060 | 0.060 | 0.060   | 0.060 | 0.060 | 0.060 | 0.060 | 0.000  | 0.000     | 0.000       | 0.000         | 0.000      |
| 8     | 0   | 0    | 8   | 0   | 0   | POST   | /comment           | 0.004 | 0.012 | 0.048   | 0.006 | 0.012 | 0.012 | 0.012 | 0.003  | 0.000     | 0.000       | 0.000         | 0.000      |
| 14    | 0   | 14   | 0   | 0   | 0   | GET    | /login             | 0.004 | 0.004 | 0.028   | 0.002 | 0.004 | 0.004 | 0.004 | 0.002  | 615.000   | 615.000     | 8610.000      | 615.000    |
| 6     | 0   | 0    | 0   | 6   | 0   | GET    | /admin/banned      | 0.004 | 0.004 | 0.008   | 0.001 | 0.004 | 0.004 | 0.004 | 0.002  | 0.000     | 0.000       | 0.000         | 0.000      |
| 7     | 0   | 0    | 7   | 0   | 0   | GET    | /logout            | 0.000 | 0.004 | 0.008   | 0.001 | 0.004 | 0.004 | 0.004 | 0.002  | 24.000    | 24.000      | 168.000       | 24.000     |
+-------+-----+------+-----+-----+-----+--------+--------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-------------+---------------+------------+

avgの時間がかかっているのは、以下の関数
- func getIndex
- func getPosts

pt-query-digest

isucon@xxxx:~/private_isu/webapp$ sudo pt-query-digest /var/log/mysql/mysqlslow.log

# 4.6s user time, 70ms system time, 39.82M rss, 42.89M vsz
# Current date: Sat Sep  9 16:40:35 2023
# Hostname: tk2-208-13709
# Files: /var/log/mysql/mysqlslow.log
# Overall: 35.56k total, 26 unique, 382.39 QPS, 4.25x concurrency ________
# Time range: 2023-09-09T07:17:29 to 2023-09-09T07:19:02
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           395s     1us   371ms    11ms    75ms    42ms    84us
# Lock time           50ms       0    12ms     1us     1us    69us       0
# Rows sent        659.85k       0   9.77k   19.00    0.99  411.17       0
# Rows examine     338.93M       0  97.68k   9.76k  97.04k  29.04k       0
# Query size         5.47M      17   1.16M  161.40   80.10  11.10k   31.70

# Profile
# Rank Query ID                      Response time  Calls R/Call V/M   Ite
# ==== ============================= ============== ===== ====== ===== ===
#    1 0x624863D30DAC59FA16849282... 275.9564 69.8%  1663 0.1659  0.04 SELECT comments
#    2 0x422390B42D4DD86C7539A5F4...  89.9631 22.8%  1746 0.0515  0.01 SELECT comments
#    3 0x100EC8B5C400F34381F9D7F7...  17.3485  4.4%    83 0.2090  0.03 SELECT comments
# MISC 0xMISC                         11.9822  3.0% 32070 0.0004   0.0 <23 ITEMS>

# Query 1: 18.08 QPS, 3.00x concurrency, ID 0x624863D30DAC59FA16849282195BE09F at byte 7287534
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.04
# Time range: 2023-09-09T07:17:30 to 2023-09-09T07:19:02
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4    1663
# Exec time     69    276s    70ms   360ms   166ms   293ms    86ms   148ms
# Lock time     40    20ms     1us    12ms    12us     3us   300us     1us
# Rows sent      0   4.42k       0       3    2.72    2.90    0.84    2.90
# Rows examine  46 158.61M  97.66k  97.67k  97.66k  97.04k       0  97.04k
# Query size     2 133.36k      80      83   82.12   80.10    0.16   80.10
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ############################################
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'comments'\G
#    SHOW CREATE TABLE `isuconp`.`comments`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `comments` WHERE `post_id` = 5790 ORDER BY `created_at` DESC LIMIT 3\G

# Query 2: 18.98 QPS, 0.98x concurrency, ID 0x422390B42D4DD86C7539A5F45EB76A80 at byte 1726331
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-09-09T07:17:30 to 2023-09-09T07:19:02
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4    1746
# Exec time     22     90s    23ms   142ms    52ms    91ms    26ms    46ms
# Lock time      8     4ms     1us   111us     2us     2us     4us     1us
# Rows sent      0   1.71k       1       1       1       1       0       1
# Rows examine  49 166.52M  97.66k  97.67k  97.66k  97.04k       0  97.04k
# Query size     1 111.03k      63      66   65.12   65.89    1.04   62.76
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  ###
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'comments'\G
#    SHOW CREATE TABLE `isuconp`.`comments`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = 10032\G

# Query 3: 0.98 QPS, 0.20x concurrency, ID 0x100EC8B5C400F34381F9D7F7FA80A53D at byte 4947308
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.03
# Time range: 2023-09-09T07:17:33 to 2023-09-09T07:18:58
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      83
# Exec time      4     17s    72ms   371ms   209ms   293ms    84ms   241ms
# Lock time      0   219us     1us    24us     2us     3us     2us     1us
# Rows sent      0     780       0      20    9.40   14.52    3.94    8.91
# Rows examine   2   7.92M  97.66k  97.68k  97.67k  97.04k       0  97.04k
# Query size     0   6.00k      73      75   74.05   72.65       0   72.65
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ###############
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'comments'\G
#    SHOW CREATE TABLE `isuconp`.`comments`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `comments` WHERE `post_id` = 9553 ORDER BY `created_at` DESC\G

推測

SELECT * FROM comments WHERE post_id = 5790 ORDER BY created_at DESC LIMIT 3\G

これを直した方が良さそう。

table
- https://github.com/ue-sho/private-isu-provisioning/issues/1#issuecomment-1710101556

実行計画

keyがNULLなので、indexとかは使われていない

3行返すために、98938行を読み取る実行計画だということがわかる

mysql> EXPLAIN SELECT * FROM comments WHERE post_id = 5790 ORDER BY created_at DESC LIMIT 3;
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | comments | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 98938 |    10.00 | Using where; Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

実践

post_idが使われているがindexがないので貼る。

mysql> ALTER TABLE comments ADD INDEX post_id_idx(post_id);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM comments WHERE post_id = 5790 ORDER BY created_at DESC LIMIT 3;
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | comments | NULL       | ref  | post_id_idx   | post_id_idx | 4       | const |    5 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)

結果

スコアが2465から5570になった。
- ローカルからしか実行しなかったが、おそらくサーバーで実行すればもっと高い。
mysqlの負荷が半分以下になった。

~/repos/github.com/ue-sho/private-isu-provisioning main 1m 31s
❯ ./bench_from_local.sh
{"pass":true,"score":5570,"success":4823,"fail":0,"messages":[]}

推測

isucon@tk2-208-13709:~/private_isu/webapp$ pt-query-digest ./log/mysql/20230912230240_mysqlslow.log
ぶっ壊れてて見れなかった。 (画像データがmysqlに入っているから？？？)

isucon@tk2-208-13709:~/private_isu/webapp$ alp --sort sum -r json --matching-groups="/posts/[0-9]+,/image/\d+,/@\w+" --file ./log/nginx/20230912230240_access.log
+-------+-----+------+-----+-----+-----+--------+--------------------+-------+-------+--------+-------+-------+-------+-------+--------+-----------+-------------+---------------+------------+
| COUNT | 1XX | 2XX  | 3XX | 4XX | 5XX | METHOD |        URI         |  MIN  |  MAX  |  SUM   |  AVG  |  P90  |  P95  |  P99  | STDDEV | MIN(BODY) |  MAX(BODY)  |   SUM(BODY)   | AVG(BODY)  |
+-------+-----+------+-----+-----+-----+--------+--------------------+-------+-------+--------+-------+-------+-------+-------+--------+-----------+-------------+---------------+------------+
| 410   | 0   | 410  | 0   | 0   | 0   | GET    | /                  | 0.068 | 0.548 | 52.700 | 0.129 | 0.168 | 0.224 | 0.464 | 0.063  | 2726.000  | 6042.000    | 1493382.000   | 3642.395   |
| 2346  | 0   | 2346 | 0   | 0   | 0   | GET    | /image/\d+         | 0.004 | 0.072 | 12.748 | 0.005 | 0.012 | 0.016 | 0.036 | 0.007  | 36588.000 | 1155884.000 | 699492411.000 | 298163.858 |
| 106   | 0   | 106  | 0   | 0   | 0   | GET    | /@\w+              | 0.048 | 0.512 | 10.740 | 0.101 | 0.132 | 0.152 | 0.492 | 0.062  | 1675.000  | 5542.000    | 345993.000    | 3264.085   |
| 298   | 0   | 0    | 298 | 0   | 0   | POST   | /login             | 0.016 | 0.140 | 8.572  | 0.029 | 0.048 | 0.056 | 0.104 | 0.017  | 0.000     | 0.000       | 0.000         | 0.000      |
| 29    | 0   | 29   | 0   | 0   | 0   | GET    | /posts             | 0.092 | 0.276 | 4.592  | 0.158 | 0.240 | 0.272 | 0.276 | 0.048  | 4965.000  | 5480.000    | 152318.000    | 5252.345   |
| 315   | 0   | 315  | 0   | 0   | 0   | GET    | /posts/[0-9]+      | 0.004 | 0.076 | 4.432  | 0.014 | 0.028 | 0.036 | 0.060 | 0.012  | 747.000   | 1899.000    | 401278.000    | 1273.898   |
| 83    | 0   | 0    | 83  | 0   | 0   | POST   | /register          | 0.016 | 0.100 | 2.844  | 0.034 | 0.052 | 0.072 | 0.100 | 0.016  | 0.000     | 0.000       | 0.000         | 0.000      |
| 90    | 0   | 0    | 45  | 45  | 0   | POST   | /                  | 0.004 | 0.192 | 2.468  | 0.027 | 0.068 | 0.096 | 0.192 | 0.033  | 0.000     | 0.000       | 0.000         | 0.000      |
| 86    | 0   | 0    | 86  | 0   | 0   | POST   | /comment           | 0.004 | 0.056 | 0.572  | 0.007 | 0.012 | 0.016 | 0.056 | 0.007  | 0.000     | 0.000       | 0.000         | 0.000      |
| 272   | 0   | 272  | 0   | 0   | 0   | GET    | /js/timeago.min.js | 0.000 | 0.016 | 0.380  | 0.001 | 0.004 | 0.008 | 0.016 | 0.003  | 1915.000  | 1915.000    | 520880.000    | 1915.000   |
| 272   | 0   | 272  | 0   | 0   | 0   | GET    | /js/main.js        | 0.000 | 0.016 | 0.356  | 0.001 | 0.004 | 0.008 | 0.012 | 0.003  | 1824.000  | 1824.000    | 496128.000    | 1824.000   |
| 272   | 0   | 272  | 0   | 0   | 0   | GET    | /css/style.css     | 0.000 | 0.012 | 0.340  | 0.001 | 0.004 | 0.004 | 0.012 | 0.002  | 1549.000  | 1549.000    | 421328.000    | 1549.000   |
| 272   | 0   | 272  | 0   | 0   | 0   | GET    | /favicon.ico       | 0.000 | 0.020 | 0.272  | 0.001 | 0.004 | 0.004 | 0.008 | 0.002  | 43.000    | 43.000      | 11696.000     | 43.000     |
| 83    | 0   | 0    | 0   | 83  | 0   | GET    | /admin/banned      | 0.000 | 0.036 | 0.232  | 0.003 | 0.008 | 0.008 | 0.036 | 0.005  | 0.000     | 0.000       | 0.000         | 0.000      |
| 1     | 0   | 1    | 0   | 0   | 0   | GET    | /initialize        | 0.196 | 0.196 | 0.196  | 0.196 | 0.196 | 0.196 | 0.196 | 0.000  | 0.000     | 0.000       | 0.000         | 0.000      |
| 26    | 0   | 26   | 0   | 0   | 0   | GET    | /login             | 0.000 | 0.024 | 0.096  | 0.004 | 0.004 | 0.004 | 0.024 | 0.004  | 615.000   | 615.000     | 15990.000     | 615.000    |
| 13    | 0   | 0    | 13  | 0   | 0   | GET    | /logout            | 0.004 | 0.008 | 0.036  | 0.003 | 0.004 | 0.008 | 0.008 | 0.002  | 24.000    | 24.000      | 312.000       | 24.000     |
+-------+-----+------+-----+-----+-----+--------+--------------------+-------+-------+--------+-------+-------+-------+-------+--------+-----------+-------------+---------------+------------+

rootのエンドポイントを直したい
最初にfunc getIndexでpostsを全件取得しているが、ここで20件以降は捨てている。
posts, usersでN+1になっている
comments, usersでN+1になっている
- func makePosts
DESCでコメントを取っているが、途中で逆順にしている(allComments次第)
※注意: makePostsは他４ヶ所でも使われている

実践

まずは、posts, usersでN+1になっているのを直す。他のmakePostsが使われている箇所も同様に直す。加えて、posts全件とって20件しか使っていないのも直す。

0109e9066535aeb891b60ce13cc067b983fba38d

結果

4倍くらい得点が上がった。(前回ローカルからの得点なので、おそらくもう少し伸びは小さい)

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:8080" -u ../benchmarker/userdata
{"pass":true,"score":25119,"success":23711,"fail":0,"messages":[]}

推測

先ほどのcomments, usersでN+1になっている箇所も修正する。

16506316d9474554b17b16d396016695393f49f6

結果

微増した。

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:8080" -u ../benchmarker/userdata
{"pass":true,"score":28523,"success":27075,"fail":0,"messages":[]}

CPU使用率68%
mysqlは1コア分くらい使っている
appの割合はまだ少なめ

推測

同じサーバーから叩いてるから(？)nginxのログが出力されなかった。

pt-query-digest

# 57.8s user time, 610ms system time, 54.04M rss, 57.31M vsz
# Current date: Wed Sep 13 17:47:02 2023
# Hostname: tk2-208-13709
# Files: ./log/mysql/20230913174309_mysqlslow.log
# Overall: 442.63k total, 33 unique, 2.26k QPS, 1.90x concurrency ________
# Time range: 2023-09-13T08:36:51 to 2023-09-13T08:40:07
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           372s     1us      1s   839us     2ms     7ms    89us
# Lock time          721ms       0    19ms     1us     1us    62us       0
# Rows sent        213.65k       0      23    0.49    2.90    1.73       0
# Rows examine      75.17M       0  97.75k  178.06   13.83   3.28k       0
# Query size       124.69M      17   1.25M  295.39  420.77  13.70k   31.70

# Profile
# Rank Query ID                     Response time  Calls  R/Call V/M   Ite
# ==== ============================ ============== ====== ====== ===== ===
#    1 0xB213742E2129B37E68649AE... 107.1479 28.8%   1657 0.0647  0.01 SELECT posts users
#    2 0xCDEB1AFF2AE2BE51B2ED5CF...  48.9082 13.2%    478 0.1023  0.01 SELECT comments
#    3 0x19759A5557089FD5B718D44...  44.6507 12.0%  28914 0.0015  0.01 SELECT posts
#    4 0xE93E8A75F4D988017AB8063...  36.4687  9.8%    400 0.0912  0.01 SELECT posts users
#    5 0xDA556F9115773A1A99AA016...  36.2488  9.7% 138903 0.0003  0.01 ADMIN PREPARE
#    6 0xC62EF1FBCF396B87C7C2787...  26.9802  7.3%  45952 0.0006  0.01 SELECT comments users
#    7 0x422390B42D4DD86C7539A5F...  20.1815  5.4%  49076 0.0004  0.00 SELECT comments
#    8 0x40771BF2BF7DC042D68D045...  15.1588  4.1%    478 0.0317  0.01 SELECT posts users
#    9 0x009A61E5EFBD5A5E4097914...  11.4648  3.1%    147 0.0780  0.06 INSERT posts
#   10 0xC9383ACA6FF14C29E819735...   5.2866  1.4%    478 0.0111  0.01 SELECT posts
#   11 0x9F2038550F51B0A3AB05CA5...   2.9304  0.8%    199 0.0147  0.56 INSERT comments
# MISC 0xMISC                        16.3690  4.4% 175951 0.0001   0.0 <22 ITEMS>

# Query 1: 8.45 QPS, 0.55x concurrency, ID 0xB213742E2129B37E68649AEE9EA0599F at byte 206054444
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-09-13T08:36:51 to 2023-09-13T08:40:07
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    1657
# Exec time     28    107s    14ms   199ms    65ms   116ms    27ms    59ms
# Lock time      1     9ms     1us     2ms     5us     2us    60us     1us
# Rows sent     15  32.36k      20      20      20      20       0      20
# Rows examine  21  15.93M   9.81k   9.88k   9.84k   9.80k    0.00   9.80k
# Query size     0 668.30k     413     413     413     413       0     413
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us

pt-query-digestを確認すると、先ほどN+1を解消したが、まだposts usersが一番遅い。なので、EXPLAINで確認してみる。

mysql> EXPLAIN SELECT p.id, p.user_id, p.body, p.mime, p.created_at, u.id, u.account_name, u.passhash, u.authority, u.del_flg, u.created_at FROM posts AS p INNER JOIN users AS u ON p.user_id = u.id WHERE u.del_flg = 0 ORDER BY p.created_at DESC LIMIT 20;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+----------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref               | rows | filtered | Extra          |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+----------------+
|  1 | SIMPLE      | p     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL              | 9100 |   100.00 | Using filesort |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | isuconp.p.user_id |    1 |    10.00 | Using where    |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)

mysql> select COUNT(*) from posts;
+----------+
| COUNT(*) |
+----------+
|    10078 |
+----------+

postsテーブルは、20行取得するのにほぼ全行読み取ろうとしている。

実践

postsテーブルはkeyが使われていないので、ORDER_BY 狙いのインデックスを追加する。

mysql> ALTER TABLE posts ADD INDEX posts_order_idx (created_at DESC)
    -> ;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT p.id, p.user_id, p.body, p.mime, p.created_at, u.id, u.account_name, u.passhash, u.authority, u.del_flg, u.created_at FROM posts AS p INNER JOIN users AS u ON p.user_id = u.id WHERE u.del_flg = 0 ORDER BY p.created_at DESC LIMIT 20;
+----+-------------+-------+------------+--------+---------------+-----------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key             | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+-----------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | p     | NULL       | index  | NULL          | posts_order_idx | 4       | NULL              |  199 |   100.00 | NULL        |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY       | PRIMARY         | 4       | isuconp.p.user_id |    1 |    10.00 | Using where |
+----+-------------+-------+------------+--------+---------------+-----------------+---------+-------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

200行くらいの読み取りに減った。

結果

微増した。

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://127.0.0.1:8080" -u ../benchmarker/userdata
{"pass":true,"score":30011,"success":28279,"fail":0,"messages":[]}

pt-query-digest

# 33.8s user time, 360ms system time, 42.94M rss, 46.27M vsz
# Current date: Wed Sep 13 18:22:43 2023
# Hostname: tk2-208-13709
# Files: ./log/mysql/20230913182133_mysqlslow.log
# Overall: 257.21k total, 28 unique, 4.15k QPS, 1.63x concurrency ________
# Time range: 2023-09-13T09:19:38 to 2023-09-13T09:20:40
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           101s     1us   290ms   394us   881us     4ms    84us
# Lock time          299ms       0     9ms     1us     1us    36us       0
# Rows sent        119.95k       0      23    0.48    0.99    1.71       0
# Rows examine      29.15M       0  97.77k  118.83   12.54   3.06k       0
# Query size        75.96M      17   1.33M  309.67  420.77  14.26k   31.70

# Profile
# Rank Query ID                      Response time Calls R/Call V/M   Item
# ==== ============================= ============= ===== ====== ===== ====
#    1 0xCDEB1AFF2AE2BE51B2ED5CF0... 19.4130 19.2%   251 0.0773  0.01 SELECT comments
#    2 0x19759A5557089FD5B718D440... 19.3387 19.1% 16223 0.0012  0.00 SELECT posts
#    3 0xDA556F9115773A1A99AA0165... 15.1554 15.0% 80637 0.0002  0.01 ADMIN PREPARE
#    4 0x40771BF2BF7DC042D68D0457... 12.3926 12.2%   251 0.0494  0.01 SELECT posts users
#    5 0xC62EF1FBCF396B87C7C27874... 10.6779 10.5% 27359 0.0004  0.00 SELECT comments users
#    6 0x422390B42D4DD86C7539A5F4...  8.1505  8.0% 28888 0.0003  0.00 SELECT comments
#    7 0x009A61E5EFBD5A5E4097914B...  6.3415  6.3%    96 0.0661  0.05 INSERT posts
#    8 0xC9383ACA6FF14C29E819735F...  2.1866  2.2%   251 0.0087  0.01 SELECT posts
#    9 0x07890000813C4CC7111FD2D3...  1.1450  1.1% 80637 0.0000  0.00 ADMIN CLOSE STMT
#   10 0x995F41A1456C1CF6746D9652...  1.1389  1.1%  9072 0.0001  0.00 SET
#   11 0x9F2038550F51B0A3AB05CA52...  1.1303  1.1%   119 0.0095  0.06 INSERT comments
# MISC 0xMISC                         4.2847  4.2% 13430 0.0003   0.0 <17 ITEMS>

# Query 1: 4.11 QPS, 0.32x concurrency, ID 0xCDEB1AFF2AE2BE51B2ED5CF03D4E749F at byte 116275440
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-09-13T09:19:38 to 2023-09-13T09:20:39
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     251
# Exec time     19     19s    33ms   156ms    77ms   116ms    24ms    71ms
# Lock time      0   668us     1us   209us     2us     1us    12us     1us
# Rows sent      0     251       1       1       1       1       0       1
# Rows examine  82  23.95M  97.66k  97.77k  97.71k  97.04k       0  97.04k
# Query size     0  15.17k      61      62   61.91   59.77       0   59.77
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us

推測

↑のログから、commentsが遅そう。

user_idにもインデックスを貼った方が良さそう。まず、ISUCON本 5章「複合インデックス・並び替えにも使われるインデックス」の方を行う。

mysql> EXPLAIN SELECT c.id, c.post_id, c.user_id, c.comment, c.created_at, u.id, u.account_name, u.passhash, u.authority, u.del_flg, u.created_at FROM comments AS c INNER JOIN users AS u ON c.user_id = u.id WHE
RE c.post_id = 1 ORDER BY c.created_at DESC;
+----+-------------+-------+------------+--------+---------------+-------------+---------+-------------------+------+----------+----------------+
| id | select_type | table | partitions | type   | possible_keys | key         | key_len | ref               | rows | filtered | Extra          |
+----+-------------+-------+------------+--------+---------------+-------------+---------+-------------------+------+----------+----------------+
|  1 | SIMPLE      | c     | NULL       | ref    | post_id_idx   | post_id_idx | 4       | const             |   12 |   100.00 | Using filesort |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY       | PRIMARY     | 4       | isuconp.c.user_id |    1 |   100.00 | NULL           |
+----+-------------+-------+------------+--------+---------------+-------------+---------+-------------------+------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)

実践

mysql> ALTER TABLE comments DROP INDEX post_id_idx, ADD INDEX post_id_idx (post_id, created_at DESC);
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT c.id, c.post_id, c.user_id, c.comment, c.created_at, u.id, u.account_name, u.passhash, u.authority, u.del_flg, u.created_at FROM comments AS c INNER JOIN users AS u ON c.user_id = u.id WHERE c.post_id = 1 ORDER BY c.created_at DESC;
+----+-------------+-------+------------+--------+---------------+-------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+-------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | c     | NULL       | ref    | post_id_idx   | post_id_idx | 4       | const             |   12 |   100.00 | NULL  |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY       | PRIMARY     | 4       | isuconp.c.user_id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+-------------+---------+-------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

結果

変わらずだが、↑のEXPLAINでExtraがNULLになったので、このままにする。

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://127.0.0.1:8080" -u ../benchmarker/userdata
{"pass":true,"score":29181,"success":27502,"fail":0,"messages":[]}

実践

上記に続き、commentsテーブルのuser_idにもインデックスを貼る。

mysql> ALTER TABLE comments ADD INDEX user_id_idx (user_id);
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

結果

微増

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:8080" -u ../benchmarker/userdata
{"pass":true,"score":31162,"success":29375,"fail":0,"messages":[]}

CPU全然使われていない

rotateでmysqlをflushしていなかったので修正 1ef197b5920473c5dd9248c52fe5b9e30918b56a

推測

+-------+-----+------+-----+-----+-----+--------+--------------------+-------+-------+--------+-------+-------+-------+-------+--------+-----------+-------------+---------------+------------+
| COUNT | 1XX | 2XX  | 3XX | 4XX | 5XX | METHOD |        URI         |  MIN  |  MAX  |  SUM   |  AVG  |  P90  |  P95  |  P99  | STDDEV | MIN(BODY) |  MAX(BODY)  |   SUM(BODY)   | AVG(BODY)  |
+-------+-----+------+-----+-----+-----+--------+--------------------+-------+-------+--------+-------+-------+-------+-------+--------+-----------+-------------+---------------+------------+
| 456   | 0   | 456  | 0   | 0   | 0   | GET    | /                  | 0.012 | 0.124 | 12.516 | 0.027 | 0.040 | 0.048 | 0.092 | 0.014  | 2668.000  | 6045.000    | 1623150.000   | 3559.539   |
| 2092  | 0   | 2092 | 0   | 0   | 0   | GET    | /image/\d+         | 0.004 | 0.028 | 7.732  | 0.004 | 0.008 | 0.008 | 0.016 | 0.004  | 36603.000 | 1155540.000 | 580909053.000 | 277681.192 |
| 336   | 0   | 0    | 336 | 0   | 0   | POST   | /login             | 0.024 | 0.080 | 7.100  | 0.021 | 0.028 | 0.032 | 0.052 | 0.008  | 0.000     | 0.000       | 0.000         | 0.000      |
| 125   | 0   | 125  | 0   | 0   | 0   | GET    | /@\w+              | 0.024 | 0.128 | 5.208  | 0.042 | 0.052 | 0.056 | 0.128 | 0.014  | 1838.000  | 4800.000    | 406802.000    | 3254.416   |
| 98    | 0   | 0    | 98  | 0   | 0   | POST   | /register          | 0.016 | 0.052 | 2.456  | 0.025 | 0.036 | 0.040 | 0.052 | 0.007  | 0.000     | 0.000       | 0.000         | 0.000      |
| 91    | 0   | 0    | 46  | 45  | 0   | POST   | /                  | 0.004 | 0.136 | 2.216  | 0.024 | 0.064 | 0.092 | 0.136 | 0.030  | 0.000     | 0.000       | 0.000         | 0.000      |
| 305   | 0   | 305  | 0   | 0   | 0   | GET    | /posts/[0-9]+      | 0.004 | 0.020 | 1.408  | 0.005 | 0.008 | 0.008 | 0.016 | 0.003  | 764.000   | 1984.000    | 390441.000    | 1280.134   |
| 26    | 0   | 26   | 0   | 0   | 0   | GET    | /posts             | 0.020 | 0.052 | 0.820  | 0.032 | 0.044 | 0.048 | 0.052 | 0.009  | 5011.000  | 5465.000    | 136204.000    | 5238.615   |
| 108   | 0   | 0    | 108 | 0   | 0   | POST   | /comment           | 0.004 | 0.020 | 0.572  | 0.005 | 0.008 | 0.008 | 0.016 | 0.003  | 0.000     | 0.000       | 0.000         | 0.000      |
| 1     | 0   | 1    | 0   | 0   | 0   | GET    | /initialize        | 0.224 | 0.224 | 0.224  | 0.224 | 0.224 | 0.224 | 0.224 | 0.000  | 0.000     | 0.000       | 0.000         | 0.000      |
| 229   | 0   | 229  | 0   | 0   | 0   | GET    | /favicon.ico       | 0.000 | 0.008 | 0.160  | 0.001 | 0.004 | 0.004 | 0.004 | 0.002  | 43.000    | 43.000      | 9847.000      | 43.000     |
| 229   | 0   | 229  | 0   | 0   | 0   | GET    | /css/style.css     | 0.004 | 0.008 | 0.156  | 0.001 | 0.004 | 0.004 | 0.004 | 0.002  | 1549.000  | 1549.000    | 354721.000    | 1549.000   |
| 98    | 0   | 0    | 0   | 98  | 0   | GET    | /admin/banned      | 0.000 | 0.008 | 0.148  | 0.002 | 0.004 | 0.004 | 0.008 | 0.002  | 0.000     | 0.000       | 0.000         | 0.000      |
| 229   | 0   | 229  | 0   | 0   | 0   | GET    | /js/timeago.min.js | 0.000 | 0.008 | 0.136  | 0.001 | 0.004 | 0.004 | 0.004 | 0.002  | 1915.000  | 1915.000    | 438535.000    | 1915.000   |
| 229   | 0   | 229  | 0   | 0   | 0   | GET    | /js/main.js        | 0.000 | 0.004 | 0.112  | 0.000 | 0.004 | 0.004 | 0.004 | 0.001  | 1824.000  | 1824.000    | 417696.000    | 1824.000   |
| 26    | 0   | 26   | 0   | 0   | 0   | GET    | /login             | 0.004 | 0.004 | 0.040  | 0.002 | 0.004 | 0.004 | 0.004 | 0.002  | 615.000   | 615.000     | 15990.000     | 615.000    |
| 13    | 0   | 0    | 13  | 0   | 0   | GET    | /logout            | 0.000 | 0.004 | 0.012  | 0.001 | 0.004 | 0.004 | 0.004 | 0.002  | 24.000    | 24.000      | 312.000       | 24.000     |
+-------+-----+------+-----+-----+-----+--------+--------------------+-------+-------+--------+-------+-------+-------+-------+--------+-----------+-------------+---------------+------------+

pt-query-digest

# 11.7s user time, 160ms system time, 39.03M rss, 42.21M vsz
# Current date: Thu Sep 14 09:31:39 2023
# Hostname: tk2-208-13709
# Files: /var/log/mysql/mysqlslow.log
# Overall: 83.03k total, 29 unique, 838.74 QPS, 0.15x concurrency ________
# Time range: 2023-09-14T00:29:30 to 2023-09-14T00:31:09
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            15s     1us   203ms   183us   332us     1ms    93us
# Lock time           74ms       0     5ms       0     1us    17us       0
# Rows sent         40.50k       0      22    0.50    2.90    1.79       0
# Rows examine       2.50M       0   9.81k   31.62    7.70  532.55       0
# Query size        30.13M      10   1.33M  380.53  420.77  16.03k   31.70

# Profile
# Rank Query ID                      Response time Calls R/Call V/M   Item
# ==== ============================= ============= ===== ====== ===== ====
#    1 0xDA556F9115773A1A99AA0165...  3.1641 20.8% 27527 0.0001  0.00 ADMIN PREPARE
#    2 0xC62EF1FBCF396B87C7C27874...  2.3023 15.1% 10880 0.0002  0.00 SELECT comments users
#    3 0x40771BF2BF7DC042D68D0457...  2.2952 15.1%   125 0.0184  0.00 SELECT posts users
#    4 0x19759A5557089FD5B718D440...  2.1131 13.9%  2092 0.0010  0.00 SELECT posts
#    5 0x422390B42D4DD86C7539A5F4...  1.8561 12.2% 11185 0.0002  0.00 SELECT comments
#    6 0x009A61E5EFBD5A5E4097914B...  1.0794  7.1%    45 0.0240  0.02 INSERT posts
#    7 0xC9383ACA6FF14C29E819735F...  0.4542  3.0%   125 0.0036  0.00 SELECT posts
#    8 0x9F2038550F51B0A3AB05CA52...  0.3251  2.1%   108 0.0030  0.00 INSERT comments
#    9 0x26489ECBE26887E480CA8067...  0.2732  1.8%    98 0.0028  0.00 INSERT users
#   10 0x07890000813C4CC7111FD2D3...  0.2330  1.5% 27527 0.0000  0.00 ADMIN CLOSE STMT
#   11 0x396201721CD58410E070DA94...  0.2147  1.4%   967 0.0002  0.00 SELECT users
#   12 0x77938F0C35A5722A798DE77E...  0.2025  1.3%     1 0.2025  0.00 DELETE posts
# MISC 0xMISC                         0.7142  4.7%  2355 0.0003   0.0 <17 ITEMS>

imageを静的ファイル化して高速化させたい。その前に、静的ファイルはnginxから配信するようにしないと。

実践

静的ファイルをnginxから配信するようにする。

/etc/nginx/sites-available/ 配下にドメインごとにある
/etc/nginx/sites-enabled/ で↑のシンボリックリンクを貼っている
/etc/nginx/nginx.conf が sites-enabled/* を読み込む

→ sites-available のところに設定を追記する

2465d0490dd0f3cb71a4f908dc6cee5480bb63e4

結果

変わらず。

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:8080" -u ../benchmarker/userdata
{"pass":true,"score":31060,"success":29284,"fail":0,"messages":[]}

js, css, favicon.ico が 0.000 になった。

+-------+-----+------+-----+-----+-----+--------+--------------------+-------+-------+--------+-------+-------+-------+-------+--------+-----------+-------------+---------------+------------+
| COUNT | 1XX | 2XX  | 3XX | 4XX | 5XX | METHOD |        URI         |  MIN  |  MAX  |  SUM   |  AVG  |  P90  |  P95  |  P99  | STDDEV | MIN(BODY) |  MAX(BODY)  |   SUM(BODY)   | AVG(BODY)  |
+-------+-----+------+-----+-----+-----+--------+--------------------+-------+-------+--------+-------+-------+-------+-------+--------+-----------+-------------+---------------+------------+
| 473   | 0   | 473  | 0   | 0   | 0   | GET    | /                  | 0.016 | 0.108 | 12.044 | 0.025 | 0.032 | 0.040 | 0.068 | 0.009  | 2735.000  | 6054.000    | 1668308.000   | 3527.078   |
| 2213  | 0   | 2213 | 0   | 0   | 0   | GET    | /image/\d+         | 0.004 | 0.216 | 9.728  | 0.004 | 0.008 | 0.008 | 0.016 | 0.012  | 36572.000 | 1057227.000 | 675561818.000 | 305269.687 |
| 348   | 0   | 0    | 348 | 0   | 0   | POST   | /login             | 0.004 | 0.116 | 7.404  | 0.021 | 0.028 | 0.032 | 0.068 | 0.010  | 0.000     | 0.000       | 0.000         | 0.000      |
| 119   | 0   | 119  | 0   | 0   | 0   | GET    | /@\w+              | 0.028 | 0.108 | 4.932  | 0.041 | 0.056 | 0.060 | 0.092 | 0.011  | 1655.000  | 5420.000    | 388719.000    | 3266.546   |
| 98    | 0   | 0    | 98  | 0   | 0   | POST   | /register          | 0.016 | 0.064 | 2.500  | 0.026 | 0.032 | 0.040 | 0.064 | 0.007  | 0.000     | 0.000       | 0.000         | 0.000      |
| 104   | 0   | 0    | 52  | 52  | 0   | POST   | /                  | 0.004 | 0.100 | 2.284  | 0.022 | 0.064 | 0.076 | 0.096 | 0.026  | 0.000     | 0.000       | 0.000         | 0.000      |
| 291   | 0   | 291  | 0   | 0   | 0   | GET    | /posts/[0-9]+      | 0.004 | 0.012 | 1.236  | 0.004 | 0.008 | 0.008 | 0.012 | 0.002  | 767.000   | 1901.000    | 369892.000    | 1271.107   |
| 25    | 0   | 25   | 0   | 0   | 0   | GET    | /posts             | 0.016 | 0.040 | 0.628  | 0.025 | 0.036 | 0.040 | 0.040 | 0.007  | 4968.000  | 5515.000    | 131215.000    | 5248.600   |
| 104   | 0   | 0    | 104 | 0   | 0   | POST   | /comment           | 0.004 | 0.016 | 0.560  | 0.005 | 0.008 | 0.012 | 0.016 | 0.003  | 0.000     | 0.000       | 0.000         | 0.000      |
| 1     | 0   | 1    | 0   | 0   | 0   | GET    | /initialize        | 0.332 | 0.332 | 0.332  | 0.332 | 0.332 | 0.332 | 0.332 | 0.000  | 0.000     | 0.000       | 0.000         | 0.000      |
| 98    | 0   | 0    | 0   | 98  | 0   | GET    | /admin/banned      | 0.000 | 0.016 | 0.192  | 0.002 | 0.004 | 0.004 | 0.016 | 0.003  | 0.000     | 0.000       | 0.000         | 0.000      |
| 28    | 0   | 28   | 0   | 0   | 0   | GET    | /login             | 0.000 | 0.008 | 0.052  | 0.002 | 0.004 | 0.008 | 0.008 | 0.003  | 615.000   | 615.000     | 17220.000     | 615.000    |
| 14    | 0   | 0    | 14  | 0   | 0   | GET    | /logout            | 0.000 | 0.004 | 0.012  | 0.001 | 0.004 | 0.004 | 0.004 | 0.002  | 24.000    | 24.000      | 336.000       | 24.000     |
| 219   | 0   | 1    | 218 | 0   | 0   | GET    | /js/timeago.min.js | 0.000 | 0.000 | 0.000  | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 1915.000    | 1915.000      | 8.744      |
| 219   | 0   | 1    | 218 | 0   | 0   | GET    | /js/main.js        | 0.000 | 0.000 | 0.000  | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 1824.000    | 1824.000      | 8.329      |
| 219   | 0   | 1    | 218 | 0   | 0   | GET    | /css/style.css     | 0.000 | 0.000 | 0.000  | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 1549.000    | 1549.000      | 7.073      |
| 219   | 0   | 1    | 218 | 0   | 0   | GET    | /favicon.ico       | 0.000 | 0.000 | 0.000  | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 43.000      | 43.000        | 0.196      |
+-------+-----+------+-----+-----+-----+--------+--------------------+-------+-------+--------+-------+-------+-------+-------+--------+-----------+-------------+---------------+------------+

実践

mysqlに保存されている画像を静的ファイルにしてnginxから配信する。

a276f8dcd4e6d1e3bbe6e407c68c99cb7fda9756

結果

+2万点くらいになった。

http://localhost:80 にしないと上手くnginxで処理できなかった。(今考えればそう🤔) ↑だったのでnginxのログが出力されず、いつも手元の環境からベンチ叩いたものでalpしていた。そのため、以前までのやつはCOUNTが少なめです。

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:80" -u ../benchmarker/userdata
{"pass":true,"score":55628,"success":53448,"fail":0,"messages":[]}

+-------+-----+-------+-------+-----+-----+--------+--------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-------------+----------------+-----------+
| COUNT | 1XX |  2XX  |  3XX  | 4XX | 5XX | METHOD |        URI         |  MIN  |  MAX  |   SUM   |  AVG  |  P90  |  P95  |  P99  | STDDEV | MIN(BODY) |  MAX(BODY)  |   SUM(BODY)    | AVG(BODY) |
+-------+-----+-------+-------+-----+-----+--------+--------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-------------+----------------+-----------+
| 1743  | 0   | 1743  | 0     | 0   | 0   | GET    | /                  | 0.016 | 0.272 | 260.204 | 0.149 | 0.192 | 0.208 | 0.240 | 0.034  | 2648.000  | 6046.000    | 5251453.000    | 3012.882  |
| 34261 | 0   | 12345 | 21916 | 0   | 0   | GET    | /image/\d+         | 0.000 | 0.100 | 105.052 | 0.003 | 0.012 | 0.016 | 0.024 | 0.006  | 39491.000 | 1095762.000 | 1756805211.000 | 51277.114 |
| 1117  | 0   | 0     | 1117  | 0   | 0   | POST   | /login             | 0.004 | 0.192 | 75.036  | 0.067 | 0.116 | 0.132 | 0.160 | 0.039  | 0.000     | 0.000       | 0.000          | 0.000     |
| 338   | 0   | 338   | 0     | 0   | 0   | GET    | /@\w+              | 0.036 | 0.376 | 58.312  | 0.173 | 0.228 | 0.244 | 0.292 | 0.043  | 1399.000  | 5257.000    | 1091813.000    | 3230.216  |
| 340   | 0   | 340   | 0     | 0   | 0   | GET    | /posts             | 0.020 | 0.300 | 50.056  | 0.147 | 0.196 | 0.208 | 0.280 | 0.043  | 4995.000  | 5785.000    | 1818619.000    | 5348.879  |
| 2316  | 0   | 2316  | 0     | 0   | 0   | GET    | /posts/[0-9]+      | 0.008 | 0.112 | 34.252  | 0.015 | 0.024 | 0.032 | 0.040 | 0.009  | 709.000   | 2015.000    | 3036380.000    | 1311.045  |
| 120   | 0   | 0     | 120   | 0   | 0   | POST   | /register          | 0.020 | 0.272 | 11.352  | 0.095 | 0.128 | 0.140 | 0.204 | 0.032  | 0.000     | 0.000       | 0.000          | 0.000     |
| 210   | 0   | 0     | 105   | 105 | 0   | POST   | /                  | 0.004 | 0.104 | 5.588   | 0.027 | 0.056 | 0.068 | 0.092 | 0.020  | 0.000     | 0.000       | 0.000          | 0.000     |
| 133   | 0   | 0     | 133   | 0   | 0   | POST   | /comment           | 0.004 | 0.128 | 2.360   | 0.018 | 0.028 | 0.036 | 0.060 | 0.013  | 0.000     | 0.000       | 0.000          | 0.000     |
| 436   | 0   | 436   | 0     | 0   | 0   | GET    | /login             | 0.004 | 0.024 | 1.412   | 0.003 | 0.008 | 0.008 | 0.016 | 0.003  | 615.000   | 615.000     | 268140.000     | 615.000   |
| 120   | 0   | 0     | 0     | 120 | 0   | GET    | /admin/banned      | 0.000 | 0.020 | 0.964   | 0.008 | 0.016 | 0.016 | 0.020 | 0.005  | 0.000     | 0.000       | 0.000          | 0.000     |
| 218   | 0   | 0     | 218   | 0   | 0   | GET    | /logout            | 0.000 | 0.012 | 0.704   | 0.003 | 0.008 | 0.008 | 0.012 | 0.003  | 24.000    | 24.000      | 5232.000       | 24.000    |
| 1     | 0   | 1     | 0     | 0   | 0   | GET    | /initialize        | 0.032 | 0.032 | 0.032   | 0.032 | 0.032 | 0.032 | 0.032 | 0.000  | 0.000     | 0.000       | 0.000          | 0.000     |
| 3443  | 0   | 1     | 3442  | 0   | 0   | GET    | /js/timeago.min.js | 0.000 | 0.000 | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 1915.000    | 1915.000       | 0.556     |
| 3443  | 0   | 1     | 3442  | 0   | 0   | GET    | /js/main.js        | 0.000 | 0.000 | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 1824.000    | 1824.000       | 0.530     |
| 3443  | 0   | 1     | 3442  | 0   | 0   | GET    | /css/style.css     | 0.000 | 0.000 | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 1549.000    | 1549.000       | 0.450     |
| 3443  | 0   | 1     | 3442  | 0   | 0   | GET    | /favicon.ico       | 0.000 | 0.000 | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 43.000      | 43.000         | 0.012     |
+-------+-----+-------+-------+-----+-----+--------+--------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-------------+----------------+-----------+

やっとpt-query-digestが全部表示されるようになった。(画像がDBに保存されていたので、pt-query-digestするときに画像byte列が出力されていた？)

/var/log/mysql/mysqlslow.log:  61% 00:18 remain

# 45.8s user time, 420ms system time, 29.67M rss, 32.73M vsz
# Current date: Thu Sep 14 21:38:26 2023
# Hostname: tk2-208-13709
# Files: /var/log/mysql/mysqlslow.log
# Overall: 361.32k total, 28 unique, 5.65k QPS, 1.54x concurrency ________
# Time range: 2023-09-14T12:34:48 to 2023-09-14T12:35:52
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            99s     1us   185ms   272us   761us     2ms    84us
# Lock time          458ms       0     7ms     1us     1us    34us       0
# Rows sent        170.17k       0      23    0.48    0.99    1.81       0
# Rows examine       6.90M       0   9.87k   20.03    6.98  427.54       0
# Query size        32.28M      10     461   93.69  420.77  135.27   31.70

# Profile
# Rank Query ID                     Response time Calls  R/Call V/M   Item
# ==== ============================ ============= ====== ====== ===== ====
#    1 0xDA556F9115773A1A99AA016... 22.9878 23.3% 116253 0.0002  0.00 ADMIN PREPARE
#    2 0x40771BF2BF7DC042D68D045... 18.2688 18.5%    338 0.0540  0.01 SELECT posts users
#    3 0xC62EF1FBCF396B87C7C2787... 18.0520 18.3%  45031 0.0004  0.00 SELECT comments users
#    4 0x422390B42D4DD86C7539A5F... 14.8466 15.1%  47347 0.0003  0.00 SELECT comments
#    5 0x19759A5557089FD5B718D44... 12.3488 12.5%  12240 0.0010  0.00 SELECT posts
#    6 0xC9383ACA6FF14C29E819735...  2.8306  2.9%    338 0.0084  0.00 SELECT posts
#    7 0x07890000813C4CC7111FD2D...  1.6531  1.7% 116253 0.0000  0.00 ADMIN CLOSE STMT
#    8 0x90F44B8FEAE9719F0F2F227...  1.3842  1.4%   2316 0.0006  0.00 SELECT comments users
#    9 0xB213742E2129B37E68649AE...  0.9859  1.0%   1743 0.0006  0.00 SELECT posts users
#   10 0x9F2038550F51B0A3AB05CA5...  0.8536  0.9%    133 0.0064  0.02 INSERT comments
# MISC 0xMISC                        4.4042  4.5%  19325 0.0002   0.0 <18 ITEMS>

# Query 1: 1.91k QPS, 0.38x concurrency, ID 0xDA556F9115773A1A99AA0165670CE848 at byte 102410304
# Scores: V/M = 0.00
# Time range: 2023-09-14T12:34:51 to 2023-09-14T12:35:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         32  116253
# Exec time     23     23s    38us    23ms   197us   596us   551us    89us
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    10   3.33M      30      30      30      30       0      30
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  ###########################################
#   1ms  ###
#  10ms  #
# 100ms
#    1s
#  10s+
administrator command: Prepare\G

# Query 2: 5.63 QPS, 0.30x concurrency, ID 0x40771BF2BF7DC042D68D0457BF75CDEC at byte 79602766
# Scores: V/M = 0.01
# Time range: 2023-09-14T12:34:51 to 2023-09-14T12:35:51
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     338
# Exec time     18     18s    16ms   134ms    54ms    91ms    20ms    48ms
# Lock time      0   736us     1us    15us     2us     2us     1us     1us
# Rows sent      1   3.29k       2      19    9.97   14.52    3.00    9.83
# Rows examine  46   3.24M   9.77k   9.87k   9.82k   9.80k      52   9.80k
# Query size     0 142.89k     432     433  432.90  420.77       0  420.77
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  ###
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'posts'\G
#    SHOW CREATE TABLE `isuconp`.`posts`\G
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'users'\G
#    SHOW CREATE TABLE `isuconp`.`users`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT p.id AS `id`, p.user_id AS `user_id`, p.body AS `body`, p.mime AS `mime`, p.created_at AS `created_at`, u.id AS `user.id`, u.account_name AS `user.account_name`, u.passhash AS `user.passhash`, u.authority AS `user.authority`, u.del_flg AS `user.del_flg`, u.created_at AS `user.created_at` FROM `posts` AS p INNER JOIN `users` AS u ON p.user_id = u.id WHERE p.user_id = 333 AND u.del_flg = 0 ORDER BY p.created_at DESC LIMIT 20\G

# Query 3: 738.21 QPS, 0.30x concurrency, ID 0xC62EF1FBCF396B87C7C278744BCF1D39 at byte 47103223
# Scores: V/M = 0.00
# Time range: 2023-09-14T12:34:51 to 2023-09-14T12:35:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         12   45031
# Exec time     18     18s   103us    31ms   400us     1ms   876us   159us
# Lock time     38   176ms       0     5ms     3us     2us    51us     1us
# Rows sent     21  37.07k       0       3    0.84    2.90    1.30       0
# Rows examine   1  74.15k       0       6    1.69    5.75    2.57       0
# Query size    57  18.54M     428     432  431.72  420.77       0  420.77
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  #####
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'comments'\G
#    SHOW CREATE TABLE `isuconp`.`comments`\G
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'users'\G
#    SHOW CREATE TABLE `isuconp`.`users`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT c.id AS `id`, c.post_id AS `post_id`, c.user_id AS `user_id`, c.comment AS `comment`, c.created_at AS `created_at`, u.id AS `user.id`, u.account_name AS `user.account_name`, u.passhash AS `user.passhash`, u.authority AS `user.authority`, u.del_flg AS `user.del_flg`, u.created_at AS `user.created_at` FROM `comments` AS c INNER JOIN `users` AS u ON c.user_id = u.id WHERE c.post_id = 12555 ORDER BY c.created_at DESC  LIMIT 3\G

# Query 4: 776.18 QPS, 0.24x concurrency, ID 0x422390B42D4DD86C7539A5F45EB76A80 at byte 35905769
# Scores: V/M = 0.00
# Time range: 2023-09-14T12:34:51 to 2023-09-14T12:35:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         13   47347
# Exec time     15     15s    85us    19ms   313us     1ms   724us   131us
# Lock time     40   184ms       0     6ms     3us     1us    60us     1us
# Rows sent     27  46.24k       1       1       1       1       0       1
# Rows examine   2 144.42k       0      23    3.12   12.54    4.86       0
# Query size     9   2.97M      62      66   65.68   65.89    1.45   65.89
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  ###
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'comments'\G
#    SHOW CREATE TABLE `isuconp`.`comments`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = 12531\G

# Query 5: 200.66 QPS, 0.20x concurrency, ID 0x19759A5557089FD5B718D440CBBB5C55 at byte 8342277
# Scores: V/M = 0.00
# Time range: 2023-09-14T12:34:51 to 2023-09-14T12:35:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3   12240
# Exec time     12     12s   131us    35ms     1ms     4ms     2ms   301us
# Lock time     11    51ms       0     7ms     4us     2us    81us     1us
# Rows sent      7  11.95k       1       1       1       1       0       1
# Rows examine   0  11.95k       1       1       1       1       0       1
# Query size     1 465.86k      36      40   38.97   38.53    0.34   38.53
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ###################
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'posts'\G
#    SHOW CREATE TABLE `isuconp`.`posts`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `posts` WHERE `id` = 5820\G

# Query 6: 5.63 QPS, 0.05x concurrency, ID 0xC9383ACA6FF14C29E819735F00B6DBDF at byte 85273629
# Scores: V/M = 0.00
# Time range: 2023-09-14T12:34:51 to 2023-09-14T12:35:51
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     338
# Exec time      2      3s     3ms    41ms     8ms    20ms     6ms     6ms
# Lock time      0   701us     1us    57us     2us     1us     3us     1us
# Rows sent      1   3.29k       2      19    9.97   14.52    3.00    9.83
# Rows examine  46   3.24M   9.77k   9.87k   9.82k   9.80k   52.50   9.80k
# Query size     0  15.15k      45      46   45.90   44.60       0   44.60
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  #########################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'posts'\G
#    SHOW CREATE TABLE `isuconp`.`posts`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `id` FROM `posts` WHERE `user_id` = 761\G

# Query 7: 1.91k QPS, 0.03x concurrency, ID 0x07890000813C4CC7111FD2D3F3B3B4EB at byte 43302214
# Scores: V/M = 0.00
# Time range: 2023-09-14T12:34:51 to 2023-09-14T12:35:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         32  116253
# Exec time      1      2s     1us    21ms    14us    11us   147us     5us
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    11   3.66M      33      33      33      33       0      33
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us  ################################################################
#  10us  ####
# 100us  #
#   1ms  #
#  10ms  #
# 100ms
#    1s
#  10s+
administrator command: Close stmt\G

# Query 8: 37.97 QPS, 0.02x concurrency, ID 0x90F44B8FEAE9719F0F2F227272752D5B at byte 28979959
# Scores: V/M = 0.00
# Time range: 2023-09-14T12:34:51 to 2023-09-14T12:35:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    2316
# Exec time      1      1s   108us    18ms   597us     2ms     1ms   273us
# Lock time      1     7ms     1us   852us     3us     2us    24us     1us
# Rows sent     12  21.91k       0      23    9.69   15.25    3.74    9.83
# Rows examine   0  43.82k       0      46   19.38   31.70    7.45   19.46
# Query size     2 954.33k     419     423  421.95  420.77    1.94  420.77
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ########
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'comments'\G
#    SHOW CREATE TABLE `isuconp`.`comments`\G
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'users'\G
#    SHOW CREATE TABLE `isuconp`.`users`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT c.id AS `id`, c.post_id AS `post_id`, c.user_id AS `user_id`, c.comment AS `comment`, c.created_at AS `created_at`, u.id AS `user.id`, u.account_name AS `user.account_name`, u.passhash AS `user.passhash`, u.authority AS `user.authority`, u.del_flg AS `user.del_flg`, u.created_at AS `user.created_at` FROM `comments` AS c INNER JOIN `users` AS u ON c.user_id = u.id WHERE c.post_id = 9834 ORDER BY c.created_at DESC\G

# Query 9: 28.57 QPS, 0.02x concurrency, ID 0xB213742E2129B37E68649AEE9EA0599F at byte 85038528
# Scores: V/M = 0.00
# Time range: 2023-09-14T12:34:51 to 2023-09-14T12:35:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    1743
# Exec time      0   986ms   206us    14ms   565us     2ms   958us   273us
# Lock time      0     5ms     1us   123us     2us     2us     4us     1us
# Rows sent     20  34.04k      20      20      20      20       0      20
# Rows examine   0  68.44k      40      44   40.21   40.45    0.75   38.53
# Query size     2 702.99k     413     413     413     413       0     413
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  #######
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'posts'\G
#    SHOW CREATE TABLE `isuconp`.`posts`\G
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'users'\G
#    SHOW CREATE TABLE `isuconp`.`users`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT p.id AS `id`, p.user_id AS `user_id`, p.body AS `body`, p.mime AS `mime`, p.created_at AS `created_at`, u.id AS `user.id`, u.account_name AS `user.account_name`, u.passhash AS `user.passhash`, u.authority AS `user.authority`, u.del_flg AS `user.del_flg`, u.created_at AS `user.created_at` FROM `posts` AS p INNER JOIN `users` AS u ON p.user_id = u.id WHERE u.del_flg = 0 ORDER BY p.created_at DESC LIMIT 20\G

# Query 10: 2.22 QPS, 0.01x concurrency, ID 0x9F2038550F51B0A3AB05CA526E3FEDDC at byte 47567545
# Scores: V/M = 0.02
# Time range: 2023-09-14T12:34:51 to 2023-09-14T12:35:51
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     133
# Exec time      0   854ms     1ms   119ms     6ms    11ms    11ms     4ms
# Lock time      0   354us     1us   121us     2us     1us    10us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     0  17.71k      89     198  136.39  174.84   24.24  136.99
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  #####
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'comments'\G
#    SHOW CREATE TABLE `isuconp`.`comments`\G
INSERT INTO `comments` (`post_id`, `user_id`, `comment`) VALUES (9621,621,'☆~~ヾ･ｪ･)o尸~ ﾏﾀﾈ~♪')\G

そろそろ何すれば良いかわからなくなってきた。

実践

コード見ていると外部コマンドを呼び出す良くなさそうな実装があったので修正。

8e609b4b8088f32e0e764ea28e019f91bbce8c46

結果

8000点UP!

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:80" -u ../benchmarker/userdata
{"pass":true,"score":63315,"success":60359,"fail":0,"messages":[]}

CPU使用率は50%ほど。

推測

2個↑の上位5個をEXPLAINで見てみる。

mysql> EXPLAIN SELECT p.id AS `id`, p.user_id AS `user_id`, p.body AS `body`, p.mime AS `mime`, p.created_at AS `created_at`, u.id AS `user.id`, u.account_name AS `user.account_name`, u.passhash AS `user.passhash`, u.authority AS `user.authority`, u.del_flg AS `user.del_flg`, u.created_at AS `user.created_at` FROM `posts` AS p INNER JOIN `users` AS u ON p.user_id = u.id WHERE p.user_id = 333 AND u.del_flg = 0 ORDER BY p.created_at DESC LIMIT 20;
+----+-------------+-------+------------+-------+---------------+-----------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key             | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | const | PRIMARY       | PRIMARY         | 4       | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | p     | NULL       | index | NULL          | posts_order_idx | 4       | NULL  |   20 |    10.00 | Using where |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

mysql> EXPLAIN SELECT c.id AS `id`, c.post_id AS `post_id`, c.user_id AS `user_id`, c.comment AS `comment`, c.created_at AS `created_at`, u.id AS `user.id`, u.account_name AS `user.account_name`, u.passhash AS `user.passhash`, u.authority AS `user.authority`, u.del_flg AS `user.del_flg`, u.created_at AS `user.created_at` FROM `comments` AS c INNER JOIN `users` AS u ON c.user_id = u.id WHERE c.post_id = 12555 ORDER BY c.created_at DESC  LIMIT 3;
+----+-------------+-------+------------+--------+-------------------------+-------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys           | key         | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-------------------------+-------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | c     | NULL       | ref    | post_id_idx,user_id_idx | post_id_idx | 4       | const             |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY                 | PRIMARY     | 4       | isuconp.c.user_id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+-------------------------+-------------+---------+-------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = 12531;
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | comments | NULL       | ref  | post_id_idx   | post_id_idx | 4       | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM `posts` WHERE `id` = 5820;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | posts | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)

mysql> EXPLAIN SELECT `id` FROM `posts` WHERE `user_id` = 761;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | posts | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10120 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

1番上はこれで解決するらしい
2, 3, 4, 5番目は index が効いてそう。
6番目はindexなかったので貼る

実践・結果

mysql> ALTER TABLE posts ADD INDEX user_id_idx (user_id)
    -> ;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT `id` FROM `posts` WHERE `user_id` = 761;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | posts | NULL       | ref  | user_id_idx   | user_id_idx | 4       | const |   11 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

indexを貼ったらスコアが下がった。おそらくindexが多すぎて書き込みに時間がかかっている。

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:80" -u ../benchmarker/userdata
{"pass":true,"score":40298,"success":39566,"fail":0,"messages":[]}

63164bfe8244d2d43c8286b4896c19995103cfc5 をした。スコア上がった。

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:80" -u ../benchmarker/userdata
{"pass":true,"score":47733,"success":46989,"fail":0,"messages":[]}

indexをdropする。かなりスコアが上がったので、postsテーブルのuser_idにはindexをつけないようにする。

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:80" -u ../benchmarker/userdata
{"pass":true,"score":80009,"success":76110,"fail":0,"messages":[]}

推測

imageがまだ遅そうな気がする。

isucon@tk2-208-13709:~/private_isu/webapp$ ./rotate_and_cp.sh
+-------+-----+-------+-------+-----+-----+--------+--------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-------------+----------------+-----------+
| COUNT | 1XX |  2XX  |  3XX  | 4XX | 5XX | METHOD |        URI         |  MIN  |  MAX  |   SUM   |  AVG  |  P90  |  P95  |  P99  | STDDEV | MIN(BODY) |  MAX(BODY)  |   SUM(BODY)    | AVG(BODY) |
+-------+-----+-------+-------+-----+-----+--------+--------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-------------+----------------+-----------+
| 2777  | 0   | 2777  | 0     | 0   | 0   | GET    | /                  | 0.012 | 0.380 | 298.004 | 0.107 | 0.148 | 0.164 | 0.196 | 0.033  | 2531.000  | 6051.000    | 8050944.000    | 2899.152  |
| 48362 | 0   | 13953 | 34409 | 0   | 0   | GET    | /image/\d+         | 0.000 | 0.120 | 91.416  | 0.002 | 0.008 | 0.012 | 0.020 | 0.005  | 48015.000 | 1221083.000 | 1955235961.000 | 40429.179 |
| 388   | 0   | 388   | 0     | 0   | 0   | GET    | /@\w+              | 0.028 | 0.472 | 74.136  | 0.191 | 0.260 | 0.280 | 0.336 | 0.055  | 1348.000  | 5356.000    | 1269069.000    | 3270.796  |
| 420   | 0   | 420   | 0     | 0   | 0   | GET    | /posts             | 0.020 | 0.464 | 46.644  | 0.111 | 0.152 | 0.168 | 0.220 | 0.040  | 4932.000  | 5773.000    | 2243682.000    | 5342.100  |
| 2587  | 0   | 2587  | 0     | 0   | 0   | GET    | /posts/[0-9]+      | 0.004 | 0.104 | 26.988  | 0.010 | 0.020 | 0.024 | 0.036 | 0.008  | 702.000   | 2027.000    | 3331666.000    | 1287.849  |
| 1969  | 0   | 0     | 1969  | 0   | 0   | POST   | /login             | 0.000 | 0.256 | 11.824  | 0.006 | 0.012 | 0.016 | 0.024 | 0.008  | 0.000     | 0.000       | 0.000          | 0.000     |
| 372   | 0   | 0     | 186   | 186 | 0   | POST   | /                  | 0.004 | 0.252 | 8.892   | 0.024 | 0.052 | 0.060 | 0.100 | 0.024  | 0.000     | 0.000       | 0.000          | 0.000     |
| 233   | 0   | 0     | 233   | 0   | 0   | POST   | /register          | 0.004 | 0.272 | 4.016   | 0.017 | 0.024 | 0.036 | 0.192 | 0.029  | 0.000     | 0.000       | 0.000          | 0.000     |
| 173   | 0   | 0     | 173   | 0   | 0   | POST   | /comment           | 0.004 | 0.328 | 3.712   | 0.021 | 0.028 | 0.040 | 0.292 | 0.038  | 0.000     | 0.000       | 0.000          | 0.000     |
| 794   | 0   | 794   | 0     | 0   | 0   | GET    | /login             | 0.004 | 0.012 | 1.652   | 0.002 | 0.004 | 0.008 | 0.012 | 0.003  | 615.000   | 615.000     | 488310.000     | 615.000   |
| 233   | 0   | 0     | 0     | 233 | 0   | GET    | /admin/banned      | 0.004 | 0.032 | 1.364   | 0.006 | 0.012 | 0.016 | 0.024 | 0.006  | 0.000     | 0.000       | 0.000          | 0.000     |
| 397   | 0   | 0     | 397   | 0   | 0   | GET    | /logout            | 0.000 | 0.016 | 0.888   | 0.002 | 0.004 | 0.008 | 0.012 | 0.003  | 24.000    | 24.000      | 9528.000       | 24.000    |
| 1     | 0   | 1     | 0     | 0   | 0   | GET    | /initialize        | 0.040 | 0.040 | 0.040   | 0.040 | 0.040 | 0.040 | 0.040 | 0.000  | 0.000     | 0.000       | 0.000          | 0.000     |
| 4209  | 0   | 1     | 4208  | 0   | 0   | GET    | /js/timeago.min.js | 0.000 | 0.000 | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 1915.000    | 1915.000       | 0.455     |
| 4209  | 0   | 1     | 4208  | 0   | 0   | GET    | /js/main.js        | 0.000 | 0.000 | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 1824.000    | 1824.000       | 0.433     |
| 4209  | 0   | 1     | 4208  | 0   | 0   | GET    | /css/style.css     | 0.000 | 0.000 | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 1549.000    | 1549.000       | 0.368     |
| 4209  | 0   | 1     | 4208  | 0   | 0   | GET    | /favicon.ico       | 0.000 | 0.000 | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 43.000      | 43.000         | 0.010     |
+-------+-----+-------+-------+-----+-----+--------+--------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-------------+----------------+-----------+

# 28.2s user time, 290ms system time, 29.67M rss, 32.73M vsz
# Current date: Sun Sep 17 18:42:30 2023
# Hostname: tk2-208-13709
# Files: /var/log/mysql/mysqlslow.log
# Overall: 186.68k total, 26 unique, 804.65 QPS, 0.96x concurrency _______
# Time range: 2023-09-17T09:38:32 to 2023-09-17T09:42:24
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           223s     1us   375ms     1ms     4ms     5ms   260us
# Lock time             1s       0    16ms     7us     2us   139us     1us
# Rows sent        226.51k       0      23    1.24    2.90    2.82    0.99
# Rows examine       7.98M       0   9.95k   44.81   12.54  639.74       0
# Query size        37.21M      10     461  208.99  420.77  180.29   65.89

# Profile
# Rank Query ID                      Response time Calls R/Call V/M   Item
# ==== ============================= ============= ===== ====== ===== ====
#    1 0xC62EF1FBCF396B87C7C27874... 74.5362 33.4% 67914 0.0011  0.00 SELECT comments users
#    2 0x422390B42D4DD86C7539A5F4... 56.7886 25.4% 70501 0.0008  0.00 SELECT comments
#    3 0x40771BF2BF7DC042D68D0457... 36.1304 16.2%   388 0.0931  0.02 SELECT posts users
#    4 0x19759A5557089FD5B718D440... 25.5133 11.4% 13767 0.0019  0.01 SELECT posts
#    5 0xC9383ACA6FF14C29E819735F...  6.0460  2.7%   388 0.0156  0.01 SELECT posts
#    6 0xB213742E2129B37E68649AEE...  4.4882  2.0%  2778 0.0016  0.04 SELECT posts users
#    7 0x90F44B8FEAE9719F0F2F2272...  3.7846  1.7%  2587 0.0015  0.00 SELECT comments users
#    8 0x26489ECBE26887E480CA8067...  2.3574  1.1%   233 0.0101  0.08 INSERT users
#    9 0x9F2038550F51B0A3AB05CA52...  2.2458  1.0%   173 0.0130  0.10 INSERT comments
#   10 0x995F41A1456C1CF6746D9652...  1.9684  0.9%  9458 0.0002  0.00 SET
# MISC 0xMISC                         9.4333  4.2% 18491 0.0005   0.0 <16 ITEMS>

・・・

実践

DBに入っているやつを先に書き出して配置。全部nginxに任せる。

fd97fb455cca5e78c86dd4ce311e5c5a7ef0f705

結果

やっと10万点超えた！

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:80" -u ../benchmarker/userdata
{"pass":true,"score":113902,"success":109300,"fail":0,"messages":[]}

実践

毎回パースするのは無駄なので、テンプレートを先にパースしておく。

b4da07971c0205ccf30006e8df992cd65ec38226

結果

微増

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:80" -u ../benchmarker/userdata
{"pass":true,"score":119113,"success":114295,"fail":0,"messages":[]}

実践

ChatGPTに以下のサーバーでのMySQLの設定ファイルを考えてもらった

SSD: 100GB
仮想3Core: 2GB メモリ

f913153780d49bb1f7ba52a8f76103de9c5630e2

結果

微増

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:80" -u ../benchmarker/userdata
{"pass":true,"score":122489,"success":117492,"fail":0,"messages":[]}

結果

MySQLの接続設定を追加

1

とーふとふさんのを真似した。

1e832ba4e3dc9f98013d302640b459be7f797c04

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:80" -u ../benchmarker/userdata
{"pass":true,"score":130059,"success":124622,"fail":0,"messages":[]}

2

unix domain soketを使ってMySQLに接続。

cc123d85b35c71c3d6ec94c342872219c2153668

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:80" -u ../benchmarker/userdata
{"pass":true,"score":133262,"success":127760,"fail":0,"messages":[]}

実践

とーふとふさん秘伝のISUCON用mysql, nginxの設定ファイルを追加

f6a780adff9942ca99ef14a7b1aca4e9a165a3d8

結果

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:80" -u ../benchmarker/userdata
{"pass":true,"score":137498,"success":131604,"fail":0,"messages":[]}

CPU使用率

alp

+-------+-----+------+-------+-----+-----+--------+--------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-------------+---------------+-----------+
| COUNT | 1XX | 2XX  |  3XX  | 4XX | 5XX | METHOD |        URI         |  MIN  |  MAX  |   SUM   |  AVG  |  P90  |  P95  |  P99  | STDDEV | MIN(BODY) |  MAX(BODY)  |   SUM(BODY)   | AVG(BODY) |
+-------+-----+------+-------+-----+-----+--------+--------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-------------+---------------+-----------+
| 4167  | 0   | 4167 | 0     | 0   | 0   | GET    | /                  | 0.012 | 0.184 | 325.276 | 0.078 | 0.108 | 0.120 | 0.136 | 0.025  | 2529.000  | 6040.000    | 11883074.000  | 2851.710  |
| 622   | 0   | 622  | 0     | 0   | 0   | GET    | /@\w+              | 0.032 | 0.344 | 111.148 | 0.179 | 0.244 | 0.260 | 0.296 | 0.053  | 1348.000  | 5356.000    | 2026265.000   | 3257.661  |
| 1120  | 0   | 1120 | 0     | 0   | 0   | GET    | /posts             | 0.012 | 0.220 | 97.664  | 0.087 | 0.120 | 0.132 | 0.156 | 0.027  | 4963.000  | 5785.000    | 5981776.000   | 5340.871  |
| 4616  | 0   | 4616 | 0     | 0   | 0   | GET    | /posts/[0-9]+      | 0.000 | 0.052 | 27.432  | 0.006 | 0.012 | 0.016 | 0.024 | 0.005  | 687.000   | 2005.000    | 5923000.000   | 1283.146  |
| 3001  | 0   | 0    | 3001  | 0   | 0   | POST   | /login             | 0.004 | 0.032 | 6.332   | 0.002 | 0.004 | 0.008 | 0.012 | 0.003  | 0.000     | 0.000       | 0.000         | 0.000     |
| 548   | 0   | 0    | 274   | 274 | 0   | POST   | /                  | 0.004 | 0.148 | 6.200   | 0.011 | 0.028 | 0.032 | 0.048 | 0.012  | 0.000     | 0.000       | 0.000         | 0.000     |
| 331   | 0   | 0    | 331   | 0   | 0   | POST   | /register          | 0.004 | 0.028 | 1.348   | 0.004 | 0.008 | 0.012 | 0.020 | 0.004  | 0.000     | 0.000       | 0.000         | 0.000     |
| 1274  | 0   | 1274 | 0     | 0   | 0   | GET    | /login             | 0.000 | 0.016 | 1.128   | 0.001 | 0.004 | 0.004 | 0.004 | 0.002  | 615.000   | 615.000     | 783510.000    | 615.000   |
| 211   | 0   | 0    | 211   | 0   | 0   | POST   | /comment           | 0.000 | 0.032 | 1.068   | 0.005 | 0.012 | 0.012 | 0.016 | 0.005  | 0.000     | 0.000       | 0.000         | 0.000     |
| 637   | 0   | 0    | 637   | 0   | 0   | GET    | /logout            | 0.000 | 0.016 | 0.772   | 0.001 | 0.004 | 0.004 | 0.008 | 0.002  | 24.000    | 24.000      | 15288.000     | 24.000    |
| 331   | 0   | 0    | 0     | 331 | 0   | GET    | /admin/banned      | 0.000 | 0.020 | 0.768   | 0.002 | 0.004 | 0.008 | 0.012 | 0.003  | 0.000     | 0.000       | 0.000         | 0.000     |
| 1     | 0   | 1    | 0     | 0   | 0   | GET    | /initialize        | 0.028 | 0.028 | 0.028   | 0.028 | 0.028 | 0.028 | 0.028 | 0.000  | 0.000     | 0.000       | 0.000         | 0.000     |
| 7288  | 0   | 1    | 7287  | 0   | 0   | GET    | /js/timeago.min.js | 0.000 | 0.000 | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 1915.000    | 1915.000      | 0.263     |
| 7288  | 0   | 1    | 7287  | 0   | 0   | GET    | /js/main.js        | 0.000 | 0.000 | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 1824.000    | 1824.000      | 0.250     |
| 7288  | 0   | 1    | 7287  | 0   | 0   | GET    | /css/style.css     | 0.000 | 0.000 | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 553.000     | 553.000       | 0.076     |
| 85856 | 0   | 3835 | 82018 | 3   | 0   | GET    | /image/\d+         | 0.000 | 0.000 | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 1219758.000 | 585439529.000 | 6818.854  |
| 7288  | 0   | 1    | 7287  | 0   | 0   | GET    | /favicon.ico       | 0.000 | 0.000 | 0.000   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000  | 0.000     | 43.000      | 43.000        | 0.006     |
+-------+-----+------+-------+-----+-----+--------+--------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-------------+---------------+-----------+

pt-query-digest

# 39.4s user time, 500ms system time, 29.45M rss, 32.62M vsz
# Current date: Sun Sep 24 17:06:01 2023
# Hostname: tk2-208-13709
# Files: /var/log/mysql/mysqlslow.log
# Overall: 254.41k total, 24 unique, 4.04k QPS, 4.57x concurrency ________
# Time range: 2023-09-24T08:03:46 to 2023-09-24T08:04:49
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           288s     3us   220ms     1ms     3ms     5ms   316us
# Lock time             2s       0    20ms     6us     2us    98us     1us
# Rows sent        376.62k       0      23    1.52    2.90    3.08    0.99
# Rows examine      12.96M       0  10.03k   53.43   15.25  695.58       0
# Query size        60.14M      10     461  247.86  420.77  177.50  381.65

# Profile
# Rank Query ID                     Response time  Calls  R/Call V/M   Ite
# ==== ============================ ============== ====== ====== ===== ===
#    1 0xC62EF1FBCF396B87C7C2787... 113.1366 39.3% 112081 0.0010  0.00 SELECT comments users
#    2 0x422390B42D4DD86C7539A5F...  83.1795 28.9% 116697 0.0007  0.00 SELECT comments
#    3 0x40771BF2BF7DC042D68D045...  60.0169 20.8%    622 0.0965  0.01 SELECT posts users
#    4 0xC9383ACA6FF14C29E819735...   8.7625  3.0%    622 0.0141  0.01 SELECT posts
#    5 0x90F44B8FEAE9719F0F2F227...   5.8528  2.0%   4616 0.0013  0.00 SELECT comments users
#    6 0xB213742E2129B37E68649AE...   5.2257  1.8%   4167 0.0013  0.00 SELECT posts users
# MISC 0xMISC                        11.9804  4.2%  15601 0.0008   0.0 <18 ITEMS>

# Query 1: 1.84k QPS, 1.85x concurrency, ID 0xC62EF1FBCF396B87C7C278744BCF1D39 at byte 110074796
# Scores: V/M = 0.00
# Time range: 2023-09-24T08:03:48 to 2023-09-24T08:04:49
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         44  112081
# Exec time     39    113s   130us    41ms     1ms     4ms     1ms   403us
# Lock time     47   839ms       0    20ms     7us     2us   107us     1us
# Rows sent     24  93.16k       0       3    0.85    2.90    1.31       0
# Rows examine   1 186.32k       0       6    1.70    5.75    2.58       0
# Query size    76  46.15M     428     432  431.71  420.77       0  420.77
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ###########################
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'comments'\G
#    SHOW CREATE TABLE `isuconp`.`comments`\G
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'users'\G
#    SHOW CREATE TABLE `isuconp`.`users`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT c.id AS `id`, c.post_id AS `post_id`, c.user_id AS `user_id`, c.comment AS `comment`, c.created_at AS `created_at`, u.id AS `user.id`, u.account_name AS `user.account_name`, u.passhash AS `user.passhash`, u.authority AS `user.authority`, u.del_flg AS `user.del_flg`, u.created_at AS `user.created_at` FROM `comments` AS c INNER JOIN `users` AS u ON c.user_id = u.id WHERE c.post_id = 17058 ORDER BY c.created_at DESC  LIMIT 3\G

# Query 2: 1.91k QPS, 1.36x concurrency, ID 0x422390B42D4DD86C7539A5F45EB76A80 at byte 50915538
# Scores: V/M = 0.00
# Time range: 2023-09-24T08:03:48 to 2023-09-24T08:04:49
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         45  116697
# Exec time     28     83s    95us    34ms   712us     3ms     1ms   247us
# Lock time     43   780ms       0    10ms     6us     1us    87us     1us
# Rows sent     30 113.96k       1       1       1       1       0       1
# Rows examine   2 347.30k       0      23    3.05   12.54    4.79       0
# Query size    12   7.31M      62      66   65.69   65.89    1.45   65.89
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  ################
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'comments'\G
#    SHOW CREATE TABLE `isuconp`.`comments`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = 16912\G

# Query 3: 10.20 QPS, 0.98x concurrency, ID 0x40771BF2BF7DC042D68D0457BF75CDEC at byte 101758809
# Scores: V/M = 0.01
# Time range: 2023-09-24T08:03:48 to 2023-09-24T08:04:49
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     622
# Exec time     20     60s    18ms   220ms    96ms   155ms    37ms    91ms
# Lock time      0     3ms     1us   894us     5us     2us    45us     1us
# Rows sent      1   6.19k       2      20   10.19   14.52    2.94    9.83
# Rows examine  46   6.01M   9.34k  10.03k   9.90k   9.80k  158.19   9.80k
# Query size     0 262.97k     432     433  432.92  420.77       0  420.77
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  #########################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'posts'\G
#    SHOW CREATE TABLE `isuconp`.`posts`\G
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'users'\G
#    SHOW CREATE TABLE `isuconp`.`users`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT p.id AS `id`, p.user_id AS `user_id`, p.body AS `body`, p.mime AS `mime`, p.created_at AS `created_at`, u.id AS `user.id`, u.account_name AS `user.account_name`, u.passhash AS `user.passhash`, u.authority AS `user.authority`, u.del_flg AS `user.del_flg`, u.created_at AS `user.created_at` FROM `posts` AS p INNER JOIN `users` AS u ON p.user_id = u.id WHERE p.user_id = 296 AND u.del_flg = 0 ORDER BY p.created_at DESC LIMIT 20\G

# Query 4: 10.20 QPS, 0.14x concurrency, ID 0xC9383ACA6FF14C29E819735F00B6DBDF at byte 75173968
# Scores: V/M = 0.01
# Time range: 2023-09-24T08:03:48 to 2023-09-24T08:04:49
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     622
# Exec time      3      9s     3ms    57ms    14ms    30ms     9ms    12ms
# Lock time      0     4ms     1us   828us     5us     1us    47us     1us
# Rows sent      1   6.19k       2      21   10.20   14.52    2.94    9.83
# Rows examine  46   6.01M   9.77k  10.03k   9.90k   9.80k     137   9.80k
# Query size     0  27.89k      45      46   45.92   44.60       0   44.60
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ###########################################
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'posts'\G
#    SHOW CREATE TABLE `isuconp`.`posts`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `id` FROM `posts` WHERE `user_id` = 389\G

# Query 5: 75.67 QPS, 0.10x concurrency, ID 0x90F44B8FEAE9719F0F2F227272752D5B at byte 78290701
# Scores: V/M = 0.00
# Time range: 2023-09-24T08:03:48 to 2023-09-24T08:04:49
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1    4616
# Exec time      2      6s   145us    43ms     1ms     4ms     2ms   515us
# Lock time      1    29ms     1us     2ms     6us     2us    59us     1us
# Rows sent     11  41.46k       0      23    9.20   14.52    3.92    9.83
# Rows examine   0  82.92k       0      46   18.39   28.75    7.79   19.46
# Query size     3   1.86M     419     423  421.98  420.77    1.83  420.77
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ###################################
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'comments'\G
#    SHOW CREATE TABLE `isuconp`.`comments`\G
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'users'\G
#    SHOW CREATE TABLE `isuconp`.`users`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT c.id AS `id`, c.post_id AS `post_id`, c.user_id AS `user_id`, c.comment AS `comment`, c.created_at AS `created_at`, u.id AS `user.id`, u.account_name AS `user.account_name`, u.passhash AS `user.passhash`, u.authority AS `user.authority`, u.del_flg AS `user.del_flg`, u.created_at AS `user.created_at` FROM `comments` AS c INNER JOIN `users` AS u ON c.user_id = u.id WHERE c.post_id = 16811 ORDER BY c.created_at DESC\G

# Query 6: 68.31 QPS, 0.09x concurrency, ID 0xB213742E2129B37E68649AEE9EA0599F at byte 58282990
# Scores: V/M = 0.00
# Time range: 2023-09-24T08:03:48 to 2023-09-24T08:04:49
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1    4167
# Exec time      1      5s   235us    27ms     1ms     5ms     2ms   541us
# Lock time      2    36ms     1us     8ms     8us     2us   163us     1us
# Rows sent     21  81.39k      20      20      20      20       0      20
# Rows examine   1 163.24k      40      44   40.11   38.53    0.58   38.53
# Query size     2   1.64M     413     413     413     413       0     413
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ##############################
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'posts'\G
#    SHOW CREATE TABLE `isuconp`.`posts`\G
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'users'\G
#    SHOW CREATE TABLE `isuconp`.`users`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT p.id AS `id`, p.user_id AS `user_id`, p.body AS `body`, p.mime AS `mime`, p.created_at AS `created_at`, u.id AS `user.id`, u.account_name AS `user.account_name`, u.passhash AS `user.passhash`, u.authority AS `user.authority`, u.del_flg AS `user.del_flg`, u.created_at AS `user.created_at` FROM `posts` AS p INNER JOIN `users` AS u ON p.user_id = u.id WHERE u.del_flg = 0 ORDER BY p.created_at DESC LIMIT 20\G

推測

/ が重いので直そう。

getIndexをキャッシュする。

実践

これをした。

複数の呼び出し元から同時に同じ関数が呼び出された際、最初の呼び出し元から呼ばれた処理結果を一時的に保存し、他の呼び出し元でも再利用することが可能となります。実際のユースケースとしては、Redis や Memcached などのキャッシュと組み合わせてDBリクエスト処理時に使用して、キャッシュの TTL が切れたタイミングでも一度の DB リクエストだけで再度キャッシュできるため、DB接続数を抑えることができます。また、CPU やメモリなどのリソースを大量に消費する処理で使用することにより、サーバリソースを節約することができます。

https://zenn.dev/nkmrkz/articles/go-singleflight

8106ad73cb67da6db2f1fa502d8496757b0612bc

結果

20万点突破！

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:80" -u ../benchmarker/userdata
{"pass":true,"score":233067,"success":222283,"fail":0,"messages":[]}

次はログとか消してベンチ回してみる

alp

なんかいっぱい出てきた。 / がめっちゃ早くなった

+--------+-----+------+--------+------+-----+---------+----------------------------------------+--------+--------+--------+--------+--------+--------+--------+--------+-----------+-------------+---------------+-----------+
| COUNT  | 1XX | 2XX  |  3XX   | 4XX  | 5XX | METHOD  |                  URI                   |  MIN   |  MAX   |  SUM   |  AVG   |  P90   |  P95   |  P99   | STDDEV | MIN(BODY) |  MAX(BODY)  |   SUM(BODY)   | AVG(BODY) |
+--------+-----+------+--------+------+-----+---------+----------------------------------------+--------+--------+--------+--------+--------+--------+--------+--------+-----------+-------------+---------------+-----------+
| 1130   | 0   | 1130 | 0      | 0    | 0   | GET     | /posts                                 | 0.016  | 0.204  | 86.682 | 0.077  | 0.116  | 0.124  | 0.148  | 0.028  | 4956.000  | 5817.000    | 6059608.000   | 5362.485  |
| 9639   | 0   | 9638 | 0      | 1    | 0   | GET     | /                                      | 0.004  | 0.108  | 85.192 | 0.009  | 0.016  | 0.024  | 0.032  | 0.007  | 45.000    | 22386.000   | 58400212.000  | 6058.742  |
| 776    | 0   | 776  | 0      | 0    | 0   | GET     | /@\w+                                  | 0.032  | 0.204  | 78.758 | 0.101  | 0.140  | 0.152  | 0.180  | 0.028  | 1567.000  | 5356.000    | 2536029.000   | 3268.079  |
| 4714   | 0   | 4714 | 0      | 0    | 0   | GET     | /posts/[0-9]+                          | 0.004  | 0.112  | 44.716 | 0.009  | 0.020  | 0.024  | 0.032  | 0.007  | 704.000   | 1981.000    | 5935064.000   | 1259.029  |
| 5440   | 0   | 0    | 5440   | 0    | 0   | POST    | /login                                 | 0.004  | 0.088  | 31.252 | 0.006  | 0.012  | 0.016  | 0.024  | 0.006  | 0.000     | 0.000       | 0.000         | 0.000     |
| 976    | 0   | 0    | 489    | 487  | 0   | POST    | /                                      | 0.000  | 0.156  | 18.664 | 0.019  | 0.044  | 0.052  | 0.072  | 0.017  | 0.000     | 0.000       | 0.000         | 0.000     |
| 1      | 0   | 0    | 0      | 1    | 0   | POST    | /HNAP1/                                | 13.579 | 13.579 | 13.579 | 13.579 | 13.579 | 13.579 | 13.579 | 0.000  | 0.000     | 0.000       | 0.000         | 0.000     |
| 2013   | 0   | 2013 | 0      | 0    | 0   | GET     | /login                                 | 0.004  | 0.044  | 9.132  | 0.005  | 0.012  | 0.016  | 0.024  | 0.005  | 550.000   | 615.000     | 1237930.000   | 614.968   |
| 1036   | 0   | 0    | 1036   | 0    | 0   | POST    | /register                              | 0.004  | 0.080  | 7.492  | 0.007  | 0.016  | 0.020  | 0.032  | 0.007  | 0.000     | 0.000       | 0.000         | 0.000     |
| 1006   | 0   | 0    | 1006   | 0    | 0   | GET     | /logout                                | 0.000  | 0.040  | 6.000  | 0.006  | 0.012  | 0.016  | 0.024  | 0.006  | 24.000    | 24.000      | 24144.000     | 24.000    |
| 1036   | 0   | 0    | 0      | 1036 | 0   | GET     | /admin/banned                          | 0.008  | 0.028  | 5.616  | 0.005  | 0.012  | 0.016  | 0.024  | 0.005  | 0.000     | 0.000       | 0.000         | 0.000     |
| 412    | 0   | 0    | 412    | 0    | 0   | POST    | /comment                               | 0.004  | 0.036  | 2.596  | 0.006  | 0.012  | 0.016  | 0.028  | 0.006  | 0.000     | 0.000       | 0.000         | 0.000     |
| 1      | 0   | 1    | 0      | 0    | 0   | GET     | /initialize                            | 0.064  | 0.064  | 0.064  | 0.064  | 0.064  | 0.064  | 0.064  | 0.000  | 0.000     | 0.000       | 0.000         | 0.000     |
| 2      | 0   | 0    | 0      | 2    | 0   | OPTIONS | /                                      | 0.004  | 0.004  | 0.008  | 0.004  | 0.004  | 0.004  | 0.004  | 0.000  | 0.000     | 0.000       | 0.000         | 0.000     |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /incl/image_test.shtml                 | 0.004  | 0.004  | 0.008  | 0.004  | 0.004  | 0.004  | 0.004  | 0.000  | 45.000    | 45.000      | 90.000        | 45.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /.env.example                          | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /robots.txt                            | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /website/wp-includes/wlwmanifest.xml   | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /xmlrpc.php                            | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /actuator/gateway/routes               | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /stag/.env                             | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /backend/.env                          | 0.004  | 0.004  | 0.004  | 0.002  | 0.004  | 0.004  | 0.004  | 0.002  | 19.000    | 19.000      | 38.000        | 19.000    |
| 5      | 0   | 0    | 0      | 5    | 0   | GET     | /.env                                  | 0.004  | 0.004  | 0.004  | 0.001  | 0.004  | 0.004  | 0.004  | 0.002  | 19.000    | 19.000      | 95.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /.env.suspected                        | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /src/.env.example                      | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /phpinfo.php                           | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /local/.env.example                    | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /back-end/app/.env.example             | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.004  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /web/.env.example                      | 0.000  | 0.004  | 0.004  | 0.002  | 0.004  | 0.004  | 0.004  | 0.002  | 19.000    | 19.000      | 38.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /env.txt                               | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /.env_old                              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /vendor/.env.example                   | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 38.000        | 19.000    |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /core/.env.example                     | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 38.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /config/.env.example                   | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /app/.env.example                      | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 38.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /config/aws.yml                        | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /public/.env.example                   | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 38.000        | 19.000    |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /en/.env.example                       | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 38.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /laravel/.env.example                  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /api/.env.example                      | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /app/frontend/.env.example             | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /application/.env.example              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /awstats/.env.example                  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /info.php                              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /backend/.env.example                  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /backup/.env.example                   | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /bootstrap/.env.example                | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /build/.env.example                    | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /data/.env.example                     | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /database/.env.example                 | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /demo/.env.example                     | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /dev/.env.example                      | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /developer/.env.example                | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /django_project_path/.env.example      | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /docker/.env.example                   | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /docs/.env.example                     | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /env/.env.example                      | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /environments/.env.example             | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /example/.env.example                  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /examples/.env.example                 | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /front-app/.env.example                | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /front-empathy/.env.example            | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /front-end/.env.example                | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /front/.env.example                    | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /frontend/.env.example                 | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /js/.env.example                       | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 181.000   | 181.000     | 181.000       | 181.000   |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /aws.yml                               | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /log/.env.example                      | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /mail/.env.example                     | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /prod/.env.example                     | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /production/.env.example               | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /pub/.env.example                      | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /rest/.env.example                     | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /route/.env.example                    | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /.aws/credentials/phpinfo              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /stag/.env.example                     | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /staging/.env.example                  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /static/.env.example                   | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /stats/.env.example                    | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /storage/.env.example                  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /system/.env.example                   | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /temp/.env.example                     | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /test/.env.example                     | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /tests/.env.example                    | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /thumb/.env.example                    | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /thumbs/.env.example                   | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /tmp/.env.example                      | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /vendor/laravel/.env.example           | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /.env_bak                              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /.env.bak                              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /.env.1                                | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /.env~                                 | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /.env.example.1                        | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /.env.example.suspected                | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /core/.env                             | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 38.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /config/.env                           | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /app/.env                              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 38.000        | 19.000    |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /web/.env                              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 38.000        | 19.000    |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /public/.env                           | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 38.000        | 19.000    |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /en/.env                               | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 38.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /laravel/.env                          | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /api/.env                              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /app/frontend/.env                     | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /application/.env                      | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /awstats/.env                          | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 38.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /back-end/app/.env                     | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /.env.txt                              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /backup/.env                           | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /bootstrap/.env                        | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /build/.env                            | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /data/.env                             | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /database/.env                         | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /demo/.env                             | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /dev/.env                              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /developer/.env                        | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /django_project_path/.env              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /docker/.env                           | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /docs/.env                             | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /env/.env                              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /environments/.env                     | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /example/.env                          | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /examples/.env                         | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /front-app/.env                        | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /front-empathy/.env                    | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /front-end/.env                        | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /front/.env                            | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /frontend/.env                         | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /js/.env                               | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 181.000   | 181.000     | 181.000       | 181.000   |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /local/.env                            | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /log/.env                              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /mail/.env                             | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /prod/.env                             | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /production/.env                       | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /pub/.env                              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /rest/.env                             | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /route/.env                            | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /src/.env                              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /vendor/.env                           | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 38.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /staging/.env                          | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /staging2/.env                         | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 38.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /static/.env                           | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /stats/.env                            | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /storage/.env                          | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /system/.env                           | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /temp/.env                             | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /test/.env                             | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /tests/.env                            | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /tmp/.env                              | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /vendor/laravel/.env                   | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /v1/.env                               | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /v2/.env                               | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /v1/.env.example                       | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /v2/.env.example                       | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /staging2/.env.example                 | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /project/.env                          | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /project/.env.example                  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /sito/wp-includes/wlwmanifest.xml      | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /Sources/API/.env                      | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /cms/wp-includes/wlwmanifest.xml       | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | //.env                                 | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 10318  | 0   | 5    | 10313  | 0    | 0   | GET     | /favicon.ico                           | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000     | 43.000      | 215.000       | 0.021     |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /site/wp-includes/wlwmanifest.xml      | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /sitemap.xml                           | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 10315  | 0   | 2    | 10313  | 0    | 0   | GET     | /js/timeago.min.js                     | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000     | 1915.000    | 3830.000      | 0.371     |
| 10315  | 0   | 2    | 10313  | 0    | 0   | GET     | /js/main.js                            | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000     | 1824.000    | 3648.000      | 0.354     |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /wp2/wp-includes/wlwmanifest.xml       | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /test/wp-includes/wlwmanifest.xml      | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /geoserver/web/                        | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /wp-content/                           | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /wp1/wp-includes/wlwmanifest.xml       | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /news/wp-includes/wlwmanifest.xml      | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /wp/wp-includes/wlwmanifest.xml        | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /wordpress/wp-includes/wlwmanifest.xml | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /web/wp-includes/wlwmanifest.xml       | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /blog/wp-includes/wlwmanifest.xml      | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 153417 | 0   | 3764 | 149653 | 0    | 0   | GET     | /image/\d+                             | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000     | 1155222.000 | 635878595.000 | 4144.773  |
| 1      | 0   | 0    | 0      | 1    | 0   | GET     | /wp-includes/wlwmanifest.xml           | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 19.000    | 19.000      | 19.000        | 19.000    |
| 10314  | 0   | 1    | 10313  | 0    | 0   | GET     | /css/style.css                         | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000     | 553.000     | 553.000       | 0.054     |
| 1      | 0   | 0    | 0      | 1    | 0   | POST    | /boaform/admin/formLogin               | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000     | 0.000       | 0.000         | 0.000     |
| 2      | 0   | 0    | 0      | 2    | 0   | POST    | /GponForm/diag_Form                    | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 150.000   | 150.000     | 300.000       | 150.000   |
| 2      | 0   | 0    | 0      | 2    | 0   | GET     | /shell                                 | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000  | 0.000     | 0.000       | 0.000         | 0.000     |
+--------+-----+------+--------+------+-----+---------+----------------------------------------+--------+--------+--------+--------+--------+--------+--------+--------+-----------+-------------+---------------+-----------+

実践

ログ出力設定をオフにすれば得点が上がりそう。

393b938c6a23afae7bb58ee276eb7098e55b0c15

結果

微増

isucon@tk2-208-13709:~/private_isu/webapp$ ../benchmarker/bin/benchmarker -t "http://localhost:80" -u ../benchmarker/userdata
{"pass":true,"score":241311,"success":230494,"fail":0,"messages":[]}

ue-sho / private-isu-provisioning

ISUCON本実践 #3

推測

実践

結果

推測

実践

結果

推測

結果

推測

実践

結果

推測

実践

結果

実践

結果

推測

実践

結果

実践

結果

実践

結果

推測

実践・結果

推測

実践

結果

実践

結果

実践

結果

結果

1

2

実践

結果

CPU使用率

alp

pt-query-digest

推測

実践

結果

alp

実践

結果