2023/11/25(Sat)16:35:05

[takeokunn]

17,737

[takeokunn]

alp:

+-------+--------+---------------------------------+-----+-------+-----+-----+-----+---------+-------+-------+--------+
| COUNT | METHOD |               URI               | 1XX |  2XX  | 3XX | 4XX | 5XX |   SUM   |  AVG  |  MIN  |  MAX   |
+-------+--------+---------------------------------+-----+-------+-----+-----+-----+---------+-------+-------+--------+
| 53    | POST   | /api/livestream/\w+/moderate    | 0   | 46    | 0   | 7   | 0   | 419.247 | 7.910 | 0.104 | 18.800 |
| 3005  | POST   | /api/livestream/\w+/livecomment | 0   | 2980  | 0   | 25  | 0   | 382.379 | 0.127 | 0.004 | 0.344  |
| 3032  | GET    | /api/livestream/\w+/livecomment | 0   | 3030  | 0   | 2   | 0   | 301.217 | 0.099 | 0.004 | 0.340  |
| 400   | GET    | /api/livestream/\w+             | 0   | 384   | 0   | 16  | 0   | 297.928 | 0.745 | 0.004 | 20.004 |
| 3131  | GET    | /api/livestream/\w+/reaction    | 0   | 3126  | 0   | 5   | 0   | 296.627 | 0.095 | 0.004 | 0.308  |
| 843   | POST   | /api/register                   | 0   | 835   | 0   | 5   | 3   | 204.553 | 0.243 | 0.004 | 0.744  |
| 2784  | POST   | /api/livestream/\w+/reaction    | 0   | 2782  | 0   | 2   | 0   | 184.657 | 0.066 | 0.004 | 0.260  |
| 32453 | GET    | /api/user/\w+/icon              | 0   | 32451 | 0   | 2   | 0   | 181.474 | 0.006 | 0.000 | 0.156  |
| 316   | POST   | /api/livestream/\w+             | 0   | 304   | 0   | 12  | 0   | 138.972 | 0.440 | 0.184 | 1.092  |
| 347   | GET    | /api/livestream                 | 0   | 346   | 0   | 1   | 0   | 31.184  | 0.090 | 0.004 | 0.224  |
| 859   | POST   | /api/login                      | 0   | 852   | 0   | 7   | 0   | 20.040  | 0.023 | 0.004 | 0.128  |
| 835   | POST   | /api/icon                       | 0   | 835   | 0   | 0   | 0   | 16.633  | 0.020 | 0.008 | 0.128  |
| 213   | GET    | /api/livestream/\w+/report      | 0   | 213   | 0   | 0   | 0   | 11.776  | 0.055 | 0.008 | 0.176  |
| 51    | GET    | /api/user/\w+/statistics        | 0   | 51    | 0   | 0   | 0   | 10.760  | 0.211 | 0.012 | 0.356  |
| 295   | POST   | /api/livestream/\w+/enter       | 0   | 295   | 0   | 0   | 0   | 9.668   | 0.033 | 0.004 | 0.172  |
| 283   | GET    | /api/tag                        | 0   | 283   | 0   | 0   | 0   | 8.720   | 0.031 | 0.004 | 0.436  |
| 3     | POST   | /api/initialize                 | 0   | 3     | 0   | 0   | 0   | 6.888   | 2.296 | 2.232 | 2.412  |
| 264   | DELETE | /api/livestream/\w+/exit        | 0   | 264   | 0   | 0   | 0   | 6.452   | 0.024 | 0.004 | 0.128  |
| 57    | GET    | /api/user/\w+                   | 0   | 57    | 0   | 0   | 0   | 4.688   | 0.082 | 0.040 | 0.176  |
| 48    | GET    | /api/user/\w+/theme             | 0   | 48    | 0   | 0   | 0   | 0.752   | 0.016 | 0.004 | 0.064  |
| 3     | GET    | /api/payment                    | 0   | 3     | 0   | 0   | 0   | 0.008   | 0.003 | 0.004 | 0.004  |
| 1     | GET    | /                               | 0   | 1     | 0   | 0   | 0   | 0.000   | 0.000 | 0.000 | 0.000  |
+-------+--------+---------------------------------+-----+-------+-----+-----+-----+---------+-------+-------+--------+

[takeokunn]

pt-query-digest:

# 36.7s user time, 80ms system time, 38.62M rss, 44.93M vsz
# Current date: Sat Nov 25 07:35:45 2023
# Hostname: ip-192-168-0-12
# Files: /var/log/mysql/mysql-slow.log
# Overall: 519.42k total, 128 unique, 443.57 QPS, 0.67x concurrency ______
# Time range: 2023-11-25T07:13:12 to 2023-11-25T07:32:43
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           790s     1us   528ms     2ms     7ms     6ms   131us
# Lock time             2s       0    82ms     3us     1us   244us     1us
# Rows sent        720.03k       0   7.41k    1.42    0.99   49.01       0
# Rows examine     158.40M       0  14.02k  319.78   1.39k   1.31k       0
# Query size        70.08M       5   1.94M  141.47  329.68   4.96k   97.36

# Profile
# Rank Query ID                      Response time  Calls R/Call V/M   Ite
# ==== ============================= ============== ===== ====== ===== ===
#    1 0x42EF7D7D98FBCC9723BF896E... 203.4565 25.7% 22446 0.0091  0.02 SELECT records
#    2 0x64CC8A4E8E4B390203375597... 114.8505 14.5%  2850 0.0403  0.01 SELECT ng_words
#    3 0x3D83BC87F3B3A00D571FFC81... 108.6701 13.8% 17217 0.0063  0.01 SELECT records
#    4 0x59F1B6DD8D9FEC059E55B3BF...  50.3133  6.4%  2653 0.0190  0.01 SELECT reservation_slots
#    5 0x4ADE2DC90689F1C4891749AF...  39.3997  5.0% 76799 0.0005  0.00 DELETE SELECT livecomments
#    6 0x38BC86A45F31C6B1EE324671...  33.5098  4.2% 19594 0.0017  0.00 SELECT themes
#    7 0x22279D81D51006139E0C7640...  21.4896  2.7% 26118 0.0008  0.01 SELECT domains domainmetadata
#    8 0x8F7679D452333ED3C7D60D22...  20.4949  2.6% 69994 0.0003  0.01 ADMIN RESET STMT
#    9 0x9EAD6C0CE525E3693EE27FFC...  18.4341  2.3%   443 0.0416  0.01 SELECT livestreams
#   10 0xFD38427AE3D09E3883A680F7...  18.3070  2.3% 67506 0.0003  0.00 SELECT livestreams livecomments
#   11 0xC499D81D570D361DB61FC43A...  17.1237  2.2% 67512 0.0003  0.00 SELECT livestreams reactions
#   12 0xFFFCA4D67EA0A788813031B8...  16.0995  2.0% 16702 0.0010  0.01 COMMIT
#   13 0x9AC623FA477E73A44D191D29...  15.2268  1.9%  1680 0.0091  0.02 SELECT records
#   14 0x859BBB7E9D760686137A9444...  13.9925  1.8%   840 0.0167  0.01 DELETE records
#   15 0xA3401CA3ABCC04C3AB221DB8...  13.9271  1.8%   306 0.0455  0.03 UPDATE reservation_slots
#   16 0x5A8A79FB9E5D1635CA10FEA9...  10.7896  1.4%  5966 0.0018  0.00 SELECT themes
#   17 0x7F9C0C0BA9473953B723EE16...  10.7261  1.4%   310 0.0346  0.02 SELECT reservation_slots
#   18 0xBB9B3D66E02B513839482132...   9.8806  1.3% 12445 0.0008  0.01 SELECT tags livestream_tags
#   19 0xEA1E6309EEEFF9A6831AD2FB...   6.5959  0.8% 18711 0.0004  0.00 SELECT users
#   20 0xD6032FE08E1FE706A928B8B7...   5.7185  0.7% 19804 0.0003  0.00 SELECT livestreams
# MISC 0xMISC                         41.1438  5.2% 69522 0.0006   0.0 <108 ITEMS>

