2023/11/25(Sat)16:15:34

[takeokunn]

16,431

[takeokunn]

alp:

+-------+--------+---------------------------------+-----+-------+-----+-----+-----+---------+-------+-------+--------+
| COUNT | METHOD |               URI               | 1XX |  2XX  | 3XX | 4XX | 5XX |   SUM   |  AVG  |  MIN  |  MAX   |
+-------+--------+---------------------------------+-----+-------+-----+-----+-----+---------+-------+-------+--------+
| 18    | POST   | /api/livestream/\w+/moderate    | 0   | 15    | 0   | 3   | 0   | 137.960 | 7.664 | 7.044 | 17.412 |
| 966   | POST   | /api/livestream/\w+/livecomment | 0   | 961   | 0   | 5   | 0   | 125.888 | 0.130 | 0.004 | 0.344  |
| 965   | GET    | /api/livestream/\w+/livecomment | 0   | 965   | 0   | 0   | 0   | 102.044 | 0.106 | 0.004 | 0.340  |
| 1001  | GET    | /api/livestream/\w+/reaction    | 0   | 996   | 0   | 5   | 0   | 100.528 | 0.100 | 0.004 | 0.308  |
| 125   | GET    | /api/livestream/\w+             | 0   | 121   | 0   | 4   | 0   | 91.040  | 0.728 | 0.008 | 20.000 |
| 267   | POST   | /api/register                   | 0   | 264   | 0   | 2   | 1   | 74.224  | 0.278 | 0.036 | 0.744  |
| 890   | POST   | /api/livestream/\w+/reaction    | 0   | 888   | 0   | 2   | 0   | 61.936  | 0.070 | 0.004 | 0.220  |
| 10426 | GET    | /api/user/\w+/icon              | 0   | 10426 | 0   | 0   | 0   | 61.424  | 0.006 | 0.004 | 0.148  |
| 101   | POST   | /api/livestream/\w+             | 0   | 97    | 0   | 4   | 0   | 43.584  | 0.432 | 0.140 | 0.976  |
| 124   | GET    | /api/livestream                 | 0   | 124   | 0   | 0   | 0   | 11.336  | 0.091 | 0.004 | 0.224  |
| 272   | POST   | /api/login                      | 0   | 270   | 0   | 2   | 0   | 6.740   | 0.025 | 0.004 | 0.128  |
| 264   | POST   | /api/icon                       | 0   | 264   | 0   | 0   | 0   | 5.240   | 0.020 | 0.004 | 0.128  |
| 67    | GET    | /api/livestream/\w+/report      | 0   | 67    | 0   | 0   | 0   | 4.016   | 0.060 | 0.004 | 0.176  |
| 89    | GET    | /api/tag                        | 0   | 89    | 0   | 0   | 0   | 3.352   | 0.038 | 0.008 | 0.436  |
| 16    | GET    | /api/user/\w+/statistics        | 0   | 16    | 0   | 0   | 0   | 3.332   | 0.208 | 0.012 | 0.312  |
| 91    | POST   | /api/livestream/\w+/enter       | 0   | 91    | 0   | 0   | 0   | 3.140   | 0.035 | 0.008 | 0.172  |
| 1     | POST   | /api/initialize                 | 0   | 1     | 0   | 0   | 0   | 2.412   | 2.412 | 2.412 | 2.412  |
| 80    | DELETE | /api/livestream/\w+/exit        | 0   | 80    | 0   | 0   | 0   | 2.068   | 0.026 | 0.036 | 0.128  |
| 18    | GET    | /api/user/\w+                   | 0   | 18    | 0   | 0   | 0   | 1.472   | 0.082 | 0.064 | 0.168  |
| 15    | GET    | /api/user/\w+/theme             | 0   | 15    | 0   | 0   | 0   | 0.264   | 0.018 | 0.004 | 0.052  |
| 1     | GET    | /api/payment                    | 0   | 1     | 0   | 0   | 0   | 0.004   | 0.004 | 0.004 | 0.004  |
+-------+--------+---------------------------------+-----+-------+-----+-----+-----+---------+-------+-------+--------+

[takeokunn]

pt-query-digest:

# 11.7s user time, 20ms system time, 36.14M rss, 42.67M vsz
# Current date: Sat Nov 25 07:15:48 2023
# Hostname: ip-192-168-0-12
# Files: /var/log/mysql/mysql-slow.log
# Overall: 160.24k total, 123 unique, 1.37k QPS, 2.29x concurrency _______
# Time range: 2023-11-25T07:13:12 to 2023-11-25T07:15:09
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           268s     1us   528ms     2ms     7ms     7ms   131us
# Lock time          782ms       0    82ms     4us     1us   333us     1us
# Rows sent        228.19k       0   7.40k    1.46    1.96   48.44       0
# Rows examine      47.66M       0  14.02k  311.88   1.39k   1.33k       0
# Query size        22.05M       5   1.94M  144.31  329.68   5.16k   97.36

# Profile
# Rank Query ID                      Response time Calls R/Call V/M   Item
# ==== ============================= ============= ===== ====== ===== ====
#    1 0x42EF7D7D98FBCC9723BF896E... 86.8590 32.4%  5858 0.0148  0.02 SELECT records
#    2 0x64CC8A4E8E4B390203375597... 36.1568 13.5%   915 0.0395  0.01 SELECT ng_words
#    3 0x3D83BC87F3B3A00D571FFC81... 27.0667 10.1%  4519 0.0060  0.01 SELECT records
#    4 0x59F1B6DD8D9FEC059E55B3BF... 14.8440  5.5%   836 0.0178  0.01 SELECT reservation_slots
#    5 0x4ADE2DC90689F1C4891749AF... 12.3158  4.6% 24311 0.0005  0.00 DELETE SELECT livecomments
#    6 0x38BC86A45F31C6B1EE324671... 10.8409  4.0%  6281 0.0017  0.00 SELECT themes
#    7 0x9AC623FA477E73A44D191D29...  9.2750  3.5%   536 0.0173  0.02 SELECT records
#    8 0x9EAD6C0CE525E3693EE27FFC...  6.4643  2.4%   154 0.0420  0.01 SELECT livestreams
#    9 0x22279D81D51006139E0C7640...  5.6794  2.1%  6734 0.0008  0.01 SELECT domains domainmetadata
#   10 0x8F7679D452333ED3C7D60D22...  5.3538  2.0% 19428 0.0003  0.01 ADMIN RESET STMT
#   11 0xFFFCA4D67EA0A788813031B8...  5.3342  2.0%  5337 0.0010  0.01 COMMIT
#   12 0xFD38427AE3D09E3883A680F7...  5.3012  2.0% 21693 0.0002  0.00 SELECT livestreams livecomments
#   13 0xC499D81D570D361DB61FC43A...  4.9860  1.9% 21696 0.0002  0.00 SELECT livestreams reactions
#   14 0x859BBB7E9D760686137A9444...  4.6621  1.7%   268 0.0174  0.02 DELETE records
#   15 0xA3401CA3ABCC04C3AB221DB8...  4.6083  1.7%    98 0.0470  0.03 UPDATE reservation_slots
#   16 0x5A8A79FB9E5D1635CA10FEA9...  3.5406  1.3%  1910 0.0019  0.00 SELECT themes
#   17 0xBB9B3D66E02B513839482132...  3.2081  1.2%  3977 0.0008  0.01 SELECT tags livestream_tags
#   18 0x7F9C0C0BA9473953B723EE16...  3.0684  1.1%    99 0.0310  0.02 SELECT reservation_slots
#   19 0xEA1E6309EEEFF9A6831AD2FB...  2.2583  0.8%  6001 0.0004  0.00 SELECT users
#   20 0xD6032FE08E1FE706A928B8B7...  1.9005  0.7%  6309 0.0003  0.00 SELECT livestreams
# MISC 0xMISC                        14.6966  5.5% 23283 0.0006   0.0 <103 ITEMS>

# Query 1: 72.32 QPS, 1.07x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 22827521
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2023-11-25T07:13:48 to 2023-11-25T07:15:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3    5858
# Exec time     32     87s   110us   173ms    15ms    48ms    17ms     8ms
# Lock time      2    21ms       0     3ms     3us     1us    73us     1us
# Rows sent      0   1.64k       0       1    0.29    0.99    0.45       0
# Rows examine  16   8.05M       1   1.51k   1.41k   1.46k   81.20   1.39k
# Query size     3 809.57k     128     218  141.52  158.58   11.43  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='hd0iq2zs9ibqu40.u.isucon.dev' and domain_id=13\G

# Query 2: 13.26 QPS, 0.52x concurrency, ID 0x64CC8A4E8E4B390203375597CE4D611F at byte 42779099
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T07:14:00 to 2023-11-25T07:15:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     915
# Exec time     13     36s     4ms   131ms    40ms    78ms    21ms    36ms
# Lock time      0     1ms       0    55us     1us     1us     2us     1us
# Rows sent      0     139       0       2    0.15    0.99    0.38       0
# Rows examine  26  12.52M  14.00k  14.01k  14.01k  13.78k       0  13.78k
# Query size     0  88.46k      97      99   99.00   97.36    0.22   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 = 1102 AND livestream_id = 7555\G

# Query 3: 43.04 QPS, 0.26x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 54305610
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T07:13:24 to 2023-11-25T07:15:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2    4519
# Exec time     10     27s   112us    88ms     6ms    20ms     8ms     3ms
# Lock time      2    20ms       0     9ms     4us     1us   133us     1us
# Rows sent      1   2.32k       0       1    0.53    0.99    0.50    0.99
# Rows examine  13   6.20M       0   1.51k   1.41k   1.46k   89.09   1.39k
# Query size     2 591.33k     126     216  133.99  151.03   10.29  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 4: 11.61 QPS, 0.21x concurrency, ID 0x59F1B6DD8D9FEC059E55B3BFD624E8C3 at byte 22828988
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T07:13:57 to 2023-11-25T07:15:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     836
# Exec time      5     15s     2ms   138ms    18ms    48ms    16ms    13ms
# Lock time      0     3ms       0     2ms     4us     1us    66us     1us
# Rows sent      0     836       1       1       1       1       0       1
# Rows examine  14   6.98M   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0  70.21k      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 = 1701025200 AND end_at = 1701028800\G

# Query 5: 398.54 QPS, 0.20x concurrency, ID 0x4ADE2DC90689F1C4891749AF54FB8D14 at byte 36742281
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T07:14:08 to 2023-11-25T07:15:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         15   24311
# Exec time      4     12s   100us    32ms   506us     2ms     1ms   152us
# Lock time      6    49ms       0     5ms     1us     1us    39us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0  47.49k       2       4    2.00    1.96    0.02    1.96
# Query size    34   7.66M     263     529  330.59  363.48   27.47  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 = 699 AND
            livestream_id = 7525 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 = 699 AND
            livestream_id = 7525 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 6: 87.24 QPS, 0.15x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 21891999
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T07:13:57 to 2023-11-25T07:15:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3    6281
# Exec time      4     11s   265us    59ms     2ms     7ms     3ms   568us
# Lock time      1    15ms       0     4ms     2us     1us    54us     1us
# Rows sent      2   6.13k       1       1       1       1       0       1
# Rows examine  14   6.79M    1000   1.23k   1.11k   1.20k   70.55   1.09k
# Query size     1 251.44k      38      41   40.99   40.45    0.14   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 = 1036\G

# Query 7: 6.62 QPS, 0.11x concurrency, ID 0x9AC623FA477E73A44D191D296850A025 at byte 30943453
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2023-11-25T07:13:48 to 2023-11-25T07:15:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     536
# Exec time      3      9s   121us   123ms    17ms    59ms    19ms    10ms
# Lock time      0   545us       0     3us     1us     1us       0     1us
# Rows sent      0     268       0       1    0.50    0.99    0.50    0.99
# Rows examine   1 731.02k       1   1.51k   1.36k   1.46k  164.28   1.33k
# Query size     0  79.83k     141     160  152.51  151.03    3.80  151.03
# 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='A' and name='hiroshifukuda0.u.isucon.dev' and domain_id=13\G

# Query 8: 2.41 QPS, 0.10x concurrency, ID 0x9EAD6C0CE525E3693EE27FFC9AAF05A3 at byte 37675698
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T07:13:57 to 2023-11-25T07:15:01
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     154
# Exec time      2      6s     5ms   135ms    42ms    91ms    24ms    38ms
# Lock time      0   179us       0     2us     1us     1us       0     1us
# Rows sent      0     185       0      21    1.20    3.89    3.08       0
# Rows examine   2   1.11M   7.32k   7.41k   7.37k   7.31k       0   7.31k
# Query size     0   6.91k      43      46   45.94   44.60    0.34   44.60
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ###
#  10ms  ################################################################
# 100ms  ###