11250343 - Githubissues

saza-ku commented 10 months ago

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

4500 ぐらい？

saza-ku commented 10 months ago

isucon1

alp

+-------+--------+------------------------------------------+--------+--------+---------+--------+--------+
| COUNT | METHOD |                   URI                    |  MIN   |  MAX   |   SUM   |  AVG   |  P99   |
+-------+--------+------------------------------------------+--------+--------+---------+--------+--------+
| 271   | GET    | /api/livestream/.+/livecomment           | 0.008  | 1.992  | 150.856 | 0.557  | 1.736  |
| 302   | GET    | /api/livestream/.+/reaction              | 0.008  | 1.536  | 121.328 | 0.402  | 1.460  |
| 1193  | GET    | /api/user/.+/icon                        | 0.004  | 1.276  | 104.620 | 0.088  | 0.544  |
| 34    | GET    | /api/livestream/.+                       | 0.004  | 4.720  | 88.961  | 2.616  | 4.720  |
| 328   | POST   | /api/livestream/.+/livecomment           | 0.008  | 0.828  | 52.172  | 0.159  | 0.480  |
| 129   | POST   | /api/livestream/.+                       | 0.064  | 0.984  | 47.049  | 0.365  | 0.896  |
| 24    | POST   | /api/livestream/.+/moderate              | 0.272  | 3.248  | 37.494  | 1.562  | 3.248  |
| 238   | POST   | /api/register                            | 0.016  | 0.720  | 36.425  | 0.153  | 0.688  |
| 2     | GET    | /api/user/.+/statistics                  | 11.548 | 20.001 | 31.549  | 15.774 | 20.001 |
| 243   | POST   | /api/icon                                | 0.012  | 0.948  | 30.756  | 0.127  | 0.632  |
| 256   | POST   | /api/livestream/.+/reaction              | 0.004  | 0.736  | 27.356  | 0.107  | 0.472  |
| 236   | POST   | /api/login                               | 0.004  | 0.776  | 16.105  | 0.068  | 0.464  |
| 108   | GET    | /api/livestream                          | 0.032  | 0.556  | 14.160  | 0.131  | 0.432  |
| 52    | POST   | /api/livestream/.+/livecomment/.+/report | 0.004  | 0.664  | 8.800   | 0.169  | 0.664  |
| 74    | GET    | /api/tag                                 | 0.004  | 0.696  | 7.136   | 0.096  | 0.696  |
| 53    | GET    | /api/livestream/.+/report                | 0.004  | 0.608  | 4.860   | 0.092  | 0.608  |
| 38    | GET    | /api/livestream/.+/ngwords               | 0.008  | 0.328  | 3.788   | 0.100  | 0.328  |
| 28    | POST   | /api/livestream/.+/enter                 | 0.004  | 0.116  | 0.692   | 0.025  | 0.116  |
| 18    | DELETE | /api/livestream/.+/exit                  | 0.004  | 0.092  | 0.468   | 0.026  | 0.092  |
| 2     | GET    | /api/livestream/.+/statistics            | 0.024  | 0.028  | 0.052   | 0.026  | 0.028  |
| 2     | GET    | /api/user/.+/theme                       | 0.016  | 0.020  | 0.036   | 0.018  | 0.020  |
+-------+--------+------------------------------------------+--------+--------+---------+--------+--------+

slow query

explain

Reading from STDIN ...

# No events processed.

netdata

http://localhost:19991/#menu_services;after=1700883793270;before=1700883869282

pprof

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

saza-ku commented 10 months ago

isucon2

alp

