11250340 - Githubissues

isucon1

alp

+-------+--------+------------------------------------------+-------+--------+---------+--------+--------+
| COUNT | METHOD |                   URI                    |  MIN  |  MAX   |   SUM   |  AVG   |  P99   |
+-------+--------+------------------------------------------+-------+--------+---------+--------+--------+
| 2666  | GET    | /api/user/.+/icon                        | 0.024 | 0.896  | 160.608 | 0.060  | 0.400  |
| 73    | GET    | /api/livestream/.+                       | 0.008 | 4.472  | 120.442 | 1.650  | 4.472  |
| 332   | GET    | /api/livestream/.+/livecomment           | 0.008 | 1.480  | 119.999 | 0.361  | 1.120  |
| 361   | GET    | /api/livestream/.+/reaction              | 0.004 | 1.308  | 108.233 | 0.300  | 1.052  |
| 7     | GET    | /api/user/.+/statistics                  | 3.448 | 20.000 | 102.028 | 14.575 | 20.000 |
| 37    | POST   | /api/livestream/.+/moderate              | 0.272 | 5.340  | 57.552  | 1.555  | 5.340  |
| 285   | POST   | /api/register                            | 0.016 | 0.804  | 50.465  | 0.177  | 0.576  |
| 342   | POST   | /api/livestream/.+/livecomment           | 0.008 | 1.020  | 45.883  | 0.134  | 0.428  |
| 139   | POST   | /api/livestream/.+                       | 0.028 | 0.852  | 43.840  | 0.315  | 0.776  |
| 313   | POST   | /api/livestream/.+/reaction              | 0.004 | 0.732  | 33.052  | 0.106  | 0.416  |
| 285   | POST   | /api/icon                                | 0.004 | 0.928  | 30.613  | 0.107  | 0.624  |
| 289   | POST   | /api/login                               | 0.004 | 0.900  | 20.428  | 0.071  | 0.660  |
| 87    | GET    | /api/livestream                          | 0.004 | 0.488  | 9.396   | 0.108  | 0.488  |
| 118   | GET    | /api/tag                                 | 0.004 | 0.584  | 8.985   | 0.076  | 0.500  |
| 51    | POST   | /api/livestream/.+/livecomment/.+/report | 0.008 | 0.420  | 8.828   | 0.173  | 0.420  |
| 70    | GET    | /api/livestream/.+/report                | 0.004 | 0.584  | 6.512   | 0.093  | 0.584  |
| 51    | GET    | /api/livestream/.+/ngwords               | 0.004 | 0.544  | 6.032   | 0.118  | 0.544  |
| 32    | POST   | /api/livestream/.+/enter                 | 0.004 | 0.672  | 2.872   | 0.090  | 0.672  |
| 22    | DELETE | /api/livestream/.+/exit                  | 0.004 | 0.416  | 1.492   | 0.068  | 0.416  |
| 6     | GET    | /api/user/.+/theme                       | 0.004 | 0.108  | 0.304   | 0.051  | 0.108  |
| 2     | GET    | /api/livestream/.+/statistics            | 0.028 | 0.028  | 0.056   | 0.028  | 0.028  |
| 4     | GET    | /api/user/.+                             | 0.004 | 0.004  | 0.008   | 0.002  | 0.004  |
| 1     | GET    | /api/payment                             | 0.004 | 0.004  | 0.004   | 0.004  | 0.004  |
| 2     | GET    | /.env                                    | 0.000 | 0.000  | 0.000   | 0.000  | 0.000  |
+-------+--------+------------------------------------------+-------+--------+---------+--------+--------+

slow query

