pt - Githubissues

# 25.9s user time, 70ms system time, 36.53M rss, 42.93M vsz
# Current date: Sat Nov 25 01:51:23 2023
# Hostname: isucon13-1
# Files: /var/log/mysql/mysql-slow.log
# Overall: 403.25k total, 94 unique, 1.97k QPS, 2.36x concurrency ________
# Time range: 2023-11-25T01:46:59 to 2023-11-25T01:50:24
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           484s     1us   829ms     1ms     4ms     5ms    80us
# Lock time          573ms       0    52ms     1us     1us   101us       0
# Rows sent        163.38k       0   7.32k    0.41    0.99   18.80       0
# Rows examine     135.82M       0  14.02k  353.18   1.26k   1.39k       0
# Query size        40.29M       5   1.94M  104.78  284.79   3.65k   31.70

# Profile
# Rank Query ID                     Response time  Calls  R/Call V/M   Ite
# ==== ============================ ============== ====== ====== ===== ===
#    1 0xF7144185D9A142A426A36DC... 138.6108 28.6%   5663 0.0245  0.01 SELECT livestream_tags
#    2 0x42EF7D7D98FBCC9723BF896...  54.2388 11.2%   6043 0.0090  0.01 SELECT records
#    3 0x84B457C910C4A79FC9EBECB...  40.2579  8.3%  10114 0.0040  0.01 SELECT icons
#    4 0xDA556F9115773A1A99AA016...  39.2391  8.1% 118370 0.0003  0.00 ADMIN PREPARE
#    5 0xF1B8EF06D6CA63B24BFF433...  27.7126  5.7%   2922 0.0095  0.02 SELECT users livestreams livecomments
#    6 0xDB74D52D39A7090F224C4DE...  27.1032  5.6%   2922 0.0093  0.02 SELECT users livestreams reactions
#    7 0x3D83BC87F3B3A00D571FFC8...  24.0113  5.0%   4466 0.0054  0.01 SELECT records
#    8 0x38BC86A45F31C6B1EE32467...  17.6215  3.6%   8434 0.0021  0.00 SELECT themes
#    9 0x59F1B6DD8D9FEC059E55B3B...  15.8571  3.3%    694 0.0228  0.01 SELECT reservation_slots
#   10 0x4ADE2DC90689F1C4891749A...  10.4751  2.2%  19052 0.0005  0.00 DELETE SELECT livecomments
# MISC 0xMISC                        88.8531 18.4% 224565 0.0004   0.0 <84 ITEMS>

# Query 1: 57.20 QPS, 1.40x concurrency, ID 0xF7144185D9A142A426A36DC55C1D2623 at byte 76009236
# Scores: V/M = 0.01
# Time range: 2023-11-25T01:48:45 to 2023-11-25T01:50:24
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1    5663
# Exec time     28    139s     2ms   126ms    24ms    56ms    18ms    23ms
# Lock time      2    15ms       0     3ms     2us     1us    50us     1us
# Rows sent     12  19.97k       0      11    3.61    4.96    1.83    4.96
# Rows examine  44  60.10M  10.71k  11.05k  10.87k  10.80k  173.50  10.80k
# Query size     0 309.51k      53      56   55.97   54.21    0.14   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 = 7530\G

# Query 2: 54.94 QPS, 0.49x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 45310535
# Scores: V/M = 0.01
# Time range: 2023-11-25T01:48:34 to 2023-11-25T01:50:24
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1    6043
# Exec time     11     54s   832us   116ms     9ms    27ms    10ms     6ms
# Lock time      7    43ms       0    12ms     7us     1us   182us     1us
# Rows sent      0   1.24k       0       1    0.21    0.99    0.40       0
# Rows examine   5   7.94M   1.25k   1.42k   1.34k   1.39k   50.12   1.33k
# Query size     2 834.11k     129     209  141.34  158.58   11.66  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='*.u.isucon.dev' and domain_id=2\G

# Query 3: 102.16 QPS, 0.41x concurrency, ID 0x84B457C910C4A79FC9EBECB8B1065C66 at byte 82456983
# Scores: V/M = 0.01
# Time range: 2023-11-25T01:48:45 to 2023-11-25T01:50:24
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2   10114
# Exec time      8     40s    55us    69ms     4ms    14ms     5ms     2ms
# Lock time      5    32ms       0     4ms     3us     1us    56us     1us
# Rows sent      4   7.32k       0       1    0.74    0.99    0.43    0.99
# Rows examine   0 729.12k       0     167   73.82  151.03   50.53   69.19
# Query size     1 432.28k      41      44   43.77   42.48    0.30   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 = 1013\G

