saza-ku
commented
1 year ago isucon1
alp
+-------+--------+--------------------+-------+-------+---------+-------+-------+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+--------------------+-------+-------+---------+-------+-------+
| 1203 | GET | / | 0.020 | 0.364 | 194.404 | 0.162 | 0.288 |
| 20391 | GET | /image/.+ | 0.000 | 0.184 | 144.036 | 0.007 | 0.028 |
| 260 | GET | /posts | 0.032 | 0.472 | 73.996 | 0.285 | 0.436 |
| 232 | GET | /@.+ | 0.064 | 0.536 | 71.456 | 0.308 | 0.516 |
| 1532 | GET | /posts/.+ | 0.004 | 0.116 | 54.948 | 0.036 | 0.080 |
| 862 | POST | /login | 0.056 | 0.148 | 35.624 | 0.041 | 0.120 |
| 214 | POST | / | 0.004 | 0.228 | 10.176 | 0.048 | 0.204 |
| 132 | POST | /register | 0.016 | 0.180 | 8.132 | 0.062 | 0.160 |
| 103 | POST | /comment | 0.008 | 0.076 | 2.064 | 0.020 | 0.060 |
| 2073 | GET | /js/timeago.min.js | 0.000 | 0.016 | 1.948 | 0.001 | 0.008 |
| 2073 | GET | /js/main.js | 0.000 | 0.016 | 1.872 | 0.001 | 0.008 |
| 2073 | GET | /favicon.ico | 0.000 | 0.020 | 1.864 | 0.001 | 0.008 |
| 2073 | GET | /css/style.css | 0.004 | 0.024 | 1.820 | 0.001 | 0.008 |
| 132 | GET | /admin/banned | 0.004 | 0.032 | 0.788 | 0.006 | 0.028 |
| 276 | GET | /login | 0.004 | 0.016 | 0.536 | 0.002 | 0.012 |
| 138 | GET | /logout | 0.000 | 0.012 | 0.212 | 0.002 | 0.012 |
+-------+--------+--------------------+-------+-------+---------+-------+-------+
slow query
Count: 344655 Time=0.00s (26s) Lock=0.00s (0s) Rows=0.6 (190140), isuconp[isuconp]@localhost
#
Count: 20391 Time=0.00s (17s) Lock=0.00s (0s) Rows=1.0 (20391), isuconp[isuconp]@localhost
SELECT * FROM `posts` WHERE `id` = N
Count: 79167 Time=0.00s (17s) Lock=0.00s (0s) Rows=1.0 (79167), isuconp[isuconp]@localhost
SELECT * FROM `users` WHERE `id` = N
Count: 232 Time=0.07s (15s) Lock=0.00s (0s) Rows=1.0 (232), isuconp[isuconp]@localhost
SELECT COUNT(*) AS count FROM `comments` WHERE `user_id` = N
Count: 232 Time=0.05s (10s) Lock=0.00s (0s) Rows=10.2 (2378), isuconp[isuconp]@localhost
SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `not_banned_posts` WHERE `user_id` = N ORDER BY `created_at` DESC LIMIT N
Count: 31628 Time=0.00s (9s) Lock=0.00s (0s) Rows=1.0 (30230), isuconp[isuconp]@localhost
SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N
Count: 33160 Time=0.00s (8s) Lock=0.00s (0s) Rows=1.0 (33160), isuconp[isuconp]@localhost
SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = N
Count: 107 Time=0.06s (6s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost
INSERT INTO `posts` (`user_id`, `mime`, `imgdata`, `body`) VALUES (N,'S','S','S')
Count: 232 Time=0.01s (2s) Lock=0.00s (0s) Rows=10.3 (2379), isuconp[isuconp]@localhost
SELECT `id` FROM `not_banned_posts` WHERE `user_id` = N LIMIT N
Count: 132 Time=0.01s (1s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost
INSERT INTO `users` (`account_name`, `passhash`) VALUES ('S','S')
explain
Reading from STDIN ...
# 34s user time, 220ms system time, 62.37M rss, 76.18M vsz
# Current date: Wed Oct 18 00:30:53 2023
# Hostname: private-isu
# Files: STDIN
# Overall: 525.77k total, 31 unique, 6.26k QPS, 1.44x concurrency ________
# Time range: 2023-10-17T15:28:55 to 2023-10-17T15:30:19
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 121s 1us 222ms 230us 626us 2ms 44us
# Lock time 491ms 0 16ms 0 1us 33us 0
# Rows sent 209.17k 0 22 0.41 0.99 1.30 0
# Rows examine 26.91M 0 97.76k 53.67 0.99 2.06k 0
# Query size 80.10M 17 1.26M 159.76 80.10 10.41k 31.70
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================ ============= ====== ====== ===== ====
# 1 0xDA556F9115773A1A99AA016... 24.6316 20.3% 170166 0.0001 0.00 ADMIN PREPARE
# 2 0x19759A5557089FD5B718D44... 17.8253 14.7% 20391 0.0009 0.00 SELECT posts
# 3 0x396201721CD58410E070DA9... 17.6765 14.6% 79167 0.0002 0.00 SELECT users
# 4 0xCDEB1AFF2AE2BE51B2ED5CF... 15.7391 13.0% 232 0.0678 0.01 SELECT comments
# MISC 0xMISC 45.4195 37.4% 255817 0.0002 0.0 <27 ITEMS>
# Query 1: 2.79k QPS, 0.40x concurrency, ID 0xDA556F9115773A1A99AA0165670CE848 at byte 21413136
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-10-17T15:28:55 to 2023-10-17T15:29:56
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 32 170166
# Exec time 20 25s 20us 166ms 144us 490us 801us 44us
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 6 4.87M 30 30 30 30 0 30
# String:
# Hosts localhost
# Users isuconp
# Query_time distribution
# 1us
# 10us ################################################################
# 100us ##########
# 1ms #
# 10ms #
# 100ms #
# 1s
# 10s+
administrator command: Prepare\G
# Query 2: 334.28 QPS, 0.29x concurrency, ID 0x19759A5557089FD5B718D440CBBB5C55 at byte 151877770
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-10-17T15:28:55 to 2023-10-17T15:29:56
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 20391
# Exec time 14 18s 74us 44ms 874us 4ms 2ms 260us
# Lock time 12 61ms 0 6ms 3us 1us 57us 1us
# Rows sent 9 19.91k 1 1 1 1 0 1
# Rows examine 0 19.91k 1 1 1 1 0 1
# Query size 0 787.31k 37 40 39.54 38.53 0.21 38.53
# String:
# Hosts localhost
# Users isuconp
# Query_time distribution
# 1us
# 10us #
# 100us ################################################################
# 1ms ##############
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'posts'\G
# SHOW CREATE TABLE `posts`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `posts` WHERE `id` = 1327\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: posts
# partitions: NULL
# type: const
# possible_keys: PRIMARY
# key: PRIMARY
# key_len: 4
# ref: const
# rows: 1
# filtered: 100.00
# Extra: NULL
# Query 3: 1.30k QPS, 0.29x concurrency, ID 0x396201721CD58410E070DA9421CA8C8D at byte 167014723
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-10-17T15:28:55 to 2023-10-17T15:29:56
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 15 79167
# Exec time 14 18s 37us 24ms 223us 881us 601us 69us
# Lock time 47 232ms 0 16ms 2us 1us 70us 1us
# Rows sent 36 77.31k 1 1 1 1 0 1
# Rows examine 0 77.31k 1 1 1 1 0 1
# Query size 3 2.86M 36 39 37.91 36.69 0.17 36.69
# String:
# Hosts localhost
# Users isuconp
# 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` = 361\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: users
# partitions: NULL
# type: const
# possible_keys: PRIMARY
# key: PRIMARY
# key_len: 4
# ref: const
# rows: 1
# filtered: 100.00
# Extra: NULL
# Query 4: 3.87 QPS, 0.26x concurrency, ID 0xCDEB1AFF2AE2BE51B2ED5CF03D4E749F at byte 161395071
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-10-17T15:28:55 to 2023-10-17T15:29:55
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 232
# Exec time 12 16s 20ms 187ms 68ms 128ms 32ms 61ms
# Lock time 0 368us 0 24us 1us 1us 2us 1us
# Rows sent 0 232 1 1 1 1 0 1
# Rows examine 82 22.14M 97.66k 97.76k 97.70k 97.04k 0 97.04k
# Query size 0 14.03k 61 62 61.91 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
SELECT COUNT(*) AS count FROM `comments` WHERE `user_id` = 629\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: 99315
# filtered: 10.00
# Extra: Using where
netdata
http://localhost:19991/#menu_apps_submenu_cpu;after=1697556535033;before=1697556611329
40391