11250723 - Githubissues

isucon1

alp

+-------+--------+------------------------------------------+-------+-------+---------+-------+-------+
| COUNT | METHOD |                   URI                    |  MIN  |  MAX  |   SUM   |  AVG  |  P99  |
+-------+--------+------------------------------------------+-------+-------+---------+-------+-------+
| 418   | GET    | /api/livestream/search                   | 0.068 | 1.128 | 188.003 | 0.450 | 0.968 |
| 3821  | GET    | /api/livestream/.+/livecomment           | 0.000 | 0.116 | 117.957 | 0.031 | 0.072 |
| 3854  | GET    | /api/livestream/.+/reaction              | 0.004 | 0.152 | 113.430 | 0.029 | 0.068 |
| 3594  | POST   | /api/livestream/.+/livecomment           | 0.004 | 0.124 | 93.390  | 0.026 | 0.076 |
| 3496  | POST   | /api/livestream/.+/reaction              | 0.004 | 0.132 | 82.108  | 0.023 | 0.068 |
| 562   | POST   | /api/register                            | 0.004 | 0.180 | 58.950  | 0.105 | 0.168 |
| 34474 | GET    | /api/user/.+/icon                        | 0.000 | 0.056 | 52.453  | 0.002 | 0.008 |
| 446   | POST   | /api/livestream/reservation              | 0.024 | 0.160 | 31.721  | 0.071 | 0.124 |
| 596   | POST   | /api/icon                                | 0.004 | 0.076 | 9.572   | 0.016 | 0.060 |
| 173   | GET    | /api/livestream                          | 0.004 | 0.148 | 8.932   | 0.052 | 0.132 |
| 36    | GET    | /api/user/.+/statistics                  | 0.028 | 0.392 | 7.320   | 0.203 | 0.392 |
| 568   | POST   | /api/login                               | 0.004 | 0.068 | 6.548   | 0.012 | 0.048 |
| 232   | POST   | /api/livestream/.+/moderate              | 0.000 | 0.068 | 5.684   | 0.024 | 0.060 |
| 449   | GET    | /api/livestream/.+/report                | 0.004 | 0.148 | 5.504   | 0.012 | 0.072 |
| 456   | GET    | /api/tag                                 | 0.004 | 0.084 | 5.424   | 0.012 | 0.056 |
| 339   | POST   | /api/livestream/.+/enter                 | 0.004 | 0.096 | 3.732   | 0.011 | 0.056 |
| 336   | GET    | /api/livestream/.+/ngwords               | 0.000 | 0.060 | 3.536   | 0.011 | 0.040 |
| 329   | DELETE | /api/livestream/.+/exit                  | 0.020 | 0.092 | 3.512   | 0.011 | 0.056 |
| 34    | GET    | /api/user/.+/livestream                  | 0.044 | 0.176 | 3.400   | 0.100 | 0.176 |
| 23    | GET    | /api/livestream/.+/statistics            | 0.028 | 0.184 | 2.956   | 0.129 | 0.184 |
| 58    | POST   | /api/livestream/.+/livecomment/.+/report | 0.004 | 0.060 | 1.784   | 0.031 | 0.060 |
| 35    | GET    | /api/user/.+/theme                       | 0.004 | 0.076 | 0.632   | 0.018 | 0.076 |
| 5     | GET    | /api/user/.+                             | 0.004 | 0.016 | 0.024   | 0.005 | 0.016 |
| 1     | GET    | /api/payment                             | 0.004 | 0.004 | 0.004   | 0.004 | 0.004 |
| 1     | GET    | /api/livestream/.+                       | 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=1.0 (1), isucon[isucon]@localhost
  select * from tags where id in (N)

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

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), isucon[isucon]@localhost
  select * from tags where id in (N,N)

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), isucon[isucon]@localhost
  SELECT DATABASE()

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

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Init DB

explain

Reading from STDIN ...

# 130ms user time, 10ms system time, 36.12M rss, 50.49M vsz
# Current date: Sat Nov 25 07:24:44 2023
# Hostname: ip-192-168-0-11
# Files: STDIN
# Overall: 7 total, 6 unique, 0.18 QPS, 0.00x concurrency ________________
# Time range: 2023-11-25T07:23:33 to 2023-11-25T07:24:11
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           11ms   107us     4ms     2ms     3ms     1ms     1ms
# Lock time           20us       0     6us     2us     5us     2us     2us
# Rows sent             22       0      12    3.14   11.95    4.01    0.99
# Rows examine          67       0      38    9.57   36.69   13.91    0.99
# Query size           174      11      36   24.86   34.95    9.38   28.75

# Profile
# Rank Query ID                          Response time Calls R/Call V/M   
# ==== ================================= ============= ===== ====== ===== 
#    1 0x751417D45B8E80EE5CBA2034458B...  0.0036 31.4%     1 0.0036  0.00 SHOW DATABASES
#    2 0x55C45664C0A27CA4AF92DD1D2D0A...  0.0032 28.5%     2 0.0016  0.00 SELECT tags
#    3 0x489B4CEB2F4301A7132628303F99...  0.0028 24.9%     1 0.0028  0.00 SHOW TABLES
#    4 0x898255B1BE4F8C3044AE35A18286...  0.0014 12.3%     1 0.0014  0.00 ADMIN INIT DB
# MISC 0xMISC                             0.0003  2.8%     2 0.0002   0.0 <2 ITEMS>

# Query 1: 0 QPS, 0x concurrency, ID 0x751417D45B8E80EE5CBA2034458B5BC9 at byte 673
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T07:23:41
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         14       1
# Exec time     31     4ms     4ms     4ms     4ms     4ms       0     4ms
# Lock time     30     6us     6us     6us     6us     6us       0     6us
# Rows sent     27       6       6       6       6       6       0       6
# Rows examine  37      25      25      25      25      25       0      25
# Query size     8      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: 2 QPS, 0.00x concurrency, ID 0x55C45664C0A27CA4AF92DD1D2D0ABDAA at byte 3404
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T07:24:10 to 2023-11-25T07:24:11
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         28       2
# Exec time     28     3ms   618us     3ms     2ms     3ms     1ms     2ms
# Lock time     35     7us     3us     4us     3us     4us       0     3us
# Rows sent      9       2       1       1       1       1       0       1
# Rows examine   2       2       1       1       1       1       0       1
# Query size    40      70      34      36      35      36    1.41      35
# 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 'tags'\G
#    SHOW CREATE TABLE `isupipe`.`tags`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from tags where id in (1)\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: tags
#    partitions: NULL
#          type: const
# possible_keys: PRIMARY
#           key: PRIMARY
#       key_len: 8
#           ref: const
#          rows: 1
#      filtered: 100.00
#         Extra: NULL

# Query 3: 0 QPS, 0x concurrency, ID 0x489B4CEB2F4301A7132628303F99240D at byte 882
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T07:23:41
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         14       1
# Exec time     24     3ms     3ms     3ms     3ms     3ms       0     3ms
# Lock time     30     6us     6us     6us     6us     6us       0     6us
# Rows sent     54      12      12      12      12      12       0      12
# Rows examine  56      38      38      38      38      38       0      38
# Query size     6      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 4: 0 QPS, 0x concurrency, ID 0x898255B1BE4F8C3044AE35A182869033 at byte 436
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T07:23:41
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         14       1
# Exec time     12     1ms     1ms     1ms     1ms     1ms       0     1ms
# Lock time      4     1us     1us     1us     1us     1us       0     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    17      30      30      30      30      30       0      30
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
administrator command: Init DB\G

netdata

http://localhost:19991/#menu_services;after=1700897008190;before=1700897084349

pprof

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

isucon2

alp

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

slow query

Count: 32400  Time=0.00s (59s)  Lock=0.00s (0s)  Rows=0.2 (5620), 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: 23396  Time=0.00s (39s)  Lock=0.00s (0s)  Rows=0.5 (11847), 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: 39528  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: 560  Time=0.00s (2s)  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: 1120  Time=0.00s (2s)  Lock=0.00s (0s)  Rows=0.5 (560), 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: 97360  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@isucon1
  #

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

Count: 560  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: 560  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (560), isudns[isudns]@isucon1
  select id,name,master,last_check,notified_serial,type,options,catalog,account from domains where name='S'

Count: 1120  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.62M rss, 51.14M vsz
# Current date: Sat Nov 25 07:25:02 2023
# Hostname: ip-192-168-0-12
# Files: STDIN
# Overall: 197.73k total, 20 unique, 2.41k QPS, 1.35x concurrency ________
# Time range: 2023-11-25T07:23:28 to 2023-11-25T07:24:50
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           111s     1us    13ms   560us     2ms   859us    84us
# Lock time          174ms       0     5ms       0     1us    22us       0
# Rows sent         18.16k       0       1    0.09    0.99    0.29       0
# Rows examine      85.76M       0   1.80k  454.80   1.69k  698.69       0
# Query size        15.17M       5     220   80.46  151.03   48.77   51.63

# Profile
# Rank Query ID                     Response time Calls  R/Call V/M   Item
# ==== ============================ ============= ====== ====== ===== ====
#    1 0x42EF7D7D98FBCC9723BF896... 59.9144 54.0%  32400 0.0018  0.00 SELECT records
#    2 0x3D83BC87F3B3A00D571FFC8... 39.9854 36.1%  23396 0.0017  0.00 SELECT records
#    3 0x22279D81D51006139E0C764...  4.8254  4.4%  39528 0.0001  0.00 SELECT domains domainmetadata
#    4 0x859BBB7E9D760686137A944...  2.3500  2.1%    560 0.0042  0.00 DELETE records
# MISC 0xMISC                        3.8363  3.5% 101847 0.0000   0.0 <16 ITEMS>

