saza-ku
commented
7 months ago isucon3
alp
+-------+--------+-----+-------+-------+-------+-------+-------+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+-----+-------+-------+-------+-------+-------+
| 1 | GET | / | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
+-------+--------+-----+-------+-------+-------+-------+-------+
slow query
Count: 52625 Time=0.00s (8s) Lock=0.00s (0s) Rows=4.6 (242711), isucon[isucon]@isucon1
SELECT livestream_id, tags.id AS tag_id, tags.name as tag_name FROM livestream_tags as l LEFT JOIN tags ON l.tag_id = tags.id WHERE livestream_id = N
Count: 716 Time=0.01s (5s) 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: 60438 Time=0.00s (5s) Lock=0.00s (0s) Rows=1.0 (60438), isucon[isucon]@isucon1
SELECT * FROM users WHERE id = N
Count: 717 Time=0.01s (5s) Lock=0.00s (0s) Rows=10.1 (7216), isucon[isucon]@isucon1
SELECT * FROM reservation_slots WHERE start_at >= N AND end_at <= N FOR UPDATE
Count: 26332 Time=0.00s (2s) Lock=0.00s (0s) Rows=1.0 (26332), isucon[isucon]@isucon1
SELECT * FROM livestreams WHERE id = N
Count: 286 Time=0.01s (2s) Lock=0.00s (0s) Rows=4.3 (1233), isucon[isucon]@isucon1
SELECT * FROM livestreams WHERE user_id = N
Count: 3113 Time=0.00s (1s) Lock=0.00s (0s) Rows=1.2 (3810), isucon[isucon]@isucon1
SELECT * FROM themes WHERE user_id IN (N, N)
Count: 22354 Time=0.00s (1s) Lock=0.00s (0s) Rows=0.0 (0), isucon[isucon]@isucon1
COMMIT
Count: 2680 Time=0.00s (1s) Lock=0.00s (0s) Rows=1.0 (2680), isucon[isucon]@isucon1
SELECT * FROM themes WHERE user_id IN (N)
Count: 370 Time=0.00s (1s) Lock=0.00s (0s) Rows=2912.6 (1077670), isucon[isucon]@isucon1
SELECT * FROM livecomments
explain
Reading from STDIN ...
# 15.5s user time, 70ms system time, 37.88M rss, 52.17M vsz
# Current date: Sat Nov 25 08:41:52 2023
# Hostname: ip-192-168-0-13
# Files: STDIN
# Overall: 244.81k total, 65 unique, 2.95k QPS, 0.59x concurrency ________
# Time range: 2023-11-25T08:40:14 to 2023-11-25T08:41:37
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 49s 1us 63ms 199us 384us 934us 89us
# Lock time 210ms 0 3ms 0 1us 9us 1us
# Rows sent 1.89M 0 7.99k 8.09 4.96 142.30 0.99
# Rows examine 30.12M 0 14.24k 129.02 420.77 797.49 0.99
# Query size 15.87M 6 453 67.96 151.03 57.36 40.45
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================ ============= ====== ====== ===== ====
# 1 0xA0E56FBF29E0231425D4BA3... 8.5243 17.4% 52625 0.0002 0.00 SELECT livestream_tags tags
# 2 0xEA1E6309EEEFF9A6831AD2F... 5.8830 12.0% 60438 0.0001 0.00 SELECT users
# 3 0xA3401CA3ABCC04C3AB221DB... 5.8607 12.0% 716 0.0082 0.00 UPDATE reservation_slots
# 4 0x7F9C0C0BA9473953B723EE1... 5.7327 11.7% 717 0.0080 0.00 SELECT reservation_slots
# MISC 0xMISC 22.9357 46.9% 130317 0.0002 0.0 <61 ITEMS>
# Query 1: 773.90 QPS, 0.13x concurrency, ID 0xA0E56FBF29E0231425D4BA3CE21653D3 at byte 10121246
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T08:40:14 to 2023-11-25T08:41:22
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 21 52625
# Exec time 17 9s 74us 50ms 161us 224us 583us 138us
# Lock time 29 62ms 0 942us 1us 1us 6us 1us
# Rows sent 12 237.02k 0 11 4.61 4.96 1.11 4.96
# Rows examine 1 474.04k 0 22 9.22 9.83 2.19 9.83
# Query size 48 7.68M 150 153 152.99 151.03 0.25 151.03
# String:
# Hosts isucon1
# Users isucon
# Query_time distribution
# 1us
# 10us #
# 100us ################################################################
# 1ms #
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'livestream_tags'\G
# SHOW CREATE TABLE `livestream_tags`\G
# SHOW TABLE STATUS LIKE 'tags'\G
# SHOW CREATE TABLE `tags`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT livestream_id, tags.id AS tag_id, tags.name as tag_name FROM livestream_tags as l LEFT JOIN tags ON l.tag_id = tags.id WHERE livestream_id = 7616\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: l
# partitions: NULL
# type: ref
# possible_keys: livestream_id
# key: livestream_id
# key_len: 8
# ref: const
# rows: 5
# filtered: 100.00
# Extra: NULL
# *************************** 2. row ***************************
# id: 1
# select_type: SIMPLE
# table: tags
# partitions: NULL
# type: eq_ref
# possible_keys: PRIMARY
# key: PRIMARY
# key_len: 8
# ref: isupipe.l.tag_id
# rows: 1
# filtered: 100.00
# Extra: NULL
# Query 2: 888.79 QPS, 0.09x concurrency, ID 0xEA1E6309EEEFF9A6831AD2FB940FC23C at byte 52692847
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T08:40:14 to 2023-11-25T08:41:22
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 24 60438
# Exec time 12 6s 49us 53ms 97us 119us 512us 76us
# Lock time 26 55ms 0 772us 0 1us 5us 1us
# Rows sent 3 59.02k 1 1 1 1 0 1
# Rows examine 0 59.02k 1 1 1 1 0 1
# Query size 12 2.01M 32 35 34.90 34.95 0.54 34.95
# String:
# Hosts isucon1
# Users isucon
# Query_time distribution
# 1us
# 10us ################################################################
# 100us #########
# 1ms #
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'users'\G
# SHOW CREATE TABLE `users`\G
# EXPLAIN
SELECT * FROM users WHERE id = 1296\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: users
# partitions: NULL
# type: const
# possible_keys: PRIMARY
# key: PRIMARY
# key_len: 8
# ref: const
# rows: 1
# filtered: 100.00
# Extra: NULL
# Query 3: 10.53 QPS, 0.09x concurrency, ID 0xA3401CA3ABCC04C3AB221DB8AD5CBF26 at byte 65669001
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T08:40:14 to 2023-11-25T08:41:22
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 716
# Exec time 11 6s 5ms 27ms 8ms 12ms 2ms 8ms
# Lock time 0 638us 0 12us 0 1us 0 1us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 19 5.98M 8.55k 8.55k 8.55k 8.55k 0 8.55k
# Query size 0 68.52k 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 >= 1706079600 AND end_at <= 1706126400\G
# Converted for EXPLAIN
# EXPLAIN
select slot = slot - 1 from reservation_slots where start_at >= 1706079600 AND end_at <= 1706126400\G
# Query 4: 10.54 QPS, 0.08x concurrency, ID 0x7F9C0C0BA9473953B723EE16C08655F1 at byte 64542081
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T08:40:14 to 2023-11-25T08:41:22
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 717
# Exec time 11 6s 5ms 35ms 8ms 12ms 2ms 7ms
# Lock time 0 984us 0 205us 1us 1us 7us 1us
# Rows sent 0 7.05k 1 21 10.06 19.46 5.81 9.83
# Rows examine 19 5.99M 8.55k 8.55k 8.55k 8.55k 0 8.55k
# Query size 0 67.22k 96 96 96 96 0 96
# 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
# EXPLAIN
SELECT * FROM reservation_slots WHERE start_at >= 1706562000 AND end_at <= 1706601600 FOR UPDATE\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: reservation_slots
# partitions: NULL
# type: ALL
# possible_keys: NULL
# key: NULL
# key_len: NULL
# ref: NULL
# rows: 8773
# filtered: 11.11
# Extra: Using where
netdata
http://localhost:19993/#menu_services;after=1700901614552;before=1700901694560
pprof
go tool pprof -http=0.0.0.0:1080 http://isucon3/pprof/11250840
isucon1
alp
slow query
explain
netdata
http://localhost:19991/#menu_services;after=1700901614552;before=1700901690607
pprof