11250322 - Githubissues

isucon1

alp

+-------+--------+------------------------------------------+-------+--------+---------+--------+--------+
| COUNT | METHOD |                   URI                    |  MIN  |  MAX   |   SUM   |  AVG   |  P99   |
+-------+--------+------------------------------------------+-------+--------+---------+--------+--------+
| 96    | GET    | /api/livestream/.+                       | 0.008 | 4.044  | 167.700 | 1.747  | 4.044  |
| 2681  | GET    | /api/user/.+/icon                        | 0.004 | 0.732  | 163.837 | 0.061  | 0.408  |
| 332   | GET    | /api/livestream/.+/livecomment           | 0.004 | 1.508  | 118.273 | 0.356  | 1.296  |
| 365   | GET    | /api/livestream/.+/reaction              | 0.008 | 1.204  | 103.956 | 0.285  | 1.088  |
| 7     | GET    | /api/user/.+/statistics                  | 3.420 | 20.001 | 97.313  | 13.902 | 20.001 |
| 369   | POST   | /api/livestream/.+/livecomment           | 0.004 | 0.696  | 54.638  | 0.148  | 0.504  |
| 292   | POST   | /api/register                            | 0.004 | 1.080  | 51.977  | 0.178  | 0.708  |
| 132   | POST   | /api/livestream/.+                       | 0.068 | 1.072  | 44.745  | 0.339  | 0.880  |
| 312   | POST   | /api/livestream/.+/reaction              | 0.008 | 0.864  | 35.260  | 0.113  | 0.544  |
| 294   | POST   | /api/icon                                | 0.012 | 0.812  | 31.376  | 0.107  | 0.564  |
| 297   | POST   | /api/login                               | 0.004 | 1.112  | 18.680  | 0.063  | 0.424  |
| 114   | GET    | /api/livestream                          | 0.004 | 0.656  | 13.957  | 0.122  | 0.544  |
| 50    | POST   | /api/livestream/.+/livecomment/.+/report | 0.012 | 1.148  | 9.324   | 0.186  | 1.148  |
| 113   | GET    | /api/tag                                 | 0.004 | 0.660  | 7.828   | 0.069  | 0.468  |
| 94    | GET    | /api/livestream/.+/report                | 0.004 | 0.700  | 6.896   | 0.073  | 0.700  |
| 69    | GET    | /api/livestream/.+/ngwords               | 0.004 | 0.444  | 6.320   | 0.092  | 0.444  |
| 47    | POST   | /api/livestream/.+/moderate              | 0.012 | 0.320  | 3.660   | 0.078  | 0.320  |
| 34    | POST   | /api/livestream/.+/enter                 | 0.004 | 0.228  | 1.684   | 0.050  | 0.228  |
| 25    | DELETE | /api/livestream/.+/exit                  | 0.004 | 0.224  | 1.428   | 0.057  | 0.224  |
| 6     | GET    | /api/user/.+/theme                       | 0.012 | 0.288  | 0.436   | 0.073  | 0.288  |
| 12    | GET    | /api/livestream/.+/statistics            | 0.004 | 0.028  | 0.060   | 0.005  | 0.028  |
| 5     | GET    | /api/user/.+                             | 0.000 | 0.004  | 0.012   | 0.002  | 0.004  |
| 1     | GET    | /api/payment                             | 0.004 | 0.004  | 0.004   | 0.004  | 0.004  |
+-------+--------+------------------------------------------+-------+--------+---------+--------+--------+

slow query

