search

sunakan / isunarabe13-suburi

0 stars 0 forks source link

分析-001(3068→4180) #2

Closed sunakan closed 19 hours ago

sunakan commented 20 hours ago
  1. ログをリセット
  2. ベンチマーク実行
  3. ダウンロード
  4. 分析
make clean-log
# ISUNARABEでベンチマーク実行→スコアがでる
make  download-files-for-analysis

ログをリセット

make clean-log

ベンチマーク実行

スコア：3068

image

ダウンロード

make download-files-for-analysis

分析(alp)

make alp
# or
alp json --sort sum -r -o count,method,uri,min,avg,max,sum --file tmp/analysis/latest/nginx-access.log.* -m '/api/user/\w+/statistics,/api/user/\w+/icon,/api/user/\w+/theme,/api/livestream/\d+/livecomment,/api/livestream/\d+/reaction,/api/livestream/\d+/moderate,/api/livestream/\d+/report,/api/livestream/\d+/ngwords,/api/livestream/\d+/exit,/api/livestream/\d+/enter,/api/livestream/\d+/statistics,/api/livestream/\d+'
+-------+--------+---------------------------------+--------+--------+--------+---------+
| COUNT | METHOD |               URI               |  MIN   |  AVG   |  MAX   |   SUM   |
+-------+--------+---------------------------------+--------+--------+--------+---------+
| 1612  | GET    | /api/user/\w+/icon              | 0.004  | 0.109  | 1.972  | 175.794 |
| 185   | GET    | /api/livestream/\d+/livecomment | 0.004  | 0.638  | 2.104  | 118.093 |
| 39    | GET    | /api/livestream/search          | 0.176  | 2.814  | 6.988  | 109.738 |
| 216   | GET    | /api/livestream/\d+/reaction    | 0.004  | 0.485  | 2.332  | 104.701 |
| 17    | POST   | /api/livestream/\d+/moderate    | 0.280  | 4.326  | 12.380 | 73.536  |
| 264   | POST   | /api/livestream/\d+/livecomment | 0.008  | 0.276  | 1.388  | 72.939  |
| 6     | GET    | /api/user/\w+/statistics        | 2.764  | 11.530 | 20.000 | 69.180  |
| 96    | POST   | /api/livestream/reservation     | 0.228  | 0.498  | 1.256  | 47.797  |
| 266   | POST   | /api/register                   | 0.028  | 0.168  | 0.928  | 44.762  |
| 263   | POST   | /api/icon                       | 0.008  | 0.135  | 0.884  | 35.552  |
| 178   | POST   | /api/livestream/\d+/reaction    | 0.008  | 0.185  | 1.284  | 32.964  |
| 272   | POST   | /api/login                      | 0.004  | 0.103  | 1.976  | 27.892  |
| 2     | GET    | /api/livestream/\d+/statistics  | 6.708  | 6.740  | 6.772  | 13.480  |
| 1     | POST   | /api/initialize                 | 13.252 | 13.252 | 13.252 | 13.252  |
| 79    | GET    | /api/livestream                 | 0.012  | 0.155  | 0.580  | 12.264  |
| 74    | GET    | /api/tag                        | 0.004  | 0.140  | 1.284  | 10.332  |
| 25    | GET    | /api/livestream/\d+/report      | 0.008  | 0.210  | 0.768  | 5.260   |
| 19    | GET    | /api/livestream/\d+/ngwords     | 0.004  | 0.170  | 0.608  | 3.232   |
| 21    | POST   | /api/livestream/\d+/enter       | 0.008  | 0.104  | 0.360  | 2.192   |
| 11    | DELETE | /api/livestream/\d+/exit        | 0.008  | 0.093  | 0.256  | 1.020   |
| 5     | GET    | /api/user/\w+/theme             | 0.016  | 0.060  | 0.136  | 0.300   |
| 3     | GET    | /api/user/me                    | 0.004  | 0.003  | 0.004  | 0.008   |
| 1     | GET    | /api/livestream/\d+             | 0.008  | 0.008  | 0.008  | 0.008   |
| 1     | GET    | /api/user/test                  | 0.000  | 0.000  | 0.000  | 0.000   |
| 1     | GET    | /api/payment                    | 0.000  | 0.000  | 0.000  | 0.000   |
+-------+--------+---------------------------------+--------+--------+--------+---------+

分析(pt-query-digest)

make pt-query-digest
# or
pt-query-digest tmp/analysis/latest/mysql-slow.log.*
# 21.4s user time, 130ms system time, 56.45M rss, 392.66G vsz
# Current date: Sun Sep 22 18:00:09 2024
# Hostname: CWPC20001.local
# Files: tmp/analysis/latest/mysql-slow.log.isu-1, tmp/analysis/latest/mysql-slow.log.isu-2, tmp/analysis/latest/mysql-slow.log.isu-3
# Overall: 547.54k total, 94 unique, 4.35k QPS, 3.63x concurrency ________
# Time range: 2024-09-22T08:52:57 to 2024-09-22T08:55:03
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           457s     1us   426ms   834us     2ms     4ms    80us
# Lock time          884ms       0    51ms     1us     1us   145us       0
# Rows sent        172.20k       0   7.32k    0.32    0.99   16.51       0
# Rows examine     119.16M       0  14.02k  228.19  964.41   1.16k       0
# Query size        60.44M       5   1.94M  115.75  284.79   3.32k   31.70

# Profile
# Rank Query ID                            Response time  Calls  R/Call V/
# ==== =================================== ============== ====== ====== ==
#    1 0xF7144185D9A142A426A36DC55C1D2623  122.2218 26.8%   5149 0.0237  0.01 SELECT livestream_tags
#    2 0x84B457C910C4A79FC9EBECB8B1065C66   64.2061 14.1%   9459 0.0068  0.01 SELECT icons
#    3 0xDA556F9115773A1A99AA0165670CE848   44.7475  9.8% 123547 0.0004  0.01 ADMIN PREPARE
#    4 0xF1B8EF06D6CA63B24BFF433E06CCEB22   24.8715  5.4%   3382 0.0074  0.01 SELECT users livestreams livecomments
#    5 0xDB74D52D39A7090F224C4DEEAF3028C9   23.0659  5.0%   3384 0.0068  0.01 SELECT users livestreams reactions
#    6 0x59F1B6DD8D9FEC059E55B3BFD624E8C3   17.6298  3.9%    769 0.0229  0.01 SELECT reservation_slots
#    7 0x38BC86A45F31C6B1EE324671506C898A   16.0163  3.5%   7856 0.0020  0.00 SELECT themes
#    8 0x22279D81D51006139E0C76405B54C261   15.6417  3.4%  36062 0.0004  0.00 SELECT domains domainmetadata
#    9 0x4ADE2DC90689F1C4891749AF54FB8D14   14.8504  3.3%  26946 0.0006  0.00 DELETE SELECT livecomments
#   10 0x42EF7D7D98FBCC9723BF896EBFC51D24   13.1236  2.9%  28504 0.0005  0.00 SELECT records
#   11 0xFFFCA4D67EA0A788813031B8BBC3B329   11.0567  2.4%   2282 0.0048  0.00 COMMIT
#   12 0x64CC8A4E8E4B390203375597CE4D611F   10.3845  2.3%    217 0.0479  0.01 SELECT ng_words
#   13 0x8F7679D452333ED3C7D60D22131CEFD4    9.9414  2.2%  80888 0.0001  0.00 ADMIN RESET STMT
#   14 0x3D83BC87F3B3A00D571FFC8104A6E50C    9.6846  2.1%  19418 0.0005  0.00 SELECT records
#   15 0xFBC5564AE716EAE82F20BFB45F6C37E7    7.2004  1.6%  18299 0.0004  0.00 SELECT tags
#   16 0xD2A0864774622BA36F6557496405CF75    5.5304  1.2%   1264 0.0044  0.00 INSERT themes
#   17 0xDFFCC1D78939C4D781C7C58349101F50    5.4131  1.2%   1000 0.0054  0.00 INSERT users
#   18 0xA3401CA3ABCC04C3AB221DB8AD5CBF26    4.5261  1.0%     93 0.0487  0.02 UPDATE reservation_slots
#   19 0x7F9C0C0BA9473953B723EE16C08655F1    4.5146  1.0%     94 0.0480  0.02 SELECT reservation_slots
#   20 0xFD38427AE3D09E3883A680F7BAF95D3A    4.2311  0.9%  14992 0.0003  0.00 SELECT livestreams livecomments
# MISC 0xMISC                               27.9032  6.1% 163939 0.0002   0.0 <74 ITEMS>

