takeokunn
commented
9 months ago pt-query-digest:
# 24.2s user time, 60ms system time, 38.50M rss, 44.97M vsz
# Current date: Sat Nov 25 07:29:04 2023
# Hostname: ip-192-168-0-12
# Files: /var/log/mysql/mysql-slow.log
# Overall: 340.08k total, 128 unique, 407.28 QPS, 0.64x concurrency ______
# Time range: 2023-11-25T07:13:12 to 2023-11-25T07:27:07
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 532s 1us 528ms 2ms 7ms 6ms 131us
# Lock time 1s 0 82ms 3us 1us 252us 1us
# Rows sent 475.37k 0 7.41k 1.43 0.99 48.89 0
# Rows examine 103.05M 0 14.02k 317.73 1.39k 1.32k 0
# Query size 45.93M 5 1.94M 141.61 329.68 5.01k 97.36
# Profile
# Rank Query ID Response time Calls R/Call V/M Ite
# ==== ============================= ============== ===== ====== ===== ===
# 1 0x42EF7D7D98FBCC9723BF896E... 145.8292 27.4% 14135 0.0103 0.02 SELECT records
# 2 0x64CC8A4E8E4B390203375597... 76.1854 14.3% 1884 0.0404 0.01 SELECT ng_words
# 3 0x3D83BC87F3B3A00D571FFC81... 67.8637 12.7% 10859 0.0062 0.01 SELECT records
# 4 0x59F1B6DD8D9FEC059E55B3BF... 32.4448 6.1% 1750 0.0185 0.01 SELECT reservation_slots
# 5 0x4ADE2DC90689F1C4891749AF... 25.9009 4.9% 49957 0.0005 0.00 DELETE SELECT livecomments
# 6 0x38BC86A45F31C6B1EE324671... 22.0893 4.1% 12944 0.0017 0.00 SELECT themes
# 7 0x22279D81D51006139E0C7640... 13.8428 2.6% 16398 0.0008 0.01 SELECT domains domainmetadata
# 8 0x8F7679D452333ED3C7D60D22... 13.2352 2.5% 44648 0.0003 0.01 ADMIN RESET STMT
# 9 0x9EAD6C0CE525E3693EE27FFC... 12.2273 2.3% 297 0.0412 0.01 SELECT livestreams
# 10 0xFD38427AE3D09E3883A680F7... 12.1552 2.3% 44913 0.0003 0.00 SELECT livestreams livecomments
# 11 0x9AC623FA477E73A44D191D29... 12.0815 2.3% 1110 0.0109 0.02 SELECT records
# 12 0xC499D81D570D361DB61FC43A... 11.2436 2.1% 44916 0.0003 0.00 SELECT livestreams reactions
# 13 0xFFFCA4D67EA0A788813031B8... 10.6679 2.0% 11036 0.0010 0.01 COMMIT
# 14 0x859BBB7E9D760686137A9444... 9.5227 1.8% 555 0.0172 0.01 DELETE records
# 15 0xA3401CA3ABCC04C3AB221DB8... 9.5144 1.8% 202 0.0471 0.03 UPDATE reservation_slots
# 16 0x7F9C0C0BA9473953B723EE16... 7.1958 1.4% 205 0.0351 0.02 SELECT reservation_slots
# 17 0x5A8A79FB9E5D1635CA10FEA9... 7.1537 1.3% 3938 0.0018 0.00 SELECT themes
# 18 0xBB9B3D66E02B513839482132... 6.8648 1.3% 8219 0.0008 0.01 SELECT tags livestream_tags
# 19 0xEA1E6309EEEFF9A6831AD2FB... 4.4655 0.8% 12361 0.0004 0.00 SELECT users
# 20 0xD6032FE08E1FE706A928B8B7... 3.8377 0.7% 13282 0.0003 0.00 SELECT livestreams
# MISC 0xMISC 28.1694 5.3% 46470 0.0006 0.0 <108 ITEMS>
# Query 1: 20.85 QPS, 0.22x 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:25:06
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 4 14135
# Exec time 27 146s 110us 173ms 10ms 38ms 14ms 5ms
# Lock time 2 38ms 0 3ms 2us 1us 52us 1us
# Rows sent 0 3.92k 0 1 0.28 0.99 0.45 0
# Rows examine 18 19.32M 1 1.53k 1.40k 1.46k 79.11 1.39k
# Query size 4 1.91M 128 221 141.54 158.58 11.61 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.83 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:25:06
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 1884
# Exec time 14 76s 4ms 148ms 40ms 82ms 22ms 36ms
# Lock time 0 3ms 0 671us 1us 1us 15us 1us
# Rows sent 0 262 0 2 0.14 0.99 0.35 0
# Rows examine 25 25.77M 14.00k 14.02k 14.01k 13.78k 0 13.78k
# Query size 0 182.14k 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: 15.47 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:25:06
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 10859
# Exec time 12 68s 112us 121ms 6ms 21ms 9ms 3ms
# Lock time 2 33ms 0 9ms 3us 1us 88us 1us
# Rows sent 1 5.53k 0 1 0.52 0.99 0.50 0.99
# Rows examine 14 14.83M 0 1.53k 1.40k 1.46k 82.77 1.39k
# Query size 3 1.39M 126 219 134.09 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.62 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:25:06
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 1750
# Exec time 6 32s 2ms 138ms 19ms 51ms 16ms 13ms
# Lock time 0 5ms 0 2ms 2us 1us 48us 1us
# Rows sent 0 1.71k 1 1 1 1 0 1
# Rows examine 14 14.62M 8.55k 8.55k 8.55k 8.55k 0 8.55k
# Query size 0 146.97k 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: 75.92 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:25:06
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 14 49957
# Exec time 4 26s 88us 32ms 518us 2ms 1ms 152us
# Lock time 11 145ms 0 13ms 2us 1us 91us 1us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 97.59k 2 4 2.00 1.96 0.03 1.96
# Query size 34 15.79M 263 532 331.52 381.65 28.23 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.35 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:25:06
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 12944
# Exec time 4 22s 265us 59ms 2ms 7ms 3ms 568us
# Lock time 2 30ms 0 4ms 2us 1us 48us 1us
# Rows sent 2 12.64k 1 1 1 1 0 1
# Rows examine 13 14.08M 1000 1.26k 1.11k 1.20k 75.30 1.09k
# Query size 1 518.18k 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: 24.44 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:25:06
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 4 16398
# Exec time 2 14s 84us 88ms 844us 3ms 3ms 159us
# Lock time 9 118ms 0 24ms 7us 1us 245us 1us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 3 1.78M 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: 63.60 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:25:06
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 13 44648
# Exec time 2 13s 8us 69ms 296us 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 1.41M 33 33 33 33 0 33
# String:
# Databases isudns
# Hosts localhost
# Users isudns
# Query_time distribution
# 1us #
# 10us ################################################################
# 100us #######
# 1ms ###
alp: