search

sunakan / isunarabe13-suburi

0 stars 0 forks source link

分析-002(4183→5705) #3

Closed sunakan closed 1 month ago

sunakan commented 1 month ago

の続き

image 
make download-files-for-analysis
make pt-query-digest
or
pt-query-digest tmp/analysis/latest/mysql-slow.log.*
# 23.1s user time, 150ms system time, 57.53M rss, 392.66G vsz
# Current date: Sun Sep 22 18:53:13 2024
# Hostname: 
# 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: 599.47k total, 94 unique, 4.80k QPS, 3.15x concurrency ________
# Time range: 2024-09-22T09:46:22 to 2024-09-22T09:48:27
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           393s     1us   397ms   656us     2ms     3ms    76us
# Lock time          981ms       0    48ms     1us     1us   135us       0
# Rows sent        202.17k       0   7.32k    0.35    0.99   15.75       0
# Rows examine      71.78M       0  14.02k  125.55  964.41  601.20       0
# Query size        61.75M       5   1.94M  108.01  158.58   3.22k   31.70

# Profile
# Rank Query ID                            Response time Calls  R/Call V/M
# ==== =================================== ============= ====== ====== ===
#    1 0x84B457C910C4A79FC9EBECB8B1065C66  88.6541 22.5%  12852 0.0069  0.01 SELECT icons
#    2 0xDA556F9115773A1A99AA0165670CE848  50.3304 12.8% 144385 0.0003  0.01 ADMIN PREPARE
#    3 0xF1B8EF06D6CA63B24BFF433E06CCEB22  29.6763  7.5%   3742 0.0079  0.01 SELECT users livestreams livecomments
#    4 0xDB74D52D39A7090F224C4DEEAF3028C9  28.5313  7.3%   3742 0.0076  0.01 SELECT users livestreams reactions
#    5 0x38BC86A45F31C6B1EE324671506C898A  21.6412  5.5%  10702 0.0020  0.00 SELECT themes
#    6 0x59F1B6DD8D9FEC059E55B3BFD624E8C3  18.4428  4.7%    914 0.0202  0.01 SELECT reservation_slots
#    7 0x22279D81D51006139E0C76405B54C261  15.6659  4.0%  32764 0.0005  0.00 SELECT domains domainmetadata
#    8 0x42EF7D7D98FBCC9723BF896EBFC51D24  13.1185  3.3%  26003 0.0005  0.00 SELECT records
#    9 0x4ADE2DC90689F1C4891749AF54FB8D14  12.8895  3.3%  24944 0.0005  0.00 DELETE SELECT livecomments
#   10 0xFFFCA4D67EA0A788813031B8BBC3B329  12.5798  3.2%   2586 0.0049  0.00 COMMIT
#   11 0x64CC8A4E8E4B390203375597CE4D611F  10.7217  2.7%    251 0.0427  0.01 SELECT ng_words
#   12 0x8F7679D452333ED3C7D60D22131CEFD4  10.2253  2.6%  75963 0.0001  0.00 ADMIN RESET STMT
#   13 0xFBC5564AE716EAE82F20BFB45F6C37E7  10.1623  2.6%  27165 0.0004  0.00 SELECT tags
#   14 0x3D83BC87F3B3A00D571FFC8104A6E50C   9.3879  2.4%  17800 0.0005  0.00 SELECT records
#   15 0xD2A0864774622BA36F6557496405CF75   5.6306  1.4%   1263 0.0045  0.00 INSERT themes
#   16 0xDFFCC1D78939C4D781C7C58349101F50   5.5149  1.4%   1000 0.0055  0.00 INSERT users
#   17 0xA3401CA3ABCC04C3AB221DB8AD5CBF26   4.7206  1.2%    104 0.0454  0.02 UPDATE reservation_slots
#   18 0x7F9C0C0BA9473953B723EE16C08655F1   4.5354  1.2%    106 0.0428  0.02 SELECT reservation_slots
#   19 0xFD38427AE3D09E3883A680F7BAF95D3A   4.2670  1.1%  14992 0.0003  0.00 SELECT livestreams livecomments
#   20 0xEA1E6309EEEFF9A6831AD2FB940FC23C   4.0819  1.0%  10433 0.0004  0.00 SELECT users
# MISC 0xMISC                              32.6174  8.3% 187754 0.0002   0.0 <74 ITEMS>