+-------+--------+-------------------------+-------+--------+--------+--------+--------+
| COUNT | METHOD |           URI           |  MIN  |  MAX   |  SUM   |  AVG   |  P99   |
+-------+--------+-------------------------+-------+--------+--------+--------+--------+
| 5     | GET    | /api/user/.+/statistics | 2.552 | 20.004 | 65.144 | 13.029 | 20.004 |
| 27    | GET    | /api/livestream/.+      | 0.152 | 6.588  | 55.029 | 2.038  | 6.588  |
| 224   | POST   | /api/register           | 0.028 | 0.308  | 18.480 | 0.082  | 0.208  |
| 1330  | GET    | /api/user/.+/icon       | 0.004 | 0.248  | 14.368 | 0.011  | 0.036  |
| 218   | POST   | /api/icon               | 0.004 | 0.264  | 3.948  | 0.018  | 0.124  |
| 62    | GET    | /api/livestream         | 0.004 | 0.172  | 3.176  | 0.051  | 0.172  |
| 230   | POST   | /api/login              | 0.000 | 0.012  | 0.660  | 0.003  | 0.012  |
| 34    | GET    | /api/tag                | 0.000 | 0.016  | 0.100  | 0.003  | 0.016  |
| 4     | GET    | /api/user/.+/theme      | 0.004 | 0.008  | 0.016  | 0.004  | 0.008  |
| 1     | GET    | /api/payment            | 0.004 | 0.004  | 0.004  | 0.004  | 0.004  |
| 4     | GET    | /api/user/.+            | 0.004 | 0.004  | 0.004  | 0.001  | 0.004  |
+-------+--------+-------------------------+-------+--------+--------+--------+--------+

slow query

Count: 30377  Time=0.00s (54s)  Lock=0.00s (0s)  Rows=0.1 (1956), isudns[isudns]@2hosts
  SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=N and name='S' and domain_id=N

Count: 20771  Time=0.00s (33s)  Lock=0.00s (0s)  Rows=0.5 (10472), isudns[isudns]@2hosts
  SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=N and type='S' and name='S'

Count: 38100  Time=0.00s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@isucon1
  select kind,content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='S'

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

Count: 916  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=0.5 (458), isudns[isudns]@2hosts
  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: 87970  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@2hosts
  #

Count: 458  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@2hosts
  commit

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

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

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

explain

Reading from STDIN ...

# 11.2s user time, 40ms system time, 36.75M rss, 51.20M vsz
# Current date: Sat Nov 25 03:44:46 2023
# Hostname: ip-192-168-0-12
# Files: STDIN
# Overall: 181.35k total, 20 unique, 2.21k QPS, 1.21x concurrency ________
# Time range: 2023-11-25T03:43:13 to 2023-11-25T03:44:35
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            99s     1us    19ms   547us     2ms   850us    89us
# Lock time          152ms       0     4ms       0     1us    23us     1us
# Rows sent         13.04k       0       1    0.07    0.99    0.26       0
# Rows examine      80.33M       0   1.70k  464.49   1.61k  711.19       0
# Query size        14.09M       5     220   81.45  151.03   48.94  112.70

# Profile
# Rank Query ID                      Response time Calls R/Call V/M   Item
# ==== ============================= ============= ===== ====== ===== ====
#    1 0x42EF7D7D98FBCC9723BF896E... 54.1292 54.6% 30377 0.0018  0.00 SELECT records
#    2 0x3D83BC87F3B3A00D571FFC81... 34.0209 34.3% 20771 0.0016  0.00 SELECT records
#    3 0x22279D81D51006139E0C7640...  4.6349  4.7% 38100 0.0001  0.00 SELECT domains domainmetadata
#    4 0x859BBB7E9D760686137A9444...  2.1406  2.2%   458 0.0047  0.00 DELETE records
# MISC 0xMISC                         4.3027  4.3% 91642 0.0000   0.0 <16 ITEMS>

# Query 1: 405.03 QPS, 0.72x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 1100919
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:43:13 to 2023-11-25T03:44:28
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         16   30377
# Exec time     54     54s   702us    15ms     2ms     3ms   609us     2ms
# Lock time     24    37ms       0     1ms     1us     1us     8us     1us
# Rows sent     14   1.91k       0       1    0.06    0.99    0.24       0
# Rows examine  57  46.55M   1.25k   1.70k   1.57k   1.61k  100.01   1.53k
# Query size    29   4.14M     129     220  142.82  158.58   12.38  136.99
# String:
# Hosts        isucon1 (30155/99%), isucon2 (222/0%)
# 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='qd3j4bfd8wzwfir7ugf0.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='qd3j4bfd8wzwfir7ugf0.u.isucon.dev' and domain_id=4"] at /usr/bin/pt-query-digest line 7796.

