search

sunakan / isunarabe13-suburi

0 stars 0 forks source link

分析-003(5705→6476) #4

Open sunakan opened 19 hours ago

sunakan commented 19 hours ago

続き

image 
make download-files-for-analysis
make pt-query-digest
or
pt-query-digest tmp/analysis/latest/mysql-slow.log.*
# 25.9s user time, 140ms system time, 58.38M rss, 392.66G vsz
# Current date: Sun Sep 22 19:03:48 2024
# Hostname: CWPC20001.local
# 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: 664.94k total, 94 unique, 5.50k QPS, 2.76x concurrency ________
# Time range: 2024-09-22T09:58:34 to 2024-09-22T10:00:35
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           333s     1us   377ms   501us     2ms     2ms    76us
# Lock time          731ms       0    21ms     1us     1us    49us       0
# Rows sent        229.89k       0   7.32k    0.35    0.99   15.71       0
# Rows examine      76.29M       0  14.03k  120.31  964.41  605.15       0
# Query size        72.37M       5   1.94M  114.12  299.03   3.17k   31.70

# Profile
# Rank Query ID                            Response time Calls  R/Call V/M
# ==== =================================== ============= ====== ====== ===
#    1 0xDA556F9115773A1A99AA0165670CE848  57.7067 17.3% 169464 0.0003  0.01 ADMIN PREPARE
#    2 0xF1B8EF06D6CA63B24BFF433E06CCEB22  31.7198  9.5%   4175 0.0076  0.01 SELECT users livestreams livecomments
#    3 0xDB74D52D39A7090F224C4DEEAF3028C9  29.7237  8.9%   4180 0.0071  0.01 SELECT users livestreams reactions
#    4 0x38BC86A45F31C6B1EE324671506C898A  24.5692  7.4%  12520 0.0020  0.00 SELECT themes
#    5 0x4ADE2DC90689F1C4891749AF54FB8D14  17.7454  5.3%  36387 0.0005  0.00 DELETE SELECT livecomments
#    6 0x59F1B6DD8D9FEC059E55B3BFD624E8C3  16.9800  5.1%    942 0.0180  0.01 SELECT reservation_slots
#    7 0xFFFCA4D67EA0A788813031B8BBC3B329  16.5000  4.9%   3177 0.0052  0.00 COMMIT
#    8 0x22279D81D51006139E0C76405B54C261  13.9334  4.2%  29788 0.0005  0.00 SELECT domains domainmetadata
#    9 0x84B457C910C4A79FC9EBECB8B1065C66  13.6568  4.1%  15162 0.0009  0.00 SELECT icons
#   10 0x64CC8A4E8E4B390203375597CE4D611F  13.5933  4.1%    342 0.0397  0.01 SELECT ng_words
#   11 0x42EF7D7D98FBCC9723BF896EBFC51D24  11.6221  3.5%  23908 0.0005  0.00 SELECT records
#   12 0xFBC5564AE716EAE82F20BFB45F6C37E7  10.5770  3.2%  30434 0.0003  0.00 SELECT tags
#   13 0x8F7679D452333ED3C7D60D22131CEFD4   9.3037  2.8%  70183 0.0001  0.00 ADMIN RESET STMT
#   14 0x3D83BC87F3B3A00D571FFC8104A6E50C   8.2228  2.5%  16547 0.0005  0.00 SELECT records
#   15 0xD2A0864774622BA36F6557496405CF75   5.6137  1.7%   1337 0.0042  0.00 INSERT themes
#   16 0xDFFCC1D78939C4D781C7C58349101F50   5.5996  1.7%   1000 0.0056  0.00 INSERT users
#   17 0xEA1E6309EEEFF9A6831AD2FB940FC23C   4.4861  1.3%  12178 0.0004  0.00 SELECT users
#   18 0xFD38427AE3D09E3883A680F7BAF95D3A   4.2552  1.3%  14992 0.0003  0.00 SELECT livestreams livecomments
#   19 0xA3401CA3ABCC04C3AB221DB8AD5CBF26   3.9209  1.2%    106 0.0370  0.02 UPDATE reservation_slots
#   20 0x7F9C0C0BA9473953B723EE16C08655F1   3.9095  1.2%    108 0.0362  0.02 SELECT reservation_slots
# MISC 0xMISC                              29.8041  8.9% 218006 0.0001   0.0 <74 ITEMS>