# Query 1: 57.21 QPS, 1.36x concurrency, ID 0xF7144185D9A142A426A36DC55C1D2623 at byte 44788075
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T08:53:33 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    5149
# Exec time     26    122s     2ms    84ms    24ms    48ms    16ms    24ms
# Lock time      1    11ms       0     4ms     2us     1us    49us     1us
# Rows sent     10  17.88k       0      11    3.56    4.96    1.85    4.96
# Rows examine  45  54.67M  10.71k  11.08k  10.87k  10.80k     191  10.80k
# Query size     0 281.37k      54      56   55.96   54.21    0.16   54.21
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  #########################
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestream_tags'\G
#    SHOW CREATE TABLE `isupipe`.`livestream_tags`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM livestream_tags WHERE livestream_id = 7492\G

# Query 2: 105.10 QPS, 0.71x concurrency, ID 0x84B457C910C4A79FC9EBECB8B1065C66 at byte 85717604
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T08:53:33 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1    9459
# Exec time     14     64s    48us    57ms     7ms    21ms     7ms     4ms
# Lock time      1    17ms       0     2ms     1us     1us    27us     1us
# Rows sent      3   6.63k       0       1    0.72    0.99    0.45    0.99
# Rows examine   0   1.09M       0     255  121.31  234.30   78.47  112.70
# Query size     0 404.11k      41      44   43.75   42.48    0.32   42.48
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us  #################
# 100us  #############
#   1ms  ################################################################
#  10ms  #####################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'icons'\G
#    SHOW CREATE TABLE `isupipe`.`icons`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT image FROM icons WHERE user_id = 1030\G

# Query 3: 1.20k QPS, 0.43x concurrency, ID 0xDA556F9115773A1A99AA0165670CE848 at byte 43942184
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T08:53:20 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         22  123547
# Exec time      9     45s    24us   426ms   362us     1ms     2ms    73us
# Lock time      0    14us       0    10us       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     5   3.53M      30      30      30      30       0      30
# String:
# Databases    isupipe (121950/98%), isudns (1597/1%)
# Hosts        localhost
# Users        isucon (121950/98%), isudns (1597/1%)
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  ######################################
#   1ms  #########
#  10ms  #
# 100ms  #
#    1s
#  10s+
administrator command: Prepare\G

# Query 4: 37.58 QPS, 0.28x concurrency, ID 0xF1B8EF06D6CA63B24BFF433E06CCEB22 at byte 140347434
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T08:53:33 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    3382
# Exec time      5     25s     1ms    62ms     7ms    26ms     9ms     1ms
# Lock time      0     6ms       0   443us     1us     1us    10us     1us
# Rows sent      1   3.30k       1       1       1       1       0       1
# Rows examine   5   6.77M   1.96k   2.34k   2.05k   2.16k  132.94   1.96k
# Query size     0 544.36k     163     166  164.82  158.58       0  158.58
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  ############################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'users'\G
#    SHOW CREATE TABLE `isupipe`.`users`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestreams'\G
#    SHOW CREATE TABLE `isupipe`.`livestreams`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livecomments'\G
#    SHOW CREATE TABLE `isupipe`.`livecomments`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT IFNULL(SUM(l2.tip), 0) FROM users u
        INNER JOIN livestreams l ON l.user_id = u.id    
        INNER JOIN livecomments l2 ON l2.livestream_id = l.id
        WHERE u.id = 396\G

# Query 5: 37.60 QPS, 0.26x concurrency, ID 0xDB74D52D39A7090F224C4DEEAF3028C9 at byte 129391975
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T08:53:33 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    3384
# Exec time      5     23s     1ms    70ms     7ms    24ms     9ms     1ms
# Lock time      0     6ms       0   446us     1us     1us    12us     1us
# Rows sent      1   3.30k       1       1       1       1       0       1
# Rows examine   5   6.74M   1.96k   2.29k   2.04k   2.06k   90.52   1.96k
# Query size     0 478.58k     143     146  144.82  143.84    0.86  143.84
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  #########################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'users'\G
#    SHOW CREATE TABLE `isupipe`.`users`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestreams'\G
#    SHOW CREATE TABLE `isupipe`.`livestreams`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'reactions'\G
#    SHOW CREATE TABLE `isupipe`.`reactions`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) FROM users u
        INNER JOIN livestreams l ON l.user_id = u.id
        INNER JOIN reactions r ON r.livestream_id = l.id
        WHERE u.id = 378\G