Count: 25714  Time=0.00s (77s)  Lock=0.00s (0s)  Rows=0.1 (1993), 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: 17689  Time=0.00s (51s)  Lock=0.00s (0s)  Rows=0.5 (8880), 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: 32401  Time=0.00s (10s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@localhost
  select kind,content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='S'

Count: 75759  Time=0.00s (7s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@localhost
  #

Count: 282  Time=0.01s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@localhost
  delete from records where domain_id=N and name='S' and type='S'

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

Count: 282  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@localhost
  commit

Count: 282  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@localhost
  insert into records (content,ttl,prio,type,domain_id,disabled,name,ordername,auth) values ('S',N,N,'S',N,N,'S',NULL,N)

Count: 564  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@localhost
  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

Count: 282  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (282), isudns[isudns]@localhost
  select id,name,master,last_check,notified_serial,type,options,catalog,account from domains where name='S'

explain

Reading from STDIN ...

# 9.5s user time, 40ms system time, 36.75M rss, 51.18M vsz
# Current date: Sat Nov 25 03:41:38 2023
# Hostname: ip-192-168-0-11
# Files: STDIN
# Overall: 154.11k total, 20 unique, 1.93k QPS, 1.90x concurrency ________
# Time range: 2023-11-25T03:40:09 to 2023-11-25T03:41:29
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           152s     1us    23ms   984us     4ms     2ms   138us
# Lock time          177ms       0     5ms     1us     1us    22us       0
# Rows sent         11.17k       0       1    0.07    0.99    0.26       0
# Rows examine      60.62M       0   1.53k  412.46   1.46k  635.40       0
# Query size        11.91M       5     224   81.07  151.03   48.83   84.10

# Profile
# Rank Query ID                      Response time Calls R/Call V/M   Item
# ==== ============================= ============= ===== ====== ===== ====
#    1 0x42EF7D7D98FBCC9723BF896E... 77.5465 51.1% 25714 0.0030  0.00 SELECT records
#    2 0x3D83BC87F3B3A00D571FFC81... 51.6336 34.0% 17689 0.0029  0.00 SELECT records
#    3 0x22279D81D51006139E0C7640... 10.0790  6.6% 32401 0.0003  0.00 SELECT domains domainmetadata
#    4 0x8F7679D452333ED3C7D60D22...  7.5845  5.0% 71956 0.0001  0.00 ADMIN RESET STMT
# MISC 0xMISC                         4.9426  3.3%  6348 0.0008   0.0 <16 ITEMS>

# Query 1: 329.67 QPS, 0.99x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 28641730
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:40:09 to 2023-11-25T03:41:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         16   25714
# Exec time     51     78s   713us    21ms     3ms     6ms     2ms     2ms
# Lock time     36    64ms       0     5ms     2us     1us    44us     1us
# Rows sent     17   1.95k       0       1    0.08    0.99    0.27       0
# Rows examine  58  35.24M   1.25k   1.52k   1.40k   1.46k   71.95   1.39k
# Query size    29   3.50M     129     224  142.71  158.58   12.34  136.99
# String:
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us
#  10us
# 100us  #
#   1ms  ################################################################
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'records'\G
#    SHOW CREATE TABLE `records`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and name='*.u.isucon.dev' and domain_id=4\G
# EXPLAIN failed: DBD::mysql::st execute failed: Table 'isupipe.records' doesn't exist [for Statement "EXPLAIN SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and name='*.u.isucon.dev' and domain_id=4"] at /usr/bin/pt-query-digest line 7796.

# Query 2: 226.78 QPS, 0.66x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 32840855
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:40:09 to 2023-11-25T03:41:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         11   17689
# Exec time     34     52s   673us    23ms     3ms     6ms     2ms     2ms
# Lock time     18    34ms       0     2ms     1us     1us    20us     1us
# Rows sent     77   8.67k       0       1    0.50    0.99    0.50    0.99
# Rows examine  39  24.23M   1.25k   1.52k   1.40k   1.46k   72.49   1.39k
# Query size    19   2.31M     126     223  136.71  151.03   11.76  124.25
# String:
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us
#  10us
# 100us  #
#   1ms  ################################################################
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'records'\G
#    SHOW CREATE TABLE `records`\G
# EXPLAIN 
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='u57t581vm2v4jcmjhlrq2ztpw0kyvl0.u.isucon.dev'\G
# EXPLAIN failed: DBD::mysql::st execute failed: Table 'isupipe.records' doesn't exist [for Statement "EXPLAIN SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='u57t581vm2v4jcmjhlrq2ztpw0kyvl0.u.isucon.dev'"] at /usr/bin/pt-query-digest line 7796.

# Query 3: 415.40 QPS, 0.13x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 19096368
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:40:09 to 2023-11-25T03:41:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         21   32401
# Exec time      6     10s    84us    14ms   311us   972us   510us   159us
# Lock time     43    76ms       0     2ms     2us     1us    21us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    29   3.52M     114     114     114     114       0     114
# String:
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  ###
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'domains'\G
#    SHOW CREATE TABLE `domains`\G
#    SHOW TABLE STATUS LIKE 'domainmetadata'\G
#    SHOW CREATE TABLE `domainmetadata`\G
# EXPLAIN 
select kind,content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='u.isucon.dev'\G
# EXPLAIN failed: DBD::mysql::st execute failed: Table 'isupipe.domains' doesn't exist [for Statement "EXPLAIN select kind,content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='u.isucon.dev'"] at /usr/bin/pt-query-digest line 7796.

# Query 4: 922.51 QPS, 0.10x concurrency, ID 0x8F7679D452333ED3C7D60D22131CEFD4 at byte 38981586
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:40:09 to 2023-11-25T03:41:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         46   71956
# Exec time      4      8s     9us    16ms   105us   287us   320us    44us
# 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    19   2.26M      33      33      33      33       0      33
# String:
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us  #
#  10us  ################################################################
# 100us  ##########
#   1ms  #
#  10ms  #
# 100ms
#    1s
#  10s+
administrator command: Reset stmt\G

netdata

http://localhost:19991/#menu_services;after=1700883609554;before=1700883685772

pprof

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

isucon3

alp

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

slow query

Count: 9217  Time=0.01s (129s)  Lock=0.00s (0s)  Rows=4.1 (37544), isucon[isucon]@isucon1
  SELECT * FROM livestream_tags WHERE livestream_id = N

Count: 16828  Time=0.00s (41s)  Lock=0.00s (0s)  Rows=0.8 (13831), isucon[isucon]@isucon1
  SELECT image FROM icons WHERE user_id = N

Count: 5023  Time=0.01s (35s)  Lock=0.00s (0s)  Rows=1.0 (5023), isucon[isucon]@isucon1
  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: 5026  Time=0.01s (33s)  Lock=0.00s (0s)  Rows=1.0 (5026), isucon[isucon]@isucon1
  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: 1236  Time=0.01s (16s)  Lock=0.00s (0s)  Rows=1.0 (1236), isucon[isucon]@isucon1
  SELECT slot FROM reservation_slots WHERE start_at = N AND end_at = N

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

Count: 340  Time=0.03s (8s)  Lock=0.00s (0s)  Rows=0.4 (152), isucon[isucon]@isucon1
  SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = N AND livestream_id = N

Count: 55890  Time=0.00s (8s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@isucon1
  DELETE FROM livecomments
  WHERE

explain

Reading from STDIN ...

# 13.8s user time, 70ms system time, 38.68M rss, 53.14M vsz
# Current date: Sat Nov 25 03:41:48 2023
# Hostname: ip-192-168-0-13
# Files: STDIN
# Overall: 184.30k total, 66 unique, 2.14k QPS, 3.61x concurrency ________
# Time range: 2023-11-25T03:40:09 to 2023-11-25T03:41:35
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           310s     1us    66ms     2ms    12ms     5ms   131us
# Lock time          308ms       0    33ms     1us     1us    93us     1us
# Rows sent        237.92k       0   7.32k    1.32    1.96   32.07    0.99
# Rows examine     157.43M       0  14.04k  895.68  10.29k   2.53k    1.96
# Query size        42.66M       6 175.32k  242.71  346.17   2.71k   42.48

# Profile
# Rank Query ID                     Response time  Calls  R/Call V/M   Ite
# ==== ============================ ============== ====== ====== ===== ===
#    1 0xF7144185D9A142A426A36DC... 129.6597 41.8%   9217 0.0141  0.00 SELECT livestream_tags
#    2 0x84B457C910C4A79FC9EBECB...  41.2701 13.3%  16828 0.0025  0.00 SELECT icons
#    3 0xF1B8EF06D6CA63B24BFF433...  35.9373 11.6%   5023 0.0072  0.01 SELECT users livestreams livecomments
#    4 0xDB74D52D39A7090F224C4DE...  33.8037 10.9%   5026 0.0067  0.01 SELECT users livestreams reactions
# MISC 0xMISC                        69.7509 22.5% 148204 0.0005   0.0 <62 ITEMS>

# Query 1: 118.17 QPS, 1.66x concurrency, ID 0xF7144185D9A142A426A36DC55C1D2623 at byte 68786943
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:40:09 to 2023-11-25T03:41:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          5    9217
# Exec time     41    130s     2ms    55ms    14ms    26ms     8ms    14ms
# Lock time      6    20ms       0     8ms     2us     1us    91us     1us
# Rows sent     15  36.66k       0      11    4.07    4.96    1.64    4.96
# Rows examine  62  98.88M  10.71k  11.29k  10.99k  10.80k  207.62  10.80k
# Query size     1 503.82k      53      56   55.97   54.21    0.13   54.21
# String:
# Hosts        isucon1
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  #############################
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'livestream_tags'\G
#    SHOW CREATE TABLE `livestream_tags`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM livestream_tags WHERE livestream_id = 7603\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: 11237
#      filtered: 10.00
#         Extra: Using where

# Query 2: 215.74 QPS, 0.53x concurrency, ID 0x84B457C910C4A79FC9EBECB8B1065C66 at byte 79875406
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:40:09 to 2023-11-25T03:41:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          9   16828
# Exec time     13     41s    46us    56ms     2ms     9ms     3ms     1ms
# Lock time      5    16ms       0   287us       0     1us     2us     1us
# Rows sent      5  13.51k       0       1    0.82    0.99    0.38    0.99
# Rows examine   1   2.05M       0     274  127.81  258.32   81.83  124.25
# Query size     1 720.47k      41      44   43.84   42.48    0.25   42.48
# String:
# Hosts        isucon1
# Users        isucon
# Query_time distribution
#   1us
#  10us  ########
# 100us  ##################################
#   1ms  ################################################################
#  10ms  #####
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'icons'\G
#    SHOW CREATE TABLE `icons`\G
# EXPLAIN 
SELECT image FROM icons WHERE user_id = 1035\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: 274
#      filtered: 10.00
#         Extra: Using where

# Query 3: 64.40 QPS, 0.46x concurrency, ID 0xF1B8EF06D6CA63B24BFF433E06CCEB22 at byte 73086708
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T03:40:09 to 2023-11-25T03:41:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2    5023
# Exec time     11     36s     1ms    47ms     7ms    20ms     7ms     3ms
# Lock time      6    19ms       0     5ms     3us     1us   102us     1us
# Rows sent      2   4.91k       1       1       1       1       0       1
# Rows examine   6  10.42M   1.96k   2.47k   2.12k   2.38k  162.94   2.06k
# Query size     1 808.63k     163     166  164.85  158.58       0  158.58
# 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 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 = 178\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: 1336
#      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

# Query 4: 64.44 QPS, 0.43x concurrency, ID 0xDB74D52D39A7090F224C4DEEAF3028C9 at byte 68774377
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T03:40:09 to 2023-11-25T03:41:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2    5026
# Exec time     10     34s     1ms    46ms     7ms    19ms     6ms     3ms
# Lock time     10    32ms       0    14ms     6us     1us   211us     1us
# Rows sent      2   4.91k       1       1       1       1       0       1
# Rows examine   6  10.40M   1.96k   2.44k   2.12k   2.38k  162.93   2.06k
# Query size     1 710.95k     143     146  144.85  143.84    0.76  143.84
# 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 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 = 550\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: r
#    partitions: NULL
#          type: ALL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 1314
#      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.r.livestream_id
#          rows: 1
#      filtered: 10.00
#         Extra: Using where

netdata

http://localhost:19993/#menu_services;after=1700883609548;before=1700883689555

pprof

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

saza-ku / isucon13

11250340 #30

isucon2

alp

slow query

explain

netdata

pprof

isucon1

alp

slow query

explain

netdata

pprof

isucon3

alp

slow query

explain

netdata

pprof