# Query 1: 146.05 QPS, 1.01x concurrency, ID 0x84B457C910C4A79FC9EBECB8B1065C66 at byte 118907580
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T09:46:59 to 2024-09-22T09:48:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2   12852
# Exec time     22     89s    45us    58ms     7ms    21ms     7ms     4ms
# Lock time      3    32ms       0     4ms     2us     1us    44us     1us
# Rows sent      4   9.61k       0       1    0.77    0.99    0.42    0.99
# Rows examine   2   1.47M       0     255  120.10  234.30   75.14  118.34
# Query size     0 549.56k      41      44   43.79   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 = 1036\G

# Query 2: 1.42k QPS, 0.49x concurrency, ID 0xDA556F9115773A1A99AA0165670CE848 at byte 44632283
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T09:46:45 to 2024-09-22T09:48:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         24  144385
# Exec time     12     50s    23us   397ms   348us     1ms     2ms    76us
# Lock time      0    18us       0    11us       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     6   4.13M      30      30      30      30       0      30
# String:
# Databases    isupipe (142794/98%), isudns (1591/1%)
# Hosts        localhost
# Users        isucon (142794/98%), isudns (1591/1%)
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  ########################################
#   1ms  ########
#  10ms  #
# 100ms  #
#    1s
#  10s+
administrator command: Prepare\G

# Query 3: 42.52 QPS, 0.34x concurrency, ID 0xF1B8EF06D6CA63B24BFF433E06CCEB22 at byte 163111078
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T09:46:59 to 2024-09-22T09:48:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    3742
# Exec time      7     30s     1ms    76ms     8ms    27ms    10ms     1ms
# Lock time      1    12ms       0     2ms     3us     1us    37us     1us
# Rows sent      1   3.65k       1       1       1       1       0       1
# Rows examine  10   7.50M   1.96k   2.42k   2.05k   2.27k  124.28   1.96k
# Query size     0 602.29k     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 = 218\G

# Query 4: 42.52 QPS, 0.32x concurrency, ID 0xDB74D52D39A7090F224C4DEEAF3028C9 at byte 85988998
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T09:46:59 to 2024-09-22T09:48:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    3742
# Exec time      7     29s     1ms    98ms     8ms    27ms     9ms     1ms
# Lock time      1    12ms       0     5ms     3us     1us    79us     1us
# Rows sent      1   3.65k       1       1       1       1       0       1
# Rows examine  10   7.50M   1.96k   2.39k   2.05k   2.16k  102.53   1.96k
# Query size     0 529.21k     143     146  144.82  143.84    0.88  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 = 132\G

# Query 5: 121.61 QPS, 0.25x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 178675169
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:46:59 to 2024-09-22T09:48:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1   10702
# Exec time      5     22s   241us    45ms     2ms     7ms     2ms   972us
# Lock time      2    24ms       0     2ms     2us     1us    27us     1us
# Rows sent      5  10.45k       1       1       1       1       0       1
# Rows examine  16  11.50M    1000   1.23k   1.10k   1.20k   82.61   1.09k
# Query size     0 426.53k      38      41   40.81   40.45    0.74   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 = 1029\G

# Query 6: 10.39 QPS, 0.21x concurrency, ID 0x59F1B6DD8D9FEC059E55B3BFD624E8C3 at byte 85951679
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T09:46:59 to 2024-09-22T09:48:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     914
# Exec time      4     18s     2ms    76ms    20ms    42ms    12ms    18ms
# Lock time      0     2ms       0   391us     2us     1us    15us     1us
# Rows sent      0     914       1       1       1       1       0       1
# Rows examine  10   7.63M   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0  76.76k      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 = 1701068400 AND end_at = 1701072000\G

