11250335

[saza-ku]

isucon1

alp

+-------+--------+--------------+-------+-------+-------+-------+-------+
| COUNT | METHOD |     URI      |  MIN  |  MAX  |  SUM  |  AVG  |  P99  |
+-------+--------+--------------+-------+-------+-------+-------+-------+
| 1     | GET    | /api/payment | 0.004 | 0.004 | 0.004 | 0.004 | 0.004 |
| 1     | POST   | /api/login   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
+-------+--------+--------------+-------+-------+-------+-------+-------+

slow query

Count: 27  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.7 (18), 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: 22  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.5 (11), 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: 30  Time=0.00s (0s)  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: 74  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@localhost
  #

Count: 74  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Reset stmt

explain

Reading from STDIN ...

# 140ms user time, 20ms system time, 36.25M rss, 50.55M vsz
# Current date: Sat Nov 25 03:35:28 2023
# Hostname: ip-192-168-0-11
# Files: STDIN
# Overall: 153 total, 4 unique, 0 QPS, 0x concurrency ____________________
# Time range: all events occurred at 2023-11-25T03:35:08
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           70ms    11us     6ms   454us     2ms   855us    93us
# Lock time           93us       0     2us       0     1us       0     1us
# Rows sent             29       0       1    0.19    0.99    0.39       0
# Rows examine      61.25k       0   1.25k  409.93   1.20k  574.29       0
# Query size        12.09k      33     144   80.90  136.99   46.62  112.70

# Profile
# Rank Query ID                           Response time Calls R/Call V/M  
# ==== ================================== ============= ===== ====== =====
#    1 0x42EF7D7D98FBCC9723BF896EBFC51D24  0.0368 52.9%    27 0.0014  0.00 SELECT records
#    2 0x3D83BC87F3B3A00D571FFC8104A6E50C  0.0265 38.0%    22 0.0012  0.00 SELECT records
#    3 0x22279D81D51006139E0C76405B54C261  0.0041  5.9%    30 0.0001  0.00 SELECT domains domainmetadata
#    4 0x8F7679D452333ED3C7D60D22131CEFD4  0.0023  3.2%    74 0.0000  0.00 ADMIN RESET STMT

# Query 1: 0 QPS, 0x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 3397
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         17      27
# Exec time     52    37ms   716us     6ms     1ms     3ms     1ms   761us
# Lock time     31    29us       0     2us     1us     1us       0     1us
# Rows sent     62      18       0       1    0.67    0.99    0.47    0.99
# Rows examine  55  33.75k   1.25k   1.25k   1.25k   1.25k       0   1.25k
# Query size    29   3.56k     129     144     135  143.84    6.23  130.47
# 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='ldap.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='ldap.u.isucon.dev' and domain_id=4"] at /usr/bin/pt-query-digest line 7796.

# Query 2: 0 QPS, 0x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 9625
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         14      22
# Exec time     38    26ms   673us     5ms     1ms     2ms     1ms   761us
# Lock time     24    23us     1us     2us     1us     1us       0     1us
# Rows sent     37      11       0       1    0.50    0.99    0.50    0.99
# Rows examine  44  27.50k   1.25k   1.25k   1.25k   1.25k       0   1.25k
# Query size    23   2.80k     126     143  130.50  136.99    4.49  130.47
# 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='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: 0 QPS, 0x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 5098
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         19      30
# Exec time      5     4ms    86us   351us   136us   224us    56us   113us
# Lock time     44    41us     1us     2us     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    27   3.34k     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: 0 QPS, 0x concurrency, ID 0x8F7679D452333ED3C7D60D22131CEFD4 at byte 892
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         48      74
# Exec time      3     2ms    11us   176us    30us    73us    27us    20us
# 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.38k      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=1700883251697;before=1700883327819

pprof

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

[saza-ku]

isucon3

alp

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

slow query

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), isucon[isucon]@isucon1
  SELECT IFNULL(SUM(tip), N) FROM livecomments

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), isucon[isucon]@isucon1
  SELECT * FROM users WHERE name = 'S'

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@isucon1
  START TRANSACTION

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@isucon1
  ROLLBACK

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

explain

Reading from STDIN ...

