takeokunn
commented
7 months ago pt-query-digest:
# 54.4s user time, 100ms system time, 42.38M rss, 48.84M vsz
# Current date: Sat Nov 25 07:56:22 2023
# Hostname: ip-192-168-0-12
# Files: /var/log/mysql/mysql-slow.log
# Overall: 739.15k total, 129 unique, 380.22 QPS, 0.54x concurrency ______
# Time range: 2023-11-25T07:13:12 to 2023-11-25T07:45:36
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 1055s 1us 528ms 1ms 6ms 6ms 131us
# Lock time 3s 0 82ms 3us 1us 237us 1us
# Rows sent 1021.69k 0 7.41k 1.42 0.99 49.42 0
# Rows examine 214.61M 0 14.02k 304.45 1.39k 1.28k 0
# Query size 102.31M 5 1.94M 145.14 329.68 5.37k 97.36
# Profile
# Rank Query ID Response time Calls R/Call V/M Ite
# ==== ============================ ============== ====== ====== ===== ===
# 1 0x42EF7D7D98FBCC9723BF896... 260.8250 24.7% 30837 0.0085 0.02 SELECT records
# 2 0x64CC8A4E8E4B39020337559... 156.0057 14.8% 3837 0.0407 0.01 SELECT ng_words
# 3 0x3D83BC87F3B3A00D571FFC8... 146.3092 13.9% 23610 0.0062 0.01 SELECT records
# 4 0x59F1B6DD8D9FEC059E55B3B... 68.1754 6.5% 3584 0.0190 0.01 SELECT reservation_slots
# 5 0x4ADE2DC90689F1C4891749A... 53.7031 5.1% 106283 0.0005 0.00 DELETE SELECT livecomments
# 6 0x38BC86A45F31C6B1EE32467... 44.7509 4.2% 26426 0.0017 0.00 SELECT themes
# 7 0x22279D81D51006139E0C764... 30.1777 2.9% 35910 0.0008 0.01 SELECT domains domainmetadata
# 8 0x8F7679D452333ED3C7D60D2... 28.2752 2.7% 96345 0.0003 0.01 ADMIN RESET STMT
# 9 0xFD38427AE3D09E3883A680F... 26.0707 2.5% 105196 0.0002 0.00 SELECT livestreams livecomments
# 10 0xC499D81D570D361DB61FC43... 24.6258 2.3% 105205 0.0002 0.00 SELECT livestreams reactions
# 11 0x9EAD6C0CE525E3693EE27FF... 23.4393 2.2% 558 0.0420 0.01 SELECT livestreams
# 12 0xFFFCA4D67EA0A788813031B... 21.5233 2.0% 22509 0.0010 0.01 COMMIT
# 13 0x859BBB7E9D760686137A944... 19.1461 1.8% 1129 0.0170 0.01 DELETE records
# 14 0x9AC623FA477E73A44D191D2... 18.3284 1.7% 2258 0.0081 0.02 SELECT records
# 15 0xA3401CA3ABCC04C3AB221DB... 18.3064 1.7% 432 0.0424 0.03 UPDATE reservation_slots
# 16 0x7F9C0C0BA9473953B723EE1... 14.5643 1.4% 437 0.0333 0.02 SELECT reservation_slots
# 17 0x5A8A79FB9E5D1635CA10FEA... 14.2574 1.4% 8037 0.0018 0.00 SELECT themes
# 18 0xBB9B3D66E02B51383948213... 13.3149 1.3% 16811 0.0008 0.01 SELECT tags livestream_tags
# 19 0xEA1E6309EEEFF9A6831AD2F... 8.9548 0.8% 25235 0.0004 0.00 SELECT users
# 20 0xD6032FE08E1FE706A928B8B... 7.9457 0.8% 27831 0.0003 0.00 SELECT livestreams
# MISC 0xMISC 56.5145 5.4% 96680 0.0006 0.0 <109 ITEMS>
# Query 1: 16.16 QPS, 0.14x 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:45:36
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 4 30837
# Exec time 24 261s 110us 173ms 8ms 31ms 11ms 4ms
# Lock time 5 158ms 0 41ms 5us 1us 246us 1us
# Rows sent 0 8.42k 0 1 0.28 0.99 0.45 0
# Rows examine 19 42.09M 1 1.53k 1.40k 1.46k 78.75 1.39k
# Query size 4 4.17M 128 221 141.64 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='hd0iq2zs9ibqu40.u.isucon.dev' and domain_id=13\G
# Query 2: 2.02 QPS, 0.08x 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:45:36
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 3837
# Exec time 14 156s 112us 148ms 41ms 82ms 22ms 36ms
# Lock time 0 10ms 0 5ms 2us 1us 72us 1us
# Rows sent 0 614 0 2 0.16 0.99 0.39 0
# Rows examine 24 52.35M 0 14.02k 13.97k 13.78k 719.63 13.78k
# Query size 0 370.94k 97 99 98.99 97.36 0.24 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: 12.22 QPS, 0.08x 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:45:36
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 23610
# Exec time 13 146s 112us 121ms 6ms 20ms 8ms 3ms
# Lock time 4 111ms 0 35ms 4us 1us 239us 1us
# Rows sent 1 11.98k 0 1 0.52 0.99 0.50 0.99
# Rows examine 15 32.20M 0 1.53k 1.40k 1.46k 80.83 1.39k
# Query size 2 3.02M 126 219 134.19 151.03 10.43 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: 1.89 QPS, 0.04x 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:45:32
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 3584
# Exec time 6 68s 2ms 138ms 19ms 53ms 17ms 14ms
# Lock time 0 7ms 0 2ms 1us 1us 33us 1us
# Rows sent 0 3.50k 1 1 1 1 0 1
# Rows examine 13 29.94M 8.55k 8.55k 8.55k 8.55k 0 8.55k
# Query size 0 301.00k 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: 56.29 QPS, 0.03x concurrency, ID 0x4ADE2DC90689F1C4891749AF54FB8D14 at byte 197649845
# 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:45:36
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 14 106283
# Exec time 5 54s 88us 46ms 505us 2ms 1ms 152us
# Lock time 12 323ms 0 22ms 3us 1us 105us 1us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 207.62k 2 4 2.00 1.96 0.03 1.96
# Query size 32 33.64M 263 532 331.87 381.65 28.04 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 = 1 AND
livestream_id = 7523 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 = 1 AND
livestream_id = 7523 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: 13.92 QPS, 0.02x 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:45:36
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 26426
# Exec time 4 45s 264us 59ms 2ms 7ms 3ms 568us
# Lock time 2 74ms 0 4ms 2us 1us 59us 1us
# Rows sent 2 25.81k 1 1 1 1 0 1
# Rows examine 13 28.82M 1000 1.26k 1.12k 1.20k 78.05 1.09k
# Query size 1 1.03M 38 41 40.99 40.45 0.15 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: 18.89 QPS, 0.02x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 218559444
# 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:45:36
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 4 35910
# Exec time 2 30s 83us 94ms 840us 3ms 3ms 159us
# Lock time 9 253ms 0 33ms 7us 1us 270us 1us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 3 3.90M 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: 49.87 QPS, 0.01x concurrency, ID 0x8F7679D452333ED3C7D60D22131CEFD4 at byte 237798612
# 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:45:36
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 13 96345
# Exec time 2 28s 7us 79ms 293us 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 2 3.03M 33 33 33 33 0 33
# String:
# Databases isudns
# Hosts localhost
# Users isudns
# Query_time distribution
# 1us #
# 10us ################################################################
# 100us #######
# 1ms ###
alp: