search

saza-ku / private-isu-2023-public

0 stars 0 forks source link

10270623 #31

Open saza-ku opened 1 year ago

saza-ku commented 1 year ago

isucon1

alp

+-------+--------+--------------------+-------+-------+---------+-------+-------+
| COUNT | METHOD |        URI         |  MIN  |  MAX  |   SUM   |  AVG  |  P99  |
+-------+--------+--------------------+-------+-------+---------+-------+-------+
| 920   | GET    | /                  | 0.040 | 0.556 | 333.048 | 0.362 | 0.528 |
| 250   | GET    | /posts             | 0.044 | 0.680 | 100.972 | 0.404 | 0.632 |
| 394   | GET    | /@.+               | 0.028 | 0.300 | 61.448  | 0.156 | 0.276 |
| 3075  | GET    | /posts/.+          | 0.004 | 0.088 | 48.912  | 0.016 | 0.048 |
| 679   | POST   | /login             | 0.004 | 0.088 | 17.368  | 0.026 | 0.068 |
| 76    | POST   | /register          | 0.012 | 0.112 | 3.496   | 0.046 | 0.112 |
| 3718  | GET    | /favicon.ico       | 0.000 | 0.020 | 3.092   | 0.001 | 0.008 |
| 140   | POST   | /                  | 0.004 | 0.084 | 2.788   | 0.020 | 0.080 |
| 3718  | GET    | /js/timeago.min.js | 0.000 | 0.032 | 2.512   | 0.001 | 0.008 |
| 3718  | GET    | /js/main.js        | 0.000 | 0.040 | 2.164   | 0.001 | 0.008 |
| 3718  | GET    | /css/style.css     | 0.000 | 0.020 | 2.132   | 0.001 | 0.008 |
| 103   | POST   | /comment           | 0.008 | 0.056 | 1.708   | 0.017 | 0.052 |
| 240   | GET    | /login             | 0.000 | 0.012 | 0.344   | 0.001 | 0.008 |
| 120   | GET    | /logout            | 0.000 | 0.012 | 0.232   | 0.002 | 0.012 |
| 76    | GET    | /admin/banned      | 0.000 | 0.020 | 0.232   | 0.003 | 0.020 |
| 21374 | GET    | /image/.+          | 0.000 | 0.000 | 0.000   | 0.000 | 0.000 |
+-------+--------+--------------------+-------+-------+---------+-------+-------+

slow query

Count: 920  Time=0.28s (258s)  Lock=0.00s (0s)  Rows=20.0 (18400), isuconp[isuconp]@localhost
  SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `not_banned_posts_without_imgdata` ORDER BY `created_at` DESC LIMIT N

Count: 250  Time=0.32s (79s)  Lock=0.00s (0s)  Rows=20.0 (5000), isuconp[isuconp]@localhost
  SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `not_banned_posts_without_imgdata` WHERE `created_at` <= 'S' ORDER BY `created_at` DESC LIMIT N

Count: 394  Time=0.10s (39s)  Lock=0.00s (0s)  Rows=1.0 (394), isuconp[isuconp]@localhost
  SELECT COUNT(*) AS count FROM `comments` WHERE `user_id` = N

Count: 4639  Time=0.01s (28s)  Lock=0.00s (0s)  Rows=1039.0 (4820011), isuconp[isuconp]@localhost
  SELECT * FROM users

Count: 27311  Time=0.00s (13s)  Lock=0.00s (0s)  Rows=1.3 (34567), isuconp[isuconp]@localhost
  SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N

Count: 30386  Time=0.00s (12s)  Lock=0.00s (0s)  Rows=1.0 (30386), isuconp[isuconp]@localhost
  SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = N

Count: 3075  Time=0.00s (2s)  Lock=0.00s (0s)  Rows=9.7 (29975), isuconp[isuconp]@localhost
  SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC

Count: 3075  Time=0.00s (2s)  Lock=0.00s (0s)  Rows=1.0 (3075), isuconp[isuconp]@localhost
  SELECT * FROM `not_banned_posts_without_imgdata` WHERE `id` = N

Count: 103  Time=0.01s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), isuconp[isuconp]@localhost
  INSERT INTO `comments` (`post_id`, `user_id`, `comment`) VALUES (N,N,'S')

Count: 70  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isuconp[isuconp]@localhost
  INSERT INTO `posts_without_imgdata` (`user_id`, `mime`, `body`) VALUES (N,'S','S')

explain

Reading from STDIN ...

# 5.4s user time, 50ms system time, 37.71M rss, 50.94M vsz
# Current date: Fri Oct 27 18:24:32 2023
# Hostname: private-isu
# Files: STDIN
# Overall: 77.08k total, 24 unique, 1.00k QPS, 5.74x concurrency _________
# Time range: 2023-10-27T09:23:10 to 2023-10-27T09:24:27
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           442s     1us   565ms     6ms     6ms    36ms   159us
# Lock time          274ms       0     7ms     3us     1us    72us     1us
# Rows sent          4.72M       0   1.05k   64.24 1012.63  241.00    0.99
# Rows examine      54.59M       0  97.76k  742.58 1012.63   7.02k    0.99
# Query size         5.08M      17     221   69.06   80.10   21.64   65.89

