saza-ku
commented
1 year ago isucon1
alp
+-------+--------+--------------------+-------+-------+---------+-------+-------+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+--------------------+-------+-------+---------+-------+-------+
| 1797 | GET | / | 0.008 | 0.288 | 103.632 | 0.058 | 0.148 |
| 1275 | POST | /login | 0.028 | 0.176 | 57.276 | 0.045 | 0.124 |
| 440 | GET | /@.+ | 0.040 | 0.228 | 52.504 | 0.119 | 0.200 |
| 2425 | GET | /posts/.+ | 0.004 | 0.080 | 42.684 | 0.018 | 0.052 |
| 530 | GET | /posts | 0.012 | 0.172 | 34.084 | 0.064 | 0.128 |
| 270 | POST | /register | 0.020 | 0.168 | 15.324 | 0.057 | 0.128 |
| 398 | POST | / | 0.004 | 0.096 | 8.704 | 0.022 | 0.080 |
| 2793 | GET | /favicon.ico | 0.000 | 0.044 | 5.820 | 0.002 | 0.016 |
| 2793 | GET | /js/timeago.min.js | 0.000 | 0.036 | 5.528 | 0.002 | 0.016 |
| 2793 | GET | /js/main.js | 0.000 | 0.052 | 5.076 | 0.002 | 0.012 |
| 2793 | GET | /css/style.css | 0.000 | 0.028 | 4.592 | 0.002 | 0.012 |
| 244 | POST | /comment | 0.004 | 0.080 | 4.216 | 0.017 | 0.060 |
| 270 | GET | /admin/banned | 0.000 | 0.052 | 1.388 | 0.005 | 0.028 |
| 242 | GET | /login | 0.000 | 0.036 | 1.124 | 0.005 | 0.028 |
| 121 | GET | /logout | 0.000 | 0.028 | 0.600 | 0.005 | 0.020 |
| 27063 | GET | /image/.+ | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
+-------+--------+--------------------+-------+-------+---------+-------+-------+
slow query
Count: 440 Time=0.04s (17s) Lock=0.00s (0s) Rows=1.0 (440), isuconp[isuconp]@localhost
SELECT COUNT(*) AS count FROM `comments` WHERE `user_id` = N
Count: 440 Time=0.02s (10s) Lock=0.00s (0s) Rows=10.2 (4480), isuconp[isuconp]@localhost
SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `not_banned_posts_without_imgdata` WHERE `user_id` = N ORDER BY `created_at` DESC LIMIT N
Count: 51020 Time=0.00s (10s) Lock=0.00s (0s) Rows=1.0 (52010), isuconp[isuconp]@localhost
SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N
Count: 53445 Time=0.00s (9s) Lock=0.00s (0s) Rows=1.0 (53445), isuconp[isuconp]@localhost
SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = N
Count: 5192 Time=0.00s (9s) Lock=0.00s (0s) Rows=1138.4 (5910607), isuconp[isuconp]@localhost
SELECT * FROM users
Count: 270 Time=0.01s (2s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost
INSERT INTO `users` (`account_name`, `passhash`) VALUES ('S','S')
Count: 244 Time=0.01s (2s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost
INSERT INTO `comments` (`post_id`, `user_id`, `comment`) VALUES (N,N,'S')
Count: 440 Time=0.00s (2s) Lock=0.00s (0s) Rows=10.2 (4480), isuconp[isuconp]@localhost
SELECT `id` FROM `not_banned_posts_without_imgdata` WHERE `user_id` = N LIMIT N
Count: 199 Time=0.01s (1s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost
INSERT INTO `not_banned_posts_without_imgdata` (`user_id`, `mime`, `body`) VALUES (N,'S','S')
Count: 2425 Time=0.00s (0s) Lock=0.00s (0s) Rows=9.3 (22542), isuconp[isuconp]@localhost
SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC
explain
Reading from STDIN ...
# 9.2s user time, 60ms system time, 37.59M rss, 50.93M vsz
# Current date: Fri Oct 27 18:41:19 2023
# Hostname: private-isu
# Files: STDIN
# Overall: 130.50k total, 28 unique, 1.67k QPS, 0.89x concurrency ________
# Time range: 2023-10-27T09:39:52 to 2023-10-27T09:41:10
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 69s 1us 108ms 531us 1ms 3ms 125us
# Lock time 276ms 0 12ms 2us 1us 51us 1us
# Rows sent 5.82M 0 1.24k 46.76 19.46 217.48 0.99
# Rows examine 56.29M 0 97.89k 452.31 158.58 5.67k 0
# Query size 8.75M 17 232 70.31 80.10 21.75 65.89
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================= ============= ===== ====== ===== ====
# 1 0xCDEB1AFF2AE2BE51B2ED5CF0... 17.9017 25.8% 440 0.0407 0.00 SELECT comments
# 2 0x77B9A1E48C001B0D14F8D79F... 10.5252 15.2% 440 0.0239 0.00 SELECT not_banned_posts_without_imgdata
# 3 0x624863D30DAC59FA16849282... 10.1191 14.6% 51020 0.0002 0.00 SELECT comments
# 4 0x422390B42D4DD86C7539A5F4... 9.9461 14.3% 53445 0.0002 0.00 SELECT comments
# MISC 0xMISC 20.8826 30.1% 25157 0.0008 0.0 <24 ITEMS>
# Query 1: 7.21 QPS, 0.29x concurrency, ID 0xCDEB1AFF2AE2BE51B2ED5CF03D4E749F at byte 1024946
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-10-27T09:39:52 to 2023-10-27T09:40:53
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 440
# Exec time 25 18s 18ms 108ms 41ms 71ms 14ms 38ms
# Lock time 0 720us 0 41us 1us 1us 3us 1us
# Rows sent 0 440 1 1 1 1 0 1
# Rows examine 74 42.01M 97.66k 97.89k 97.77k 97.04k 0 97.04k
# Query size 0 26.59k 61 62 61.89 59.77 0 59.77
# String:
# Hosts localhost
# Users isuconp
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms ################################################################
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'comments'\G
# SHOW CREATE TABLE `comments`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) AS count FROM `comments` WHERE `user_id` = 831\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: comments
# partitions: NULL
# type: ALL
# possible_keys: NULL
# key: NULL
# key_len: NULL
# ref: NULL
# rows: 99815
# filtered: 10.00
# Extra: Using where
# Query 2: 7.21 QPS, 0.17x concurrency, ID 0x77B9A1E48C001B0D14F8D79F7EAF30FF at byte 1966614
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-10-27T09:39:52 to 2023-10-27T09:40:53
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 440
# Exec time 15 11s 12ms 85ms 24ms 42ms 10ms 21ms
# Lock time 0 713us 0 20us 1us 1us 1us 1us
# Rows sent 0 4.38k 3 19 10.18 15.25 2.87 9.83
# Rows examine 7 4.15M 9.55k 9.75k 9.65k 9.33k 0 9.33k
# Query size 0 64.41k 149 150 149.89 143.84 0 143.84
# String:
# Hosts localhost
# Users isuconp
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms ################################################################
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'not_banned_posts_without_imgdata'\G
# SHOW CREATE TABLE `not_banned_posts_without_imgdata`\G
# EXPLAIN
SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `not_banned_posts_without_imgdata` WHERE `user_id` = 999 ORDER BY `created_at` DESC LIMIT 20\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: not_banned_posts_without_imgdata
# partitions: NULL
# type: index
# possible_keys: NULL
# key: posts_without_imgdata_created_at_index
# key_len: 4
# ref: NULL
# rows: 20
# filtered: 10.00
# Extra: Using where
# Query 3: 822.90 QPS, 0.16x concurrency, ID 0x624863D30DAC59FA16849282195BE09F at byte 2655354
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-10-27T09:39:52 to 2023-10-27T09:40:54
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 39 51020
# Exec time 14 10s 76us 21ms 198us 403us 426us 119us
# Lock time 42 116ms 0 12ms 2us 1us 70us 1us
# Rows sent 0 50.79k 0 3 1.02 2.90 1.37 0
# Rows examine 0 50.79k 0 3 1.02 2.90 1.37 0
# Query size 45 4.02M 79 83 82.65 80.10 0.11 80.10
# String:
# Hosts localhost
# Users isuconp
# Query_time distribution
# 1us
# 10us ############
# 100us ################################################################
# 1ms #
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'comments'\G
# SHOW CREATE TABLE `comments`\G
# EXPLAIN
SELECT * FROM `comments` WHERE `post_id` = 9991 ORDER BY `created_at` DESC LIMIT 3\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: comments
# partitions: NULL
# type: ref
# possible_keys: comments_post_id_index
# key: comments_post_id_index
# key_len: 4
# ref: const
# rows: 12
# filtered: 100.00
# Extra: NULL
# Query 4: 862.02 QPS, 0.16x concurrency, ID 0x422390B42D4DD86C7539A5F45EB76A80 at byte 23339266
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-10-27T09:39:52 to 2023-10-27T09:40:54
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 40 53445
# Exec time 14 10s 70us 14ms 186us 366us 386us 119us
# Lock time 39 109ms 0 4ms 2us 1us 29us 1us
# Rows sent 0 52.19k 1 1 1 1 0 1
# Rows examine 0 189.87k 0 23 3.64 12.54 5.07 0
# Query size 38 3.34M 62 66 65.62 65.89 1.51 65.89
# String:
# Hosts localhost
# Users isuconp
# Query_time distribution
# 1us
# 10us ################
# 100us ################################################################
# 1ms #
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'comments'\G
# SHOW CREATE TABLE `comments`\G
# EXPLAIN
SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = 10128\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: comments
# partitions: NULL
# type: ref
# possible_keys: comments_post_id_index
# key: comments_post_id_index
# key_len: 4
# ref: const
# rows: 1
# filtered: 100.00
# Extra: Using index
netdata
http://localhost:19991/#menu_apps_submenu_cpu;after=1698399592613;before=1698399668831
49706
https://github.com/Saza-ku/private-isu-2023/pull/36