11250243 - Githubissues

saza-ku commented 1 year ago

https://github.com/Saza-ku/isucon13/pull/16

3300 ぐらい

saza-ku commented 1 year ago

isucon3

alp

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

slow query

Count: 2  Time=0.02s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  RENAME USER 'S'@'S' to 'S'@'S'

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (2), isucon[isucon]@localhost
  select @@version_comment limit N

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  #

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Quit

explain

Reading from STDIN ...

# 120ms user time, 20ms system time, 36.00M rss, 50.43M vsz
# Current date: Sat Nov 25 02:44:55 2023
# Hostname: ip-192-168-0-13
# Files: STDIN
# Overall: 6 total, 3 unique, 0.23 QPS, 0.00x concurrency ________________
# Time range: 2023-11-25T02:44:04 to 2023-11-25T02:44:30
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           42ms     1us    37ms     7ms    36ms    13ms     2ms
# Lock time           20us       0    15us     3us    14us     5us       0
# Rows sent              2       0       1    0.33    0.99    0.47       0
# Rows examine           2       0       1    0.33    0.99    0.47       0
# Query size           214      27      48   35.67   46.83    8.76   31.70

# Profile
# Rank Query ID                           Response time Calls R/Call V/M  
# ==== ================================== ============= ===== ====== =====
#    1 0xD8FBF7E199B815D9A4C331CF767EC263  0.0408 98.0%     2 0.0204  0.03 RENAME
#    2 0x0E7680C04FF2596BE3A3649C5FAC418D  0.0008  2.0%     2 0.0004  0.00 SELECT
#    3 0xEDBC971AEC392917AA353644DE4C4CB4  0.0000  0.0%     2 0.0000  0.00 ADMIN QUIT

# Query 1: 0.08 QPS, 0.00x concurrency, ID 0xD8FBF7E199B815D9A4C331CF767EC263 at byte 927
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.03
# Time range: 2023-11-25T02:44:04 to 2023-11-25T02:44:30
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         33       2
# Exec time     97    41ms     4ms    37ms    20ms    37ms    23ms    20ms
# Lock time    100    20us     5us    15us    10us    15us     7us    10us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    44      96      48      48      48      48       0      48
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
RENAME USER 'isudns'@'localhost' to 'isudns'@'%'\G

# Query 2: 0.08 QPS, 0.00x concurrency, ID 0x0E7680C04FF2596BE3A3649C5FAC418D at byte 0
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T02:44:04 to 2023-11-25T02:44:30
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         33       2
# Exec time      1   831us    81us   750us   415us   750us   473us   415us
# Lock time      0       0       0       0       0       0       0       0
# Rows sent    100       2       1       1       1       1       0       1
# Rows examine 100       2       1       1       1       1       0       1
# Query size    29      64      32      32      32      32       0      32
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  ################################################################
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# EXPLAIN /*!50100 PARTITIONS*/
select @@version_comment limit 1\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: NULL
#    partitions: NULL
#          type: NULL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: NULL

# Query 3: 0.08 QPS, 0.00x concurrency, ID 0xEDBC971AEC392917AA353644DE4C4CB4 at byte 480
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T02:44:04 to 2023-11-25T02:44:30
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         33       2
# Exec time      0     4us     1us     3us     2us     3us     1us     2us
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    25      54      27      27      27      27       0      27
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us  ################################################################
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
administrator command: Quit\G

netdata

http://localhost:19993/#menu_services;after=1700880215128;before=1700880295135

pprof

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

saza-ku commented 1 year ago

isucon2

alp

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

slow query

Count: 2  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  RENAME USER 'S'@'S' to 'S'@'S'

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (2), isucon[isucon]@localhost
  select @@version_comment limit N

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  #

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Quit

explain

Reading from STDIN ...