# Query 2: 276.95 QPS, 0.45x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 4556730
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:43:13 to 2023-11-25T03:44:28
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         11   20771
# Exec time     34     34s   662us    16ms     2ms     2ms   554us     2ms
# Lock time     17    26ms       0     1ms     1us     1us     7us     1us
# Rows sent     78  10.23k       0       1    0.50    0.99    0.50    0.99
# Rows examine  39  31.80M   1.25k   1.70k   1.57k   1.61k  100.72   1.53k
# Query size    19   2.71M     126     219  136.79  151.03   11.85  124.25
# String:
# Hosts        isucon1 (20549/98%), isucon2 (222/1%)
# 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='66dv5rmzq7cy3x305eavsoy0.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='66dv5rmzq7cy3x305eavsoy0.u.isucon.dev'"] at /usr/bin/pt-query-digest line 7796.

# Query 3: 508 QPS, 0.06x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 6712498
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:43:13 to 2023-11-25T03:44:28
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         21   38100
# Exec time      4      5s    72us     9ms   121us   144us   148us   103us
# Lock time     40    62ms       0     3ms     1us     1us    19us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    29   4.14M     114     114     114     114       0     114
# String:
# Hosts        isucon1
# 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: 6.54 QPS, 0.03x concurrency, ID 0x859BBB7E9D760686137A944406874C4D at byte 6304513
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T03:43:18 to 2023-11-25T03:44:28
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     458
# Exec time      2      2s     2ms    19ms     5ms     9ms     2ms     4ms
# Lock time     15    24ms       0     4ms    52us     1us   424us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0 674.70k   1.25k   1.70k   1.47k   1.61k  129.68   1.46k
# Query size     0  38.24k      78      93   85.51   88.31    3.37   84.10
# String:
# Hosts        isucon1 (236/51%), isucon2 (222/48%)
# 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
delete from records where domain_id=4 and name='nanamihashimoto0.u.isucon.dev' and type='A'\G
# Converted for EXPLAIN
# EXPLAIN 
select * from  records where domain_id=4 and name='nanamihashimoto0.u.isucon.dev' and type='A'\G

netdata

http://localhost:19992/#menu_services;after=1700883793263;before=1700883873271

pprof

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

saza-ku commented 10 months ago

isucon3

alp

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

slow query

Count: 7949  Time=0.02s (142s)  Lock=0.00s (0s)  Rows=4.0 (31667), isucon[isucon]@2hosts
  SELECT * FROM livestream_tags WHERE livestream_id = N

Count: 14969  Time=0.01s (110s)  Lock=0.00s (0s)  Rows=0.8 (11796), isucon[isucon]@2hosts
  SELECT image FROM icons WHERE user_id = N

Count: 4631  Time=0.01s (29s)  Lock=0.00s (0s)  Rows=1.0 (4631), isucon[isucon]@2hosts
  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: 4632  Time=0.01s (27s)  Lock=0.00s (0s)  Rows=1.0 (4632), isucon[isucon]@2hosts
  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: 1124  Time=0.01s (16s)  Lock=0.00s (0s)  Rows=1.0 (1124), isucon[isucon]@isucon1
  SELECT slot FROM reservation_slots WHERE start_at = N AND end_at = N

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

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

Count: 172  Time=0.04s (6s)  Lock=0.00s (0s)  Rows=0.8 (135), isucon[isucon]@2hosts
  SELECT * FROM livestreams WHERE user_id = N

explain

Reading from STDIN ...

# 11.4s user time, 70ms system time, 39.17M rss, 53.49M vsz
# Current date: Sat Nov 25 03:44:51 2023
# Hostname: ip-192-168-0-13
# Files: STDIN
# Overall: 146.66k total, 66 unique, 1.71k QPS, 4.52x concurrency ________
# Time range: 2023-11-25T03:43:13 to 2023-11-25T03:44:39
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           389s     1us   310ms     3ms    18ms     7ms   131us
# Lock time          547ms       0    49ms     3us     1us   233us     1us
# Rows sent        193.52k       0   7.32k    1.35    1.96   33.39    0.99
# Rows examine     141.40M       0  14.02k 1011.03  10.29k   2.62k    1.96
# Query size        46.12M       6 175.32k  329.74  346.17   3.83k   40.45