# Query 6: 8.54 QPS, 0.20x concurrency, ID 0x59F1B6DD8D9FEC059E55B3BFD624E8C3 at byte 146631383
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T08:53:33 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     769
# Exec time      3     18s     2ms    78ms    23ms    42ms    13ms    22ms
# Lock time      0     1ms       0   215us     1us     1us    10us     1us
# Rows sent      0     769       1       1       1       1       0       1
# Rows examine   5   6.42M   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0  64.58k      86      86      86      86       0      86
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ###############
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'reservation_slots'\G
#    SHOW CREATE TABLE `isupipe`.`reservation_slots`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT slot FROM reservation_slots WHERE start_at = 1701302400 AND end_at = 1701306000\G

# Query 7: 87.29 QPS, 0.18x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 159711286
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T08:53:33 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1    7856
# Exec time      3     16s   244us    29ms     2ms     7ms     2ms   925us
# Lock time      1    17ms       0     1ms     2us     1us    29us     1us
# Rows sent      4   7.67k       1       1       1       1       0       1
# Rows examine   7   8.43M    1000   1.23k   1.10k   1.20k   87.86   1.09k
# Query size     0 312.76k      38      41   40.77   40.45    0.79   40.45
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ###########################################################
#  10ms  ##
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'themes'\G
#    SHOW CREATE TABLE `isupipe`.`themes`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM themes WHERE user_id = 1059\G

# Query 8: 350.12 QPS, 0.15x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 45074280
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T08:53:20 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   36062
# Exec time      3     16s    84us    30ms   433us     2ms   796us   167us
# Lock time     13   119ms       0     6ms     3us     1us    50us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     6   3.89M     113     113     113     113       0     113
# String:
# Databases    isudns
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  ######
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isudns` LIKE 'domains'\G
#    SHOW CREATE TABLE `isudns`.`domains`\G
#    SHOW TABLE STATUS FROM `isudns` LIKE 'domainmetadata'\G
#    SHOW CREATE TABLE `isudns`.`domainmetadata`\G
# EXPLAIN /*!50100 PARTITIONS*/
select kind,content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='t.isucon.pw'\G

# Query 9: 456.71 QPS, 0.25x concurrency, ID 0x4ADE2DC90689F1C4891749AF54FB8D14 at byte 109281772
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T08:54:03 to 2024-09-22T08:55:02
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   26946
# Exec time      3     15s    50us    28ms   551us     2ms   921us   236us
# Lock time      8    76ms       0     6ms     2us     1us    53us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0  52.64k       2       4    2.00    1.96    0.03    1.96
# Query size    14   8.47M     263     463  329.44  363.48   25.96  313.99
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us  ######################
# 100us  ################################################################
#   1ms  ##############
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livecomments'\G
#    SHOW CREATE TABLE `isupipe`.`livecomments`\G
DELETE FROM livecomments
            WHERE
            id = 3 AND
            livestream_id = 7529 AND
            (SELECT COUNT(*)
            FROM
            (SELECT 'こんな素晴らしい情報をシェアしてくれてありがとう！' AS text) AS texts
            INNER JOIN
            (SELECT CONCAT('%', '念動器', '%') AS pattern) AS patterns
            ON texts.text LIKE patterns.pattern) >= 1\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select * from  livecomments
            WHERE
            id = 3 AND
            livestream_id = 7529 AND
            (SELECT COUNT(*)
            FROM
            (SELECT 'こんな素晴らしい情報をシェアしてくれてありがとう！' AS text) AS texts
            INNER JOIN
            (SELECT CONCAT('%', '念動器', '%') AS pattern) AS patterns
            ON texts.text LIKE patterns.pattern) >= 1\G

# Query 10: 276.74 QPS, 0.13x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 87639806
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T08:53:20 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          5   28504
# Exec time      2     13s    89us    22ms   460us     2ms   811us   185us
# Lock time      7    63ms       0     5ms     2us     1us    38us     1us
# Rows sent      0   1.70k       0       1    0.06    0.99    0.24       0
# Rows examine   0   1.70k       0       1    0.06    0.99    0.24       0
# Query size     6   3.86M     128     210  141.87  151.03   11.95  136.99
# String:
# Databases    isudns
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  #######
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isudns` LIKE 'records'\G
#    SHOW CREATE TABLE `isudns`.`records`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and name='*.t.isucon.pw' and domain_id=1\G

