11250415 - Githubissues

isucon1

alp

+-------+--------+------------------------------------------+-------+-------+---------+-------+-------+
| COUNT | METHOD |                   URI                    |  MIN  |  MAX  |   SUM   |  AVG  |  P99  |
+-------+--------+------------------------------------------+-------+-------+---------+-------+-------+
| 9264  | GET    | /api/user/.+/icon                        | 0.004 | 0.252 | 168.512 | 0.018 | 0.112 |
| 981   | GET    | /api/livestream/.+/livecomment           | 0.004 | 0.720 | 153.072 | 0.156 | 0.572 |
| 1011  | GET    | /api/livestream/.+/reaction              | 0.004 | 0.716 | 143.140 | 0.142 | 0.556 |
| 292   | GET    | /api/livestream/.+                       | 0.004 | 1.040 | 132.916 | 0.455 | 0.976 |
| 1086  | POST   | /api/livestream/.+/livecomment           | 0.004 | 0.292 | 68.736  | 0.063 | 0.176 |
| 527   | POST   | /api/register                            | 0.004 | 0.396 | 54.436  | 0.103 | 0.228 |
| 223   | POST   | /api/livestream/.+                       | 0.040 | 0.512 | 42.440  | 0.190 | 0.396 |
| 900   | POST   | /api/livestream/.+/reaction              | 0.000 | 0.204 | 29.448  | 0.033 | 0.128 |
| 357   | GET    | /api/livestream                          | 0.004 | 0.228 | 19.836  | 0.056 | 0.184 |
| 540   | POST   | /api/icon                                | 0.004 | 0.240 | 16.436  | 0.030 | 0.156 |
| 250   | GET    | /api/livestream/.+/ngwords               | 0.008 | 0.200 | 11.244  | 0.045 | 0.152 |
| 532   | POST   | /api/login                               | 0.004 | 0.184 | 10.504  | 0.020 | 0.108 |
| 330   | GET    | /api/livestream/.+/report                | 0.008 | 0.200 | 7.760   | 0.024 | 0.140 |
| 170   | POST   | /api/livestream/.+/moderate              | 0.004 | 0.220 | 4.764   | 0.028 | 0.144 |
| 211   | GET    | /api/tag                                 | 0.004 | 0.400 | 4.660   | 0.022 | 0.152 |
| 57    | POST   | /api/livestream/.+/livecomment/.+/report | 0.004 | 0.136 | 2.888   | 0.051 | 0.136 |
| 16    | GET    | /api/user/.+/statistics                  | 0.032 | 0.272 | 2.756   | 0.172 | 0.272 |
| 95    | POST   | /api/livestream/.+/enter                 | 0.004 | 0.124 | 2.156   | 0.023 | 0.124 |
| 84    | DELETE | /api/livestream/.+/exit                  | 0.004 | 0.116 | 1.392   | 0.017 | 0.116 |
| 14    | GET    | /api/user/.+/livestream                  | 0.024 | 0.104 | 0.924   | 0.066 | 0.104 |
| 5     | GET    | /api/livestream/.+/statistics            | 0.028 | 0.160 | 0.508   | 0.102 | 0.160 |
| 15    | GET    | /api/user/.+/theme                       | 0.004 | 0.072 | 0.304   | 0.020 | 0.072 |
| 4     | GET    | /api/user/.+                             | 0.004 | 0.004 | 0.012   | 0.003 | 0.004 |
| 1     | GET    | /api/payment                             | 0.004 | 0.004 | 0.004   | 0.004 | 0.004 |
+-------+--------+------------------------------------------+-------+-------+---------+-------+-------+

slow query

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

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=6.0 (6), isucon[isucon]@localhost
  show databases

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=12.0 (12), isucon[isucon]@localhost
  show tables

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=10.0 (10), isucon[isucon]@localhost
  SELECT * FROM reservation_slots limit N

explain

