Open takeokunn opened 10 months ago
alp:
+-------+--------+---------------------------------+-----+-------+-----+-----+-----+---------+-------+-------+--------+
| COUNT | METHOD | URI | 1XX | 2XX | 3XX | 4XX | 5XX | SUM | AVG | MIN | MAX |
+-------+--------+---------------------------------+-----+-------+-----+-----+-----+---------+-------+-------+--------+
| 513 | GET | /api/livestream/\w+ | 0 | 507 | 0 | 6 | 0 | 121.457 | 0.237 | 0.004 | 20.000 |
| 956 | GET | /api/livestream/\w+/livecomment | 0 | 955 | 0 | 1 | 0 | 120.205 | 0.126 | 0.004 | 0.480 |
| 1271 | POST | /api/livestream/\w+/livecomment | 0 | 1263 | 0 | 8 | 0 | 117.027 | 0.092 | 0.004 | 0.324 |
| 989 | GET | /api/livestream/\w+/reaction | 0 | 987 | 0 | 2 | 0 | 106.981 | 0.108 | 0.004 | 0.400 |
| 10534 | GET | /api/user/\w+/icon | 0 | 10533 | 0 | 1 | 0 | 96.569 | 0.009 | 0.004 | 0.136 |
| 881 | POST | /api/livestream/\w+/reaction | 0 | 880 | 0 | 1 | 0 | 71.544 | 0.081 | 0.004 | 0.256 |
| 334 | POST | /api/register | 0 | 331 | 0 | 2 | 1 | 68.034 | 0.204 | 0.028 | 0.392 |
| 553 | GET | /api/livestream | 0 | 553 | 0 | 0 | 0 | 57.730 | 0.104 | 0.004 | 0.240 |
| 116 | POST | /api/livestream/\w+ | 0 | 112 | 0 | 4 | 0 | 49.713 | 0.429 | 0.164 | 0.912 |
| 322 | GET | /api/livestream/\w+/report | 0 | 322 | 0 | 0 | 0 | 20.200 | 0.063 | 0.008 | 0.208 |
| 339 | POST | /api/login | 0 | 337 | 0 | 2 | 0 | 10.848 | 0.032 | 0.004 | 0.168 |
| 331 | POST | /api/icon | 0 | 331 | 0 | 0 | 0 | 8.396 | 0.025 | 0.004 | 0.116 |
| 142 | POST | /api/livestream/\w+/moderate | 0 | 142 | 0 | 0 | 0 | 8.004 | 0.056 | 0.004 | 0.152 |
| 104 | GET | /api/tag | 0 | 104 | 0 | 0 | 0 | 4.092 | 0.039 | 0.004 | 0.152 |
| 91 | POST | /api/livestream/\w+/enter | 0 | 91 | 0 | 0 | 0 | 3.544 | 0.039 | 0.004 | 0.132 |
| 16 | GET | /api/user/\w+/statistics | 0 | 16 | 0 | 0 | 0 | 3.104 | 0.194 | 0.016 | 0.284 |
| 80 | DELETE | /api/livestream/\w+/exit | 0 | 80 | 0 | 0 | 0 | 2.528 | 0.032 | 0.004 | 0.124 |
| 1 | POST | /api/initialize | 0 | 1 | 0 | 0 | 0 | 2.260 | 2.260 | 2.260 | 2.260 |
| 18 | GET | /api/user/\w+ | 0 | 18 | 0 | 0 | 0 | 1.552 | 0.086 | 0.004 | 0.200 |
| 15 | GET | /api/user/\w+/theme | 0 | 15 | 0 | 0 | 0 | 0.456 | 0.030 | 0.056 | 0.060 |
| 1 | GET | /api/payment | 0 | 1 | 0 | 0 | 0 | 0.004 | 0.004 | 0.004 | 0.004 |
+-------+--------+---------------------------------+-----+-------+-----+-----+-----+---------+-------+-------+--------+
pt-query-digest:
# 14.3s user time, 80ms system time, 36.36M rss, 42.72M vsz
# Current date: Sat Nov 25 08:17:56 2023
# Hostname: ip-192-168-0-11
# Files: /var/log/mysql/mysql-slow.log
# Overall: 215.91k total, 127 unique, 1.42k QPS, 1.34x concurrency _______
# Time range: 2023-11-25T08:14:49 to 2023-11-25T08:17:21
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 204s 1us 358ms 943us 4ms 4ms 125us
# Lock time 358ms 0 16ms 1us 1us 62us 1us
# Rows sent 272.04k 0 7.41k 1.29 0.99 36.99 0
# Rows examine 44.74M 0 8.55k 217.27 1.33k 852.72 0
# Query size 20.33M 5 1.94M 98.73 151.03 4.44k 97.36
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================= ============= ===== ====== ===== ====
# 1 0x3D83BC87F3B3A00D571FFC81... 79.3355 39.0% 13634 0.0058 0.01 SELECT records
# 2 0x9EAD6C0CE525E3693EE27FFC... 20.9189 10.3% 583 0.0359 0.01 SELECT livestreams
# 3 0x59F1B6DD8D9FEC059E55B3BF... 16.3032 8.0% 1015 0.0161 0.01 SELECT reservation_slots
# 4 0x22279D81D51006139E0C7640... 12.4002 6.1% 22564 0.0005 0.01 SELECT domains domainmetadata
# 5 0x42EF7D7D98FBCC9723BF896E... 11.6483 5.7% 18664 0.0006 0.00 SELECT records
# 6 0x38BC86A45F31C6B1EE324671... 11.1753 5.5% 7621 0.0015 0.00 SELECT themes
# 7 0x8F7679D452333ED3C7D60D22... 10.9520 5.4% 53881 0.0002 0.01 ADMIN RESET STMT
# 8 0xFFFCA4D67EA0A788813031B8... 5.0063 2.5% 7036 0.0007 0.00 COMMIT
# 9 0xA3401CA3ABCC04C3AB221DB8... 4.3857 2.2% 112 0.0392 0.02 UPDATE reservation_slots
# 10 0xFD38427AE3D09E3883A680F7... 4.0614 2.0% 19617 0.0002 0.00 SELECT livestreams livecomments
# 11 0xC499D81D570D361DB61FC43A... 3.7635 1.8% 19619 0.0002 0.00 SELECT livestreams reactions
# 12 0x5A8A79FB9E5D1635CA10FEA9... 3.4116 1.7% 2140 0.0016 0.00 SELECT themes
# 13 0x7F9C0C0BA9473953B723EE16... 3.2893 1.6% 114 0.0289 0.01 SELECT reservation_slots
# 14 0xBB9B3D66E02B513839482132... 2.2811 1.1% 4525 0.0005 0.00 SELECT tags livestream_tags
# 15 0xEA1E6309EEEFF9A6831AD2FB... 1.9101 0.9% 7274 0.0003 0.00 SELECT users
# 16 0xD6032FE08E1FE706A928B8B7... 1.7798 0.9% 6978 0.0003 0.00 SELECT livestreams
# 17 0x2C5B3DD85325F07F33914C2E... 0.9360 0.5% 274 0.0034 0.00 SELECT tags livestream_tags
# MISC 0xMISC 10.1213 5.0% 30262 0.0003 0.0 <110 ITEMS>
# Query 1: 133.67 QPS, 0.78x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 50593407
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T08:14:58 to 2023-11-25T08:16:40
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 6 13634
# Exec time 38 79s 670us 88ms 6ms 17ms 6ms 4ms
# Lock time 12 44ms 0 5ms 3us 1us 68us 1us
# Rows sent 2 6.74k 0 1 0.51 0.99 0.50 0.99
# Rows examine 42 18.89M 1.25k 1.57k 1.42k 1.53k 90.00 1.39k
# Query size 8 1.76M 126 214 135.42 151.03 11.26 124.25
# String:
# Databases isudns
# Hosts localhost
# Users isudns
# Query_time distribution
# 1us
# 10us
# 100us #
# 1ms ################################################################
# 10ms ###########
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isudns` LIKE 'records'\G
# SHOW CREATE TABLE `isudns`.`records`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='u.isucon.dev'\G
# Query 2: 8.33 QPS, 0.30x concurrency, ID 0x9EAD6C0CE525E3693EE27FFC9AAF05A3 at byte 55883120
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T08:15:29 to 2023-11-25T08:16:39
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 583
# Exec time 10 21s 4ms 123ms 36ms 65ms 17ms 33ms
# Lock time 0 2ms 0 1ms 3us 1us 42us 1us
# Rows sent 0 415 0 21 0.71 2.90 1.78 0
# Rows examine 9 4.21M 7.32k 7.43k 7.39k 7.31k 0 7.31k
# Query size 0 26.18k 43 46 45.98 44.60 0.18 44.60
# 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 'livestreams'\G
# SHOW CREATE TABLE `isupipe`.`livestreams`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM livestreams WHERE user_id = 1146\G
# Query 3: 14.30 QPS, 0.23x concurrency, ID 0x59F1B6DD8D9FEC059E55B3BFD624E8C3 at byte 55921380
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T08:15:29 to 2023-11-25T08:16:40
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 1015
# Exec time 8 16s 2ms 84ms 16ms 40ms 12ms 13ms
# Lock time 2 7ms 0 3ms 7us 1us 123us 1us
# Rows sent 0 1015 1 1 1 1 0 1
# Rows examine 18 8.48M 8.55k 8.55k 8.55k 8.55k 0 8.55k
# Query size 0 85.24k 86 86 86 86 0 86
# 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 slot FROM reservation_slots WHERE start_at = 1701439200 AND end_at = 1701442800\G
# Query 4: 304.92 QPS, 0.17x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 50592289
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T08:15:26 to 2023-11-25T08:16:40
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 10 22564
# Exec time 6 12s 81us 78ms 549us 2ms 2ms 152us
# Lock time 27 97ms 0 16ms 4us 1us 145us 1us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 12 2.45M 114 114 114 114 0 114
# String:
# Databases isudns
# Hosts localhost
# Users isudns
# Query_time distribution
# 1us
# 10us #
# 100us ################################################################
# 1ms #######
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isudns` LIKE 'domains'\G
# SHOW CREATE TABLE `isudns`.`domains`\G
# SHOW TABLE STATUS FROM `isudns` LIKE 'domainmetadata'\G
# SHOW CREATE TABLE `isudns`.`domainmetadata`\G
# EXPLAIN /*!50100 PARTITIONS*/
select kind,content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='u.isucon.dev'\G
# Query 5: 252.22 QPS, 0.16x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 54193021
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T08:15:26 to 2023-11-25T08:16:40
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 8 18664
# Exec time 5 12s 89us 50ms 624us 2ms 2ms 176us
# Lock time 9 34ms 0 3ms 1us 1us 29us 1us
# Rows sent 1 3.63k 0 1 0.20 0.99 0.40 0
# Rows examine 0 3.63k 0 1 0.20 0.99 0.40 0
# Query size 12 2.52M 129 215 141.76 158.58 12.05 136.99
# String:
# Databases isudns
# Hosts localhost
# Users isudns
# Query_time distribution
# 1us
# 10us #
# 100us ################################################################
# 1ms ########
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isudns` LIKE 'records'\G
# SHOW CREATE TABLE `isudns`.`records`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and name='pipe.u.isucon.dev' and domain_id=3\G
# Query 6: 107.34 QPS, 0.16x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 22179753
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T08:15:29 to 2023-11-25T08:16:40
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 7621
# Exec time 5 11s 266us 30ms 1ms 6ms 2ms 596us
# Lock time 6 24ms 0 6ms 3us 1us 81us 1us
# Rows sent 2 7.44k 1 1 1 1 0 1
# Rows examine 19 8.54M 1000 1.30k 1.15k 1.26k 93.14 1.14k
# Query size 1 305.09k 38 41 40.99 40.45 0.13 40.45
# 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 'themes'\G
# SHOW CREATE TABLE `isupipe`.`themes`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM themes WHERE user_id = 1058\G
# Query 7: 376.79 QPS, 0.08x concurrency, ID 0x8F7679D452333ED3C7D60D22131CEFD4 at byte 51729535
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T08:14:58 to 2023-11-25T08:17:21
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 24 53881
# Exec time 5 11s 6us 64ms 203us 626us 1ms 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 8 1.70M 33 33 33 33 0 33
# String:
# Databases isudns
# Hosts localhost
# Users isudns
# Query_time distribution
# 1us #
# 10us ################################################################
# 100us ##########
# 1ms ##
# 10ms #
# 100ms
# 1s
# 10s+
administrator command: Reset stmt\G
# Query 8: 64.55 QPS, 0.05x concurrency, ID 0xFFFCA4D67EA0A788813031B8BBC3B329 at byte 60613128
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T08:14:51 to 2023-11-25T08:16:40
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 7036
# Exec time 2 5s 16us 30ms 711us 4ms 2ms 57us
# 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 0 41.23k 6 6 6 6 0 6
# String:
# Databases isupipe (6702/95%), isudns (334/4%)
# Hosts localhost
# Users isucon (6702/95%), isudns (334/4%)
# Query_time distribution
# 1us
# 10us ################################################################
# 100us ########################
# 1ms #################
# 10ms #
# 100ms
# 1s
# 10s+
COMMIT\G
# Query 9: 1.60 QPS, 0.06x concurrency, ID 0xA3401CA3ABCC04C3AB221DB8AD5CBF26 at byte 40932065
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2023-11-25T08:15:29 to 2023-11-25T08:16:39
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 112
# Exec time 2 4s 5ms 109ms 39ms 82ms 24ms 40ms
# Lock time 0 144us 0 24us 1us 1us 2us 1us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 2 958.02k 8.55k 8.55k 8.55k 8.55k 0 8.55k
# Query size 0 10.72k 98 98 98 98 0 98
# String:
# Databases isupipe
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ###################
dnsのほうにindexはった