# Query 11: 25.36 QPS, 0.12x concurrency, ID 0xFFFCA4D67EA0A788813031B8BBC3B329 at byte 105755614
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T08:53:33 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    2282
# Exec time      2     11s    22us    41ms     5ms    14ms     5ms     5ms
# 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     0  13.37k       6       6       6       6       0       6
# String:
# Databases    isupipe (2017/88%), isudns (265/11%)
# Hosts        localhost
# Users        isucon (2017/88%), isudns (265/11%)
# Query_time distribution
#   1us
#  10us  ######################
# 100us  ###########
#   1ms  ################################################################
#  10ms  ############
# 100ms
#    1s
#  10s+
COMMIT\G

# Query 12: 2.71 QPS, 0.13x concurrency, ID 0x64CC8A4E8E4B390203375597CE4D611F at byte 136514299
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T08:53:43 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     217
# Exec time      2     10s     4ms   102ms    48ms    78ms    21ms    48ms
# Lock time      0   497us       0   249us     2us     1us    16us     1us
# Rows sent      0      57       0       4    0.26    0.99    0.66       0
# Rows examine   2   2.97M  14.00k  14.02k  14.01k  13.78k       0  13.78k
# Query size     0  20.98k      97      99   98.98   97.36    0.44   97.36
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ###
#  10ms  ################################################################
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'ng_words'\G
#    SHOW CREATE TABLE `isupipe`.`ng_words`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = 1017 AND livestream_id = 7532\G

# Query 13: 785.32 QPS, 0.10x concurrency, ID 0x8F7679D452333ED3C7D60D22131CEFD4 at byte 47361479
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T08:53:20 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         14   80888
# Exec time      2     10s     9us    26ms   122us   403us   412us    40us
# 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     4   2.55M      33      33      33      33       0      33
# String:
# Databases    isudns
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us  #
#  10us  ################################################################
# 100us  ############
#   1ms  #
#  10ms  #
# 100ms
#    1s
#  10s+
administrator command: Reset stmt\G

