saza-ku
commented
1 year ago isucon2
alp
+-------+--------+-----+-----+-----+-----+-----+-----+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+-----+-----+-----+-----+-----+-----+
+-------+--------+-----+-----+-----+-----+-----+-----+
slow query
Count: 32155 Time=0.00s (58s) Lock=0.00s (0s) Rows=0.2 (5236), 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: 23153 Time=0.00s (39s) Lock=0.00s (0s) Rows=0.5 (11761), 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: 39420 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: 597 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: 1194 Time=0.00s (2s) Lock=0.00s (0s) Rows=0.5 (597), 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: 95150 Time=0.00s (1s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@isucon1
#
Count: 597 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@isucon1
commit
Count: 597 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: 597 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (597), isudns[isudns]@isucon1
select id,name,master,last_check,notified_serial,type,options,catalog,account from domains where name='S'
Count: 1195 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@isucon1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
explain
Reading from STDIN ...
# 12.4s user time, 50ms system time, 36.75M rss, 51.07M vsz
# Current date: Sat Nov 25 06:13:52 2023
# Hostname: ip-192-168-0-12
# Files: STDIN
# Overall: 195.26k total, 20 unique, 2.35k QPS, 1.32x concurrency ________
# Time range: 2023-11-25T06:12:17 to 2023-11-25T06:13:40
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 110s 1us 15ms 560us 2ms 857us 84us
# Lock time 194ms 0 7ms 0 1us 34us 0
# Rows sent 17.77k 0 1 0.09 0.99 0.29 0
# Rows examine 86.28M 0 1.83k 463.33 1.69k 709.40 0
# Query size 15.05M 5 216 80.80 151.03 48.73 80.10
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================= ============= ===== ====== ===== ====
# 1 0x42EF7D7D98FBCC9723BF896E... 59.0250 53.9% 32155 0.0018 0.00 SELECT records
# 2 0x3D83BC87F3B3A00D571FFC81... 39.3292 35.9% 23153 0.0017 0.00 SELECT records
# 3 0x22279D81D51006139E0C7640... 4.7286 4.3% 39420 0.0001 0.00 SELECT domains domainmetadata
# 4 0x859BBB7E9D760686137A9444... 2.4934 2.3% 597 0.0042 0.00 DELETE records
# MISC 0xMISC 3.9640 3.6% 99934 0.0000 0.0 <16 ITEMS>
# Query 1: 466.01 QPS, 0.86x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 23636535
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:12:17 to 2023-11-25T06:13:26
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 16 32155
# Exec time 53 59s 705us 14ms 2ms 3ms 600us 2ms
# Lock time 21 42ms 0 581us 1us 1us 4us 1us
# Rows sent 28 5.11k 0 1 0.16 0.99 0.37 0
# Rows examine 56 48.58M 1.25k 1.83k 1.55k 1.78k 163.99 1.53k
# Query size 28 4.35M 129 216 141.71 158.58 11.76 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='iuzvg7nk8dk0.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='iuzvg7nk8dk0.u.isucon.dev' and domain_id=4"] at /usr/bin/pt-query-digest line 7796.
# Query 2: 335.55 QPS, 0.57x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 33793529
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:12:17 to 2023-11-25T06:13:26
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 11 23153
# Exec time 35 39s 660us 10ms 2ms 3ms 560us 2ms
# Lock time 14 29ms 0 329us 1us 1us 2us 1us
# Rows sent 64 11.49k 0 1 0.51 0.99 0.50 0.99
# Rows examine 40 35.00M 1.25k 1.83k 1.55k 1.78k 164.18 1.53k
# Query size 19 2.99M 126 215 135.55 151.03 11.11 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: 571.30 QPS, 0.07x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 38864344
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:12:17 to 2023-11-25T06:13:26
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 20 39420
# Exec time 4 5s 71us 4ms 119us 144us 106us 103us
# Lock time 31 62ms 0 3ms 1us 1us 14us 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.29M 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.78 QPS, 0.04x concurrency, ID 0x859BBB7E9D760686137A944406874C4D at byte 55151017
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:12:18 to 2023-11-25T06:13:26
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 597
# Exec time 2 2s 2ms 15ms 4ms 7ms 1ms 4ms
# Lock time 30 59ms 0 7ms 98us 1us 605us 1us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 1 919.97k 1.25k 1.83k 1.54k 1.78k 170.38 1.53k
# Query size 0 49.95k 78 93 85.68 88.31 3.36 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='akemi640.u.isucon.dev' and type='A'\G
# Converted for EXPLAIN
# EXPLAIN
select * from records where domain_id=4 and name='akemi640.u.isucon.dev' and type='A'\G
netdata
http://localhost:19992/#menu_services;after=1700892737738;before=1700892817745
pprof
go tool pprof -http=0.0.0.0:1080 http://isucon2/pprof/11250612
isucon1
alp
slow query
explain
netdata
http://localhost:19991/#menu_services;after=1700892737731;before=1700892813790
pprof