2023/11/25(Sat)17:05:04

[takeokunn]

alp:

+-------+--------+---------------------------------+-----+-------+-----+-----+-----+---------+-------+-------+--------+
| COUNT | METHOD |               URI               | 1XX |  2XX  | 3XX | 4XX | 5XX |   SUM   |  AVG  |  MIN  |  MAX   |
+-------+--------+---------------------------------+-----+-------+-----+-----+-----+---------+-------+-------+--------+
| 585   | GET    | /api/livestream/\w+             | 0   | 581   | 0   | 4   | 0   | 126.892 | 0.217 | 0.020 | 20.000 |
| 992   | GET    | /api/livestream/\w+/livecomment | 0   | 990   | 0   | 2   | 0   | 116.580 | 0.118 | 0.004 | 0.388  |
| 1024  | GET    | /api/livestream/\w+/reaction    | 0   | 1023  | 0   | 1   | 0   | 106.392 | 0.104 | 0.004 | 0.348  |
| 1251  | POST   | /api/livestream/\w+/livecomment | 0   | 1245  | 0   | 6   | 0   | 105.904 | 0.085 | 0.004 | 0.276  |
| 10883 | GET    | /api/user/\w+/icon              | 0   | 10883 | 0   | 0   | 0   | 95.720  | 0.009 | 0.028 | 0.136  |
| 908   | POST   | /api/livestream/\w+/reaction    | 0   | 907   | 0   | 1   | 0   | 70.208  | 0.077 | 0.004 | 0.292  |
| 283   | POST   | /api/register                   | 0   | 281   | 0   | 1   | 1   | 66.824  | 0.236 | 0.004 | 0.684  |
| 514   | GET    | /api/livestream                 | 0   | 514   | 0   | 0   | 0   | 50.204  | 0.098 | 0.004 | 0.216  |
| 116   | POST   | /api/livestream/\w+             | 0   | 112   | 0   | 4   | 0   | 49.064  | 0.423 | 0.088 | 0.980  |
| 362   | GET    | /api/livestream/\w+/report      | 0   | 362   | 0   | 0   | 0   | 23.520  | 0.065 | 0.004 | 0.212  |
| 176   | POST   | /api/livestream/\w+/moderate    | 0   | 176   | 0   | 0   | 0   | 9.280   | 0.053 | 0.004 | 0.168  |
| 289   | POST   | /api/login                      | 0   | 287   | 0   | 2   | 0   | 8.692   | 0.030 | 0.004 | 0.096  |
| 282   | POST   | /api/icon                       | 0   | 282   | 0   | 0   | 0   | 6.684   | 0.024 | 0.004 | 0.104  |
| 104   | GET    | /api/tag                        | 0   | 104   | 0   | 0   | 0   | 3.500   | 0.034 | 0.004 | 0.136  |
| 99    | POST   | /api/livestream/\w+/enter       | 0   | 99    | 0   | 0   | 0   | 3.448   | 0.035 | 0.016 | 0.116  |
| 16    | GET    | /api/user/\w+/statistics        | 0   | 16    | 0   | 0   | 0   | 3.252   | 0.203 | 0.012 | 0.356  |
| 1     | POST   | /api/initialize                 | 0   | 1     | 0   | 0   | 0   | 2.464   | 2.464 | 2.464 | 2.464  |
| 89    | DELETE | /api/livestream/\w+/exit        | 0   | 89    | 0   | 0   | 0   | 2.360   | 0.027 | 0.004 | 0.064  |
| 18    | GET    | /api/user/\w+                   | 0   | 18    | 0   | 0   | 0   | 1.440   | 0.080 | 0.032 | 0.156  |
| 15    | GET    | /api/user/\w+/theme             | 0   | 15    | 0   | 0   | 0   | 0.504   | 0.034 | 0.004 | 0.084  |
| 1     | GET    | /api/payment                    | 0   | 1     | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000  |
+-------+--------+---------------------------------+-----+-------+-----+-----+-----+---------+-------+-------+--------+

[takeokunn]

pt-query-digest:

# 11.4s user time, 40ms system time, 36.34M rss, 42.64M vsz
# Current date: Sat Nov 25 08:05:18 2023
# Hostname: ip-192-168-0-12
# Files: /var/log/mysql/mysql-slow.log
# Overall: 169.37k total, 116 unique, 1.66k QPS, 2.05x concurrency _______
# Time range: 2023-11-25T08:01:40 to 2023-11-25T08:03:22
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           209s     1us   458ms     1ms     6ms     4ms   125us
# Lock time          324ms       0    15ms     1us     1us    77us     1us
# Rows sent        291.15k       0   7.42k    1.76    4.96   45.62    0.99
# Rows examine      51.26M       0   8.55k  317.36   1.39k  962.74       0
# Query size        16.69M       5   1.94M  103.34  151.03   5.02k   97.36

# Profile
# Rank Query ID                      Response time Calls R/Call V/M   Item
# ==== ============================= ============= ===== ====== ===== ====
#    1 0x42EF7D7D98FBCC9723BF896E... 58.9054 28.1% 10171 0.0058  0.01 SELECT records
#    2 0x3D83BC87F3B3A00D571FFC81... 42.5273 20.3%  7904 0.0054  0.01 SELECT records
#    3 0x9EAD6C0CE525E3693EE27FFC... 18.3418  8.8%   544 0.0337  0.01 SELECT livestreams
#    4 0x59F1B6DD8D9FEC059E55B3BF... 15.2524  7.3%  1013 0.0151  0.01 SELECT reservation_slots
#    5 0x38BC86A45F31C6B1EE324671... 10.5420  5.0%  7663 0.0014  0.00 SELECT themes
#    6 0x8F7679D452333ED3C7D60D22...  8.1414  3.9% 31767 0.0003  0.01 ADMIN RESET STMT
#    7 0x22279D81D51006139E0C7640...  7.5703  3.6% 11844 0.0006  0.01 SELECT domains domainmetadata
#    8 0xFFFCA4D67EA0A788813031B8...  5.1485  2.5%  7091 0.0007  0.00 COMMIT
#    9 0xA3401CA3ABCC04C3AB221DB8...  4.3836  2.1%   112 0.0391  0.02 UPDATE reservation_slots
#   10 0xFD38427AE3D09E3883A680F7...  4.2890  2.0% 20001 0.0002  0.00 SELECT livestreams livecomments
#   11 0xC499D81D570D361DB61FC43A...  3.8566  1.8% 20003 0.0002  0.00 SELECT livestreams reactions
#   12 0x859BBB7E9D760686137A9444...  3.5452  1.7%   281 0.0126  0.01 DELETE records
#   13 0x7F9C0C0BA9473953B723EE16...  3.5343  1.7%   114 0.0310  0.01 SELECT reservation_slots
#   14 0x5A8A79FB9E5D1635CA10FEA9...  3.2492  1.6%  2265 0.0014  0.00 SELECT themes
#   15 0xBB9B3D66E02B513839482132...  2.4724  1.2%  4646 0.0005  0.00 SELECT tags livestream_tags
#   16 0x9AC623FA477E73A44D191D29...  2.4550  1.2%   562 0.0044  0.01 SELECT records
#   17 0xEA1E6309EEEFF9A6831AD2FB...  1.8739  0.9%  7366 0.0003  0.00 SELECT users
#   18 0xD6032FE08E1FE706A928B8B7...  1.7247  0.8%  7038 0.0002  0.00 SELECT livestreams
#   19 0x2C5B3DD85325F07F33914C2E...  1.0116  0.5%   308 0.0033  0.00 SELECT tags livestream_tags
#   20 0xDA556F9115773A1A99AA0165...  0.9419  0.5%  1703 0.0006  0.22 ADMIN PREPARE
# MISC 0xMISC                         9.5420  4.6% 26974 0.0004   0.0 <96 ITEMS>

# Query 1: 139.33 QPS, 0.81x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 42774451
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T08:02:09 to 2023-11-25T08:03:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   10171
# Exec time     28     59s   711us    88ms     6ms    17ms     6ms     4ms
# Lock time     10    35ms       0     8ms     3us     1us    92us     1us
# Rows sent      1   3.20k       0       1    0.32    0.99    0.46       0
# Rows examine  26  13.83M   1.25k   1.52k   1.39k   1.46k   75.85   1.39k
# Query size     8   1.37M     130     208  141.69  158.58   11.60  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='o4w57gtu9p8570.u.isucon.dev' and domain_id=13\G

