search

saza-ku / private-isu-2023-public

0 stars 0 forks source link

11100215 #88

Open saza-ku opened 1 year ago

saza-ku commented 1 year ago

isucon1

alp

+-------+--------+--------------------+-------+-------+-------+-------+-------+
| COUNT | METHOD |        URI         |  MIN  |  MAX  |  SUM  |  AVG  |  P99  |
+-------+--------+--------------------+-------+-------+-------+-------+-------+
| 61    | GET    | /image/.+          | 0.000 | 0.126 | 0.128 | 0.002 | 0.126 |
| 8     | GET    | /                  | 0.004 | 0.068 | 0.104 | 0.013 | 0.068 |
| 1     | GET    | /@.+               | 0.036 | 0.036 | 0.036 | 0.036 | 0.036 |
| 2     | POST   | /                  | 0.004 | 0.004 | 0.008 | 0.004 | 0.004 |
| 1     | GET    | /admin/banned      | 0.004 | 0.004 | 0.004 | 0.004 | 0.004 |
| 2     | GET    | /posts/.+          | 0.000 | 0.004 | 0.004 | 0.002 | 0.004 |
| 1     | POST   | /comment           | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 2     | GET    | /login             | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 7     | POST   | /login             | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 2     | GET    | /favicon.ico       | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 2     | GET    | /js/timeago.min.js | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 2     | GET    | /js/main.js        | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 2     | GET    | /css/style.css     | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 1     | GET    | /logout            | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 1     | POST   | /register          | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
+-------+--------+--------------------+-------+-------+-------+-------+-------+

slow query

Count: 8  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=188.2 (1506), isuconp[isuconp]@localhost
  SELECT * FROM comments WHERE post_id IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N) ORDER BY `created_at` DESC

Count: 1  Time=0.03s (0s)  Lock=0.00s (0s)  Rows=138.0 (138), isuconp[isuconp]@localhost
  SELECT * FROM comments WHERE post_id IN (N, N, N, N, N, N, N, N, N, N, N, N) ORDER BY `created_at` DESC

Count: 1  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=12.0 (12), 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: 6  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=188.0 (1128), isuconp[isuconp]@localhost
  SELECT * FROM users WHERE id IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)

Count: 8  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=19.2 (154), isuconp[isuconp]@localhost
  SELECT `post_id`, COUNT(*) AS `count` FROM `comments` WHERE `post_id` IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N) GROUP BY `post_id`

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=191.0 (382), isuconp[isuconp]@localhost
  SELECT * FROM users WHERE id IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)

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

Count: 8  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=20.0 (160), 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: 7  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.9 (6), isuconp[isuconp]@localhost
  SELECT * FROM users WHERE account_name = 'S' AND del_flg = N

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=129.0 (129), isuconp[isuconp]@localhost
  SELECT * FROM users WHERE id IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)

explain

Reading from STDIN ...

# 130ms user time, 10ms system time, 36.12M rss, 50.65M vsz
# Current date: Fri Nov 10 14:16:55 2023
# Hostname: ip-172-31-22-189
# Files: STDIN
# Overall: 72 total, 16 unique, 5.54 QPS, 0.01x concurrency ______________
# Time range: 2023-11-10T05:15:39 to 2023-11-10T05:15:52
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          117ms    75us    61ms     2ms     1ms     7ms   194us
# Lock time          116us       0    12us     1us     2us     1us     1us
# Rows sent          3.60k       0     192   51.22  183.58   74.60   14.17
# Rows examine       6.91k       0     384   98.26  363.48  123.80   19.46
# Query size        15.95k      36   1.04k  226.89 1012.63  295.66  124.25

# Profile
# Rank Query ID                         Response time Calls R/Call V/M   I
# ==== ================================ ============= ===== ====== ===== =
#    1 0x1ADC9E80BE48F05D4A5F6D6502F...  0.0960 81.9%    11 0.0087  0.04 SELECT comments
#    2 0x395AE969FAFDA16C400891B7C96...  0.0064  5.5%    11 0.0006  0.00 SELECT users
#    3 0x77B9A1E48C001B0D14F8D79F7EA...  0.0054  4.6%     1 0.0054  0.00 SELECT not_banned_posts_without_imgdata
#    4 0x9B36FBB6889AD40E391C181F37D...  0.0030  2.6%    11 0.0003  0.00 SELECT comments
# MISC 0xMISC                            0.0063  5.4%    38 0.0002   0.0 <12 ITEMS>