# 130ms user time, 20ms system time, 36.00M rss, 50.43M vsz
# Current date: Sat Nov 25 02:44:55 2023
# Hostname: ip-192-168-0-12
# Files: STDIN
# Overall: 6 total, 3 unique, 0.23 QPS, 0.00x concurrency ________________
# Time range: 2023-11-25T02:44:04 to 2023-11-25T02:44:30
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           16ms     1us    10ms     3ms    10ms     4ms     3ms
# Lock time           24us       0    17us     4us    16us     6us       0
# Rows sent              2       0       1    0.33    0.99    0.47       0
# Rows examine           2       0       1    0.33    0.99    0.47       0
# Query size           214      27      48   35.67   46.83    8.76   31.70

# Profile
# Rank Query ID                           Response time Calls R/Call V/M  
# ==== ================================== ============= ===== ====== =====
#    1 0xD8FBF7E199B815D9A4C331CF767EC263  0.0143 91.5%     2 0.0072  0.00 RENAME
#    2 0x0E7680C04FF2596BE3A3649C5FAC418D  0.0013  8.5%     2 0.0007  0.00 SELECT
#    3 0xEDBC971AEC392917AA353644DE4C4CB4  0.0000  0.0%     2 0.0000  0.00 ADMIN QUIT

# Query 1: 0.08 QPS, 0.00x concurrency, ID 0xD8FBF7E199B815D9A4C331CF767EC263 at byte 927
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T02:44:04 to 2023-11-25T02:44:30
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         33       2
# Exec time     91    14ms     4ms    10ms     7ms    10ms     4ms     7ms
# Lock time    100    24us     7us    17us    12us    17us     7us    12us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    44      96      48      48      48      48       0      48
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
RENAME USER 'isudns'@'localhost' to 'isudns'@'%'\G

# Query 2: 0.08 QPS, 0.00x concurrency, ID 0x0E7680C04FF2596BE3A3649C5FAC418D at byte 0
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T02:44:04 to 2023-11-25T02:44:30
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         33       2
# Exec time      8     1ms    79us     1ms   666us     1ms   830us   666us
# Lock time      0       0       0       0       0       0       0       0
# Rows sent    100       2       1       1       1       1       0       1
# Rows examine 100       2       1       1       1       1       0       1
# Query size    29      64      32      32      32      32       0      32
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
# EXPLAIN /*!50100 PARTITIONS*/
select @@version_comment limit 1\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: NULL
#    partitions: NULL
#          type: NULL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: NULL

# Query 3: 0.08 QPS, 0.00x concurrency, ID 0xEDBC971AEC392917AA353644DE4C4CB4 at byte 480
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T02:44:04 to 2023-11-25T02:44:30
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         33       2
# Exec time      0     3us     1us     2us     1us     2us       0     1us
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    25      54      27      27      27      27       0      27
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us  ################################################################
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
administrator command: Quit\G

netdata

http://localhost:19992/#menu_services;after=1700880215147;before=1700880295155

pprof

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

saza-ku commented 1 year ago

isucon1

alp