# Query 1: 22.11 QPS, 0.20x 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:30:43
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   22446
# Exec time     25    203s   110us   173ms     9ms    33ms    12ms     5ms
# Lock time      4    86ms       0    11ms     3us     1us   102us     1us
# Rows sent      0   6.18k       0       1    0.28    0.99    0.45       0
# Rows examine  19  30.65M       1   1.53k   1.40k   1.46k   78.77   1.39k
# Query size     4   3.03M     128     221  141.58  158.58   11.62  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: 2.84 QPS, 0.11x concurrency, ID 0x64CC8A4E8E4B390203375597CE4D611F at byte 95476270
# 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:30:43
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    2850
# Exec time     14    115s     4ms   148ms    40ms    82ms    22ms    36ms
# Lock time      0     4ms       0   671us     1us     1us    12us     1us
# Rows sent      0     415       0       2    0.15    0.99    0.38       0
# Rows examine  24  38.99M  14.00k  14.02k  14.01k  13.78k    0.00  13.78k
# Query size     0 275.53k      97      99   99.00   97.36    0.21   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 = 1035 AND livestream_id = 7557\G

# Query 3: 16.57 QPS, 0.10x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 90505032
# 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:30:43
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3   17217
# Exec time     13    109s   112us   121ms     6ms    21ms     9ms     3ms
# Lock time      2    49ms       0     9ms     2us     1us    75us     1us
# Rows sent      1   8.75k       0       1    0.52    0.99    0.50    0.99
# Rows examine  14  23.49M       0   1.53k   1.40k   1.46k   81.23   1.39k
# Query size     3   2.20M     126     219  134.14  151.03   10.40  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='eq49ugzqqohiy426yc0.u.isucon.dev'\G

# Query 4: 2.64 QPS, 0.05x 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:30:43
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    2653
# Exec time      6     50s     2ms   138ms    19ms    51ms    16ms    14ms
# Lock time      0     6ms       0     2ms     2us     1us    39us     1us
# Rows sent      0   2.59k       1       1       1       1       0       1
# Rows examine  13  22.16M   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0 222.81k      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: 77.18 QPS, 0.04x 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:30:43
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         14   76799
# Exec time      4     39s    88us    32ms   513us     2ms     1ms   152us
# Lock time     12   239ms       0    22ms     3us     1us   113us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0 150.03k       2       4    2.00    1.96    0.03    1.96
# Query size    34  24.33M     263     532  332.14  381.65   28.06  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: 19.48 QPS, 0.03x 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:30:43
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3   19594
# Exec time      4     34s   265us    59ms     2ms     7ms     3ms   568us
# Lock time      2    48ms       0     4ms     2us     1us    55us     1us
# Rows sent      2  19.13k       1       1       1       1       0       1
# Rows examine  13  21.34M    1000   1.26k   1.12k   1.20k   75.89   1.09k
# Query size     1 784.39k      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: 25.91 QPS, 0.02x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 111146195
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T07:13:55 to 2023-11-25T07:30:43
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          5   26118
# Exec time      2     21s    84us    88ms   822us     3ms     3ms   159us
# Lock time      9   185ms       0    24ms     7us     1us   236us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     4   2.84M     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: 67.37 QPS, 0.02x concurrency, ID 0x8F7679D452333ED3C7D60D22131CEFD4 at byte 105892584
# 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:30:43
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         13   69994
# Exec time      2     20s     8us    69ms   292us     1ms     2ms    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     3   2.20M      33      33      33      33       0      33
# String:
# Databases    isudns
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us  #
#  10us  ################################################################
# 100us  #######
#   1ms  ###