# Query 7: 321.22 QPS, 0.15x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 172207304
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:46:45 to 2024-09-22T09:48:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          5   32764
# Exec time      3     16s    84us    22ms   478us     2ms   809us   194us
# Lock time     11   112ms       0     5ms     3us     1us    46us     1us
# 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     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 8: 254.93 QPS, 0.13x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 184340060
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:46:45 to 2024-09-22T09:48:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   26003
# Exec time      3     13s    92us    21ms   504us     2ms   822us   214us
# Lock time      5    56ms       0     1ms     2us     1us    20us     1us
# Rows sent      0   1.79k       0       1    0.07    0.99    0.25       0
# Rows examine   0   1.79k       0       1    0.07    0.99    0.25       0
# Query size     5   3.52M     128     221  141.86  151.03   12.05  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 9: 408.92 QPS, 0.21x concurrency, ID 0x4ADE2DC90689F1C4891749AF54FB8D14 at byte 180220063
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:47:26 to 2024-09-22T09:48:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   24944
# Exec time      3     13s    48us    24ms   516us     2ms   953us   185us
# Lock time     10   102ms       0     7ms     4us     1us    78us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0  48.73k       2       4    2.00    1.96    0.02    1.96
# Query size    12   7.84M     263     526  329.40  363.48   27.18  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 = 52 AND
            livestream_id = 7559 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 = 52 AND
            livestream_id = 7559 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: 29.39 QPS, 0.14x concurrency, ID 0xFFFCA4D67EA0A788813031B8BBC3B329 at byte 125547761
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:46:59 to 2024-09-22T09:48:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    2586
# Exec time      3     13s    25us    50ms     5ms    13ms     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  15.15k       6       6       6       6       0       6
# String:
# Databases    isupipe (2322/89%), isudns (264/10%)
# Hosts        localhost
# Users        isucon (2322/89%), isudns (264/10%)
# Query_time distribution
#   1us
#  10us  #########################
# 100us  ############
#   1ms  ################################################################
#  10ms  #############
# 100ms
#    1s
#  10s+
COMMIT\G

# Query 11: 3.26 QPS, 0.14x concurrency, ID 0x64CC8A4E8E4B390203375597CE4D611F at byte 138574121
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T09:47:09 to 2024-09-22T09:48:26
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     251
# Exec time      2     11s     4ms   116ms    43ms    71ms    21ms    44ms
# Lock time      0   272us       0     2us     1us     1us       0     1us
# Rows sent      0      83       0       2    0.33    0.99    0.47       0
# Rows examine   4   3.43M  14.00k  14.02k  14.01k  13.78k       0  13.78k
# Query size     0  24.26k      97      99   98.98   97.36    0.41   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 = 1069 AND livestream_id = 7548\G

# Query 12: 744.74 QPS, 0.10x concurrency, ID 0x8F7679D452333ED3C7D60D22131CEFD4 at byte 123411338
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:46:45 to 2024-09-22T09:48:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         12   75963
# Exec time      2     10s    10us    19ms   134us   445us   429us    44us
# 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.39M      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 13: 399.49 QPS, 0.15x concurrency, ID 0xFBC5564AE716EAE82F20BFB45F6C37E7 at byte 130089938
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:47:19 to 2024-09-22T09:48:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   27165
# Exec time      2     10s    34us    25ms   374us     1ms   821us   125us
# Lock time      6    68ms       0     4ms     2us     1us    41us     1us
# Rows sent     13  26.53k       1       1       1       1       0       1
# Rows examine   0  26.53k       1       1       1       1       0       1
# Query size     1 847.89k      31      33   31.96   31.70    0.40   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 = 84\G

# Query 14: 174.51 QPS, 0.09x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 136720691
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:46:45 to 2024-09-22T09:48:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2   17800
# Exec time      2      9s    91us    22ms   527us     2ms   909us   214us
# Lock time      7    71ms       0     5ms     3us     1us    70us     1us
# Rows sent      4   8.71k       0       1    0.50    0.99    0.50    0.99
# Rows examine   0   8.71k       0       1    0.50    0.99    0.50    0.99
# Query size     3   2.31M     125     220  135.85  151.03   11.54  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='joma7v20t0p2oe2otl1ws82qketp3l0.t.isucon.pw'\G