# Query 14: 188.52 QPS, 0.09x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 168772754
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T08:53:20 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3   19418
# Exec time      2     10s    89us    30ms   498us     2ms   928us   194us
# Lock time      5    53ms       0     4ms     2us     1us    40us     1us
# Rows sent      5   9.49k       0       1    0.50    0.99    0.50    0.99
# Rows examine   0   9.49k       0       1    0.50    0.99    0.50    0.99
# Query size     4   2.52M     125     209  135.92  151.03   11.45  124.25
# String:
# Databases    isudns
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  ########
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isudns` LIKE 'records'\G
#    SHOW CREATE TABLE `isudns`.`records`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='t.isucon.pw'\G

# Query 15: 261.41 QPS, 0.10x concurrency, ID 0xFBC5564AE716EAE82F20BFB45F6C37E7 at byte 83074233
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T08:53:53 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3   18299
# Exec time      1      7s    37us    24ms   393us     2ms   933us   113us
# Lock time      5    52ms       0     4ms     2us     1us    51us     1us
# Rows sent     10  17.87k       1       1       1       1       0       1
# Rows examine   0  17.87k       1       1       1       1       0       1
# Query size     0 570.32k      31      33   31.91   31.70    0.51   31.70
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  ##############################################################
#   1ms  ###########
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'tags'\G
#    SHOW CREATE TABLE `isupipe`.`tags`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM tags WHERE id = 93\G

# Query 16: 12.39 QPS, 0.05x concurrency, ID 0xD2A0864774622BA36F6557496405CF75 at byte 428693
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T08:53:20 to 2024-09-22T08:55:02
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    1264
# Exec time      1      6s    58us    20ms     4ms     8ms     2ms     5ms
# Lock time      0     1ms       0    18us     1us     1us       0     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     0  71.19k      55      60   57.68   56.92    1.11   56.92
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us  ######
# 100us  ######
#   1ms  ################################################################
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'themes'\G
#    SHOW CREATE TABLE `isupipe`.`themes`\G
INSERT INTO themes (user_id, dark_mode) VALUES (482, true)\G

# Query 17: 90.91 QPS, 0.49x concurrency, ID 0xDFFCC1D78939C4D781C7C58349101F50 at byte 262106
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T08:53:20 to 2024-09-22T08:53:31
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    1000
# Exec time      1      5s     1ms    20ms     5ms     8ms     2ms     5ms
# Lock time      0     1ms       0     2us     1us     1us       0     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     0 401.18k     192     463  410.81  420.77   16.87  400.73
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'users'\G
#    SHOW CREATE TABLE `isupipe`.`users`\G
INSERT INTO users (id, name, display_name, description, password) VALUES (292, 'kumiko980', '月影だんご', '普段配管工をしています。\nよろしくおねがいします！\n\n連絡は以下からお願いします。\n\nウェブサイト: http://kumiko98.example.com/\nメールアドレス: kumiko98@example.com\n', '$2a$04$w5.MtTa6RDKEbD9j7XnUnOAYBcpug2XAVrjLgFg3wrlRHppbO52Gy')\G

# Query 18: 1.03 QPS, 0.05x concurrency, ID 0xA3401CA3ABCC04C3AB221DB8AD5CBF26 at byte 164008414
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2024-09-22T08:53:33 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      93
# Exec time      0      5s     5ms   126ms    49ms    91ms    35ms    51ms
# Lock time      0   135us       0    43us     1us     1us     4us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0 795.50k   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0   8.90k      98      98      98      98       0      98
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ############################
#  10ms  ################################################################
# 100ms  #####
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'reservation_slots'\G
#    SHOW CREATE TABLE `isupipe`.`reservation_slots`\G
UPDATE reservation_slots SET slot = slot - 1 WHERE start_at >= 1701378000 AND end_at <= 1701385200\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select  slot = slot - 1 from reservation_slots where  start_at >= 1701378000 AND end_at <= 1701385200\G

# Query 19: 1.04 QPS, 0.05x concurrency, ID 0x7F9C0C0BA9473953B723EE16C08655F1 at byte 86462796
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2024-09-22T08:53:33 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      94
# Exec time      0      5s     5ms   122ms    48ms    91ms    33ms    51ms
# Lock time      0   344us       0   243us     3us     1us    24us     1us
# Rows sent      0     769       1      20    8.18   17.65    5.97    8.91
# Rows examine   0 804.05k   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0   8.81k      96      96      96      96       0      96
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ###########################
#  10ms  ################################################################
# 100ms  #####
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'reservation_slots'\G
#    SHOW CREATE TABLE `isupipe`.`reservation_slots`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM reservation_slots WHERE start_at >= 1701108000 AND end_at <= 1701140400 FOR UPDATE\G

# Query 20: 881.88 QPS, 0.25x concurrency, ID 0xFD38427AE3D09E3883A680F7BAF95D3A at byte 10792084
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T08:53:36 to 2024-09-22T08:53:53
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2   14992
# Exec time      0      4s   264us     4ms   282us   287us    33us   273us
# Lock time      1    13ms       0    24us       0     1us       0     1us
# Rows sent      8  14.64k       1       1       1       1       0       1
# Rows examine  12  14.33M    1001    1003    1002  964.41       0  964.41
# Query size     2   1.71M     117     120  119.85  118.34    0.64  118.34
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  #
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestreams'\G
#    SHOW CREATE TABLE `isupipe`.`livestreams`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livecomments'\G
#    SHOW CREATE TABLE `isupipe`.`livecomments`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT IFNULL(SUM(l2.tip), 0) FROM livestreams l INNER JOIN livecomments l2 ON l.id = l2.livestream_id WHERE l.id = 2538\G
sunakan commented 19 hours ago 
# Query 1: 57.21 QPS, 1.36x concurrency, ID 0xF7144185D9A142A426A36DC55C1D2623 at byte 44788075
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T08:53:33 to 2024-09-22T08:55:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    5149
# Exec time     26    122s     2ms    84ms    24ms    48ms    16ms    24ms
# Lock time      1    11ms       0     4ms     2us     1us    49us     1us
# Rows sent     10  17.88k       0      11    3.56    4.96    1.85    4.96
# Rows examine  45  54.67M  10.71k  11.08k  10.87k  10.80k     191  10.80k
# Query size     0 281.37k      54      56   55.96   54.21    0.16   54.21
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  #########################
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestream_tags'\G
#    SHOW CREATE TABLE `isupipe`.`livestream_tags`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM livestream_tags WHERE livestream_id = 7492\G
image

livestream_tags#livestream_idにインデックスを貼れば良さそう

alter table livestream_tags add index livestream_id_idx (livestream_id);
cat tmp/db-servers | xargs -I{} ssh {} "sudo mysql isupipe -e 'alter table livestream_tags add index livestream_id_idx (livestream_id);'"
sunakan commented 19 hours ago
image

indexが貼れたことを確認

make clean-log

して計測

sunakan commented 19 hours ago

約3400→約4180

image