saza-ku
commented
1 year ago isucon1
alp
+-------+--------+--------------------+-------+-------+---------+-------+-------+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+--------------------+-------+-------+---------+-------+-------+
| 772 | GET | / | 0.064 | 0.688 | 250.709 | 0.325 | 0.624 |
| 13741 | GET | /image/.+ | 0.000 | 0.128 | 131.340 | 0.010 | 0.056 |
| 180 | GET | /posts | 0.072 | 0.808 | 81.357 | 0.452 | 0.720 |
| 177 | GET | /@.+ | 0.056 | 0.800 | 64.433 | 0.364 | 0.748 |
| 1179 | GET | /posts/.+ | 0.004 | 0.220 | 53.964 | 0.046 | 0.136 |
| 565 | POST | /login | 0.004 | 0.148 | 22.992 | 0.041 | 0.108 |
| 138 | POST | / | 0.004 | 0.208 | 6.436 | 0.047 | 0.196 |
| 80 | POST | /register | 0.012 | 0.188 | 5.344 | 0.067 | 0.188 |
| 1539 | GET | /favicon.ico | 0.004 | 0.052 | 2.940 | 0.002 | 0.020 |
| 1539 | GET | /js/timeago.min.js | 0.000 | 0.044 | 2.328 | 0.002 | 0.020 |
| 1539 | GET | /js/main.js | 0.000 | 0.044 | 2.084 | 0.001 | 0.016 |
| 1539 | GET | /css/style.css | 0.000 | 0.036 | 1.964 | 0.001 | 0.016 |
| 74 | POST | /comment | 0.004 | 0.156 | 1.872 | 0.025 | 0.156 |
| 80 | GET | /admin/banned | 0.004 | 0.044 | 0.788 | 0.010 | 0.044 |
| 182 | GET | /login | 0.004 | 0.056 | 0.640 | 0.004 | 0.036 |
| 91 | GET | /logout | 0.000 | 0.040 | 0.352 | 0.004 | 0.040 |
+-------+--------+--------------------+-------+-------+---------+-------+-------+
slow query
Count: 772 Time=0.07s (56s) Lock=0.00s (0s) Rows=10033.4 (7745823), isuconp[isuconp]@localhost
SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` ORDER BY `created_at` DESC
Count: 243990 Time=0.00s (18s) Lock=0.00s (0s) Rows=7.9 (1918063), isuconp[isuconp]@localhost
#
Count: 14920 Time=0.00s (14s) Lock=0.00s (0s) Rows=1.0 (14920), isuconp[isuconp]@localhost
SELECT * FROM `posts` WHERE `id` = N
Count: 59089 Time=0.00s (14s) Lock=0.00s (0s) Rows=1.0 (59089), isuconp[isuconp]@localhost
SELECT * FROM `users` WHERE `id` = N
Count: 180 Time=0.08s (13s) Lock=0.00s (0s) Rows=9896.3 (1781340), isuconp[isuconp]@localhost
SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` WHERE `created_at` <= 'S' ORDER BY `created_at` DESC
Count: 177 Time=0.07s (12s) Lock=0.00s (0s) Rows=1.0 (177), isuconp[isuconp]@localhost
SELECT COUNT(*) AS count FROM `comments` WHERE `user_id` = N
Count: 21140 Time=0.00s (6s) Lock=0.00s (0s) Rows=1.2 (24713), isuconp[isuconp]@localhost
SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N
Count: 22319 Time=0.00s (6s) Lock=0.00s (0s) Rows=1.0 (22319), isuconp[isuconp]@localhost
SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = N
Count: 177 Time=0.02s (4s) Lock=0.00s (0s) Rows=10.1 (1796), isuconp[isuconp]@localhost
SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` WHERE `user_id` = N ORDER BY `created_at` DESC
Count: 69 Time=0.05s (3s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost
INSERT INTO `posts` (`user_id`, `mime`, `imgdata`, `body`) VALUES (N,'S','S','S')
explain
Reading from STDIN ...
# 24.2s user time, 140ms system time, 52.41M rss, 65.91M vsz
# Current date: Tue Oct 17 22:39:45 2023
# Hostname: private-isu
# Files: STDIN
# Overall: 371.42k total, 26 unique, 4.53k QPS, 1.91x concurrency ________
# Time range: 2023-10-17T13:37:58 to 2023-10-17T13:39:20
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 156s 1us 285ms 420us 657us 5ms 44us
# Lock time 411ms 0 21ms 1us 1us 56us 0
# Rows sent 9.22M 0 9.83k 26.02 0.99 493.45 0
# Rows examine 36.96M 0 97.73k 104.34 2.90 2.32k 0
# Query size 51.16M 17 1.26M 144.43 80.10 9.78k 31.70
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================ ============= ====== ====== ===== ====
# 1 0x4858CF4D8CAA743E839C127... 56.0758 35.9% 772 0.0726 0.02 SELECT posts
# 2 0xDA556F9115773A1A99AA016... 17.5391 11.2% 120580 0.0001 0.00 ADMIN PREPARE
# 3 0x396201721CD58410E070DA9... 14.7515 9.4% 59089 0.0002 0.00 SELECT users
# 4 0x19759A5557089FD5B718D44... 14.6948 9.4% 14920 0.0010 0.00 SELECT posts
# MISC 0xMISC 53.1942 34.0% 176056 0.0003 0.0 <22 ITEMS>
# Query 1: 12.45 QPS, 0.90x concurrency, ID 0x4858CF4D8CAA743E839C127C71B69E75 at byte 42017819
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2023-10-17T13:37:58 to 2023-10-17T13:39:00
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 772
# Exec time 35 56s 18ms 231ms 73ms 141ms 37ms 65ms
# Lock time 0 2ms 0 322us 2us 1us 16us 1us
# Rows sent 80 7.39M 9.77k 9.83k 9.80k 9.80k 34.50 9.80k
# Rows examine 39 14.77M 19.53k 19.67k 19.60k 19.40k 0 19.40k
# Query size 0 69.36k 92 92 92 92 0 92
# 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 `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` ORDER BY `created_at` DESC\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: posts
# partitions: NULL
# type: ALL
# possible_keys: NULL
# key: NULL
# key_len: NULL
# ref: NULL
# rows: 9477
# filtered: 100.00
# Extra: Using filesort
# Query 2: 1.91k QPS, 0.28x concurrency, ID 0xDA556F9115773A1A99AA0165670CE848 at byte 88198978
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-10-17T13:37:58 to 2023-10-17T13:39:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 32 120580
# Exec time 11 18s 21us 43ms 145us 490us 550us 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 3.45M 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 3: 937.92 QPS, 0.23x concurrency, ID 0x396201721CD58410E070DA9421CA8C8D at byte 56969290
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-10-17T13:37:58 to 2023-10-17T13:39:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 15 59089
# Exec time 9 15s 37us 33ms 249us 881us 771us 69us
# Lock time 54 223ms 0 21ms 3us 1us 120us 1us
# Rows sent 0 57.70k 1 1 1 1 0 1
# Rows examine 0 57.70k 1 1 1 1 0 1
# Query size 4 2.14M 36 39 37.90 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` = 912\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: 236.83 QPS, 0.23x concurrency, ID 0x19759A5557089FD5B718D440CBBB5C55 at byte 60660414
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-10-17T13:37:58 to 2023-10-17T13:39:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 4 14920
# Exec time 9 15s 91us 42ms 984us 4ms 2ms 260us
# Lock time 9 40ms 0 4ms 2us 1us 44us 1us
# Rows sent 0 14.57k 1 1 1 1 0 1
# Rows examine 0 14.57k 1 1 1 1 0 1
# Query size 1 574.57k 36 40 39.43 38.53 0.27 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
SELECT * FROM `posts` WHERE `id` = 10093\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
netdata
http://localhost:19991/#menu_apps_submenu_cpu;after=1697549878582;before=1697549955007
ALTER TABLE
postsADD INDEXposts_created_at_index(created_atDESC);score: 24664