# Query 1: 1.68k QPS, 0.57x concurrency, ID 0xDA556F9115773A1A99AA0165670CE848 at byte 45157370
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T09:58:54 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         25  169464
# Exec time     17     58s    21us   358ms   340us     1ms     1ms    84us
# Lock time      0    14us       0     9us       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.85M      30      30      30      30       0      30
# String:
# Databases    isupipe (167429/98%), isudns (2035/1%)
# Hosts        localhost
# Users        isucon (167429/98%), isudns (2035/1%)
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  #############################################
#   1ms  #########
#  10ms  #
# 100ms  #
#    1s
#  10s+
administrator command: Prepare\G

# Query 2: 47.99 QPS, 0.36x concurrency, ID 0xF1B8EF06D6CA63B24BFF433E06CCEB22 at byte 185159193
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T09:59:08 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    4175
# Exec time      9     32s     1ms    67ms     8ms    27ms     9ms     2ms
# Lock time      4    33ms       0     8ms     7us     1us   159us     1us
# Rows sent      1   4.08k       1       1       1       1       0       1
# Rows examine  11   8.51M   1.96k   2.44k   2.09k   2.38k  157.27   2.06k
# Query size     0 671.99k     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 = 153\G

# Query 3: 48.05 QPS, 0.34x concurrency, ID 0xDB74D52D39A7090F224C4DEEAF3028C9 at byte 182488055
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T09:59:08 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    4180
# Exec time      8     30s     1ms    63ms     7ms    26ms     9ms     2ms
# Lock time      3    28ms       0     9ms     6us     1us   151us     1us
# Rows sent      1   4.08k       1       1       1       1       0       1
# Rows examine  11   8.48M   1.96k   2.38k   2.08k   2.38k  143.87   2.06k
# Query size     0 591.16k     143     146  144.82  143.84    0.83  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 = 135\G

# Query 4: 143.91 QPS, 0.28x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 200999204
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:59:08 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1   12520
# Exec time      7     25s   241us    28ms     2ms     6ms     2ms   972us
# Lock time      3    27ms       0     3ms     2us     1us    35us     1us
# Rows sent      5  12.23k       1       1       1       1       0       1
# Rows examine  18  13.88M    1000   1.31k   1.14k   1.26k  103.92   1.09k
# Query size     0 499.34k      38      41   40.84   40.45    0.69   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 = 1017\G

# Query 5: 606.45 QPS, 0.30x concurrency, ID 0x4ADE2DC90689F1C4891749AF54FB8D14 at byte 195895459
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:59:35 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          5   36387
# Exec time      5     18s    50us    26ms   487us     2ms   956us   152us
# Lock time     12    93ms       0     5ms     2us     1us    46us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0  71.08k       2       4    2.00    1.96    0.02    1.96
# Query size    15  11.44M     266     523  329.59  363.48   25.92  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 = 737 AND
            livestream_id = 7538 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 = 737 AND
            livestream_id = 7538 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: 10.83 QPS, 0.20x concurrency, ID 0x59F1B6DD8D9FEC059E55B3BFD624E8C3 at byte 124627805
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T09:59:08 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     942
# Exec time      5     17s     2ms    59ms    18ms    38ms    11ms    15ms
# Lock time      0     6ms       0     4ms     6us     1us   137us     1us
# Rows sent      0     942       1       1       1       1       0       1
# Rows examine  10   7.87M   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0  79.11k      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 = 1701223200 AND end_at = 1701226800\G

# Query 7: 36.10 QPS, 0.19x concurrency, ID 0xFFFCA4D67EA0A788813031B8BBC3B329 at byte 123648384
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:59:07 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    3177
# Exec time      4     16s    23us    42ms     5ms    14ms     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  18.62k       6       6       6       6       0       6
# String:
# Databases    isupipe (2839/89%), isudns (338/10%)
# Hosts        localhost
# Users        isucon (2839/89%), isudns (338/10%)
# Query_time distribution
#   1us
#  10us  #######################
# 100us  #############
#   1ms  ################################################################
#  10ms  #################
# 100ms
#    1s
#  10s+
COMMIT\G

# Query 8: 294.93 QPS, 0.14x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 155421398
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:58:54 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   29788
# Exec time      4     14s    90us    23ms   467us     2ms   830us   185us
# Lock time     13    98ms       0     3ms     3us     1us    39us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     4   3.21M     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 9: 174.28 QPS, 0.16x concurrency, ID 0x84B457C910C4A79FC9EBECB8B1065C66 at byte 179652812
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:59:08 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2   15162
# Exec time      4     14s    59us    49ms   900us     3ms     2ms   384us
# Lock time      5    42ms       0     4ms     2us     1us    56us     1us
# Rows sent      5  11.85k       0       1    0.80    0.99    0.40    0.99
# Rows examine   0  11.85k       0       1    0.80    0.99    0.40    0.99
# Query size     0 648.90k      41      44   43.82   42.48    0.26   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 = 1062\G

# Query 10: 4.44 QPS, 0.18x concurrency, ID 0x64CC8A4E8E4B390203375597CE4D611F at byte 55063042
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-22T09:59:18 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     342
# Exec time      4     14s     4ms   105ms    40ms    75ms    20ms    38ms
# Lock time      0     1ms       0   340us     2us     1us    25us     1us
# Rows sent      0     101       0       2    0.30    0.99    0.52       0
# Rows examine   6   4.68M  14.00k  14.02k  14.01k  13.78k       0  13.78k
# Query size     0  33.06k      97      99   98.99   97.36    0.35   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 = 1024 AND livestream_id = 7526\G

# Query 11: 236.71 QPS, 0.12x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 73816666
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:58:54 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3   23908
# Exec time      3     12s    92us    35ms   486us     2ms   835us   194us
# Lock time      8    63ms       0     3ms     2us     1us    40us     1us
# Rows sent      0   2.12k       0       1    0.09    0.99    0.29       0
# Rows examine   0   2.12k       0       1    0.09    0.99    0.29       0
# Query size     4   3.23M     128     219  141.72  151.03   12.01  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='nkato0.t.isucon.pw' and domain_id=1\G

# Query 12: 447.56 QPS, 0.16x concurrency, ID 0xFBC5564AE716EAE82F20BFB45F6C37E7 at byte 192986942
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:59:27 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   30434
# Exec time      3     11s    32us    20ms   347us     1ms   689us   119us
# Lock time     11    83ms       0     7ms     2us     1us    54us     1us
# Rows sent     12  29.72k       1       1       1       1       0       1
# Rows examine   0  29.72k       1       1       1       1       0       1
# Query size     1 949.21k      31      33   31.94   31.70    0.44   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 = 90\G

# Query 13: 694.88 QPS, 0.09x concurrency, ID 0x8F7679D452333ED3C7D60D22131CEFD4 at byte 202749278
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:58:54 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         10   70183
# Exec time      2      9s     9us    21ms   132us   445us   434us    42us
# 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.21M      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 14: 163.83 QPS, 0.08x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 48107900
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:58:54 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2   16547
# Exec time      2      8s    88us    28ms   496us     2ms   887us   204us
# Lock time      8    60ms       0     5ms     3us     1us    62us     1us
# Rows sent      3   8.13k       0       1    0.50    0.99    0.50    0.99
# Rows examine   0   8.13k       0       1    0.50    0.99    0.50    0.99
# Query size     2   2.14M     125     218  135.60  151.03   11.51  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='t.isucon.pw'\G

# Query 15: 13.24 QPS, 0.06x concurrency, ID 0xD2A0864774622BA36F6557496405CF75 at byte 79322935
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:58:54 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    1337
# Exec time      1      6s    60us    14ms     4ms     7ms     2ms     5ms
# Lock time      0     2ms       0   159us     1us     1us     5us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     0  75.12k      55      60   57.53   56.92    1.18   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(1108, 1)\G

# Query 16: 90.91 QPS, 0.51x concurrency, ID 0xDFFCC1D78939C4D781C7C58349101F50 at byte 571326
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:58:54 to 2024-09-22T09:59:05
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    1000
# Exec time      1      6s     2ms    14ms     6ms     8ms     1ms     5ms
# Lock time      0     1ms       0    11us     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 (646, 'hiroshiendo0', '感慨', '普段営業をしています。\nよろしくおねがいします！\n\n連絡は以下からお願いします。\n\nウェブサイト: http://hiroshiendo.example.com/\nメールアドレス: hiroshiendo@example.com\n', '$2a$04$UwZb6IFm4tgS1pLvujdaV.sK3Gq9zN2IJyIuGjqtMyuSL/dZXfqjK')\G