Reading from STDIN ...

# 130ms user time, 20ms system time, 36.12M rss, 50.48M vsz
# Current date: Sat Nov 25 04:17:06 2023
# Hostname: ip-192-168-0-11
# Files: STDIN
# Overall: 3 total, 3 unique, 0 QPS, 0x concurrency ______________________
# Time range: all events occurred at 2023-11-25T04:16:26
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            5ms   349us     3ms     2ms     3ms     1ms     1ms
# Lock time           11us     1us     5us     3us     4us     1us     4us
# Rows sent             28       6      12    9.33   11.95    2.57    9.83
# Rows examine          73      10      38   24.33   36.69   10.99   24.84
# Query size            65      11      40   21.67   38.53   12.41   13.83

# Profile
# Rank Query ID                           Response time Calls R/Call V/M  
# ==== ================================== ============= ===== ====== =====
#    1 0x751417D45B8E80EE5CBA2034458B5BC9  0.0032 63.6%     1 0.0032  0.00 SHOW DATABASES
#    2 0x489B4CEB2F4301A7132628303F99240D  0.0015 29.5%     1 0.0015  0.00 SHOW TABLES
#    3 0x1779FF747826CFCABC11E93E680CDA04  0.0003  6.9%     1 0.0003  0.00 SELECT reservation_slots

# Query 1: 0 QPS, 0x concurrency, ID 0x751417D45B8E80EE5CBA2034458B5BC9 at byte 0
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T04:16:26
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         33       1
# Exec time     63     3ms     3ms     3ms     3ms     3ms       0     3ms
# Lock time     45     5us     5us     5us     5us     5us       0     5us
# Rows sent     21       6       6       6       6       6       0       6
# Rows examine  34      25      25      25      25      25       0      25
# Query size    21      14      14      14      14      14       0      14
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
show databases\G

# Query 2: 0 QPS, 0x concurrency, ID 0x489B4CEB2F4301A7132628303F99240D at byte 222
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T04:16:26
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         33       1
# Exec time     29     1ms     1ms     1ms     1ms     1ms       0     1ms
# Lock time     45     5us     5us     5us     5us     5us       0     5us
# Rows sent     42      12      12      12      12      12       0      12
# Rows examine  52      38      38      38      38      38       0      38
# Query size    16      11      11      11      11      11       0      11
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
show tables\G

# Query 3: 0 QPS, 0x concurrency, ID 0x1779FF747826CFCABC11E93E680CDA04 at byte 2744
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T04:16:26
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         33       1
# Exec time      6   349us   349us   349us   349us   349us       0   349us
# Lock time      9     1us     1us     1us     1us     1us       0     1us
# Rows sent     35      10      10      10      10      10       0      10
# Rows examine  13      10      10      10      10      10       0      10
# Query size    61      40      40      40      40      40       0      40
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'reservation_slots'\G
#    SHOW CREATE TABLE `isupipe`.`reservation_slots`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM reservation_slots limit 10\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: reservation_slots
#    partitions: NULL
#          type: ALL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 8773
#      filtered: 100.00
#         Extra: NULL

netdata

http://localhost:19991/#menu_services;after=1700885750451;before=1700885826481

pprof

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

isucon2

alp

+-------+--------+-----+-------+-------+-------+-------+-------+
| COUNT | METHOD | URI |  MIN  |  MAX  |  SUM  |  AVG  |  P99  |
+-------+--------+-----+-------+-------+-------+-------+-------+
| 1     | GET    | /   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
+-------+--------+-----+-------+-------+-------+-------+-------+

slow query

Count: 34311  Time=0.00s (56s)  Lock=0.00s (0s)  Rows=0.1 (4398), isudns[isudns]@isucon1
  SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=N and name='S' and domain_id=N

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

Count: 42442  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: 525  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@isucon1
  delete from records where domain_id=N and name='S' and type='S'

