Open saza-ku opened 1 year ago
+-------+--------+-----+-----+-----+-----+-----+-----+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+-----+-----+-----+-----+-----+-----+
+-------+--------+-----+-----+-----+-----+-----+-----+
Count: 34231 Time=0.00s (56s) Lock=0.00s (0s) Rows=0.1 (4340), isudns[isudns]@isucon1
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=N and name='S' and domain_id=N
Count: 24181 Time=0.00s (37s) Lock=0.00s (0s) Rows=0.5 (12227), isudns[isudns]@isucon1
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=N and type='S' and name='S'
Count: 42424 Time=0.00s (4s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@isucon1
select kind,content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='S'
Count: 536 Time=0.00s (1s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@isucon1
delete from records where domain_id=N and name='S' and type='S'
Count: 1072 Time=0.00s (1s) Lock=0.00s (0s) Rows=0.5 (536), isudns[isudns]@isucon1
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=N and type='S' and name='S' and domain_id=N
Count: 95506 Time=0.00s (1s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@isucon1
#
Count: 536 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@isucon1
commit
Count: 536 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@isucon1
insert into records (content,ttl,prio,type,domain_id,disabled,name,ordername,auth) values ('S',N,N,'S',N,N,'S',NULL,N)
Count: 536 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (536), isudns[isudns]@isucon1
select id,name,master,last_check,notified_serial,type,options,catalog,account from domains where name='S'
Count: 1072 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@isucon1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
Reading from STDIN ...
# 12.4s user time, 60ms system time, 36.75M rss, 51.06M vsz
# Current date: Sat Nov 25 04:28:00 2023
# Hostname: ip-192-168-0-12
# Files: STDIN
# Overall: 201.17k total, 20 unique, 2.42k QPS, 1.25x concurrency ________
# Time range: 2023-11-25T04:26:25 to 2023-11-25T04:27:48
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 104s 1us 11ms 515us 2ms 754us 89us
# Lock time 158ms 0 4ms 0 1us 22us 1us
# Rows sent 17.23k 0 1 0.09 0.99 0.28 0
# Rows examine 89.10M 0 1.77k 464.44 1.61k 699.34 0
# Query size 15.78M 5 223 82.27 151.03 48.82 112.70
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================= ============= ===== ====== ===== ====
# 1 0x42EF7D7D98FBCC9723BF896E... 56.3622 54.3% 34231 0.0016 0.00 SELECT records
# 2 0x3D83BC87F3B3A00D571FFC81... 37.2058 35.9% 24181 0.0015 0.00 SELECT records
# 3 0x22279D81D51006139E0C7640... 4.7179 4.5% 42424 0.0001 0.00 SELECT domains domainmetadata
# 4 0x859BBB7E9D760686137A9444... 2.0283 2.0% 536 0.0038 0.00 DELETE records
# MISC 0xMISC 3.4491 3.3% 99801 0.0000 0.0 <16 ITEMS>
# Query 1: 496.10 QPS, 0.82x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 20899088
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:26:25 to 2023-11-25T04:27:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 17 34231
# Exec time 54 56s 700us 10ms 2ms 2ms 434us 2ms
# Lock time 24 39ms 0 832us 1us 1us 4us 1us
# Rows sent 24 4.24k 0 1 0.13 0.99 0.33 0
# Rows examine 57 50.82M 1.25k 1.77k 1.52k 1.69k 144.50 1.46k
# Query size 29 4.64M 129 223 142.21 158.58 12.03 136.99
# String:
# Hosts isucon1
# Users isudns
# Query_time distribution
# 1us
# 10us
# 100us #
# 1ms ################################################################
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'records'\G
# SHOW CREATE TABLE `records`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and name='gw6bwo5ew2aqiegywqaa3uws7d0.u.isucon.dev' and domain_id=4\G
# EXPLAIN failed: DBD::mysql::st execute failed: Table 'isupipe.records' doesn't exist [for Statement "EXPLAIN SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and name='gw6bwo5ew2aqiegywqaa3uws7d0.u.isucon.dev' and domain_id=4"] at /usr/bin/pt-query-digest line 7796.
# Query 2: 350.45 QPS, 0.54x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 35001199
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:26:25 to 2023-11-25T04:27:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 12 24181
# Exec time 35 37s 653us 9ms 2ms 2ms 412us 1ms
# Lock time 16 27ms 0 127us 1us 1us 1us 1us
# Rows sent 69 11.94k 0 1 0.51 0.99 0.50 0.99
# Rows examine 40 35.91M 1.25k 1.77k 1.52k 1.69k 144.74 1.46k
# Query size 19 3.14M 126 222 136.07 151.03 11.44 124.25
# String:
# Hosts isucon1
# Users isudns
# Query_time distribution
# 1us
# 10us
# 100us #
# 1ms ################################################################
# 10ms
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'records'\G
# SHOW CREATE TABLE `records`\G
# EXPLAIN
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='e3x5ehasca0.u.isucon.dev'\G
# EXPLAIN failed: DBD::mysql::st execute failed: Table 'isupipe.records' doesn't exist [for Statement "EXPLAIN SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='e3x5ehasca0.u.isucon.dev'"] at /usr/bin/pt-query-digest line 7796.
# Query 3: 614.84 QPS, 0.07x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 41864447
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:26:25 to 2023-11-25T04:27:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 21 42424
# Exec time 4 5s 71us 4ms 111us 125us 83us 98us
# Lock time 35 56ms 0 307us 1us 1us 2us 1us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 29 4.61M 114 114 114 114 0 114
# String:
# Hosts isucon1
# Users isudns
# Query_time distribution
# 1us
# 10us ########################
# 100us ################################################################
# 1ms #
# 10ms
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'domains'\G
# SHOW CREATE TABLE `domains`\G
# SHOW TABLE STATUS LIKE 'domainmetadata'\G
# SHOW CREATE TABLE `domainmetadata`\G
# EXPLAIN
select kind,content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='u.isucon.dev'\G
# EXPLAIN failed: DBD::mysql::st execute failed: Table 'isupipe.domains' doesn't exist [for Statement "EXPLAIN select kind,content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='u.isucon.dev'"] at /usr/bin/pt-query-digest line 7796.
# Query 4: 8 QPS, 0.03x concurrency, ID 0x859BBB7E9D760686137A944406874C4D at byte 26385158
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:26:26 to 2023-11-25T04:27:33
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 536
# Exec time 1 2s 2ms 11ms 4ms 5ms 910us 4ms
# Lock time 21 34ms 0 4ms 64us 1us 436us 1us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 810.01k 1.25k 1.77k 1.51k 1.69k 150.99 1.46k
# Query size 0 44.82k 78 93 85.62 88.31 3.34 84.10
# String:
# Hosts isucon1
# Users isudns
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ################################################################
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'records'\G
# SHOW CREATE TABLE `records`\G
delete from records where domain_id=4 and name='suzukiharuka2.u.isucon.dev' and type='A'\G
# Converted for EXPLAIN
# EXPLAIN
select * from records where domain_id=4 and name='suzukiharuka2.u.isucon.dev' and type='A'\G
http://localhost:19992/#menu_services;after=1700886385950;before=1700886465957
go tool pprof -http=0.0.0.0:1080 http://isucon2/pprof/11250426
+-------+--------+-----+-----+-----+-----+-----+-----+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+-----+-----+-----+-----+-----+-----+
+-------+--------+-----+-----+-----+-----+-----+-----+
Count: 55818 Time=0.00s (46s) Lock=0.00s (0s) Rows=1.0 (55818), isucon[isucon]@isucon1
SELECT * FROM themes WHERE user_id = N
Count: 1178 Time=0.02s (21s) Lock=0.00s (0s) Rows=0.0 (2), isucon[isucon]@isucon1
SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = N AND livestream_id = N
Count: 65238 Time=0.00s (16s) Lock=0.00s (0s) Rows=0.9 (60078), isucon[isucon]@isucon1
SELECT icon_path FROM icons WHERE user_id = N
Count: 171889 Time=0.00s (14s) Lock=0.00s (0s) Rows=1.0 (171889), isucon[isucon]@isucon1
SELECT * FROM tags WHERE id = N
Count: 377 Time=0.03s (9s) Lock=0.00s (0s) Rows=0.0 (0), isucon[isucon]@isucon1
UPDATE reservation_slots SET slot = slot - N WHERE start_at >= N AND end_at <= N
Count: 378 Time=0.02s (7s) Lock=0.00s (0s) Rows=9.6 (3647), isucon[isucon]@isucon1
SELECT * FROM reservation_slots WHERE start_at >= N AND end_at <= N FOR UPDATE
Count: 55266 Time=0.00s (6s) Lock=0.00s (0s) Rows=1.0 (55266), isucon[isucon]@isucon1
SELECT * FROM users WHERE id = N
Count: 36612 Time=0.00s (5s) Lock=0.00s (0s) Rows=4.7 (171900), isucon[isucon]@isucon1
SELECT * FROM livestream_tags WHERE livestream_id = N
Count: 277 Time=0.02s (5s) Lock=0.00s (0s) Rows=0.0 (0), isucon[isucon]@isucon1
SELECT * FROM ng_words WHERE user_id = N AND livestream_id = N ORDER BY created_at DESC
Count: 206 Time=0.02s (4s) Lock=0.00s (0s) Rows=2.5 (514), isucon[isucon]@isucon1
SELECT * FROM livestreams WHERE user_id = N
Reading from STDIN ...
# 27.9s user time, 90ms system time, 37.75M rss, 52.07M vsz
# Current date: Sat Nov 25 04:28:18 2023
# Hostname: ip-192-168-0-13
# Files: STDIN
# Overall: 467.67k total, 63 unique, 5.57k QPS, 1.81x concurrency ________
# Time range: 2023-11-25T04:26:26 to 2023-11-25T04:27:50
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 152s 1us 66ms 324us 725us 2ms 80us
# Lock time 437ms 0 7ms 0 1us 18us 1us
# Rows sent 998.21k 0 7.66k 2.19 4.96 42.98 0.99
# Rows examine 117.55M 0 14.01k 263.57 1.33k 960.83 0.99
# Query size 17.35M 6 447 38.90 54.21 21.01 34.95
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================ ============= ====== ====== ===== ====
# 1 0x38BC86A45F31C6B1EE32467... 46.9710 30.9% 55818 0.0008 0.00 SELECT themes
# 2 0x64CC8A4E8E4B39020337559... 21.3098 14.0% 1178 0.0181 0.00 SELECT ng_words
# 3 0xB4FC1F99A422ACB1A54E6E2... 16.9837 11.2% 65238 0.0003 0.00 SELECT icons
# 4 0xFBC5564AE716EAE82F20BFB... 14.8088 9.8% 171889 0.0001 0.00 SELECT tags
# MISC 0xMISC 51.7696 34.1% 173545 0.0003 0.0 <59 ITEMS>
# Query 1: 820.85 QPS, 0.69x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 21343204
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:26:26 to 2023-11-25T04:27:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 11 55818
# Exec time 30 47s 236us 19ms 841us 2ms 811us 596us
# Lock time 11 49ms 0 1ms 0 1us 8us 1us
# Rows sent 5 54.51k 1 1 1 1 0 1
# Rows examine 57 68.00M 1000 1.50k 1.25k 1.46k 149.68 1.20k
# Query size 12 2.18M 38 41 40.95 40.45 0.42 40.45
# String:
# Hosts isucon1
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms ############
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'themes'\G
# SHOW CREATE TABLE `themes`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM themes WHERE user_id = 1062\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: themes
# partitions: NULL
# type: ALL
# possible_keys: NULL
# key: NULL
# key_len: NULL
# ref: NULL
# rows: 1536
# filtered: 10.00
# Extra: Using where
# Query 2: 17.32 QPS, 0.31x concurrency, ID 0x64CC8A4E8E4B390203375597CE4D611F at byte 8863395
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:26:26 to 2023-11-25T04:27:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 1178
# Exec time 14 21s 4ms 57ms 18ms 33ms 8ms 16ms
# Lock time 0 1ms 0 131us 0 1us 3us 1us
# Rows sent 0 2 0 1 0.00 0 0.04 0
# Rows examine 13 16.11M 14.00k 14.01k 14.00k 13.78k 0 13.78k
# Query size 0 113.88k 97 99 99.00 97.36 0.19 97.36
# String:
# Hosts isucon1
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ############
# 10ms ################################################################
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'ng_words'\G
# SHOW CREATE TABLE `ng_words`\G
# EXPLAIN
SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = 1017 AND livestream_id = 7535\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: ng_words
# partitions: NULL
# type: ALL
# possible_keys: NULL
# key: NULL
# key_len: NULL
# ref: NULL
# rows: 14613
# filtered: 1.00
# Extra: Using where
# Query 3: 959.38 QPS, 0.25x concurrency, ID 0xB4FC1F99A422ACB1A54E6E2F01B84879 at byte 105648496
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:26:26 to 2023-11-25T04:27:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 13 65238
# Exec time 11 17s 45us 14ms 260us 596us 403us 185us
# Lock time 15 66ms 0 4ms 1us 1us 20us 1us
# Rows sent 5 58.67k 0 1 0.92 0.99 0.27 0.99
# Rows examine 14 16.69M 0 529 268.20 487.09 147.09 258.32
# Query size 17 2.98M 45 48 47.93 46.83 0.19 46.83
# String:
# Hosts isucon1
# Users isucon
# Query_time distribution
# 1us
# 10us ######
# 100us ################################################################
# 1ms ##
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'icons'\G
# SHOW CREATE TABLE `icons`\G
# EXPLAIN
SELECT icon_path FROM icons WHERE user_id = 1120\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: icons
# partitions: NULL
# type: ALL
# possible_keys: NULL
# key: NULL
# key_len: NULL
# ref: NULL
# rows: 529
# filtered: 10.00
# Extra: Using where
# Query 4: 2.53k QPS, 0.22x concurrency, ID 0xFBC5564AE716EAE82F20BFB45F6C37E7 at byte 48163590
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:26:26 to 2023-11-25T04:27:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 36 171889
# Exec time 9 15s 41us 15ms 86us 93us 181us 66us
# Lock time 39 172ms 0 7ms 0 1us 20us 1us
# Rows sent 16 167.86k 1 1 1 1 0 1
# Rows examine 0 167.86k 1 1 1 1 0 1
# Query size 30 5.23M 31 33 31.93 31.70 0.46 31.70
# String:
# Hosts isucon1
# Users isucon
# Query_time distribution
# 1us
# 10us ################################################################
# 100us ##
# 1ms #
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'tags'\G
# SHOW CREATE TABLE `tags`\G
# EXPLAIN
SELECT * FROM tags WHERE id = 78\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: tags
# partitions: NULL
# type: const
# possible_keys: PRIMARY
# key: PRIMARY
# key_len: 8
# ref: const
# rows: 1
# filtered: 100.00
# Extra: NULL
http://localhost:19993/#menu_services;after=1700886385930;before=1700886465937
go tool pprof -http=0.0.0.0:1080 http://isucon3/pprof/11250426
19508
isucon1
alp
slow query
explain
netdata
http://localhost:19991/#menu_services;after=1700886385931;before=1700886462061
pprof