moririn2528
commented
1 year ago 45424
Open saza-ku opened 1 year ago
moririn2528
commented
1 year ago 45424
saza-ku
commented
1 year ago +-------+--------+-----+-----+-----+-----+-----+-----+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+-----+-----+-----+-----+-----+-----+
+-------+--------+-----+-----+-----+-----+-----+-----+
Count: 33214 Time=0.00s (56s) Lock=0.00s (0s) Rows=0.2 (5312), 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: 23849 Time=0.00s (37s) Lock=0.00s (0s) Rows=0.5 (12092), 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: 40660 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: 599 Time=0.00s (2s) 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: 1198 Time=0.00s (2s) Lock=0.00s (0s) Rows=0.5 (599), 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: 93160 Time=0.00s (1s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@isucon1
#
Count: 599 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@isucon1
commit
Count: 599 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: 599 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (599), isudns[isudns]@isucon1
select id,name,master,last_check,notified_serial,type,options,catalog,account from domains where name='S'
Count: 1199 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 ...
# 12s user time, 20ms system time, 36.75M rss, 51.07M vsz
# Current date: Sat Nov 25 06:36:00 2023
# Hostname: ip-192-168-0-12
# Files: STDIN
# Overall: 196.28k total, 20 unique, 2.36k QPS, 1.26x concurrency ________
# Time range: 2023-11-25T06:34:26 to 2023-11-25T06:35:49
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 105s 1us 16ms 533us 2ms 787us 89us
# Lock time 170ms 0 5ms 0 1us 26us 1us
# Rows sent 18.17k 0 1 0.09 0.99 0.29 0
# Rows examine 88.90M 0 1.83k 474.93 1.69k 714.25 0
# Query size 15.36M 5 218 82.08 151.03 48.82 112.70
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================= ============= ===== ====== ===== ====
# 1 0x42EF7D7D98FBCC9723BF896E... 56.2307 53.7% 33214 0.0017 0.00 SELECT records
# 2 0x3D83BC87F3B3A00D571FFC81... 37.8185 36.1% 23849 0.0016 0.00 SELECT records
# 3 0x22279D81D51006139E0C7640... 4.6144 4.4% 40660 0.0001 0.00 SELECT domains domainmetadata
# 4 0x859BBB7E9D760686137A9444... 2.3387 2.2% 599 0.0039 0.00 DELETE records
# MISC 0xMISC 3.7525 3.6% 97960 0.0000 0.0 <16 ITEMS>
# Query 1: 488.44 QPS, 0.83x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 5899739
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:34:26 to 2023-11-25T06:35:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 16 33214
# Exec time 53 56s 703us 16ms 2ms 3ms 494us 2ms
# Lock time 24 41ms 0 2ms 1us 1us 11us 1us
# Rows sent 28 5.19k 0 1 0.16 0.99 0.36 0
# Rows examine 56 50.15M 1.25k 1.83k 1.55k 1.78k 164.56 1.53k
# Query size 29 4.50M 129 218 141.94 158.58 12.02 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='4oiu1bsvsvo8sssg0.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='4oiu1bsvsvo8sssg0.u.isucon.dev' and domain_id=4"] at /usr/bin/pt-query-digest line 7796.
# Query 2: 350.72 QPS, 0.56x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 39986669
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:34:26 to 2023-11-25T06:35:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 12 23849
# Exec time 36 38s 667us 10ms 2ms 2ms 453us 2ms
# Lock time 16 29ms 0 1ms 1us 1us 9us 1us
# Rows sent 64 11.81k 0 1 0.51 0.99 0.50 0.99
# Rows examine 40 36.04M 1.25k 1.83k 1.55k 1.78k 164.60 1.53k
# Query size 20 3.09M 126 217 135.71 151.03 11.33 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='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='u.isucon.dev'"] at /usr/bin/pt-query-digest line 7796.
# Query 3: 597.94 QPS, 0.07x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 33433781
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:34:26 to 2023-11-25T06:35:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 20 40660
# Exec time 4 5s 69us 3ms 113us 131us 96us 103us
# Lock time 34 59ms 0 2ms 1us 1us 11us 1us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 28 4.42M 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.81 QPS, 0.03x concurrency, ID 0x859BBB7E9D760686137A944406874C4D at byte 52091481
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:34:26 to 2023-11-25T06:35:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 599
# Exec time 2 2s 2ms 11ms 4ms 5ms 935us 4ms
# Lock time 22 38ms 0 5ms 64us 1us 447us 1us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 1 923.64k 1.25k 1.83k 1.54k 1.78k 170.85 1.53k
# Query size 0 50.00k 78 93 85.48 88.31 3.35 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='kenichinakagawa0.u.isucon.dev' and type='A'\G
# Converted for EXPLAIN
# EXPLAIN
select * from records where domain_id=4 and name='kenichinakagawa0.u.isucon.dev' and type='A'\G
http://localhost:19992/#menu_services;after=1700894066580;before=1700894146589
go tool pprof -http=0.0.0.0:1080 http://isucon2/pprof/11250634+-------+--------+-----+-----+-----+-----+-----+-----+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+-----+-----+-----+-----+-----+-----+
+-------+--------+-----+-----+-----+-----+-----+-----+
Count: 37380 Time=0.00s (30s) Lock=0.00s (0s) Rows=1.0 (37380), isucon[isucon]@isucon1
SELECT * FROM themes WHERE user_id = N
Count: 59800 Time=0.00s (15s) Lock=0.00s (0s) Rows=0.9 (51808), isucon[isucon]@isucon1
SELECT icon_path FROM icons WHERE user_id = N
Count: 141907 Time=0.00s (13s) Lock=0.00s (0s) Rows=1.0 (141907), isucon[isucon]@isucon1
SELECT * FROM tags WHERE id = N
Count: 466 Time=0.02s (8s) 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: 467 Time=0.02s (7s) Lock=0.00s (0s) Rows=9.8 (4591), isucon[isucon]@isucon1
SELECT * FROM reservation_slots WHERE start_at >= N AND end_at <= N FOR UPDATE
Count: 31599 Time=0.00s (5s) Lock=0.00s (0s) Rows=4.5 (141912), isucon[isucon]@isucon1
SELECT * FROM livestream_tags WHERE livestream_id = N
Count: 2426 Time=0.00s (5s) Lock=0.00s (0s) Rows=9.5 (23096), isucon[isucon]@isucon1
SELECT * FROM livecomments WHERE livestream_id = N ORDER BY created_at DESC
Count: 2462 Time=0.00s (4s) Lock=0.00s (0s) Rows=9.2 (22625), isucon[isucon]@isucon1
SELECT * FROM reactions WHERE livestream_id = N ORDER BY created_at DESC
Count: 36754 Time=0.00s (4s) Lock=0.00s (0s) Rows=1.0 (36754), isucon[isucon]@isucon1
SELECT * FROM users WHERE id = N
Count: 214 Time=0.02s (3s) Lock=0.00s (0s) Rows=2.9 (627), isucon[isucon]@isucon1
SELECT * FROM livestreams WHERE user_id = N
Reading from STDIN ...
# 27.9s user time, 110ms system time, 37.88M rss, 52.29M vsz
# Current date: Sat Nov 25 06:36:18 2023
# Hostname: ip-192-168-0-13
# Files: STDIN
# Overall: 460.56k total, 66 unique, 5.42k QPS, 1.44x concurrency ________
# Time range: 2023-11-25T06:34:26 to 2023-11-25T06:35:51
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 122s 1us 58ms 265us 761us 1ms 80us
# Lock time 390ms 0 3ms 0 1us 14us 1us
# Rows sent 1.19M 0 7.77k 2.71 4.96 66.17 0.99
# Rows examine 93.68M 0 13.02k 213.29 1.26k 622.45 0.99
# Query size 17.01M 6 453 38.72 59.77 25.61 34.95
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================ ============= ====== ====== ===== ====
# 1 0x38BC86A45F31C6B1EE32467... 30.8056 25.2% 37380 0.0008 0.00 SELECT themes
# 2 0xB4FC1F99A422ACB1A54E6E2... 16.0567 13.2% 59800 0.0003 0.00 SELECT icons
# 3 0xFBC5564AE716EAE82F20BFB... 13.2805 10.9% 141907 0.0001 0.00 SELECT tags
# 4 0xA3401CA3ABCC04C3AB221DB... 8.5414 7.0% 466 0.0183 0.00 UPDATE reservation_slots
# MISC 0xMISC 53.4126 43.7% 221006 0.0002 0.0 <62 ITEMS>
# Query 1: 549.71 QPS, 0.45x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 97927168
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:34:26 to 2023-11-25T06:35:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 8 37380
# Exec time 25 31s 235us 27ms 824us 2ms 718us 596us
# Lock time 8 34ms 0 861us 0 1us 9us 1us
# Rows sent 2 36.50k 1 1 1 1 0 1
# Rows examine 49 46.17M 1000 1.56k 1.26k 1.46k 171.82 1.26k
# Query size 8 1.46M 38 41 40.87 40.45 0.62 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 = 894\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: 1598
# filtered: 10.00
# Extra: Using where
# Query 2: 879.41 QPS, 0.24x concurrency, ID 0xB4FC1F99A422ACB1A54E6E2F01B84879 at byte 105749495
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:34:26 to 2023-11-25T06:35:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 12 59800
# Exec time 13 16s 45us 11ms 268us 626us 358us 204us
# Lock time 15 59ms 0 3ms 0 1us 17us 1us
# Rows sent 4 50.59k 0 1 0.87 0.99 0.34 0.99
# Rows examine 18 16.87M 0 590 295.85 563.87 166.73 284.79
# Query size 16 2.73M 45 48 47.87 46.83 0.26 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 = 1025\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: 590
# filtered: 10.00
# Extra: Using where
# Query 3: 2.12k QPS, 0.20x concurrency, ID 0xFBC5564AE716EAE82F20BFB45F6C37E7 at byte 30796473
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:34:27 to 2023-11-25T06:35:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 30 141907
# Exec time 10 13s 40us 10ms 93us 125us 176us 66us
# Lock time 34 134ms 0 3ms 0 1us 13us 1us
# Rows sent 11 138.58k 1 1 1 1 0 1
# Rows examine 0 138.58k 1 1 1 1 0 1
# Query size 25 4.32M 31 33 31.95 31.70 0.43 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 = 27\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
# Query 4: 6.85 QPS, 0.13x concurrency, ID 0xA3401CA3ABCC04C3AB221DB8AD5CBF26 at byte 43210700
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:34:26 to 2023-11-25T06:35:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 466
# Exec time 6 9s 5ms 47ms 18ms 34ms 9ms 16ms
# Lock time 0 462us 0 90us 0 1us 4us 1us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 4 3.89M 8.55k 8.55k 8.55k 8.55k 0 8.55k
# Query size 0 44.60k 98 98 98 98 0 98
# String:
# Hosts isucon1
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ###########
# 10ms ################################################################
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'reservation_slots'\G
# SHOW CREATE TABLE `reservation_slots`\G
UPDATE reservation_slots SET slot = slot - 1 WHERE start_at >= 1702641600 AND end_at <= 1702702800\G
# Converted for EXPLAIN
# EXPLAIN
select slot = slot - 1 from reservation_slots where start_at >= 1702641600 AND end_at <= 1702702800\G
http://localhost:19993/#menu_services;after=1700894066562;before=1700894146571
go tool pprof -http=0.0.0.0:1080 http://isucon3/pprof/11250634
isucon1
alp
slow query
explain
netdata
http://localhost:19991/#menu_services;after=1700894066565;before=1700894142615
pprof