Count: 1050  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=0.5 (525), isudns[isudns]@isucon1
  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: 94655  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@isucon1
  #

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

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

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

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

explain

Reading from STDIN ...

# 12.2s user time, 70ms system time, 36.75M rss, 51.07M vsz
# Current date: Sat Nov 25 04:17:25 2023
# Hostname: ip-192-168-0-12
# Files: STDIN
# Overall: 200.37k total, 20 unique, 2.44k QPS, 1.26x concurrency ________
# Time range: 2023-11-25T04:15:50 to 2023-11-25T04:17:12
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           104s     1us    14ms   517us     2ms   753us    89us
# Lock time          152ms       0     5ms       0     1us    18us     1us
# Rows sent         17.27k       0       1    0.09    0.99    0.28       0
# Rows examine      88.95M       0   1.76k  465.52   1.61k  698.22       0
# Query size        15.77M       5     220   82.51  151.03   48.81  112.70

# Profile
# Rank Query ID                      Response time Calls R/Call V/M   Item
# ==== ============================= ============= ===== ====== ===== ====
#    1 0x42EF7D7D98FBCC9723BF896E... 56.5377 54.5% 34311 0.0016  0.00 SELECT records
#    2 0x3D83BC87F3B3A00D571FFC81... 37.2013 35.9% 24229 0.0015  0.00 SELECT records
#    3 0x22279D81D51006139E0C7640...  4.6970  4.5% 42442 0.0001  0.00 SELECT domains domainmetadata
#    4 0x859BBB7E9D760686137A9444...  1.9110  1.8%   525 0.0036  0.00 DELETE records
# MISC 0xMISC                         3.3656  3.2% 98862 0.0000   0.0 <16 ITEMS>

# Query 1: 504.57 QPS, 0.83x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 37870663
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:15:50 to 2023-11-25T04:16:58
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         17   34311
# Exec time     54     57s   701us    14ms     2ms     2ms   432us     2ms
# Lock time     26    40ms       0     1ms     1us     1us     5us     1us
# Rows sent     24   4.29k       0       1    0.13    0.99    0.33       0
# Rows examine  57  50.77M   1.25k   1.76k   1.52k   1.69k  141.90   1.46k
# Query size    29   4.65M     129     220  142.21  158.58   12.04  136.99
# String:
# Hosts        isucon1
# 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='xjook2d7vcpi1qu0.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='xjook2d7vcpi1qu0.u.isucon.dev' and domain_id=4"] at /usr/bin/pt-query-digest line 7796.

# Query 2: 356.31 QPS, 0.55x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 37871250
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:15:50 to 2023-11-25T04:16:58
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         12   24229
# Exec time     35     37s   658us    12ms     2ms     2ms   423us     1ms
# Lock time     17    27ms       0    23us     1us     1us       0     1us
# Rows sent     69  11.95k       0       1    0.50    0.99    0.50    0.99
# Rows examine  40  35.86M   1.25k   1.76k   1.52k   1.69k  142.15   1.46k
# Query size    19   3.14M     126     219  136.06  151.03   11.47  124.25
# String:
# Hosts        isucon1
# 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='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='u.isucon.dev'"] at /usr/bin/pt-query-digest line 7796.

# Query 3: 624.15 QPS, 0.07x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 49856416
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:15:50 to 2023-11-25T04:16:58
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         21   42442
# Exec time      4      5s    71us     4ms   110us   125us    78us    98us
# Lock time     41    63ms       0     2ms     1us     1us    14us     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.61M     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: 7.72 QPS, 0.03x concurrency, ID 0x859BBB7E9D760686137A944406874C4D at byte 46239159
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:15:50 to 2023-11-25T04:16:58
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     525
# Exec time      1      2s     2ms     9ms     4ms     4ms   728us     3ms
# Lock time     12    20ms       0     5ms    37us     1us   338us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0 790.57k   1.25k   1.76k   1.51k   1.69k  149.06   1.46k
# Query size     0  43.83k      78      93   85.50   88.31    3.27   84.10
# String:
# Hosts        isucon1
# 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='shota110.u.isucon.dev' and type='A'\G
# Converted for EXPLAIN
# EXPLAIN 
select * from  records where domain_id=4 and name='shota110.u.isucon.dev' and type='A'\G

