Open saza-ku opened 8 months ago
+-------+--------+-----+-----+-----+-----+-----+-----+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+-----+-----+-----+-----+-----+-----+
+-------+--------+-----+-----+-----+-----+-----+-----+
Count: 32391 Time=0.00s (59s) Lock=0.00s (0s) Rows=0.2 (5208), 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: 23283 Time=0.00s (39s) Lock=0.00s (0s) Rows=0.5 (11816), 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: 39680 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: 594 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: 1188 Time=0.00s (2s) Lock=0.00s (0s) Rows=0.5 (594), 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: 93117 Time=0.00s (1s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@isucon1
#
Count: 594 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@isucon1
commit
Count: 594 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: 594 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (594), isudns[isudns]@isucon1
select id,name,master,last_check,notified_serial,type,options,catalog,account from domains where name='S'
Count: 1189 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 ...
# 11.9s user time, 70ms system time, 36.75M rss, 51.07M vsz
# Current date: Sat Nov 25 06:25:12 2023
# Hostname: ip-192-168-0-12
# Files: STDIN
# Overall: 193.82k total, 20 unique, 2.36k QPS, 1.34x concurrency ________
# Time range: 2023-11-25T06:23:38 to 2023-11-25T06:25:00
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 110s 1us 12ms 567us 2ms 857us 89us
# Lock time 159ms 0 4ms 0 1us 19us 1us
# Rows sent 17.79k 0 1 0.09 0.99 0.29 0
# Rows examine 86.73M 0 1.83k 469.22 1.69k 711.39 0
# Query size 15.07M 5 218 81.50 151.03 48.78 112.70
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================= ============= ===== ====== ===== ====
# 1 0x42EF7D7D98FBCC9723BF896E... 59.2918 53.9% 32391 0.0018 0.00 SELECT records
# 2 0x3D83BC87F3B3A00D571FFC81... 39.5210 35.9% 23283 0.0017 0.00 SELECT records
# 3 0x22279D81D51006139E0C7640... 4.7781 4.3% 39680 0.0001 0.00 SELECT domains domainmetadata
# 4 0x859BBB7E9D760686137A9444... 2.4428 2.2% 594 0.0041 0.00 DELETE records
# MISC 0xMISC 3.9238 3.6% 97877 0.0000 0.0 <16 ITEMS>
# Query 1: 476.34 QPS, 0.87x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 49036119
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:23:38 to 2023-11-25T06:24:46
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 16 32391
# Exec time 53 59s 696us 12ms 2ms 3ms 604us 2ms
# Lock time 25 40ms 0 209us 1us 1us 2us 1us
# Rows sent 28 5.09k 0 1 0.16 0.99 0.36 0
# Rows examine 56 48.89M 1.25k 1.83k 1.55k 1.78k 163.93 1.53k
# Query size 29 4.38M 129 218 141.88 158.58 11.88 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='q990tdk1awcrdok6p57e710.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='q990tdk1awcrdok6p57e710.u.isucon.dev' and domain_id=4"] at /usr/bin/pt-query-digest line 7796.
# Query 2: 342.40 QPS, 0.58x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 49037924
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:23:38 to 2023-11-25T06:24:46
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 12 23283
# Exec time 35 40s 655us 11ms 2ms 3ms 551us 2ms
# Lock time 18 30ms 0 1ms 1us 1us 8us 1us
# Rows sent 64 11.54k 0 1 0.51 0.99 0.50 0.99
# Rows examine 40 35.17M 1.25k 1.83k 1.55k 1.78k 164.04 1.53k
# Query size 19 3.01M 126 217 135.66 151.03 11.24 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: 583.53 QPS, 0.07x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 14632803
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:23:38 to 2023-11-25T06:24:46
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 20 39680
# Exec time 4 5s 72us 5ms 120us 152us 102us 103us
# Lock time 41 66ms 0 2ms 1us 1us 16us 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.31M 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.74 QPS, 0.04x concurrency, ID 0x859BBB7E9D760686137A944406874C4D at byte 49874955
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:23:38 to 2023-11-25T06:24:46
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 594
# Exec time 2 2s 2ms 12ms 4ms 6ms 1ms 4ms
# Lock time 12 20ms 0 4ms 34us 1us 312us 1us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 1 914.48k 1.25k 1.83k 1.54k 1.78k 169.66 1.53k
# Query size 0 49.65k 78 93 85.60 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='rei280.u.isucon.dev' and type='A'\G
# Converted for EXPLAIN
# EXPLAIN
select * from records where domain_id=4 and name='rei280.u.isucon.dev' and type='A'\G
http://localhost:19992/#menu_services;after=1700893418016;before=1700893498023
go tool pprof -http=0.0.0.0:1080 http://isucon2/pprof/11250623
+-------+--------+-----+-----+-----+-----+-----+-----+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+-----+-----+-----+-----+-----+-----+
+-------+--------+-----+-----+-----+-----+-----+-----+
Count: 2177 Time=0.02s (39s) Lock=0.00s (0s) Rows=0.0 (30), isucon[isucon]@isucon1
SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = N AND livestream_id = N
Count: 33969 Time=0.00s (30s) Lock=0.00s (0s) Rows=1.0 (33969), isucon[isucon]@isucon1
SELECT * FROM themes WHERE user_id = N
Count: 53192 Time=0.00s (14s) Lock=0.00s (0s) Rows=0.9 (45863), isucon[isucon]@isucon1
SELECT icon_path FROM icons WHERE user_id = N
Count: 129659 Time=0.00s (12s) Lock=0.00s (0s) Rows=1.0 (129659), isucon[isucon]@isucon1
SELECT * FROM tags WHERE id = N
Count: 414 Time=0.02s (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: 415 Time=0.02s (8s) Lock=0.00s (0s) Rows=9.8 (4058), isucon[isucon]@isucon1
SELECT * FROM reservation_slots WHERE start_at >= N AND end_at <= N FOR UPDATE
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: 236 Time=0.02s (5s) Lock=0.00s (0s) Rows=2.6 (609), isucon[isucon]@isucon1
SELECT * FROM livestreams WHERE user_id = N
Count: 28912 Time=0.00s (4s) Lock=0.00s (0s) Rows=4.5 (129659), isucon[isucon]@isucon1
SELECT * FROM livestream_tags WHERE livestream_id = N
Count: 33351 Time=0.00s (4s) Lock=0.00s (0s) Rows=1.0 (33351), isucon[isucon]@isucon1
SELECT * FROM users WHERE id = N
Reading from STDIN ...
# 24.8s user time, 80ms system time, 38.00M rss, 52.24M vsz
# Current date: Sat Nov 25 06:25:26 2023
# Hostname: ip-192-168-0-13
# Files: STDIN
# Overall: 411.91k total, 66 unique, 4.90k QPS, 1.95x concurrency ________
# Time range: 2023-11-25T06:23:38 to 2023-11-25T06:25:02
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 164s 1us 79ms 398us 881us 2ms 80us
# Lock time 359ms 0 8ms 0 1us 19us 1us
# Rows sent 1.08M 0 7.71k 2.74 4.96 64.56 0.99
# Rows examine 116.35M 0 14.01k 296.19 1.33k 1.21k 0.99
# Query size 15.24M 6 453 38.80 59.77 25.76 34.95
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================ ============= ====== ====== ===== ====
# 1 0x64CC8A4E8E4B39020337559... 39.8724 24.3% 2177 0.0183 0.00 SELECT ng_words
# 2 0x38BC86A45F31C6B1EE32467... 30.0689 18.3% 33969 0.0009 0.00 SELECT themes
# 3 0xB4FC1F99A422ACB1A54E6E2... 14.9564 9.1% 53192 0.0003 0.00 SELECT icons
# 4 0xFBC5564AE716EAE82F20BFB... 12.1412 7.4% 129659 0.0001 0.00 SELECT tags
# MISC 0xMISC 67.0101 40.8% 192914 0.0003 0.0 <62 ITEMS>
# Query 1: 32.01 QPS, 0.59x concurrency, ID 0x64CC8A4E8E4B390203375597CE4D611F at byte 98846789
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:23:38 to 2023-11-25T06:24:46
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 2177
# Exec time 24 40s 4ms 49ms 18ms 34ms 8ms 16ms
# Lock time 1 4ms 0 2ms 1us 1us 41us 1us
# Rows sent 0 30 0 2 0.01 0 0.15 0
# Rows examine 25 29.77M 14.00k 14.01k 14.00k 13.78k 0 13.78k
# Query size 1 210.47k 97 99 99.00 97.36 0.14 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 /*!50100 PARTITIONS*/
SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = 1054 AND livestream_id = 7601\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: 14284
# filtered: 1.00
# Extra: Using where
# Query 2: 499.54 QPS, 0.44x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 65116641
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:23:38 to 2023-11-25T06:24:46
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 8 33969
# Exec time 18 30s 237us 19ms 885us 2ms 931us 596us
# Lock time 10 39ms 0 8ms 1us 1us 44us 1us
# Rows sent 3 33.17k 1 1 1 1 0 1
# Rows examine 35 41.85M 1000 1.55k 1.26k 1.46k 170.60 1.26k
# Query size 8 1.32M 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
SELECT * FROM themes WHERE user_id = 1025\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: 1592
# filtered: 10.00
# Extra: Using where
# Query 3: 782.24 QPS, 0.22x concurrency, ID 0xB4FC1F99A422ACB1A54E6E2F01B84879 at byte 35589348
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:23:38 to 2023-11-25T06:24:46
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 12 53192
# Exec time 9 15s 45us 14ms 281us 626us 451us 204us
# Lock time 16 60ms 0 4ms 1us 1us 25us 1us
# Rows sent 4 44.79k 0 1 0.86 0.99 0.34 0.99
# Rows examine 12 14.89M 0 585 293.52 537.02 165.57 284.79
# Query size 15 2.43M 45 48 47.86 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 = 1126\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: 585
# filtered: 10.00
# Extra: Using where
# Query 4: 1.91k QPS, 0.18x concurrency, ID 0xFBC5564AE716EAE82F20BFB45F6C37E7 at byte 93897740
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:23:38 to 2023-11-25T06:24:46
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 31 129659
# Exec time 7 12s 39us 18ms 93us 108us 214us 69us
# Lock time 31 114ms 0 2ms 0 1us 7us 1us
# Rows sent 11 126.62k 1 1 1 1 0 1
# Rows examine 0 126.62k 1 1 1 1 0 1
# Query size 25 3.95M 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 = 3\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=1700893418017;before=1700893498024
go tool pprof -http=0.0.0.0:1080 http://isucon3/pprof/11250623
isucon1
alp
slow query
explain
netdata
http://localhost:19991/#menu_services;after=1700893418001;before=1700893494089
pprof