# Query 4: 1.08k QPS, 0.36x concurrency, ID 0xDA556F9115773A1A99AA0165670CE848 at byte 42715829
# Scores: V/M = 0.00
# Time range: 2023-11-25T01:48:34 to 2023-11-25T01:50:24
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         29  118370
# Exec time      8     39s    23us   103ms   331us     1ms     1ms    84us
# Lock time      0    13us       0     8us       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     8   3.39M      30      30      30      30       0      30
# String:
# Databases    isupipe (117307/99%), isudns (1063/0%)
# Hosts        localhost
# Users        isucon (117307/99%), isudns (1063/0%)
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  ########################################
#   1ms  ########
#  10ms  #
# 100ms  #
#    1s
#  10s+
administrator command: Prepare\G

# Query 5: 29.52 QPS, 0.28x concurrency, ID 0xF1B8EF06D6CA63B24BFF433E06CCEB22 at byte 75220200
# Scores: V/M = 0.02
# Time range: 2023-11-25T01:48:45 to 2023-11-25T01:50:24
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    2922
# Exec time      5     28s     2ms   115ms     9ms    36ms    13ms     2ms
# Lock time      0     6ms       0   335us     1us     1us     8us     1us
# Rows sent      1   2.85k       1       1       1       1       0       1
# Rows examine   4   5.77M   1.96k   2.33k   2.02k   2.16k  113.34   1.96k
# Query size     1 470.29k     163     166  164.81  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 = 40\G

# Query 6: 29.52 QPS, 0.27x concurrency, ID 0xDB74D52D39A7090F224C4DEEAF3028C9 at byte 76001503
# Scores: V/M = 0.02
# Time range: 2023-11-25T01:48:45 to 2023-11-25T01:50:24
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    2922
# Exec time      5     27s     2ms   150ms     9ms    36ms    13ms     2ms
# Lock time      0     5ms       0   129us     1us     1us     4us     1us
# Rows sent      1   2.85k       1       1       1       1       0       1
# Rows examine   4   5.77M   1.96k   2.30k   2.02k   2.16k  113.34   1.96k
# Query size     1 413.21k     143     146  144.81  143.84    0.92  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 = 48\G

# Query 7: 40.60 QPS, 0.22x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 108356096
# Scores: V/M = 0.01
# Time range: 2023-11-25T01:48:34 to 2023-11-25T01:50:24
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1    4466
# Exec time      4     24s   673us    94ms     5ms    16ms     6ms     2ms
# Lock time      5    30ms       0    13ms     6us     1us   198us     1us
# Rows sent      1   2.25k       0       1    0.51    0.99    0.50    0.99
# Rows examine   4   5.86M   1.25k   1.42k   1.34k   1.39k   51.02   1.33k
# Query size     1 588.68k     126     208  134.98  151.03   10.99  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='oyo3lwedthzljey0.u.isucon.dev'\G

# Query 8: 85.19 QPS, 0.18x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 45906936
# Scores: V/M = 0.00
# Time range: 2023-11-25T01:48:45 to 2023-11-25T01:50:24
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2    8434
# Exec time      3     18s   252us    57ms     2ms     7ms     3ms   761us
# Lock time      2    14ms       0   530us     1us     1us    10us     1us
# Rows sent      5   8.24k       1       1       1       1       0       1
# Rows examine   6   8.68M    1000   1.15k   1.05k   1.09k   55.23   1.04k
# Query size     0 335.89k      38      41   40.78   40.45    0.78   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 = 1018\G

# Query 9: 7.01 QPS, 0.16x concurrency, ID 0x59F1B6DD8D9FEC059E55B3BFD624E8C3 at byte 56096727
# Scores: V/M = 0.01
# Time range: 2023-11-25T01:48:45 to 2023-11-25T01:50:24
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     694
# Exec time      3     16s     2ms    94ms    23ms    48ms    14ms    21ms
# Lock time      0     1ms       0   255us     1us     1us    10us     1us
# Rows sent      0     694       1       1       1       1       0       1
# Rows examine   4   5.80M   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0  58.29k      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 = 1701010800 AND end_at = 1701014400\G

# Query 10: 312.33 QPS, 0.17x concurrency, ID 0x4ADE2DC90689F1C4891749AF54FB8D14 at byte 119297974
# Scores: V/M = 0.00
# Time range: 2023-11-25T01:49:23 to 2023-11-25T01:50:24
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   19052
# Exec time      2     10s    56us    33ms   549us     2ms     1ms   176us
# Lock time     10    59ms       0     3ms     3us     1us    48us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0  37.22k       2       4    2.00    1.96    0.02    1.96
# Query size    14   5.93M     263     481  326.51  363.48   25.24  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 = 994 AND
            livestream_id = 7549 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 = 994 AND
            livestream_id = 7549 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
illmaticindustries / isucon13

pt #8