# Profile
# Rank Query ID                     Response time  Calls  R/Call V/M   Ite
# ==== ============================ ============== ====== ====== ===== ===
#    1 0xF7144185D9A142A426A36DC... 142.3749 36.6%   7949 0.0179  0.01 SELECT livestream_tags
#    2 0x84B457C910C4A79FC9EBECB... 110.9264 28.5%  14969 0.0074  0.02 SELECT icons
#    3 0xF1B8EF06D6CA63B24BFF433...  29.7157  7.6%   4631 0.0064  0.01 SELECT users livestreams livecomments
#    4 0xDB74D52D39A7090F224C4DE...  27.6585  7.1%   4632 0.0060  0.01 SELECT users livestreams reactions
# MISC 0xMISC                        78.2106 20.1% 114474 0.0007   0.0 <62 ITEMS>

# Query 1: 105.99 QPS, 1.90x concurrency, ID 0xF7144185D9A142A426A36DC55C1D2623 at byte 10482017
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T03:43:13 to 2023-11-25T03:44:28
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          5    7949
# Exec time     36    142s     2ms    87ms    18ms    34ms    11ms    18ms
# Lock time      2    13ms       0     4ms     1us     1us    46us     1us
# Rows sent     15  30.92k       0      11    3.98    4.96    1.68    4.96
# Rows examine  60  85.00M  10.71k  11.24k  10.95k  10.80k  237.56  10.80k
# Query size     0 434.50k      54      56   55.97   54.21    0.11   54.21
# String:
# Hosts        isucon1 (5745/72%), isucon2 (2204/27%)
# 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 = 5421\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: 11684
#      filtered: 10.00
#         Extra: Using where

# Query 2: 199.59 QPS, 1.48x concurrency, ID 0x84B457C910C4A79FC9EBECB8B1065C66 at byte 14399890
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2023-11-25T03:43:13 to 2023-11-25T03:44:28
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         10   14969
# Exec time     28    111s    47us   253ms     7ms    21ms    11ms     3ms
# Lock time      2    14ms       0   621us       0     1us     5us     1us
# Rows sent      5  11.52k       0       1    0.79    0.99    0.41    0.99
# Rows examine   2   3.47M       0     451  243.11  420.77  143.31  271.23
# Query size     1 640.55k      41      44   43.82   42.48    0.27   42.48
# String:
# Hosts        isucon1 (11209/74%), isucon2 (3760/25%)
# 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: 448
#      filtered: 10.00
#         Extra: Using where

# Query 3: 61.75 QPS, 0.40x concurrency, ID 0xF1B8EF06D6CA63B24BFF433E06CCEB22 at byte 71350407
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T03:43:13 to 2023-11-25T03:44:28
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3    4631
# Exec time      7     30s     1ms    53ms     6ms    19ms     7ms     3ms
# Lock time      1    11ms       0     2ms     2us     1us    42us     1us
# Rows sent      2   4.52k       1       1       1       1       0       1
# Rows examine   6   9.52M   1.96k   2.50k   2.11k   2.50k  163.91   2.06k
# Query size     1 745.47k     163     166  164.84  158.58       0  158.58
# String:
# Hosts        isucon2 (3652/78%), isucon1 (979/21%)
# 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 = 123\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: 1325
#      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: 61.76 QPS, 0.37x concurrency, ID 0xDB74D52D39A7090F224C4DEEAF3028C9 at byte 46595812
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T03:43:13 to 2023-11-25T03:44:28
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3    4632
# Exec time      7     28s     1ms    46ms     6ms    18ms     7ms     2ms
# Lock time      1     8ms       0     1ms     1us     1us    19us     1us
# Rows sent      2   4.52k       1       1       1       1       0       1
# Rows examine   6   9.42M   1.96k   2.39k   2.08k   2.38k  139.56   2.06k
# Query size     1 655.16k     143     146  144.84  143.84    0.79  143.84
# String:
# Hosts        isucon2 (3652/78%), isucon1 (980/21%)
# 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 = 258\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: 1257
#      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=1700883793270;before=1700883873277

pprof

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

saza-ku / isucon13

11250343 #32

isucon1

alp

slow query

explain

netdata

pprof

isucon2

alp

slow query

explain

netdata

pprof

isucon3

alp

slow query

explain

netdata

pprof