+-------+--------+------------------------------------------+--------+--------+---------+--------+--------+
| COUNT | METHOD |                   URI                    |  MIN   |  MAX   |   SUM   |  AVG   |  P99   |
+-------+--------+------------------------------------------+--------+--------+---------+--------+--------+
| 1710  | GET    | /api/user/.+/icon                        | 0.004  | 1.216  | 160.396 | 0.094  | 0.708  |
| 48    | GET    | /api/livestream/.+                       | 0.008  | 7.348  | 130.008 | 2.708  | 7.348  |
| 193   | GET    | /api/livestream/.+/livecomment           | 0.004  | 3.204  | 124.676 | 0.646  | 2.932  |
| 226   | GET    | /api/livestream/.+/reaction              | 0.004  | 2.460  | 106.500 | 0.471  | 2.388  |
| 6     | GET    | /api/user/.+/statistics                  | 2.556  | 20.004 | 74.152  | 12.359 | 20.004 |
| 206   | POST   | /api/livestream/.+/livecomment           | 0.004  | 1.508  | 52.024  | 0.253  | 0.964  |
| 166   | POST   | /api/register                            | 0.008  | 1.288  | 49.168  | 0.296  | 1.212  |
| 18    | POST   | /api/livestream/.+/moderate              | 0.188  | 6.828  | 48.964  | 2.720  | 6.828  |
| 84    | POST   | /api/livestream/.+                       | 0.200  | 2.012  | 43.428  | 0.517  | 2.012  |
| 187   | POST   | /api/livestream/.+/reaction              | 0.008  | 0.976  | 31.596  | 0.169  | 0.608  |
| 163   | POST   | /api/icon                                | 0.012  | 1.072  | 30.524  | 0.187  | 1.040  |
| 171   | POST   | /api/login                               | 0.004  | 1.128  | 16.120  | 0.094  | 1.092  |
| 1     | POST   | /api/initialize                          | 15.736 | 15.736 | 15.736  | 15.736 | 15.736 |
| 67    | GET    | /api/livestream                          | 0.012  | 0.820  | 11.796  | 0.176  | 0.820  |
| 2     | GET    | /api/livestream/.+/statistics            | 5.580  | 5.665  | 11.245  | 5.623  | 5.665  |
| 45    | POST   | /api/livestream/.+/livecomment/.+/report | 0.012  | 0.848  | 11.168  | 0.248  | 0.848  |
| 38    | GET    | /api/livestream/.+/report                | 0.004  | 2.096  | 10.440  | 0.275  | 2.096  |
| 64    | GET    | /api/tag                                 | 0.004  | 0.984  | 8.152   | 0.127  | 0.984  |
| 27    | GET    | /api/livestream/.+/ngwords               | 0.008  | 0.908  | 6.420   | 0.238  | 0.908  |
| 13    | DELETE | /api/livestream/.+/exit                  | 0.008  | 0.688  | 1.360   | 0.105  | 0.688  |
| 21    | POST   | /api/livestream/.+/enter                 | 0.004  | 0.304  | 1.268   | 0.060  | 0.304  |
| 5     | GET    | /api/user/.+/theme                       | 0.024  | 0.292  | 0.540   | 0.108  | 0.292  |
| 1     | GET    | /api/payment                             | 0.004  | 0.004  | 0.004   | 0.004  | 0.004  |
| 6     | GET    | /api/user/.+                             | 0.000  | 0.004  | 0.004   | 0.001  | 0.004  |
| 1     | GET    | /account/login                           | 0.000  | 0.000  | 0.000   | 0.000  | 0.000  |
| 1     | GET    | /favicon.ico                             | 0.000  | 0.000  | 0.000   | 0.000  | 0.000  |
| 1     | GET    | /assets/ISUPipe_yoko_color-fc80178e.png  | 0.000  | 0.000  | 0.000   | 0.000  | 0.000  |
| 1     | GET    | /assets/login-dc4055a7.js                | 0.000  | 0.000  | 0.000   | 0.000  | 0.000  |
| 1     | GET    | /assets/index.esm-bd2deef7.js            | 0.000  | 0.000  | 0.000   | 0.000  | 0.000  |
| 1     | GET    | /assets/FormLabel-c342aa29.js            | 0.000  | 0.000  | 0.000   | 0.000  | 0.000  |
| 1     | GET    | /assets/index-55457d82.js                | 0.000  | 0.000  | 0.000   | 0.000  | 0.000  |
| 1     | GET    | /assets/index-5265c558.css               | 0.000  | 0.000  | 0.000   | 0.000  | 0.000  |
+-------+--------+------------------------------------------+--------+--------+---------+--------+--------+

slow query

Count: 4907  Time=0.03s (124s)  Lock=0.00s (0s)  Rows=3.3 (16029), isucon[isucon]@localhost
  SELECT * FROM livestream_tags WHERE livestream_id = N

Count: 4626  Time=0.01s (33s)  Lock=0.00s (0s)  Rows=0.2 (971), isudns[isudns]@localhost
  SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=N and name='S' and domain_id=N