# Query 15: 12.63 QPS, 0.06x concurrency, ID 0xD2A0864774622BA36F6557496405CF75 at byte 178657566
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:46:46 to 2024-09-22T09:48:26
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    1263
# Exec time      1      6s    59us    25ms     4ms     7ms     2ms     5ms
# Lock time      0     2ms       0   475us     1us     1us    13us     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.14k      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(1252, 1)\G

# Query 16: 100 QPS, 0.55x concurrency, ID 0xDFFCC1D78939C4D781C7C58349101F50 at byte 203286
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:46:46 to 2024-09-22T09:46:56
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    1000
# Exec time      1      6s     2ms    19ms     6ms     8ms     1ms     5ms
# Lock time      0     1ms       0    17us     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 (225, 'kana990', 'きんとききんとかんきんこうせ', '普段行政書士をしています。\nよろしくおねがいします！\n\n連絡は以下からお願いします。\n\nウェブサイト: http://kana99.example.com/\nメールアドレス: kana99@example.com\n', '$2a$04$rJ7mfRo9Aw9VW5mcgkH/se52xwvhBDUVDZEKjsrpxkUsp5ITv2zOS')\G

# Query 17: 1.21 QPS, 0.05x concurrency, ID 0xA3401CA3ABCC04C3AB221DB8AD5CBF26 at byte 182023464
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2024-09-22T09:46:59 to 2024-09-22T09:48:25
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     104
# Exec time      1      5s     5ms   107ms    45ms    91ms    30ms    48ms
# Lock time      0   142us       0    19us     1us     1us     2us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   1 889.59k   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0   9.95k      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 >= 1701460800 AND end_at <= 1701478800\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select  slot = slot - 1 from reservation_slots where  start_at >= 1701460800 AND end_at <= 1701478800\G

# Query 18: 1.22 QPS, 0.05x concurrency, ID 0x7F9C0C0BA9473953B723EE16C08655F1 at byte 118272287
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2024-09-22T09:46:59 to 2024-09-22T09:48:26
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     106
# Exec time      1      5s     5ms   101ms    43ms    87ms    29ms    44ms
# Lock time      0   126us       0    17us     1us     1us     1us     1us
# Rows sent      0     918       1      21    8.66   18.53    6.13    8.91
# Rows examine   1 906.69k   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0   9.94k      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 >= 1701230400 AND end_at <= 1701270000 FOR UPDATE\G

# Query 19: 881.88 QPS, 0.25x concurrency, ID 0xFD38427AE3D09E3883A680F7BAF95D3A at byte 12624071
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:47:02 to 2024-09-22T09:47:19
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2   14992
# Exec time      1      4s   263us     3ms   284us   287us    22us   273us
# Lock time      1    14ms       0    21us       0     1us       0     1us
# Rows sent      7  14.64k       1       1       1       1       0       1
# Rows examine  19  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 = 3678\G

# Query 20: 118.56 QPS, 0.05x concurrency, ID 0xEA1E6309EEEFF9A6831AD2FB940FC23C at byte 126379262
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:46:59 to 2024-09-22T09:48:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1   10433
# Exec time      1      4s    46us    40ms   391us     1ms   930us   125us
# Lock time      2    27ms       0     8ms     2us     1us    82us     1us
# Rows sent      5  10.19k       1       1       1       1       0       1
# Rows examine   0  10.19k       1       1       1       1       0       1
# Query size     0 354.62k      32      35   34.81   34.95    0.73   34.95
# 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
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM users WHERE id = 1025\G
sunakan commented 1 month ago 
SELECT image FROM icons WHERE user_id = 1036\G

が重いことから

image

次は iconsテーブルのuser_idにインデックス貼れば良さそう

alter table icons add index user_id_idx (user_id);
sunakan commented 1 month ago 
cat tmp/db-servers | xargs -I{} ssh {} "sudo mysql isupipe -e 'alter table icons add index user_id_idx (user_id);'"
image 
make clean-log

して計測する

sunakan commented 1 month ago

スコア：5705

image