search

saza-ku / isucon13

0 stars 0 forks source link

11250810 #92

Open saza-ku opened 10 months ago

saza-ku commented 10 months ago

isucon1

alp

+-------+--------+------------------------------------------+-------+-------+-------+-------+-------+
| COUNT | METHOD |                   URI                    |  MIN  |  MAX  |  SUM  |  AVG  |  P99  |
+-------+--------+------------------------------------------+-------+-------+-------+-------+-------+
| 2     | GET    | /api/livestream/.+/statistics            | 0.028 | 0.032 | 0.060 | 0.030 | 0.032 |
| 2     | GET    | /api/user/.+/statistics                  | 0.024 | 0.028 | 0.052 | 0.026 | 0.028 |
| 2     | POST   | /api/livestream/reservation              | 0.004 | 0.028 | 0.032 | 0.016 | 0.028 |
| 3     | POST   | /api/livestream/.+/reaction              | 0.004 | 0.004 | 0.012 | 0.004 | 0.004 |
| 2     | POST   | /api/livestream/.+/livecomment/.+/report | 0.004 | 0.008 | 0.012 | 0.006 | 0.008 |
| 4     | GET    | /api/tag                                 | 0.004 | 0.004 | 0.012 | 0.003 | 0.004 |
| 2     | POST   | /api/livestream/.+/livecomment           | 0.004 | 0.004 | 0.008 | 0.004 | 0.004 |
| 3     | POST   | /api/login                               | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 1     | POST   | /api/livestream/.+/enter                 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 1     | POST   | /api/register                            | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 1     | GET    | /api/user/.+                             | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
+-------+--------+------------------------------------------+-------+-------+-------+-------+-------+

slow query

explain

Reading from STDIN ...

# No events processed.

netdata

http://localhost:19991/#menu_services;after=1700899819872;before=1700899895983

pprof

go tool pprof -http=0.0.0.0:1080 http://isucon1/pprof/11250810
saza-ku commented 10 months ago

isucon2

alp

+-------+--------+-----+-----+-----+-----+-----+-----+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+-----+-----+-----+-----+-----+-----+
+-------+--------+-----+-----+-----+-----+-----+-----+

slow query

explain

Reading from STDIN ...

# No events processed.

netdata

http://localhost:19992/#menu_services;after=1700899819875;before=1700899899882

pprof

go tool pprof -http=0.0.0.0:1080 http://isucon2/pprof/11250810
saza-ku commented 10 months ago

isucon3

alp

+-------+--------+-----+-----+-----+-----+-----+-----+
| COUNT | METHOD | URI | MIN | MAX | SUM | AVG | P99 |
+-------+--------+-----+-----+-----+-----+-----+-----+
+-------+--------+-----+-----+-----+-----+-----+-----+

slow query

Count: 2  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=7496.0 (14992), isucon[isucon]@isucon1
  SELECT * FROM livestreams

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=9.0 (18), isucon[isucon]@isucon1
  SELECT * FROM livestreams WHERE user_id = N

Count: 1  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=7.0 (7), isucon[isucon]@isucon1
  SELECT * FROM reservation_slots WHERE start_at >= N AND end_at <= N FOR UPDATE

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=642.0 (1284), isucon[isucon]@isucon1
  SELECT u.id AS id, IFNULL(SUM(l2.tip), N) AS count FROM users u
  INNER JOIN livestreams l ON l.user_id = u.id  
  INNER JOIN livecomments l2 ON l2.livestream_id = l.id
  GROUP BY u.id

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=632.5 (1265), isucon[isucon]@isucon1
  SELECT u.id AS id, COUNT(*) AS count FROM users u
  INNER JOIN livestreams l ON l.user_id = u.id
  INNER JOIN reactions r ON r.livestream_id = l.id
  GROUP BY u.id

Count: 1  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@isucon1
  UPDATE reservation_slots SET slot = slot - N WHERE start_at >= N AND end_at <= N

Count: 18  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (18), isucon[isucon]@isucon1
  SELECT * FROM themes WHERE user_id = N

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=933.5 (1867), isucon[isucon]@isucon1
  SELECT l.id AS id, IFNULL(SUM(l2.tip), N) AS count FROM livestreams l INNER JOIN livecomments l2 ON l.id = l2.livestream_id GROUP BY l.id

explain

Reading from STDIN ...

# 150ms user time, 10ms system time, 36.50M rss, 50.87M vsz
# Current date: Sat Nov 25 08:11:40 2023
# Hostname: ip-192-168-0-13
# Files: STDIN
# Overall: 192 total, 41 unique, 4.27 QPS, 0.00x concurrency _____________
# Time range: 2023-11-25T08:10:19 to 2023-11-25T08:11:04
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           86ms    14us     8ms   445us     2ms     1ms    66us
# Lock time          105us       0     5us       0     1us       0     1us
# Rows sent         24.25k       0   7.32k  129.35  621.67  779.53    0.99
# Rows examine      94.83k       0   8.55k  505.76   1.96k   1.43k    0.99
# Query size        10.75k       6     416   57.36  158.58   54.53   40.45