# Query 1: 476.47 QPS, 0.88x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 9627773
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T07:23:28 to 2023-11-25T07:24:36
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         16   32400
# Exec time     54     60s   705us    11ms     2ms     3ms   609us     2ms
# Lock time     23    41ms       0   994us     1us     1us     5us     1us
# Rows sent     30   5.49k       0       1    0.17    0.99    0.38       0
# Rows examine  56  48.33M   1.25k   1.80k   1.53k   1.69k  154.53   1.46k
# Query size    28   4.38M     129     220  141.89  158.58   11.97  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='*.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: 344.06 QPS, 0.59x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 21629398
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T07:23:28 to 2023-11-25T07:24:36
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         11   23396
# Exec time     36     40s   660us    10ms     2ms     3ms   555us     2ms
# Lock time     18    33ms       0     2ms     1us     1us    16us     1us
# Rows sent     63  11.57k       0       1    0.51    0.99    0.50    0.99
# Rows examine  40  34.93M   1.25k   1.80k   1.53k   1.69k  154.68   1.46k
# Query size    19   3.03M     126     219  135.63  151.03   11.32  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: 581.29 QPS, 0.07x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 19697721
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T07:23:28 to 2023-11-25T07:24:36
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         19   39528
# Exec time      4      5s    71us     5ms   122us   159us   105us   103us
# Lock time     35    62ms       0     1ms     1us     1us     8us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    28   4.30M     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: 8.24 QPS, 0.03x concurrency, ID 0x859BBB7E9D760686137A944406874C4D at byte 53750941
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T07:23:28 to 2023-11-25T07:24:36
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     560
# Exec time      2      2s     2ms    13ms     4ms     7ms     1ms     4ms
# Lock time     20    36ms       0     5ms    64us     1us   400us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0 852.84k   1.25k   1.80k   1.52k   1.69k  159.71   1.46k
# Query size     0  46.81k      78      93   85.60   88.31    3.39   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='shota860.u.isucon.dev' and type='A'\G
# Converted for EXPLAIN
# EXPLAIN 
select * from  records where domain_id=4 and name='shota860.u.isucon.dev' and type='A'\G

netdata

http://localhost:19992/#menu_services;after=1700897008197;before=1700897088205

pprof

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

isucon3

alp

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

slow query

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

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

Count: 3483  Time=0.00s (10s)  Lock=0.00s (0s)  Rows=12.5 (43546), isucon[isucon]@isucon1
  SELECT * FROM livecomments WHERE livestream_id = N ORDER BY created_at DESC

Count: 443  Time=0.02s (9s)  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

Count: 3518  Time=0.00s (9s)  Lock=0.00s (0s)  Rows=12.2 (42819), isucon[isucon]@isucon1
  SELECT * FROM reactions WHERE livestream_id = N ORDER BY created_at DESC

Count: 444  Time=0.02s (7s)  Lock=0.00s (0s)  Rows=9.9 (4379), isucon[isucon]@isucon1
  SELECT * FROM reservation_slots WHERE start_at >= N AND end_at <= N FOR UPDATE

Count: 39597  Time=0.00s (6s)  Lock=0.00s (0s)  Rows=4.5 (179034), isucon[isucon]@isucon1
  SELECT * FROM livestream_tags WHERE livestream_id = N

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

Count: 243  Time=0.02s (4s)  Lock=0.00s (0s)  Rows=3.5 (853), isucon[isucon]@isucon1
  SELECT * FROM livestreams WHERE user_id = N

Count: 2788  Time=0.00s (3s)  Lock=0.00s (0s)  Rows=1.3 (3659), isucon[isucon]@isucon1
  SELECT * FROM themes WHERE user_id IN (N, N)

explain

Reading from STDIN ...

# 25.9s user time, 120ms system time, 38.00M rss, 52.38M vsz
# Current date: Sat Nov 25 07:25:18 2023
# Hostname: ip-192-168-0-13
# Files: STDIN
# Overall: 429.27k total, 66 unique, 4.13k QPS, 1.33x concurrency ________
# Time range: 2023-11-25T07:23:28 to 2023-11-25T07:25:12
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           138s     1us    90ms   321us   925us     1ms    76us
# Lock time          394ms       0     3ms       0     1us    15us     1us
# Rows sent          1.57M       0   7.75k    3.83    4.96   88.94    0.99
# Rows examine     101.68M       0  12.90k  248.38   1.33k  724.10    0.99
# Query size        16.46M       6     453   40.20   72.65   26.89   31.70