Count: 8349  Time=0.00s (30s)  Lock=0.00s (0s)  Rows=0.7 (5702), isucon[isucon]@localhost
  SELECT image FROM icons WHERE user_id = N

Count: 3375  Time=0.01s (25s)  Lock=0.00s (0s)  Rows=1.0 (3375), isucon[isucon]@localhost
  SELECT IFNULL(SUM(l2.tip), N) FROM users u
  INNER JOIN livestreams l ON l.user_id = u.id  
  INNER JOIN livecomments l2 ON l2.livestream_id = l.id
  WHERE u.id = N

Count: 3378  Time=0.01s (24s)  Lock=0.00s (0s)  Rows=1.0 (3378), isucon[isucon]@localhost
  SELECT COUNT(*) FROM users u
  INNER JOIN livestreams l ON l.user_id = u.id
  INNER JOIN reactions r ON r.livestream_id = l.id
  WHERE u.id = N

Count: 3418  Time=0.01s (18s)  Lock=0.00s (0s)  Rows=0.5 (1756), isudns[isudns]@localhost
  SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=N and type='S' and name='S'

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

Count: 15284  Time=0.00s (14s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  DELETE FROM livecomments
  WHERE

explain

Reading from STDIN ...

# 19.8s user time, 280ms system time, 44.43M rss, 59.21M vsz
# Current date: Sat Nov 25 02:45:24 2023
# Hostname: ip-192-168-0-11
# Files: STDIN
# Overall: 174.38k total, 102 unique, 1.69k QPS, 5.18x concurrency _______
# Time range: 2023-11-25T02:43:35 to 2023-11-25T02:45:18
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           534s     1us   427ms     3ms    16ms     9ms   301us
# Lock time          664ms       0    64ms     3us     1us   221us     1us
# Rows sent        167.54k       0   7.32k    0.98    0.99   28.67    0.99
# Rows examine     140.71M       0  14.02k  846.08   2.06k   2.07k    1.96
# Query size        32.08M       5   1.94M  192.87  329.68   5.41k   54.21

# Profile
# Rank Query ID                     Response time  Calls  R/Call V/M   Ite
# ==== ============================ ============== ====== ====== ===== ===
#    1 0xF7144185D9A142A426A36DC... 177.5224 33.3%   5923 0.0300  0.01 SELECT livestream_tags
#    2 0x84B457C910C4A79FC9EBECB...  53.8416 10.1%  10494 0.0051  0.01 SELECT icons
#    3 0x42EF7D7D98FBCC9723BF896...  47.5078  8.9%   5957 0.0080  0.01 SELECT records
#    4 0xF1B8EF06D6CA63B24BFF433...  28.3764  5.3%   3491 0.0081  0.01 SELECT users livestreams livecomments
# MISC 0xMISC                       226.3613 42.4% 148520 0.0015   0.0 <98 ITEMS>

# Query 1: 67.31 QPS, 2.02x concurrency, ID 0xF7144185D9A142A426A36DC55C1D2623 at byte 30885672
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T02:43:50 to 2023-11-25T02:45:18
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3    5923
# Exec time     33    178s     2ms   114ms    30ms    65ms    21ms    30ms
# Lock time      3    22ms       0     7ms     3us     1us   102us     1us
# Rows sent     12  20.50k       0      11    3.54    4.96    1.87    4.96
# Rows examine  44  62.82M  10.71k  11.03k  10.86k  10.80k  163.50  10.80k
# Query size     0 323.69k      54      56   55.96   54.21    0.13   54.21
# String:
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  #####################
#  10ms  ################################################################
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestream_tags'\G
#    SHOW CREATE TABLE `isupipe`.`livestream_tags`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM livestream_tags WHERE livestream_id = 7522\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: livestream_tags
#    partitions: NULL
#          type: ALL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 11464
#      filtered: 10.00
#         Extra: Using where

# Query 2: 119.25 QPS, 0.61x concurrency, ID 0x84B457C910C4A79FC9EBECB8B1065C66 at byte 56363522
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T02:43:50 to 2023-11-25T02:45:18
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   10494
# Exec time     10     54s    64us    62ms     5ms    19ms     6ms     2ms
# Lock time      5    34ms       0     4ms     3us     1us    66us     1us
# Rows sent      4   7.54k       0       1    0.74    0.99    0.44    0.99
# Rows examine   0 762.18k       0     155   74.37  143.84   47.21   72.65
# Query size     1 448.42k      41      44   43.76   42.48    0.31   42.48
# String:
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us  #############
# 100us  ##########################
#   1ms  ################################################################
#  10ms  ########################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'icons'\G
#    SHOW CREATE TABLE `isupipe`.`icons`\G
# EXPLAIN 
SELECT image FROM icons WHERE user_id = 1018\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: icons
#    partitions: NULL
#          type: ALL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 155
#      filtered: 10.00
#         Extra: Using where

# Query 3: 67.69 QPS, 0.54x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 56495414
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T02:43:50 to 2023-11-25T02:45:18
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3    5957
# Exec time      8     48s   709us    60ms     8ms    22ms     7ms     6ms
# Lock time      2    17ms       0     3ms     2us     1us    49us     1us
# Rows sent      0   1.20k       0       1    0.21    0.99    0.40       0
# Rows examine   5   7.81M   1.25k   1.41k   1.34k   1.39k   48.19   1.33k
# Query size     2 823.12k     129     212  141.49  158.58   11.86  136.99
# String:
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us
#  10us
# 100us  #
#   1ms  ################################################################
#  10ms  ################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isudns` LIKE 'records'\G
#    SHOW CREATE TABLE `isudns`.`records`\G
# EXPLAIN 
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and name='*.u.isucon.dev' and domain_id=3\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: records
#    partitions: NULL
#          type: ALL
# possible_keys: domain_id
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 1443
#      filtered: 1.00
#         Extra: Using where

# Query 4: 39.67 QPS, 0.32x concurrency, ID 0xF1B8EF06D6CA63B24BFF433E06CCEB22 at byte 54322382
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T02:43:50 to 2023-11-25T02:45:18
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2    3491
# Exec time      5     28s     1ms    60ms     8ms    33ms    11ms     1ms
# Lock time      1     7ms       0   849us     1us     1us    21us     1us
# Rows sent      2   3.41k       1       1       1       1       0       1
# Rows examine   4   6.97M   1.96k   2.32k   2.05k   2.06k   96.83   1.96k
# Query size     1 561.88k     163     166  164.81  158.58    0.00  158.58
# String:
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  ###########################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'users'\G
#    SHOW CREATE TABLE `isupipe`.`users`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestreams'\G
#    SHOW CREATE TABLE `isupipe`.`livestreams`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livecomments'\G
#    SHOW CREATE TABLE `isupipe`.`livecomments`\G
# EXPLAIN 
SELECT IFNULL(SUM(l2.tip), 0) FROM users u
        INNER JOIN livestreams l ON l.user_id = u.id    
        INNER JOIN livecomments l2 ON l2.livestream_id = l.id
        WHERE u.id = 426\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: u
#    partitions: NULL
#          type: const
# possible_keys: PRIMARY
#           key: PRIMARY
#       key_len: 8
#           ref: const
#          rows: 1
#      filtered: 100.00
#         Extra: Using index
# *************************** 2. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: l2
#    partitions: NULL
#          type: ALL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 1203
#      filtered: 100.00
#         Extra: NULL
# *************************** 3. 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: 10.00
#         Extra: Using where

netdata

http://localhost:19991/#menu_services;after=1700880215136;before=1700880291292

pprof

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

saza-ku / isucon13

11250243 #14

isucon3

alp

slow query

explain

netdata

pprof

isucon2

alp

slow query

explain

netdata

pprof

isucon1

alp

slow query

explain

netdata

pprof