# Query 17: 139.98 QPS, 0.05x concurrency, ID 0xEA1E6309EEEFF9A6831AD2FB940FC23C at byte 78505493
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-22T09:59:08 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1   12178
# Exec time      1      4s    44us    24ms   368us     1ms   750us   125us
# Lock time      3    26ms       0     3ms     2us     1us    34us     1us
# Rows sent      5  11.89k       1       1       1       1       0       1
# Rows examine   0  11.89k       1       1       1       1       0       1
# Query size     0 414.30k      32      35   34.84   34.95    0.67   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 = 1044\G

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

# Query 19: 1.22 QPS, 0.05x concurrency, ID 0xA3401CA3ABCC04C3AB221DB8AD5CBF26 at byte 75549659
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2024-09-22T09:59:08 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     106
# Exec time      1      4s     5ms    95ms    37ms    78ms    26ms    33ms
# Lock time      0   393us       0   292us     3us     1us    27us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   1 906.69k   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0  10.14k      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 >= 1701032400 AND end_at <= 1701100800\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select  slot = slot - 1 from reservation_slots where  start_at >= 1701032400 AND end_at <= 1701100800\G

# Query 20: 1.24 QPS, 0.04x concurrency, ID 0x7F9C0C0BA9473953B723EE16C08655F1 at byte 89334123
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2024-09-22T09:59:08 to 2024-09-22T10:00:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     108
# Exec time      1      4s     5ms   100ms    36ms    75ms    24ms    34ms
# Lock time      0   153us       0    41us     1us     1us     3us     1us
# Rows sent      0     953       1      21    8.82   18.53    6.19    8.91
# Rows examine   1 923.80k   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0  10.12k      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 >= 1701050400 AND end_at <= 1701097200 FOR UPDATE\G
sunakan commented 19 hours ago

次はADMIN PREPARE

# Profile
# Rank Query ID                            Response time Calls  R/Call V/M
# ==== =================================== ============= ====== ====== ===
#    1 0xDA556F9115773A1A99AA0165670CE848  57.7067 17.3% 169464 0.0003  0.01 ADMIN PREPARE
#    2 0xF1B8EF06D6CA63B24BFF433E06CCEB22  31.7198  9.5%   4175 0.0076  0.01 SELECT users livestreams livecomments
#    3 0xDB74D52D39A7090F224C4DEEAF3028C9  29.7237  8.9%   4180 0.0071  0.01 SELECT users livestreams reactions
sunakan commented 18 hours ago 
ssh isu-1

vim webapp/go/main.go
func connectDB(logger echo.Logger) (*sqlx.DB, error) {
        // ...
        db, err := sqlx.Open("mysql", conf.FormatDSN())
        // ...
        return db, nil
}

から conf.FormatDSN() をちょっと見る

自作じゃなかった

func connectDB(logger echo.Logger) (*sqlx.DB, error) {
        // ...
        conf := mysql.NewConfig()
        // ...
        conf.Net = "tcp"
        conf.Addr = net.JoinHostPort("127.0.0.1", "3306")
        conf.User = "isucon"
        conf.Passwd = "isucon"
        conf.DBName = "isupipe"
        conf.ParseTime = true
        // ...
}
sunakan commented 18 hours ago

AIにきいてみた

user:password@tcp(localhost:3306)/testdb?parseTime=true&collation=utf8mb4_general_ci

例として↑のような文字列が返ってくるらしい

sunakan commented 18 hours ago 
user:password@tcp(localhost:3306)/testdb?parseTime=true&collation=utf8mb4_general_ci&interpolateParams=true

みたいに interpolateParams=true が付けば良い

sunakan commented 18 hours ago 
        conf.Net = "tcp"
        conf.Addr = net.JoinHostPort("127.0.0.1", "3306")
        conf.User = "isucon"
        conf.Passwd = "isucon"
        conf.DBName = "isupipe"
        conf.ParseTime = true
+       conf.InterpolateParams = true

これで良さそう

sunakan commented 18 hours ago 
make true-interpolate-params
make clean-log

して計測

sunakan commented 18 hours ago

スコア：6476

image