# 120ms user time, 20ms system time, 36.00M rss, 50.49M vsz
# Current date: Sat Nov 25 03:35:31 2023
# Hostname: ip-192-168-0-13
# Files: STDIN
# Overall: 6 total, 5 unique, 0 QPS, 0x concurrency ______________________
# Time range: all events occurred at 2023-11-25T03:35:08
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          798us    21us   469us   133us   467us   158us   117us
# Lock time            3us       0     2us       0     1us       0       0
# Rows sent              2       0       1    0.33    0.99    0.47       0
# Rows examine        1002       0    1001     167  964.41  359.34       0
# Query size           141       6      49   23.50   46.83   16.10   16.81

# Profile
# Rank Query ID                          Response time Calls R/Call V/M   
# ==== ================================= ============= ===== ====== ===== 
#    1 0xC4625761E0CFD817F41B4802F152...  0.0005 58.8%     1 0.0005  0.00 SELECT livecomments
#    2 0x3F155F5E71EFF856F0CF7B8430DE...  0.0002 21.2%     1 0.0002  0.00 SELECT users
#    3 0x9E2DA589A20EC24C34E11DDE0FBF...  0.0001 11.3%     2 0.0000  0.00 START
#    4 0xFFF66E9B3D962FA319C8068B5C19...  0.0000  5.0%     1 0.0000  0.00 ROLLBACK
# MISC 0xMISC                             0.0000  3.8%     1 0.0000   0.0 <1 ITEMS>

# Query 1: 0 QPS, 0x concurrency, ID 0xC4625761E0CFD817F41B4802F152A489 at byte 221
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         16       1
# Exec time     58   469us   469us   469us   469us   469us       0   469us
# Lock time     66     2us     2us     2us     2us     2us       0     2us
# Rows sent     50       1       1       1       1       1       0       1
# Rows examine  99    1001    1001    1001    1001    1001       0    1001
# Query size    31      44      44      44      44      44       0      44
# 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 /*!50100 PARTITIONS*/
SELECT IFNULL(SUM(tip), 0) FROM livecomments\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: 1001
#      filtered: 100.00
#         Extra: NULL

# Query 2: 0 QPS, 0x concurrency, ID 0x3F155F5E71EFF856F0CF7B8430DE8C23 at byte 900
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         16       1
# Exec time     21   169us   169us   169us   169us   169us       0   169us
# Lock time     33     1us     1us     1us     1us     1us       0     1us
# Rows sent     50       1       1       1       1       1       0       1
# Rows examine   0       1       1       1       1       1       0       1
# Query size    34      49      49      49      49      49       0      49
# 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
# EXPLAIN 
SELECT * FROM users WHERE name = 'suzukitsubasa0'\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: users
#    partitions: NULL
#          type: const
# possible_keys: uniq_user_name
#           key: uniq_user_name
#       key_len: 1022
#           ref: const
#          rows: 1
#      filtered: 100.00
#         Extra: NULL

# Query 3: 0 QPS, 0x concurrency, ID 0x9E2DA589A20EC24C34E11DDE0FBF5564 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-25T03:35:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         33       2
# Exec time     11    90us    21us    69us    45us    69us    33us    45us
# 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    24      34      17      17      17      17       0      17
# String:
# Hosts        isucon1
# Users        isucon
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
START TRANSACTION\G

# Query 4: 0 QPS, 0x concurrency, ID 0xFFF66E9B3D962FA319C8068B5C1997CD at byte 1152
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         16       1
# Exec time      5    40us    40us    40us    40us    40us       0    40us
# 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     5       8       8       8       8       8       0       8
# String:
# Hosts        isucon1
# Users        isucon
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
ROLLBACK\G

netdata

http://localhost:19993/#menu_services;after=1700883251674;before=1700883331681

pprof

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

[saza-ku]

isucon2

alp

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

slow query

explain

Reading from STDIN ...

# No events processed.

netdata

http://localhost:19992/#menu_services;after=1700883251663;before=1700883331670

pprof

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

[saza-ku]

isucon1

alp

+-------+--------+--------------+-------+-------+-------+-------+-------+
| COUNT | METHOD |     URI      |  MIN  |  MAX  |  SUM  |  AVG  |  P99  |
+-------+--------+--------------+-------+-------+-------+-------+-------+
| 1     | GET    | /api/payment | 0.004 | 0.004 | 0.004 | 0.004 | 0.004 |
| 1     | POST   | /api/login   | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
+-------+--------+--------------+-------+-------+-------+-------+-------+