# Profile
# Rank Query ID                      Response time  Calls R/Call V/M   Ite
# ==== ============================= ============== ===== ====== ===== ===
#    1 0x8FFC6FCCBEEC525A7DE53A12... 258.5573 58.5%   920 0.2810  0.02 SELECT not_banned_posts_without_imgdata
#    2 0x22EEC6F1102EDD0BFD59A9BF...  79.7038 18.0%   250 0.3188  0.02 SELECT not_banned_posts_without_imgdata
#    3 0xCDEB1AFF2AE2BE51B2ED5CF0...  39.1131  8.8%   394 0.0993  0.02 SELECT comments
#    4 0x19A1F14EFC0F221D30AFCB1E...  28.0473  6.3%  4639 0.0060  0.01 SELECT users
# MISC 0xMISC                         36.8930  8.3% 70879 0.0005   0.0 <20 ITEMS>

# Query 1: 15.08 QPS, 4.24x concurrency, ID 0x8FFC6FCCBEEC525A7DE53A12C542F25B at byte 13874275
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2023-10-27T09:23:10 to 2023-10-27T09:24:11
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1     920
# Exec time     58    259s    29ms   473ms   281ms   393ms    72ms   279ms
# Lock time      1     5ms     1us     3ms     5us     2us    85us     1us
# Rows sent      0  17.97k      20      20      20      20       0      20
# Rows examine  17   9.55M  10.56k  10.70k  10.63k  10.29k       0  10.29k
# Query size     2 115.00k     128     128     128     128       0     128
# 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` ORDER BY `created_at` DESC LIMIT 20\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: users
#    partitions: NULL
#          type: ALL
# possible_keys: PRIMARY
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 1076
#      filtered: 10.00
#         Extra: Using where; Using temporary; Using filesort
# *************************** 2. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: p
#    partitions: NULL
#          type: ref
# possible_keys: posts_without_imgdata_user_id_index
#           key: posts_without_imgdata_user_id_index
#       key_len: 4
#           ref: isuconp.users.id
#          rows: 9
#      filtered: 100.00
#         Extra: NULL

# Query 2: 4.10 QPS, 1.31x concurrency, ID 0x22EEC6F1102EDD0BFD59A9BF8326832F at byte 18101217
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2023-10-27T09:23:11 to 2023-10-27T09:24:12
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     250
# Exec time     18     80s    33ms   565ms   319ms   433ms    88ms   323ms
# Lock time      0     2ms     1us     2ms     9us     2us    99us     1us
# Rows sent      0   4.88k      20      20      20      20       0      20
# Rows examine   4   2.60M  10.56k  10.70k  10.63k  10.29k       0  10.29k
# Query size     0  43.46k     178     178     178     178       0     178
# 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 `created_at` <= '2016-01-02T11:45:02+09:00' ORDER BY `created_at` DESC LIMIT 20\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: users
#    partitions: NULL
#          type: ALL
# possible_keys: PRIMARY
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 1076
#      filtered: 10.00
#         Extra: Using where; Using temporary; Using filesort
# *************************** 2. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: p
#    partitions: NULL
#          type: ref
# possible_keys: posts_without_imgdata_created_at_index,posts_without_imgdata_user_id_index
#           key: posts_without_imgdata_user_id_index
#       key_len: 4
#           ref: isuconp.users.id
#          rows: 9
#      filtered: 100.00
#         Extra: Using where

# Query 3: 6.46 QPS, 0.64x concurrency, ID 0xCDEB1AFF2AE2BE51B2ED5CF03D4E749F at byte 18819798
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2023-10-27T09:23:10 to 2023-10-27T09:24:11
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     394
# Exec time      8     39s    20ms   225ms    99ms   163ms    39ms    95ms
# Lock time      0   546us       0    21us     1us     1us     1us     1us
# Rows sent      0     394       1       1       1       1       0       1
# Rows examine  68  37.59M  97.66k  97.76k  97.71k  97.04k       0  97.04k
# Query size     0  23.82k      61      62   61.92   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` = 499\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: 99674
#      filtered: 10.00
#         Extra: Using where

# Query 4: 74.82 QPS, 0.45x concurrency, ID 0x19A1F14EFC0F221D30AFCB1E1344BEBD at byte 10737529
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-10-27T09:23:10 to 2023-10-27T09:24:12
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6    4639
# Exec time      6     28s   893us    68ms     6ms    19ms     7ms     3ms
# Lock time      7    20ms       0     5ms     4us     1us    82us     1us
# Rows sent     97   4.60M    1000   1.05k   1.01k   1.04k   28.28 1012.63
# Rows examine   8   4.60M    1000   1.05k   1.01k   1.04k   28.28 1012.63
# Query size     1  86.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: 1076
#      filtered: 100.00
#         Extra: NULL

netdata

http://localhost:19991/#menu_apps_submenu_cpu;after=1698398590153;before=1698398666490