# Query 2: 86.86 QPS, 0.47x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 42522637
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T08:01:51 to 2023-11-25T08:03:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4    7904
# Exec time     20     43s   672us    78ms     5ms    16ms     6ms     3ms
# Lock time      5    19ms       0     7ms     2us     1us    77us     1us
# Rows sent      1   3.97k       0       1    0.51    0.99    0.50    0.99
# Rows examine  21  10.77M   1.25k   1.52k   1.40k   1.46k   76.67   1.39k
# Query size     6   1.01M     126     206  134.19  151.03   10.39  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='u.isucon.dev'\G

# Query 3: 7.66 QPS, 0.26x concurrency, ID 0x9EAD6C0CE525E3693EE27FFC9AAF05A3 at byte 41875601
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T08:02:11 to 2023-11-25T08:03:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     544
# Exec time      8     18s     4ms   105ms    34ms    61ms    17ms    30ms
# Lock time      0   624us       0    16us     1us     1us       0     1us
# Rows sent      0     441       0      21    0.81    1.96    1.82       0
# Rows examine   7   3.92M   7.32k   7.43k   7.39k   7.31k       0   7.31k
# Query size     0  24.43k      43      46   45.98   44.60    0.18   44.60
# 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
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM livestreams WHERE user_id = 1032\G

# Query 4: 14.27 QPS, 0.21x concurrency, ID 0x59F1B6DD8D9FEC059E55B3BFD624E8C3 at byte 46897120
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T08:02:11 to 2023-11-25T08:03:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    1013
# Exec time      7     15s     2ms    65ms    15ms    38ms    12ms    11ms
# Lock time      1     3ms       0     2ms     3us     1us    70us     1us
# Rows sent      0    1013       1       1       1       1       0       1
# Rows examine  16   8.46M   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0  85.08k      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 = 1701522000 AND end_at = 1701525600\G

# Query 5: 107.93 QPS, 0.15x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 20512675
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T08:02:11 to 2023-11-25T08:03:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4    7663
# Exec time      5     11s   265us    32ms     1ms     5ms     2ms   568us
# Lock time      3    13ms       0     2ms     1us     1us    25us     1us
# Rows sent      2   7.48k       1       1       1       1       0       1
# Rows examine  16   8.40M    1000   1.25k   1.12k   1.20k   81.14   1.09k
# Query size     1 306.77k      38      41   40.99   40.45    0.12   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 = 1038\G

# Query 6: 349.09 QPS, 0.09x concurrency, ID 0x8F7679D452333ED3C7D60D22131CEFD4 at byte 42761428
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T08:01:51 to 2023-11-25T08:03:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         18   31767
# Exec time      3      8s     9us    69ms   256us     1ms     1ms    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     5 1023.74k      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 7: 162.25 QPS, 0.10x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 48140553
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T08:02:09 to 2023-11-25T08:03:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   11844
# Exec time      3      8s    82us    57ms   639us     2ms     2ms   159us
# Lock time     13    45ms       0     6ms     3us     1us    83us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     7   1.29M     114     114     114     114       0     114
# 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='u.isucon.dev'\G

# Query 8: 77.92 QPS, 0.06x concurrency, ID 0xFFFCA4D67EA0A788813031B8BBC3B329 at byte 29822631
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T08:01:51 to 2023-11-25T08:03:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4    7091
# Exec time      2      5s    18us    25ms   726us     4ms     2ms    54us
# 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  41.55k       6       6       6       6       0       6
# String:
# Databases    isupipe (6808/96%), isudns (283/3%)
# Hosts        localhost
# Users        isucon (6808/96%), isudns (283/3%)
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  ########################
#   1ms  ##################
#  10ms  #
# 100ms
#    1s
#  10s+
COMMIT\G

# Query 9: 1.58 QPS, 0.06x concurrency, ID 0xA3401CA3ABCC04C3AB221DB8AD5CBF26 at byte 39969383
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2023-11-25T08:02:11 to 2023-11-25T08:03:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     112
# Exec time      2      4s     5ms   119ms    39ms    91ms    27ms    34ms
# Lock time      0   113us       0     2us     1us     1us       0     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   1 958.02k   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0  10.72k      98      98      98      98       0      98
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us