Count: 25894  Time=0.00s (75s)  Lock=0.00s (0s)  Rows=0.1 (2139), 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: 17851  Time=0.00s (50s)  Lock=0.00s (0s)  Rows=0.5 (8960), 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: 32547  Time=0.00s (9s)  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: 77218  Time=0.00s (7s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@localhost
  #

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

Count: 289  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: 578  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=0.5 (289), 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: 578  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@localhost
  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

Count: 289  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: 289  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (289), 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.6s user time, 50ms system time, 36.75M rss, 51.13M vsz
# Current date: Sat Nov 25 03:23:32 2023
# Hostname: ip-192-168-0-11
# Files: STDIN
# Overall: 156.12k total, 20 unique, 1.98k QPS, 1.91x concurrency ________
# Time range: 2023-11-25T03:22:04 to 2023-11-25T03:23:23
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           151s     1us    35ms   966us     4ms     2ms   131us
# Lock time          219ms       0    21ms     1us     1us    72us       0
# Rows sent         11.41k       0       1    0.07    0.99    0.26       0
# Rows examine      61.32M       0   1.53k  411.83   1.46k  635.72       0
# Query size        12.02M       5     217   80.75  151.03   48.79   51.63

# Profile
# Rank Query ID                      Response time Calls R/Call V/M   Item
# ==== ============================= ============= ===== ====== ===== ====
#    1 0x42EF7D7D98FBCC9723BF896E... 75.8385 50.3% 25894 0.0029  0.00 SELECT records
#    2 0x3D83BC87F3B3A00D571FFC81... 51.0055 33.8% 17851 0.0029  0.00 SELECT records
#    3 0x22279D81D51006139E0C7640...  9.6085  6.4% 32547 0.0003  0.00 SELECT domains domainmetadata
#    4 0x8F7679D452333ED3C7D60D22...  7.5616  5.0% 73335 0.0001  0.00 ADMIN RESET STMT
# MISC 0xMISC                         6.8029  4.5%  6491 0.0010   0.0 <16 ITEMS>

# Query 1: 336.29 QPS, 0.98x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 27398642
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:22:04 to 2023-11-25T03:23:21
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         16   25894
# Exec time     50     76s   717us    19ms     3ms     6ms     2ms     2ms
# Lock time     20    44ms       0     1ms     1us     1us    15us     1us
# Rows sent     18   2.09k       0       1    0.08    0.99    0.27       0
# Rows examine  58  35.60M   1.25k   1.53k   1.41k   1.46k   72.87   1.39k
# Query size    29   3.52M     129     217  142.59  158.58   12.26  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='82bhewypvn1glmqsoulg3ku926ky40.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='82bhewypvn1glmqsoulg3ku926ky40.u.isucon.dev' and domain_id=4"] at /usr/bin/pt-query-digest line 7796.

# Query 2: 231.83 QPS, 0.66x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 29190666
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:22:04 to 2023-11-25T03:23:21
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         11   17851
# Exec time     33     51s   676us    22ms     3ms     6ms     2ms     2ms
# Lock time     13    30ms       0   943us     1us     1us    12us     1us
# Rows sent     76   8.75k       0       1    0.50    0.99    0.50    0.99
# Rows examine  40  24.53M   1.25k   1.53k   1.41k   1.46k   73.33   1.39k
# Query size    19   2.33M     126     216  136.59  151.03   11.70  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='gw2mx8do2diw0vgv8t3vob0.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='gw2mx8do2diw0vgv8t3vob0.u.isucon.dev'"] at /usr/bin/pt-query-digest line 7796.

# Query 3: 422.69 QPS, 0.12x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 25893867
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:22:04 to 2023-11-25T03:23:21
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         20   32547
# Exec time      6     10s    83us    14ms   295us   925us   476us   152us
# Lock time     32    71ms       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.54M     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: 952.40 QPS, 0.10x concurrency, ID 0x8F7679D452333ED3C7D60D22131CEFD4 at byte 29386996
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:22:04 to 2023-11-25T03:23:21
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         46   73335
# Exec time      5      8s     9us    14ms   103us   273us   315us    42us
# 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.31M      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=1700882524151;before=1700882600163

pprof

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

isucon3

alp

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

slow query

Count: 10210  Time=0.02s (161s)  Lock=0.00s (0s)  Rows=4.1 (41930), isucon[isucon]@isucon1
  SELECT * FROM livestream_tags WHERE livestream_id = N

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

Count: 4932  Time=0.01s (38s)  Lock=0.00s (0s)  Rows=1.0 (4932), 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: 4934  Time=0.01s (36s)  Lock=0.00s (0s)  Rows=1.0 (4934), 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: 1168  Time=0.01s (16s)  Lock=0.00s (0s)  Rows=1.0 (1168), isucon[isucon]@isucon1
  SELECT slot FROM reservation_slots WHERE start_at = N AND end_at = N

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

Count: 369  Time=0.03s (9s)  Lock=0.00s (0s)  Rows=0.0 (11), isucon[isucon]@isucon1
  SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = N AND livestream_id = N

Count: 2981  Time=0.00s (8s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@isucon1
  COMMIT

explain

Reading from STDIN ...

# 9.4s user time, 70ms system time, 38.78M rss, 53.09M vsz
# Current date: Sat Nov 25 03:23:37 2023
# Hostname: ip-192-168-0-13
# Files: STDIN
# Overall: 136.77k total, 64 unique, 1.63k QPS, 4.33x concurrency ________
# Time range: 2023-11-25T03:22:04 to 2023-11-25T03:23:28
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           363s     1us    85ms     3ms    17ms     6ms   103us
# Lock time          397ms       0    33ms     2us     1us   176us     1us
# Rows sent        248.26k       0   7.32k    1.86    4.96   37.17    0.99
# Rows examine     168.49M       0  14.01k   1.26k  10.80k   2.99k    0.99
# Query size        26.43M       6 175.32k  202.65  143.84   3.26k   34.95

# Profile
# Rank Query ID                      Response time  Calls R/Call V/M   Ite
# ==== ============================= ============== ===== ====== ===== ===
#    1 0xF7144185D9A142A426A36DC5... 161.5286 44.4% 10210 0.0158  0.00 SELECT livestream_tags
#    2 0x84B457C910C4A79FC9EBECB8...  53.4400 14.7% 17816 0.0030  0.00 SELECT icons
#    3 0xF1B8EF06D6CA63B24BFF433E...  38.6393 10.6%  4932 0.0078  0.01 SELECT users livestreams livecomments
#    4 0xDB74D52D39A7090F224C4DEE...  36.2912 10.0%  4934 0.0074  0.01 SELECT users livestreams reactions
# MISC 0xMISC                         73.5570 20.2% 98882 0.0007   0.0 <60 ITEMS>

# Query 1: 132.60 QPS, 2.10x concurrency, ID 0xF7144185D9A142A426A36DC55C1D2623 at byte 49569799
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:22:04 to 2023-11-25T03:23:21
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          7   10210
# Exec time     44    162s     2ms    48ms    16ms    27ms     8ms    16ms
# Lock time      2    11ms       0   835us     1us     1us     9us     1us
# Rows sent     16  40.95k       0      11    4.11    4.96    1.63    4.96
# Rows examine  64 109.47M  10.71k  11.26k  10.98k  10.80k  198.51  10.80k
# Query size     2 558.16k      53      56   55.98   54.21    0.09   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 = 7588\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: 11699
#      filtered: 10.00
#         Extra: Using where

# Query 2: 231.38 QPS, 0.69x concurrency, ID 0x84B457C910C4A79FC9EBECB8B1065C66 at byte 52680483
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:22:04 to 2023-11-25T03:23:21
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         13   17816
# Exec time     14     53s    48us    85ms     3ms    11ms     4ms     2ms
# Lock time      4    19ms       0     1ms     1us     1us     9us     1us
# Rows sent      5  14.48k       0       1    0.83    0.99    0.37    0.99
# Rows examine   1   2.24M       0     281  132.05  258.32   83.73  124.25
# Query size     2 762.98k      41      44   43.85   42.48    0.24   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 = 1040\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: 279
#      filtered: 10.00
#         Extra: Using where

# Query 3: 64.05 QPS, 0.50x concurrency, ID 0xF1B8EF06D6CA63B24BFF433E06CCEB22 at byte 24130106
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T03:22:04 to 2023-11-25T03:23:21
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3    4932
# Exec time     10     39s     1ms    47ms     8ms    20ms     7ms     6ms
# Lock time      3    14ms       0     4ms     2us     1us    70us     1us
# Rows sent      1   4.82k       1       1       1       1       0       1
# Rows examine   6  10.24M   1.96k   2.57k   2.13k   2.50k  173.14   2.06k
# Query size     2 793.97k     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 = 408\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: 1365
#      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.08 QPS, 0.47x concurrency, ID 0xDB74D52D39A7090F224C4DEEAF3028C9 at byte 18625652
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T03:22:04 to 2023-11-25T03:23:21
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3    4934
# Exec time      9     36s     1ms    41ms     7ms    19ms     6ms     5ms
# Lock time      3    12ms       0     3ms     2us     1us    53us     1us
# Rows sent      1   4.82k       1       1       1       1       0       1
# Rows examine   6  10.18M   1.96k   2.49k   2.11k   2.38k  157.27   2.06k
# Query size     2 697.93k     143     146  144.85  143.84    0.77  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 = 66\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: 1313
#      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=1700882524143;before=1700882604150

pprof

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

saza-ku / isucon13

11250322 #24

isucon2

alp

slow query

explain

netdata

pprof

isucon1

alp

slow query

explain

netdata

pprof

isucon3

alp

slow query

explain

netdata

pprof