netdata

http://localhost:19992/#menu_services;after=1700885750460;before=1700885830474

pprof

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

isucon3

alp

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

slow query

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

Count: 1086  Time=0.02s (20s)  Lock=0.00s (0s)  Rows=0.1 (55), isucon[isucon]@isucon1
  SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = N AND livestream_id = N

Count: 2101  Time=0.01s (20s)  Lock=0.00s (0s)  Rows=1.0 (2101), isucon[isucon]@isucon1
  SELECT slot FROM reservation_slots WHERE start_at = N AND end_at = N

Count: 64343  Time=0.00s (16s)  Lock=0.00s (0s)  Rows=0.9 (58821), isucon[isucon]@isucon1
  SELECT icon_path FROM icons WHERE user_id = N

Count: 162187  Time=0.00s (13s)  Lock=0.00s (0s)  Rows=1.0 (162187), isucon[isucon]@isucon1
  SELECT * FROM tags WHERE id = N

Count: 387  Time=0.02s (8s)  Lock=0.00s (0s)  Rows=1.1 (417), isucon[isucon]@isucon1
  SELECT * FROM livestreams WHERE user_id = N

Count: 35439  Time=0.00s (5s)  Lock=0.00s (0s)  Rows=4.6 (162192), isucon[isucon]@isucon1
  SELECT * FROM livestream_tags WHERE livestream_id = N

Count: 54560  Time=0.00s (5s)  Lock=0.00s (0s)  Rows=1.0 (54560), isucon[isucon]@isucon1
  SELECT * FROM users WHERE id = N

Count: 250  Time=0.02s (5s)  Lock=0.00s (0s)  Rows=0.0 (7), isucon[isucon]@isucon1
  SELECT * FROM ng_words WHERE user_id = N AND livestream_id = N ORDER BY created_at DESC