slow query

Count: 27  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.7 (18), 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: 22  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.5 (11), 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: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (2), isucon[isucon]@localhost
  SHOW VARIABLES LIKE 'S'

Count: 30  Time=0.00s (0s)  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: 75  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 2users@localhost
  #

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

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

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  EXPLAIN select kind,content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='S'

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  SET @@SQL_QUOTE_SHOW_CREATE = N/*!N, @@SQL_MODE='S'*/

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

explain

Reading from STDIN ...

# 140ms user time, 10ms system time, 36.25M rss, 50.68M vsz
# Current date: Sat Nov 25 03:36:24 2023
# Hostname: ip-192-168-0-11
# Files: STDIN
# Overall: 164 total, 14 unique, 8.20 QPS, 0.00x concurrency _____________
# Time range: 2023-11-25T03:35:08 to 2023-11-25T03:35:28
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           75ms     2us     6ms   456us     2ms   868us    93us
# Lock time          103us       0     4us       0     1us       0     1us
# Rows sent             33       0       1    0.20    0.99    0.40       0
# Rows examine      61.25k       0   1.25k  382.46   1.20k  563.38       0
# Query size        12.85k      16     195   80.21  136.99   47.71  112.70

# Profile
# Rank Query ID                         Response time Calls R/Call V/M   I
# ==== ================================ ============= ===== ====== ===== =
#    1 0x42EF7D7D98FBCC9723BF896EBFC...  0.0368 49.1%    27 0.0014  0.00 SELECT records
#    2 0x3D83BC87F3B3A00D571FFC8104A...  0.0265 35.3%    22 0.0012  0.00 SELECT records
#    3 0xE77769C62EF669AA7DD5F6760F2...  0.0047  6.2%     2 0.0023  0.00 SHOW VARIABLES
#    4 0x22279D81D51006139E0C76405B5...  0.0041  5.5%    30 0.0001  0.00 SELECT domains domainmetadata
# MISC 0xMISC                            0.0030  4.0%    83 0.0000   0.0 <10 ITEMS>

# Query 1: 0 QPS, 0x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 3397
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         16      27
# Exec time     49    37ms   716us     6ms     1ms     3ms     1ms   761us
# Lock time     28    29us       0     2us     1us     1us       0     1us
# Rows sent     54      18       0       1    0.67    0.99    0.47    0.99
# Rows examine  55  33.75k   1.25k   1.25k   1.25k   1.25k       0   1.25k
# Query size    27   3.56k     129     144     135  143.84    6.23  130.47
# 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='ldap.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='ldap.u.isucon.dev' and domain_id=4"] at /usr/bin/pt-query-digest line 7796.

# Query 2: 0 QPS, 0x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 9625
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         13      22
# Exec time     35    26ms   673us     5ms     1ms     2ms     1ms   761us
# Lock time     22    23us     1us     2us     1us     1us       0     1us
# Rows sent     33      11       0       1    0.50    0.99    0.50    0.99
# Rows examine  44  27.50k   1.25k   1.25k   1.25k   1.25k       0   1.25k
# Query size    21   2.80k     126     143  130.50  136.99    4.49  130.47
# 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='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: 0 QPS, 0x concurrency, ID 0xE77769C62EF669AA7DD5F6760F2D2EBB at byte 43579
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1       2
# Exec time      6     5ms   907us     4ms     2ms     4ms     2ms     2ms
# Lock time      1     2us       0     2us     1us     2us     1us     1us
# Rows sent      6       2       1       1       1       1       0       1
# Rows examine   0       2       1       1       1       1       0       1
# Query size     0      77      35      42   38.50      42    4.95   38.50
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
SHOW VARIABLES LIKE 'wait\_timeout'\G

# Query 4: 0 QPS, 0x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 5098
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         18      30
# Exec time      5     4ms    86us   351us   136us   224us    56us   113us
# Lock time     39    41us     1us     2us     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    25   3.34k     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.

netdata

http://localhost:19991/#menu_services;after=1700883308188;before=1700883384340

pprof

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

[saza-ku]

isucon2

alp

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

slow query

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (2), isucon[isucon]@localhost
  SHOW VARIABLES LIKE 'S'

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  SET @@SQL_QUOTE_SHOW_CREATE = N/*!N, @@SQL_MODE='S'*/

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

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  SET SESSION wait_timeout=N

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  SET NAMES 'S'

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

