saza-ku
commented
1 year ago isucon1
alp
+-------+--------+--------------------+-------+-------+--------+-------+-------+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+--------------------+-------+-------+--------+-------+-------+
| 1960 | GET | / | 0.008 | 0.160 | 99.121 | 0.051 | 0.124 |
| 1424 | POST | /login | 0.004 | 0.116 | 57.389 | 0.040 | 0.096 |
| 2659 | GET | /posts/.+ | 0.004 | 0.072 | 44.669 | 0.017 | 0.048 |
| 538 | GET | /@.+ | 0.024 | 0.172 | 37.685 | 0.070 | 0.140 |
| 560 | GET | /posts | 0.012 | 0.160 | 33.564 | 0.060 | 0.132 |
| 294 | POST | /register | 0.016 | 0.136 | 15.908 | 0.054 | 0.116 |
| 406 | POST | / | 0.004 | 0.104 | 8.764 | 0.022 | 0.076 |
| 2994 | GET | /favicon.ico | 0.000 | 0.032 | 7.012 | 0.002 | 0.016 |
| 2994 | GET | /js/timeago.min.js | 0.000 | 0.056 | 6.300 | 0.002 | 0.016 |
| 2994 | GET | /js/main.js | 0.000 | 0.036 | 5.968 | 0.002 | 0.016 |
| 2994 | GET | /css/style.css | 0.000 | 0.028 | 5.548 | 0.002 | 0.016 |
| 322 | POST | /comment | 0.004 | 0.072 | 5.016 | 0.016 | 0.040 |
| 294 | GET | /admin/banned | 0.000 | 0.032 | 1.316 | 0.004 | 0.028 |
| 268 | GET | /login | 0.000 | 0.032 | 1.212 | 0.005 | 0.024 |
| 134 | GET | /logout | 0.004 | 0.028 | 0.516 | 0.004 | 0.016 |
| 28551 | GET | /image/.+ | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
+-------+--------+--------------------+-------+-------+--------+-------+-------+
slow query
Count: 538 Time=0.02s (11s) Lock=0.00s (0s) Rows=10.1 (5442), 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: 55842 Time=0.00s (9s) Lock=0.00s (0s) Rows=1.0 (57127), isuconp[isuconp]@localhost
SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N
Count: 58501 Time=0.00s (9s) Lock=0.00s (0s) Rows=1.0 (58501), isuconp[isuconp]@localhost
SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = N
Count: 5717 Time=0.00s (9s) Lock=0.00s (0s) Rows=1143.9 (6539618), isuconp[isuconp]@localhost
SELECT * FROM users
Count: 322 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: 294 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: 538 Time=0.00s (1s) Lock=0.00s (0s) Rows=10.1 (5442), isuconp[isuconp]@localhost
SELECT `id` FROM `not_banned_posts_without_imgdata` WHERE `user_id` = N LIMIT N
Count: 203 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: 2659 Time=0.00s (0s) Lock=0.00s (0s) Rows=9.2 (24431), isuconp[isuconp]@localhost
SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC
Count: 3319 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (3319), isuconp[isuconp]@localhost
SELECT * FROM `users` WHERE `id` = N
explain
Reading from STDIN ...
# 10.3s user time, 40ms system time, 37.71M rss, 50.94M vsz
# Current date: Fri Oct 27 18:47:28 2023
# Hostname: private-isu
# Files: STDIN
# Overall: 143.28k total, 24 unique, 1.86k QPS, 0.69x concurrency ________
# Time range: 2023-10-27T09:46:00 to 2023-10-27T09:47:17
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 53s 1us 73ms 369us 1ms 2ms 119us
# Lock time 239ms 0 4ms 1us 1us 18us 1us
# Rows sent 6.44M 0 1.26k 47.11 19.46 218.65 0.99
# Rows examine 16.81M 0 9.75k 123.05 102.22 849.20 0
# Query size 9.61M 17 223 70.33 80.10 21.81 65.89
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================= ============= ===== ====== ===== ====
# 1 0x77B9A1E48C001B0D14F8D79F... 11.9811 22.6% 538 0.0223 0.00 SELECT not_banned_posts_without_imgdata
# 2 0x624863D30DAC59FA16849282... 9.7084 18.3% 55842 0.0002 0.00 SELECT comments
# 3 0x422390B42D4DD86C7539A5F4... 9.6551 18.2% 58501 0.0002 0.00 SELECT comments
# 4 0x19A1F14EFC0F221D30AFCB1E... 9.2845 17.5% 5717 0.0016 0.00 SELECT users
# MISC 0xMISC 12.3365 23.3% 22686 0.0005 0.0 <20 ITEMS>
# Query 1: 8.97 QPS, 0.20x concurrency, ID 0x77B9A1E48C001B0D14F8D79F7EAF30FF at byte 1365716
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-10-27T09:46:00 to 2023-10-27T09:47:00
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 538
# Exec time 22 12s 11ms 73ms 22ms 38ms 8ms 20ms
# Lock time 0 2ms 0 853us 3us 1us 37us 1us
# Rows sent 0 5.31k 3 18 10.12 14.52 2.91 9.83
# Rows examine 30 5.07M 9.55k 9.75k 9.65k 9.33k 0 9.33k
# Query size 0 78.76k 149 150 149.91 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 /*!50100 PARTITIONS*/
SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `not_banned_posts_without_imgdata` WHERE `user_id` = 617 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 2: 915.44 QPS, 0.16x concurrency, ID 0x624863D30DAC59FA16849282195BE09F at byte 6933585
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-10-27T09:46:00 to 2023-10-27T09:47:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 38 55842
# Exec time 18 10s 75us 26ms 173us 301us 349us 119us
# Lock time 37 91ms 0 4ms 1us 1us 19us 1us
# Rows sent 0 55.79k 0 3 1.02 2.90 1.37 0
# Rows examine 0 55.79k 0 3 1.02 2.90 1.37 0
# Query size 45 4.40M 79 83 82.65 80.10 0.12 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` = 6191 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: 8
# filtered: 100.00
# Extra: NULL
# Query 3: 959.03 QPS, 0.16x concurrency, ID 0x422390B42D4DD86C7539A5F45EB76A80 at byte 6933872
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-10-27T09:46:00 to 2023-10-27T09:47:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 40 58501
# Exec time 18 10s 73us 15ms 165us 273us 316us 119us
# Lock time 41 99ms 0 3ms 1us 1us 17us 1us
# Rows sent 0 57.13k 1 1 1 1 0 1
# Rows examine 1 207.67k 0 23 3.64 13.83 5.06 0
# Query size 38 3.66M 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` = 10218\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
# Query 4: 93.72 QPS, 0.15x concurrency, ID 0x19A1F14EFC0F221D30AFCB1E1344BEBD at byte 22030785
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-10-27T09:46:00 to 2023-10-27T09:47:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 5717
# Exec time 17 9s 937us 20ms 2ms 3ms 1ms 1ms
# Lock time 6 16ms 0 2ms 2us 1us 38us 1us
# Rows sent 96 6.24M 1000 1.26k 1.12k 1.20k 85.43 1.09k
# Rows examine 37 6.24M 1000 1.26k 1.12k 1.20k 85.43 1.09k
# Query size 1 106.08k 19 19 19 19 0 19
# 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\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: users
# partitions: NULL
# type: ALL
# possible_keys: NULL
# key: NULL
# key_len: NULL
# ref: NULL
# rows: 1294
# filtered: 100.00
# Extra: NULL
netdata
http://localhost:19991/#menu_apps_submenu_cpu;after=1698399959876;before=1698400036046
53119
https://github.com/Saza-ku/private-isu-2023/pull/38