# Profile
# Rank Query ID                        Response time Calls R/Call V/M   It
# ==== =============================== ============= ===== ====== ===== ==
#    1 0x050F7D44808F43E5D33D0B90BC...  0.0152 17.8%     2 0.0076  0.00 SELECT livestreams
#    2 0x9EAD6C0CE525E3693EE27FFC9A...  0.0090 10.5%     2 0.0045  0.00 SELECT livestreams
#    3 0x7F9C0C0BA9473953B723EE16C0...  0.0085  9.9%     1 0.0085  0.00 SELECT reservation_slots
#    4 0x57DABFD7079AF832BCC382ACA0...  0.0079  9.3%     2 0.0040  0.00 SELECT users livestreams livecomments
# MISC 0xMISC                           0.0449 52.5%   185 0.0002   0.0 <37 ITEMS>

# Query 1: 2 QPS, 0.02x concurrency, ID 0x050F7D44808F43E5D33D0B90BCF57CC9 at byte 41536
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T08:10:19 to 2023-11-25T08:10:20
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1       2
# Exec time     17    15ms     8ms     8ms     8ms     8ms    88us     8ms
# Lock time      1     2us     1us     1us     1us     1us       0     1us
# Rows sent     60  14.64k   7.32k   7.32k   7.32k   7.32k       0   7.32k
# Rows examine  15  14.64k   7.32k   7.32k   7.32k   7.32k       0   7.32k
# Query size     0      50      25      25      25      25       0      25
# String:
# Hosts        isucon1
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'livestreams'\G
#    SHOW CREATE TABLE `livestreams`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM livestreams\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: livestreams
#    partitions: NULL
#          type: ALL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 7395
#      filtered: 100.00
#         Extra: NULL

# Query 2: 2 QPS, 0.01x concurrency, ID 0x9EAD6C0CE525E3693EE27FFC9AAF05A3 at byte 4439
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T08:10:19 to 2023-11-25T08:10:20
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1       2
# Exec time     10     9ms     4ms     4ms     4ms     4ms     1us     4ms
# Lock time      0     1us       0     1us       0     1us       0       0
# Rows sent      0      18       9       9       9       9       0       9
# Rows examine  15  14.64k   7.32k   7.32k   7.32k   7.32k       0   7.32k
# Query size     0      88      44      44      44      44       0      44
# String:
# Hosts        isucon1
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'livestreams'\G
#    SHOW CREATE TABLE `livestreams`\G
# EXPLAIN 
SELECT * FROM livestreams WHERE user_id = 22\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: livestreams
#    partitions: NULL
#          type: ALL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 7395
#      filtered: 10.00
#         Extra: Using where

# Query 3: 0 QPS, 0x concurrency, ID 0x7F9C0C0BA9473953B723EE16C08655F1 at byte 221
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T08:10:19
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0       1
# Exec time      9     8ms     8ms     8ms     8ms     8ms       0     8ms
# Lock time      0     1us     1us     1us     1us     1us       0     1us
# Rows sent      0       7       7       7       7       7       0       7
# Rows examine   9   8.55k   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0      96      96      96      96      96       0      96
# String:
# Hosts        isucon1
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'reservation_slots'\G
#    SHOW CREATE TABLE `reservation_slots`\G
# EXPLAIN 
SELECT * FROM reservation_slots WHERE start_at >= 1700874000 AND end_at <= 1700899200 FOR UPDATE\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: reservation_slots
#    partitions: NULL
#          type: ALL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 8773
#      filtered: 11.11
#         Extra: Using where

# Query 4: 2 QPS, 0.01x concurrency, ID 0x57DABFD7079AF832BCC382ACA0818B43 at byte 34617
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T08:10:19 to 2023-11-25T08:10:20
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1       2
# Exec time      9     8ms     4ms     4ms     4ms     4ms   293us     4ms
# Lock time      1     2us     1us     1us     1us     1us       0     1us
# Rows sent      5   1.25k     642     642     642     642       0     642
# Rows examine   6   5.87k   2.93k   2.94k   2.94k   2.94k    4.24   2.94k
# Query size     3     360     180     180     180     180       0     180
# String:
# Hosts        isucon1
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'users'\G
#    SHOW CREATE TABLE `users`\G
#    SHOW TABLE STATUS LIKE 'livestreams'\G
#    SHOW CREATE TABLE `livestreams`\G
#    SHOW TABLE STATUS LIKE 'livecomments'\G
#    SHOW CREATE TABLE `livecomments`\G
# EXPLAIN 
SELECT u.id AS id, IFNULL(SUM(l2.tip), 0) AS count FROM users u
    INNER JOIN livestreams l ON l.user_id = u.id    
    INNER JOIN livecomments l2 ON l2.livestream_id = l.id
    GROUP BY u.id\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: l2
#    partitions: NULL
#          type: ALL
# possible_keys: comm_livestream_id_created_idx
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 1003
#      filtered: 100.00
#         Extra: Using temporary
# *************************** 2. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: l
#    partitions: NULL
#          type: eq_ref
# possible_keys: PRIMARY
#           key: PRIMARY
#       key_len: 8
#           ref: isupipe.l2.livestream_id
#          rows: 1
#      filtered: 100.00
#         Extra: NULL
# *************************** 3. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: u
#    partitions: NULL
#          type: eq_ref
# possible_keys: PRIMARY,uniq_user_name
#           key: PRIMARY
#       key_len: 8
#           ref: isupipe.l.user_id
#          rows: 1
#      filtered: 100.00
#         Extra: Using index

netdata

http://localhost:19993/#menu_services;after=1700899819878;before=1700899899886

pprof

go tool pprof -http=0.0.0.0:1080 http://isucon3/pprof/11250810