# Profile
# Rank Query ID                     Response time Calls  R/Call V/M   Item
# ==== ============================ ============= ====== ====== ===== ====
#    1 0x38BC86A45F31C6B1EE32467... 38.5085 27.9%  47491 0.0008  0.00 SELECT themes
#    2 0xFBC5564AE716EAE82F20BFB... 15.6262 11.3% 179026 0.0001  0.00 SELECT tags
#    3 0xB78E63D0D9C72DDDAB7A3E5... 10.5850  7.7%   3483 0.0030  0.00 SELECT livecomments
#    4 0xA3401CA3ABCC04C3AB221DB...  9.5216  6.9%    443 0.0215  0.01 UPDATE reservation_slots
# MISC 0xMISC                       63.7401 46.2% 198824 0.0003   0.0 <62 ITEMS>

# Query 1: 456.64 QPS, 0.37x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 76293664
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T07:23:28 to 2023-11-25T07:25:12
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         11   47491
# Exec time     27     39s   240us    18ms   810us     2ms   728us   596us
# Lock time     10    42ms       0     2ms       0     1us     8us     1us
# Rows sent      2  46.38k       1       1       1       1       0       1
# Rows examine  56  57.78M    1000   1.52k   1.25k   1.46k  157.92   1.20k
# Query size    11   1.85M      38      41   40.88   40.45    0.60   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 = 1063\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: 1560
#      filtered: 10.00
#         Extra: Using where

# Query 2: 2.63k QPS, 0.23x concurrency, ID 0xFBC5564AE716EAE82F20BFB45F6C37E7 at byte 75838625
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T07:23:28 to 2023-11-25T07:24:36
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         41  179026
# Exec time     11     16s    38us    12ms    87us    98us   160us    66us
# Lock time     44   174ms       0     3ms       0     1us    15us     1us
# Rows sent     10 174.83k       1       1       1       1       0       1
# Rows examine   0 174.83k       1       1       1       1       0       1
# Query size    33   5.45M      31      33   31.95   31.70    0.44   31.70
# String:
# Hosts        isucon1
# Users        isucon
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  ###
#   1ms  #
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'tags'\G
#    SHOW CREATE TABLE `tags`\G
# EXPLAIN 
SELECT * FROM tags WHERE id = 82\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: tags
#    partitions: NULL
#          type: const
# possible_keys: PRIMARY
#           key: PRIMARY
#       key_len: 8
#           ref: const
#          rows: 1
#      filtered: 100.00
#         Extra: NULL

# Query 3: 57.10 QPS, 0.17x concurrency, ID 0xB78E63D0D9C72DDDAB7A3E53517FA04C at byte 83747687
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T07:23:35 to 2023-11-25T07:24:36
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    3483
# Exec time      7     11s   412us    52ms     3ms     8ms     3ms     2ms
# Lock time      0     3ms       0    18us       0     1us       0     1us
# Rows sent      2  42.53k       0      63   12.50   34.95   11.21    8.91
# Rows examine   9   9.33M    1004   4.50k   2.74k   4.27k 1006.84   2.62k
# Query size     1 265.31k      78      78      78      78       0      78
# String:
# Hosts        isucon1
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  ######
#   1ms  ################################################################
#  10ms  ##
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'livecomments'\G
#    SHOW CREATE TABLE `livecomments`\G
# EXPLAIN 
SELECT * FROM livecomments WHERE livestream_id = 7605 ORDER BY created_at DESC\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: livecomments
#    partitions: NULL
#          type: ALL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 4618
#      filtered: 10.00
#         Extra: Using where; Using filesort

# Query 4: 6.51 QPS, 0.14x concurrency, ID 0xA3401CA3ABCC04C3AB221DB8AD5CBF26 at byte 69121597
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T07:23:28 to 2023-11-25T07:24:36
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     443
# Exec time      6     10s     5ms    62ms    21ms    40ms    11ms    19ms
# Lock time      0   707us       0   284us     1us     1us    12us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   3   3.70M   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0  42.40k      98      98      98      98       0      98
# 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
UPDATE reservation_slots SET slot = slot - 1 WHERE start_at >= 1703606400 AND end_at <= 1703610000\G
# Converted for EXPLAIN
# EXPLAIN 
select  slot = slot - 1 from reservation_slots where  start_at >= 1703606400 AND end_at <= 1703610000\G

netdata

http://localhost:19993/#menu_services;after=1700897008191;before=1700897088198

pprof

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

saza-ku / isucon13

11250723 #77

isucon1

alp

slow query

explain

netdata

pprof

isucon2

alp

slow query

explain

netdata

pprof

isucon3

alp

slow query

explain

netdata

pprof