Count: 219  Time=0.02s (5s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@isucon1
  UPDATE reservation_slots SET slot = slot - N WHERE start_at >= N AND end_at <= N

explain

Reading from STDIN ...

# 27.1s user time, 170ms system time, 37.88M rss, 52.10M vsz
# Current date: Sat Nov 25 04:17:41 2023
# Hostname: ip-192-168-0-13
# Files: STDIN
# Overall: 452.01k total, 64 unique, 5.38k QPS, 1.94x concurrency ________
# Time range: 2023-11-25T04:15:50 to 2023-11-25T04:17:14
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           163s     1us    62ms   361us   761us     2ms    80us
# Lock time          405ms       0     9ms       0     1us    18us     1us
# Rows sent        913.79k       0   7.46k    2.07    4.96   40.14    0.99
# Rows examine     129.56M       0  14.01k  300.56   1.33k   1.07k    0.99
# Query size        16.83M       6     444   39.04   54.21   20.15   34.95

# Profile
# Rank Query ID                     Response time Calls  R/Call V/M   Item
# ==== ============================ ============= ====== ====== ===== ====
#    1 0x38BC86A45F31C6B1EE32467... 45.0947 27.6%  55100 0.0008  0.00 SELECT themes
#    2 0x64CC8A4E8E4B39020337559... 20.3930 12.5%   1086 0.0188  0.00 SELECT ng_words
#    3 0x59F1B6DD8D9FEC059E55B3B... 20.1082 12.3%   2101 0.0096  0.00 SELECT reservation_slots
#    4 0xB4FC1F99A422ACB1A54E6E2... 16.4953 10.1%  64343 0.0003  0.00 SELECT icons
# MISC 0xMISC                       61.1939 37.5% 329378 0.0002   0.0 <60 ITEMS>

# Query 1: 810.29 QPS, 0.66x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 54964059
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:15:50 to 2023-11-25T04:16:58
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         12   55100
# Exec time     27     45s   240us    23ms   818us     2ms   794us   596us
# Lock time     12    50ms       0     1ms       0     1us    10us     1us
# Rows sent      5  53.81k       1       1       1       1       0       1
# Rows examine  51  66.86M    1000   1.49k   1.24k   1.39k  145.66   1.20k
# Query size    12   2.15M      38      41   40.94   40.45    0.44   40.45
# String:
# Hosts        isucon1
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ##########
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'themes'\G
#    SHOW CREATE TABLE `themes`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM themes WHERE user_id = 1036\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: themes
#    partitions: NULL
#          type: ALL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 1524
#      filtered: 10.00
#         Extra: Using where

# Query 2: 15.97 QPS, 0.30x concurrency, ID 0x64CC8A4E8E4B390203375597CE4D611F at byte 53044077
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:15:50 to 2023-11-25T04:16:58
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    1086
# Exec time     12     20s     4ms    53ms    19ms    33ms     8ms    17ms
# Lock time      0   935us       0    41us       0     1us     1us     1us
# Rows sent      0      55       0       2    0.05       0    0.24       0
# Rows examine  11  14.85M  14.00k  14.01k  14.01k  13.78k       0  13.78k
# Query size     0 104.99k      97      99   99.00   97.36    0.20   97.36
# String:
# Hosts        isucon1
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ########
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'ng_words'\G
#    SHOW CREATE TABLE `ng_words`\G
# EXPLAIN 
SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = 1049 AND livestream_id = 7562\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: ng_words
#    partitions: NULL
#          type: ALL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 14628
#      filtered: 1.00
#         Extra: Using where

# Query 3: 30.90 QPS, 0.30x concurrency, ID 0x59F1B6DD8D9FEC059E55B3BFD624E8C3 at byte 9640734
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:15:50 to 2023-11-25T04:16:58
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    2101
# Exec time     12     20s     2ms    36ms    10ms    20ms     6ms     8ms
# Lock time      0     4ms       0     2ms     1us     1us    35us     1us
# Rows sent      0   2.05k       1       1       1       1       0       1
# Rows examine  13  17.55M   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     1 176.45k      86      86      86      86       0      86
# String:
# Hosts        isucon1
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  #############################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'reservation_slots'\G
#    SHOW CREATE TABLE `reservation_slots`\G
# EXPLAIN 
SELECT slot FROM reservation_slots WHERE start_at = 1701090000 AND end_at = 1701093600\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: reservation_slots
#    partitions: NULL
#          type: ALL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 8773
#      filtered: 1.00
#         Extra: Using where

# Query 4: 946.22 QPS, 0.24x concurrency, ID 0xB4FC1F99A422ACB1A54E6E2F01B84879 at byte 31294341
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T04:15:50 to 2023-11-25T04:16:58
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         14   64343
# Exec time     10     16s    45us    16ms   256us   541us   412us   185us
# Lock time     14    59ms       0     2ms       0     1us     9us     1us
# Rows sent      6  57.44k       0       1    0.91    0.99    0.28    0.99
# Rows examine  12  16.13M       0     516  262.88  463.90  142.75  258.32
# Query size    17   2.94M      45      48   47.92   46.83    0.19   46.83
# 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 icon_path FROM icons WHERE user_id = 1046\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: 516
#      filtered: 10.00
#         Extra: Using where

netdata

http://localhost:19993/#menu_services;after=1700885750452;before=1700885830459

pprof

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

saza-ku / isucon13

11250415 #40

41

isucon1

alp

slow query

explain

netdata

pprof

isucon2

alp

slow query

explain

netdata

pprof

isucon3

alp

slow query

explain

netdata

pprof