Count: 1  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=0.0 (0), 0users@0hosts
  administrator command: Quit

explain

Reading from STDIN ...

# 140ms user time, 10ms system time, 36.12M rss, 50.55M vsz
# Current date: Sat Nov 25 03:36:28 2023
# Hostname: ip-192-168-0-12
# Files: STDIN
# Overall: 8 total, 7 unique, 0 QPS, 0x concurrency ______________________
# Time range: all events occurred at 2023-11-25T03:35:31
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            5ms     3us     4ms   675us     4ms     1ms    48us
# Lock time            6us       0     5us       0     4us     1us       0
# Rows sent              4       0       1    0.50    0.99    0.50    0.99
# Rows examine           4       0       1    0.50    0.99    0.50    0.99
# Query size           381      16     195   47.62  192.76   55.83   31.85

# Profile
# Rank Query ID                          Response time Calls R/Call V/M   
# ==== ================================= ============= ===== ====== ===== 
#    1 0xE77769C62EF669AA7DD5F6760F2D...  0.0052 95.9%     2 0.0026  0.00 SHOW VARIABLES
#    2 0xEC21548748A08C1CDA69864C6215...  0.0001  0.9%     1 0.0001  0.00 SET
#    3 0xDFB7062963B5842FD3F15165B890...  0.0000  0.9%     1 0.0000  0.00 SELECT
#    4 0x9B758E42947B8A0DAD312D56C6B3...  0.0000  0.8%     1 0.0000  0.00 SET
# MISC 0xMISC                             0.0001  1.4%     3 0.0000   0.0 <3 ITEMS>

# Query 1: 0 QPS, 0x concurrency, ID 0xE77769C62EF669AA7DD5F6760F2D2EBB 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-25T03:35:31
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         25       2
# Exec time     95     5ms     1ms     4ms     3ms     4ms     2ms     3ms
# Lock time    100     6us     1us     5us     3us     5us     2us     3us
# Rows sent     50       2       1       1       1       1       0       1
# Rows examine  50       2       1       1       1       1       0       1
# Query size    20      77      35      42   38.50      42    4.95   38.50
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
SHOW VARIABLES LIKE 'wait\_timeout'\G

# Query 2: 0 QPS, 0x concurrency, ID 0xEC21548748A08C1CDA69864C6215FEDB at byte 675
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:31
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         12       1
# Exec time      0    50us    50us    50us    50us    50us       0    50us
# 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    51     195     195     195     195     195       0     195
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'*/\G

# Query 3: 0 QPS, 0x concurrency, ID 0xDFB7062963B5842FD3F15165B8904806 at byte 465
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:31
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         12       1
# Exec time      0    49us    49us    49us    49us    49us       0    49us
# Lock time      0       0       0       0       0       0       0       0
# Rows sent     25       1       1       1       1       1       0       1
# Rows examine  25       1       1       1       1       1       0       1
# Query size     4      17      17      17      17      17       0      17
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# EXPLAIN /*!50100 PARTITIONS*/
SELECT @@SQL_MODE\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: NULL
#    partitions: NULL
#          type: NULL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: NULL

# Query 4: 0 QPS, 0x concurrency, ID 0x9B758E42947B8A0DAD312D56C6B3D880 at byte 242
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:31
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         12       1
# Exec time      0    45us    45us    45us    45us    45us       0    45us
# 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     7      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+
SET SESSION wait_timeout=10000\G

netdata

http://localhost:19992/#menu_services;after=1700883308177;before=1700883388184

pprof

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

[saza-ku]

isucon3

alp

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

slow query

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (2), isucon[isucon]@localhost
  SHOW VARIABLES LIKE 'S'

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), isucon[isucon]@isucon1
  SELECT IFNULL(SUM(tip), N) FROM livecomments

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (2), isucon[isucon]@localhost
  EXPLAIN SELECT IFNULL(SUM(tip), N) FROM livecomments

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (2), isucon[isucon]@localhost
  EXPLAIN SELECT * FROM users WHERE name = 'S'

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), isucon[isucon]@isucon1
  SELECT * FROM users WHERE name = 'S'

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@isucon1
  START TRANSACTION

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

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  SET @@SQL_QUOTE_SHOW_CREATE = N/*!N, @@SQL_MODE='S'*/

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  SET SESSION wait_timeout=N

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@isucon1
  ROLLBACK

