Open saza-ku opened 10 months ago
+-------+--------+-----+-----+-----+-----+-----+-----+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+-----+-----+-----+-----+-----+-----+
+-------+--------+-----+-----+-----+-----+-----+-----+
Count: 34097 Time=0.00s (56s) Lock=0.00s (0s) Rows=0.1 (4350), 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: 24054 Time=0.00s (37s) Lock=0.00s (0s) Rows=0.5 (12142), 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: 42090 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: 533 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: 1066 Time=0.00s (1s) Lock=0.00s (0s) Rows=0.5 (533), 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: 95377 Time=0.00s (1s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@isucon1
#
Count: 533 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@isucon1
commit
Count: 533 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: 533 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (533), isudns[isudns]@isucon1
select id,name,master,last_check,notified_serial,type,options,catalog,account from domains where name='S'
Count: 1066 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.3s user time, 50ms system time, 36.75M rss, 51.07M vsz
# Current date: Sat Nov 25 06:20:18 2023
# Hostname: ip-192-168-0-12
# Files: STDIN
# Overall: 200.42k total, 20 unique, 2.44k QPS, 1.27x concurrency ________
# Time range: 2023-11-25T06:18:43 to 2023-11-25T06:20:05
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 104s 1us 10ms 518us 2ms 759us 89us
# Lock time 155ms 0 4ms 0 1us 16us 1us
# Rows sent 17.15k 0 1 0.09 0.99 0.28 0
# Rows examine 88.73M 0 1.77k 464.20 1.61k 699.23 0
# Query size 15.71M 5 216 82.18 151.03 48.85 112.70
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================= ============= ===== ====== ===== ====
# 1 0x42EF7D7D98FBCC9723BF896E... 56.6431 54.5% 34097 0.0017 0.00 SELECT records
# 2 0x3D83BC87F3B3A00D571FFC81... 37.1985 35.8% 24054 0.0015 0.00 SELECT records
# 3 0x22279D81D51006139E0C7640... 4.6845 4.5% 42090 0.0001 0.00 SELECT domains domainmetadata
# 4 0x859BBB7E9D760686137A9444... 1.9907 1.9% 533 0.0037 0.00 DELETE records
# MISC 0xMISC 3.3991 3.3% 99648 0.0000 0.0 <16 ITEMS>
# Query 1: 501.43 QPS, 0.83x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 30648802
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:18:43 to 2023-11-25T06:19:51
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 17 34097
# Exec time 54 57s 702us 10ms 2ms 2ms 450us 2ms
# Lock time 25 39ms 0 131us 1us 1us 1us 1us
# Rows sent 24 4.25k 0 1 0.13 0.99 0.33 0
# Rows examine 57 50.64M 1.25k 1.77k 1.52k 1.69k 144.39 1.46k
# Query size 29 4.63M 129 216 142.27 158.58 12.09 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='rxp1ta7dzcqhrbj8rkd1q0.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='rxp1ta7dzcqhrbj8rkd1q0.u.isucon.dev' and domain_id=4"] at /usr/bin/pt-query-digest line 7796.
# Query 2: 353.74 QPS, 0.55x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 11792467
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:18:43 to 2023-11-25T06:19:51
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 12 24054
# Exec time 35 37s 658us 9ms 2ms 2ms 423us 1ms
# Lock time 18 29ms 0 665us 1us 1us 4us 1us
# Rows sent 69 11.86k 0 1 0.50 0.99 0.50 0.99
# Rows examine 40 35.73M 1.25k 1.77k 1.52k 1.69k 144.67 1.46k
# Query size 19 3.12M 126 215 136.10 151.03 11.49 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: 618.97 QPS, 0.07x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 49419470
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:18:43 to 2023-11-25T06:19:51
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 21 42090
# Exec time 4 5s 72us 3ms 111us 125us 80us 98us
# Lock time 39 61ms 0 1ms 1us 1us 5us 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.58M 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: 7.84 QPS, 0.03x concurrency, ID 0x859BBB7E9D760686137A944406874C4D at byte 56514538
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:18:43 to 2023-11-25T06:19:51
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 533
# Exec time 1 2s 2ms 8ms 4ms 5ms 761us 3ms
# Lock time 15 24ms 0 4ms 44us 1us 319us 1us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 804.70k 1.25k 1.77k 1.51k 1.69k 150.49 1.46k
# Query size 0 44.57k 78 93 85.63 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='minoru750.u.isucon.dev' and type='A'\G
# Converted for EXPLAIN
# EXPLAIN
select * from records where domain_id=4 and name='minoru750.u.isucon.dev' and type='A'\G
http://localhost:19992/#menu_services;after=1700893123356;before=1700893203364
go tool pprof -http=0.0.0.0:1080 http://isucon2/pprof/11250618
+-------+--------+-----+-----+-----+-----+-----+-----+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+-----+-----+-----+-----+-----+-----+
+-------+--------+-----+-----+-----+-----+-----+-----+
Count: 56440 Time=0.00s (46s) Lock=0.00s (0s) Rows=1.0 (56440), isucon[isucon]@isucon1
SELECT * FROM themes WHERE user_id = N
Count: 1120 Time=0.02s (20s) Lock=0.00s (0s) Rows=0.0 (49), isucon[isucon]@isucon1
SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = N AND livestream_id = N
Count: 65779 Time=0.00s (17s) Lock=0.00s (0s) Rows=0.9 (60684), isucon[isucon]@isucon1
SELECT icon_path FROM icons WHERE user_id = N
Count: 172384 Time=0.00s (14s) Lock=0.00s (0s) Rows=1.0 (172384), isucon[isucon]@isucon1
SELECT * FROM tags WHERE id = N
Count: 362 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: 363 Time=0.02s (7s) Lock=0.00s (0s) Rows=9.7 (3514), isucon[isucon]@isucon1
SELECT * FROM reservation_slots WHERE start_at >= N AND end_at <= N FOR UPDATE
Count: 36743 Time=0.00s (6s) Lock=0.00s (0s) Rows=4.7 (172398), isucon[isucon]@isucon1
SELECT * FROM livestream_tags WHERE livestream_id = N
Count: 55891 Time=0.00s (5s) Lock=0.00s (0s) Rows=1.0 (55891), isucon[isucon]@isucon1
SELECT * FROM users WHERE id = N
Count: 270 Time=0.02s (5s) Lock=0.00s (0s) Rows=0.0 (3), isucon[isucon]@isucon1
SELECT * FROM ng_words WHERE user_id = N AND livestream_id = N ORDER BY created_at DESC
Count: 225 Time=0.02s (4s) Lock=0.00s (0s) Rows=2.4 (547), isucon[isucon]@isucon1
SELECT * FROM livestreams WHERE user_id = N
Reading from STDIN ...
# 27.8s user time, 90ms system time, 37.75M rss, 52.02M vsz
# Current date: Sat Nov 25 06:20:35 2023
# Hostname: ip-192-168-0-13
# Files: STDIN
# Overall: 469.07k total, 63 unique, 5.58k QPS, 1.80x concurrency ________
# Time range: 2023-11-25T06:18:43 to 2023-11-25T06:20:07
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 151s 1us 75ms 321us 725us 2ms 80us
# Lock time 449ms 0 9ms 0 1us 22us 1us
# Rows sent 997.98k 0 7.65k 2.18 4.96 42.52 0.99
# Rows examine 117.28M 0 14.01k 262.16 1.33k 944.44 0.99
# Query size 17.40M 6 453 38.89 54.21 20.73 34.95
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================ ============= ====== ====== ===== ====
# 1 0x38BC86A45F31C6B1EE32467... 46.6406 30.9% 56440 0.0008 0.00 SELECT themes
# 2 0x64CC8A4E8E4B39020337559... 20.6234 13.7% 1120 0.0184 0.00 SELECT ng_words
# 3 0xB4FC1F99A422ACB1A54E6E2... 17.1467 11.4% 65779 0.0003 0.00 SELECT icons
# 4 0xFBC5564AE716EAE82F20BFB... 14.6687 9.7% 172384 0.0001 0.00 SELECT tags
# MISC 0xMISC 51.8109 34.3% 173351 0.0003 0.0 <59 ITEMS>
# Query 1: 830 QPS, 0.69x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 78425078
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:18:43 to 2023-11-25T06:19:51
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 12 56440
# Exec time 30 47s 238us 15ms 826us 2ms 770us 596us
# Lock time 11 52ms 0 1ms 0 1us 9us 1us
# Rows sent 5 55.12k 1 1 1 1 0 1
# Rows examine 58 68.83M 1000 1.50k 1.25k 1.46k 149.35 1.20k
# Query size 12 2.20M 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 = 1196\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: 1533
# filtered: 10.00
# Extra: Using where
# Query 2: 16.47 QPS, 0.30x concurrency, ID 0x64CC8A4E8E4B390203375597CE4D611F at byte 102231746
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:18:43 to 2023-11-25T06:19:51
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 1120
# Exec time 13 21s 4ms 70ms 18ms 33ms 8ms 16ms
# Lock time 0 927us 0 13us 0 1us 0 1us
# Rows sent 0 49 0 2 0.04 0 0.25 0
# Rows examine 13 15.32M 14.00k 14.01k 14.01k 13.78k 0 13.78k
# Query size 0 108.28k 97 99 99.00 97.36 0.20 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 = 1013 AND livestream_id = 7524\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: 14282
# filtered: 1.00
# Extra: Using where
# Query 3: 967.34 QPS, 0.25x concurrency, ID 0xB4FC1F99A422ACB1A54E6E2F01B84879 at byte 100031448
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:18:43 to 2023-11-25T06:19:51
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 14 65779
# Exec time 11 17s 46us 13ms 260us 568us 413us 185us
# Lock time 14 66ms 0 3ms 1us 1us 18us 1us
# Rows sent 5 59.26k 0 1 0.92 0.99 0.27 0.99
# Rows examine 14 16.89M 0 525 269.22 487.09 147.13 271.23
# Query size 17 3.01M 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 = 1250\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: 525
# filtered: 10.00
# Extra: Using where
# Query 4: 2.54k QPS, 0.22x concurrency, ID 0xFBC5564AE716EAE82F20BFB45F6C37E7 at byte 47731648
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:18:43 to 2023-11-25T06:19:51
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 36 172384
# Exec time 9 15s 42us 12ms 85us 93us 174us 66us
# Lock time 39 178ms 0 6ms 1us 1us 24us 1us
# Rows sent 16 168.34k 1 1 1 1 0 1
# Rows examine 0 168.34k 1 1 1 1 0 1
# Query size 30 5.25M 31 33 31.92 31.70 0.48 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 = 44\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=1700893123350;before=1700893203357
go tool pprof -http=0.0.0.0:1080 http://isucon3/pprof/11250618
isucon1
alp
slow query
explain
netdata
http://localhost:19991/#menu_services;after=1700893123347;before=1700893199376
pprof