# Query 1: 0.85 QPS, 0.01x concurrency, ID 0x1ADC9E80BE48F05D4A5F6D6502F7A387 at byte 846
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.04
# Time range: 2023-11-10T05:15:39 to 2023-11-10T05:15:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         15      11
# Exec time     81    96ms   152us    61ms     9ms    27ms    18ms   925us
# Lock time     22    26us     1us    12us     2us     1us     2us     1us
# Rows sent     44   1.62k       0     192  150.73  183.58   67.52  183.58
# Rows examine  46   3.22k       0     384  300.18  381.65  138.10  363.48
# Query size    10   1.75k      73     189  163.27  183.58   42.89  183.58
# 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 * FROM comments WHERE post_id IN (10000, 9999, 9998, 9997, 9996, 9995, 9994, 9993, 9991, 9990, 9989, 9988, 9987, 9986, 9985, 9983, 9982, 9981, 9980, 9979) ORDER BY `created_at` DESC\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: comments
#    partitions: NULL
#          type: range
# possible_keys: comments_post_id_index
#           key: comments_post_id_index
#       key_len: 4
#           ref: NULL
#          rows: 192
#      filtered: 100.00
#         Extra: Using index condition; Using filesort

# Query 2: 0.85 QPS, 0.00x concurrency, ID 0x395AE969FAFDA16C400891B7C96D9565 at byte 1242
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-10T05:15:39 to 2023-11-10T05:15:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         15      11
# Exec time      5     6ms    87us     1ms   586us   839us   255us   596us
# Lock time     13    16us     1us     3us     1us     1us       0     1us
# Rows sent     44   1.62k       1     191  150.45  183.58   67.48  183.58
# Rows examine  23   1.62k       1     191  150.45  183.58   67.48  183.58
# Query size    56   9.04k      36   1.04k  841.18 1012.63  360.86 1012.63
# 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 IN (112, 792, 149, 89, 876, 517, 455, 945, 618, 304, 775, 878, 161, 963, 649, 92, 544, 740, 37, 476/*... omitted 192 items ...*/)\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: users
#    partitions: NULL
#          type: range
# possible_keys: PRIMARY
#           key: PRIMARY
#       key_len: 4
#           ref: NULL
#          rows: 20
#      filtered: 100.00
#         Extra: Using where

# Query 3: 0 QPS, 0x concurrency, ID 0x77B9A1E48C001B0D14F8D79F7EAF30FF at byte 3207
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-10T05:15:39
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1       1
# Exec time      4     5ms     5ms     5ms     5ms     5ms       0     5ms
# Lock time      1     2us     2us     2us     2us     2us       0     2us
# Rows sent      0      12      12      12      12      12       0      12
# Rows examine   0      12      12      12      12      12       0      12
# Query size     0     150     150     150     150     150       0     150
# 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` = 115 ORDER BY `created_at` DESC LIMIT 20\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: not_banned_posts_without_imgdata
#    partitions: NULL
#          type: ref
# possible_keys: not_banned_posts_user_id_created_at_index
#           key: not_banned_posts_user_id_created_at_index
#       key_len: 4
#           ref: const
#          rows: 12
#      filtered: 100.00
#         Extra: NULL

# Query 4: 0.85 QPS, 0.00x concurrency, ID 0x9B36FBB6889AD40E391C181F37DD0542 at byte 16303
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-10T05:15:39 to 2023-11-10T05:15:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         15      11
# Exec time      2     3ms   118us   405us   273us   348us    79us   273us
# Lock time     10    12us       0     2us     1us     1us       0     1us
# Rows sent      4     167       0      20   15.18   19.46    7.04   18.53
# Rows examine  23   1.62k       0     192  150.73  183.58   67.52  183.58
# Query size    12   2.02k      98     214  188.27  212.52   44.81  212.52
# 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 `post_id`, COUNT(*) AS `count` FROM `comments` WHERE `post_id` IN (11060, 10000, 9999, 9998, 9997, 9996, 9995, 9994, 9993, 9991, 9990, 9989, 9988, 9987, 9986, 9985, 9983, 9982, 9981, 9980) GROUP BY `post_id`\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: comments
#    partitions: NULL
#          type: range
# possible_keys: comments_post_id_index
#           key: comments_post_id_index
#       key_len: 4
#           ref: NULL
#          rows: 188
#      filtered: 100.00
#         Extra: Using where; Using index

netdata

http://localhost:19991/#menu_apps_submenu_cpu;after=1699593339702;before=1699593415672

pprof

go tool pprof -http=0.0.0.0:1080 http://isucon1/pprof/11100215