explain

Reading from STDIN ...

# 120ms user time, 20ms system time, 36.12M rss, 50.56M vsz
# Current date: Sat Nov 25 03:36:28 2023
# Hostname: ip-192-168-0-13
# Files: STDIN
# Overall: 18 total, 14 unique, 0.78 QPS, 0.00x concurrency ______________
# Time range: 2023-11-25T03:35:08 to 2023-11-25T03:35:31
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            5ms     4us     2ms   266us   925us   523us    66us
# Lock time           13us       0     5us       0     1us     1us       0
# Rows sent             10       0       1    0.56    0.99    0.49    0.99
# Rows examine        1006       0    1001   55.89    0.99  220.84       0
# Query size           740       6     195   41.11   56.92   40.47   34.95

# Profile
# Rank Query ID                         Response time Calls R/Call V/M   I
# ==== ================================ ============= ===== ====== ===== =
#    1 0xE77769C62EF669AA7DD5F6760F2...  0.0032 67.5%     2 0.0016  0.00 SHOW VARIABLES
#    2 0xC4625761E0CFD817F41B4802F15...  0.0005  9.8%     1 0.0005  0.00 SELECT livecomments
#    3 0x8A50AE3621C4867999F0FBB5792...  0.0003  5.9%     2 0.0001  0.00 SELECT livecomments
#    4 0xD8EB1CCCD0783E56CF68D5A7F34...  0.0003  5.7%     2 0.0001  0.00 SELECT users
# MISC 0xMISC                            0.0005 11.2%    11 0.0000   0.0 <10 ITEMS>

# Query 1: 0 QPS, 0x concurrency, ID 0xE77769C62EF669AA7DD5F6760F2D2EBB at byte 1363
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:31
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         11       2
# Exec time     67     3ms   930us     2ms     2ms     2ms   975us     2ms
# Lock time     46     6us     1us     5us     3us     5us     2us     3us
# Rows sent     20       2       1       1       1       1       0       1
# Rows examine   0       2       1       1       1       1       0       1
# Query size    10      77      35      42   38.50      42    4.95   38.50
# String:
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
SHOW VARIABLES LIKE 'wait\_timeout'\G

# Query 2: 0 QPS, 0x concurrency, ID 0xC4625761E0CFD817F41B4802F152A489 at byte 221
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          5       1
# Exec time      9   469us   469us   469us   469us   469us       0   469us
# Lock time     15     2us     2us     2us     2us     2us       0     2us
# Rows sent     10       1       1       1       1       1       0       1
# Rows examine  99    1001    1001    1001    1001    1001       0    1001
# Query size     5      44      44      44      44      44       0      44
# 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 /*!50100 PARTITIONS*/
SELECT IFNULL(SUM(tip), 0) FROM livecomments\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: 1001
#      filtered: 100.00
#         Extra: NULL

# Query 3: 0 QPS, 0x concurrency, ID 0x8A50AE3621C4867999F0FBB579224740 at byte 3068
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:31
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         11       2
# Exec time      5   283us    95us   188us   141us   188us    65us   141us
# Lock time      7     1us       0     1us       0     1us       0       0
# Rows sent     20       2       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Query size    14     104      52      52      52      52       0      52
# String:
# Hosts        localhost
# 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 IFNULL(SUM(tip), 0) FROM livecomments\G
# Converted for EXPLAIN
# EXPLAIN 
SELECT IFNULL(SUM(tip), 0) FROM livecomments\G

# Query 4: 0 QPS, 0x concurrency, ID 0xD8EB1CCCD0783E56CF68D5A7F3453825 at byte 3558
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2023-11-25T03:35:31
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         11       2
# Exec time      5   274us    99us   175us   137us   175us    53us   137us
# Lock time     23     3us     1us     2us     1us     2us       0     1us
# Rows sent     20       2       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Query size    15     114      57      57      57      57       0      57
# String:
# Hosts        localhost
# 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
EXPLAIN SELECT * FROM users WHERE name = 'suzukitsubasa0'\G
# Converted for EXPLAIN
# EXPLAIN 
SELECT * FROM users WHERE name = 'suzukitsubasa0'\G

netdata

http://localhost:19993/#menu_services;after=1700883308177;before=1700883388184

pprof

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