Skip to content
This repository has been archived by the owner on Nov 26, 2024. It is now read-only.

11250700 #70

Open
saza-ku opened this issue Nov 25, 2023 · 3 comments
Open

11250700 #70

saza-ku opened this issue Nov 25, 2023 · 3 comments
Labels
measure 計測結果

Comments

@saza-ku
Copy link
Owner

saza-ku commented Nov 25, 2023

No description provided.

@saza-ku saza-ku added the measure 計測結果 label Nov 25, 2023
@saza-ku
Copy link
Owner Author

saza-ku commented Nov 25, 2023

isucon1

alp

+-------+--------+------------------------------------------+-------+-------+-------+-------+-------+
| COUNT | METHOD |                   URI                    |  MIN  |  MAX  |  SUM  |  AVG  |  P99  |
+-------+--------+------------------------------------------+-------+-------+-------+-------+-------+
| 2     | GET    | /api/user/.+/statistics                  | 0.028 | 0.032 | 0.060 | 0.030 | 0.032 |
| 2     | GET    | /api/livestream/.+/statistics            | 0.028 | 0.028 | 0.056 | 0.028 | 0.028 |
| 1     | POST   | /api/livestream/reservation              | 0.024 | 0.024 | 0.024 | 0.024 | 0.024 |
| 1     | POST   | /api/register                            | 0.020 | 0.020 | 0.020 | 0.020 | 0.020 |
| 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 |
| 2     | POST   | /api/livestream/.+/livecomment           | 0.004 | 0.004 | 0.008 | 0.004 | 0.004 |
| 2     | POST   | /api/login                               | 0.000 | 0.004 | 0.004 | 0.002 | 0.004 |
| 1     | POST   | /api/livestream/.+/enter                 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 1     | GET    | /api/user/.+                             | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 1     | GET    | /api/tag                                 | 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=1700895622054;before=1700895698142

pprof

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

@saza-ku
Copy link
Owner Author

saza-ku commented Nov 25, 2023

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=1700895622053;before=1700895702060

pprof

go tool pprof -http=0.0.0.0:1080 http://isucon2/pprof/11250700

@saza-ku
Copy link
Owner Author

saza-ku commented Nov 25, 2023

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: 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=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: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=9.0 (18), isucon[isucon]@isucon1
  SELECT * FROM livestreams WHERE user_id = N

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: 18  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.2 (4), isucon[isucon]@isucon1
  SELECT * FROM livecomments WHERE livestream_id = N

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: 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 ...

# 130ms user time, 30ms system time, 36.62M rss, 50.81M vsz
# Current date: Sat Nov 25 07:01:42 2023
# Hostname: ip-192-168-0-13
# Files: STDIN
# Overall: 188 total, 36 unique, 26.86 QPS, 0.01x concurrency ____________
# Time range: 2023-11-25T07:00:22 to 2023-11-25T07:00:29
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           99ms    14us    10ms   526us     2ms     1ms    66us
# Lock time          114us       0     2us       0     1us       0     1us
# Rows sent         22.95k       0   7.32k  125.02  621.67  785.41    0.99
# Rows examine     114.03k       0   8.55k  621.09   1.96k   1.44k    0.99
# Query size        10.97k       6     416   59.76  158.58   52.64   44.60

# Profile
# Rank Query ID                        Response time Calls R/Call V/M   It
# ==== =============================== ============= ===== ====== ===== ==
#    1 0x050F7D44808F43E5D33D0B90BC...  0.0164 16.5%     2 0.0082  0.00 SELECT livestreams
#    2 0x7F9C0C0BA9473953B723EE16C0...  0.0103 10.4%     1 0.0103  0.00 SELECT reservation_slots
#    3 0xD00CB321F396631866985B8902...  0.0094  9.5%     2 0.0047  0.00 SELECT users livestreams reactions
#    4 0x9EAD6C0CE525E3693EE27FFC9A...  0.0089  9.0%     2 0.0045  0.00 SELECT livestreams
# MISC 0xMISC                           0.0539 54.5%   181 0.0003   0.0 <32 ITEMS>

# Query 1: 0 QPS, 0x concurrency, ID 0x050F7D44808F43E5D33D0B90BCF57CC9 at byte 45267
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T07:00:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1       2
# Exec time     16    16ms     8ms     8ms     8ms     8ms   190us     8ms
# Lock time      1     2us     1us     1us     1us     1us       0     1us
# Rows sent     63  14.64k   7.32k   7.32k   7.32k   7.32k       0   7.32k
# Rows examine  12  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: 7187
#      filtered: 100.00
#         Extra: NULL

# Query 2: 0 QPS, 0x concurrency, ID 0x7F9C0C0BA9473953B723EE16C08655F1 at byte 0
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T07:00:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0       1
# Exec time     10    10ms    10ms    10ms    10ms    10ms       0    10ms
# Lock time      0     1us     1us     1us     1us     1us       0     1us
# Rows sent      0       7       7       7       7       7       0       7
# Rows examine   7   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: 8593
#      filtered: 11.11
#         Extra: Using where

# Query 3: 0 QPS, 0x concurrency, ID 0xD00CB321F396631866985B89023C5836 at byte 3089
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T07:00:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1       2
# Exec time      9     9ms     4ms     6ms     5ms     6ms     1ms     5ms
# Lock time      1     2us     1us     1us     1us     1us       0     1us
# Rows sent      5   1.24k     632     633  632.50     633    0.71  632.50
# Rows examine   5   5.87k   2.93k   2.94k   2.94k   2.94k    6.36   2.94k
# Query size     2     320     160     160     160     160       0     160
# 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 'reactions'\G
#    SHOW CREATE TABLE `reactions`\G
# EXPLAIN 
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\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: r
#    partitions: NULL
#          type: ALL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 1004
#      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.r.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

# Query 4: 0 QPS, 0x concurrency, ID 0x9EAD6C0CE525E3693EE27FFC9AAF05A3 at byte 39083
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T07:00:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1       2
# Exec time      9     9ms     4ms     4ms     4ms     4ms    18us     4ms
# Lock time      1     2us     1us     1us     1us     1us       0     1us
# Rows sent      0      18       9       9       9       9       0       9
# Rows examine  12  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: 7187
#      filtered: 10.00
#         Extra: Using where

netdata

http://localhost:19993/#menu_services;after=1700895622057;before=1700895702065

pprof

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

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
measure 計測結果
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@saza-ku