Skip to content

Instantly share code, notes, and snippets.

@motemen

motemen/alp.txt Secret

Last active August 21, 2021 04:09
Show Gist options
  • Save motemen/4147280ca3d2b782f2ecc973a59d10d0 to your computer and use it in GitHub Desktop.
Save motemen/4147280ca3d2b782f2ecc973a59d10d0 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
alp.txt
ssh isu01 alp ltsv --file /var/log/nginx/access_log.ltsv -m '/api/condition/.*,/api/isu/[^/]*/icon,/api/isu/[^/]*/graph,/api/isu/[^/]*$,/isu/[^/]*/condition,/isu/[^/]*/graph,/isu/[^/]*$,/assets/.*' --sort sum --reverse
+--------+-----+--------+------+------+-----+--------+----------------------+-------+-------+----------+-------+-------+-------+-------+--------+-----------+------------+----------------+-----------+
| COUNT | 1XX | 2XX | 3XX | 4XX | 5XX | METHOD | URI | MIN | MAX | SUM | AVG | P1 | P50 | P99 | STDDEV | MIN(BODY) | MAX(BODY) | SUM(BODY) | AVG(BODY) |
+--------+-----+--------+------+------+-----+--------+----------------------+-------+-------+----------+-------+-------+-------+-------+--------+-----------+------------+----------------+-----------+
| 3046 | 0 | 2832 | 0 | 214 | 0 | GET | /api/trend | 0.012 | 1.004 | 1567.878 | 0.515 | 1.000 | 0.956 | 0.384 | 0.218 | 0.000 | 8437.000 | 23283338.000 | 7643.906 |
| 317574 | 0 | 317155 | 0 | 419 | 0 | POST | /api/condition/.* | 0.032 | 0.104 | 983.859 | 0.003 | 0.000 | 0.000 | 0.000 | 0.008 | 0.000 | 14.000 | 294.000 | 0.001 |
| 26794 | 0 | 24246 | 0 | 2548 | 0 | GET | /api/condition/.* | 0.016 | 0.744 | 791.516 | 0.030 | 0.020 | 0.008 | 0.020 | 0.034 | 0.000 | 7363.000 | 127198142.000 | 4747.262 |
| 61660 | 0 | 60567 | 0 | 1093 | 0 | GET | /api/isu/[^/]*/icon | 0.008 | 0.472 | 608.089 | 0.010 | 0.036 | 0.004 | 0.004 | 0.012 | 0.000 | 99032.000 | 1135010724.000 | 18407.569 |
| 12024 | 0 | 11658 | 0 | 366 | 0 | GET | /api/isu | 0.004 | 0.440 | 447.216 | 0.037 | 0.028 | 0.040 | 0.032 | 0.032 | 0.000 | 10000.000 | 34115478.000 | 2837.282 |
| 10843 | 0 | 9025 | 0 | 1814 | 4 | GET | /api/isu/[^/]*/graph | 0.004 | 0.804 | 166.926 | 0.015 | 0.000 | 0.020 | 0.008 | 0.019 | 0.000 | 7923.000 | 44354861.000 | 4090.645 |
| 12442 | 0 | 11356 | 0 | 1086 | 0 | GET | /api/isu/[^/]*$ | 0.004 | 0.484 | 102.264 | 0.008 | 0.020 | 0.000 | 0.000 | 0.011 | 14.000 | 154.000 | 1587605.000 | 127.600 |
| 21024 | 0 | 14096 | 6928 | 0 | 0 | GET | /assets/.* | 0.000 | 0.020 | 30.844 | 0.001 | 0.000 | 0.004 | 0.000 | 0.002 | 592.000 | 743417.000 | 1871369398.000 | 89011.102 |
| 6226 | 0 | 3329 | 0 | 2897 | 0 | POST | /api/auth | 0.000 | 0.104 | 24.844 | 0.004 | 0.000 | 0.008 | 0.000 | 0.006 | 0.000 | 19.000 | 33304.000 | 5.349 |
| 4801 | 0 | 2243 | 0 | 2558 | 0 | GET | /api/user/me | 0.004 | 0.104 | 14.932 | 0.003 | 0.000 | 0.004 | 0.012 | 0.006 | 21.000 | 45.000 | 139788.000 | 29.116 |
| 232 | 0 | 216 | 0 | 16 | 0 | POST | /api/isu | 0.004 | 0.160 | 14.300 | 0.062 | 0.060 | 0.064 | 0.072 | 0.020 | 15.000 | 154.000 | 30148.000 | 129.948 |
| 2574 | 0 | 2204 | 0 | 370 | 0 | POST | /api/signout | 0.004 | 0.116 | 12.784 | 0.005 | 0.004 | 0.012 | 0.008 | 0.007 | 0.000 | 21.000 | 7770.000 | 3.019 |
| 5681 | 0 | 4594 | 1087 | 0 | 0 | GET | / | 0.000 | 0.024 | 6.800 | 0.001 | 0.000 | 0.000 | 0.004 | 0.002 | 528.000 | 528.000 | 2425632.000 | 426.973 |
| 6 | 0 | 6 | 0 | 0 | 0 | POST | /initialize | 0.208 | 0.268 | 1.384 | 0.231 | 0.224 | 0.208 | 0.268 | 0.019 | 23.000 | 23.000 | 138.000 | 23.000 |
| 84 | 0 | 28 | 56 | 0 | 0 | GET | /isu/[^/]*/condition | 0.000 | 0.004 | 0.044 | 0.001 | 0.000 | 0.000 | 0.000 | 0.001 | 0.000 | 528.000 | 14784.000 | 176.000 |
| 32 | 0 | 32 | 0 | 0 | 0 | GET | /isu/[^/]*$ | 0.004 | 0.004 | 0.028 | 0.001 | 0.000 | 0.004 | 0.000 | 0.002 | 528.000 | 528.000 | 16896.000 | 528.000 |
| 32 | 0 | 32 | 0 | 0 | 0 | GET | /isu/[^/]*/graph | 0.000 | 0.004 | 0.012 | 0.000 | 0.000 | 0.000 | 0.000 | 0.001 | 528.000 | 528.000 | 16896.000 | 528.000 |
| 12 | 0 | 4 | 8 | 0 | 0 | GET | /register | 0.000 | 0.004 | 0.004 | 0.000 | 0.000 | 0.000 | 0.000 | 0.001 | 0.000 | 528.000 | 2112.000 | 176.000 |
+--------+-----+--------+------+------+-----+--------+----------------------+-------+-------+----------+-------+-------+-------+-------+--------+-----------+------------+----------------+-----------+
Raw
gistfile1.txt
ssh isu03 sudo pt-query-digest /tmp/mysql-slow.log
# 42.5s user time, 70ms system time, 31.84M rss, 38.77M vsz
# Current date: Sat Aug 21 13:07:07 2021
# Hostname: ip-192-168-0-13
# Files: /tmp/mysql-slow.log
# Overall: 576.61k total, 27 unique, 9.45k QPS, 5.67x concurrency ________
# Time range: 2021-08-21 13:05:00 to 13:06:01
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 346s 0 418ms 599us 2ms 5ms 42us
# Lock time 18s 0 62ms 31us 20us 523us 0
# Rows sent 3.93M 0 1.64k 7.14 1.96 75.35 0
# Rows examine 3.81M 0 1.64k 6.93 0.99 75.38 0
# Rows affecte 77.56k 0 10 0.14 0 1.14 0
# Bytes sent 2.59G 0 646.20k 4.72k 23.58k 20.82k 92.72
# Query size 41.95M 6 146.59k 76.29 124.25 377.58 31.70
# Boolean:
# QC hit 13% yes, 86% no
# Profile
# Rank Query ID Response time Calls R/Call V/M Ite
# ==== ============================ ============== ====== ====== ===== ===
# 1 0x9C6C682008AE0D08F3E2A00... 112.3025 32.5% 5405 0.0208 0.04 SELECT isu_condition
# 2 0xAC9E2250E1642BFE9823A9B... 65.6683 19.0% 68423 0.0010 0.01 SELECT isu_condition
# 3 0xFFFCA4D67EA0A788813031B... 63.4473 18.4% 14057 0.0045 0.03 COMMIT
# 4 0xDA556F9115773A1A99AA016... 35.9244 10.4% 179244 0.0002 0.01 ADMIN PREPARE
# 5 0xB8B32624C3268C0925657C3... 22.2433 6.4% 7938 0.0028 0.08 INSERT isu_condition
# 6 0x8155B89FFD74A9D523D19AC... 15.3027 4.4% 20082 0.0008 0.01 SELECT isu_condition
# 7 0x8C2BC651CBBBF3DB41D1CAD... 7.8594 2.3% 21300 0.0004 0.01 SELECT isu
# 8 0xADCA4F127A769A45A2D1B74... 5.5782 1.6% 22598 0.0002 0.01 SELECT user
# 9 0x9C92D790D45C9F0EB13BB31... 4.8050 1.4% 2688 0.0018 0.01 SELECT isu_condition
# MISC 0xMISC 12.5977 3.6% 234877 0.0001 0.0 <18 ITEMS>
# Query 1: 88.61 QPS, 1.84x concurrency, ID 0x9C6C682008AE0D08F3E2A0049B030C70 at byte 106704173
# Scores: V/M = 0.04
# Time range: 2021-08-21 13:05:00 to 13:06:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 5405
# Exec time 32 112s 97us 251ms 21ms 87ms 29ms 8ms
# Lock time 3 667ms 7us 32ms 123us 204us 908us 13us
# Rows sent 89 3.52M 0 1.64k 682.38 1.33k 406.39 652.75
# Rows examine 92 3.52M 0 1.64k 682.38 1.33k 406.39 652.75
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 19 522.13M 589 241.58k 98.92k 192.13k 58.73k 92.42k
# Query size 1 828.70k 157 157 157 157 0 157
# String:
# Databases isucondition
# Hosts isuconditi... (1836/33%)... 2 more
# Users isucon
# Query_time distribution
# 1us
# 10us #
# 100us ##############
# 1ms ################################################################
# 10ms ###########################################################
# 100ms #####
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucondition` LIKE 'isu_condition'\G
# SHOW CREATE TABLE `isucondition`.`isu_condition`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = '7319febb-e524-4379-b475-c4b78b87ca13' AND `timestamp` < '2021-08-18 11:54:17' ORDER BY `timestamp` DESC\G
# Query 2: 1.14k QPS, 1.09x concurrency, ID 0xAC9E2250E1642BFE9823A9B9ECA1A419 at byte 106681244
# Scores: V/M = 0.01
# Time range: 2021-08-21 13:05:01 to 13:06:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 11 68423
# Exec time 18 66s 7us 248ms 959us 5ms 3ms 125us
# Lock time 67 12s 0 62ms 179us 301us 1ms 12us
# Rows sent 1 66.37k 0 1 0.99 0.99 0.08 0.99
# Rows examine 1 61.33k 0 1 0.92 0.99 0.27 0.99
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 1 48.03M 589 789 736.08 755.64 16.10 719.66
# Query size 19 8.03M 123 123 123 123 0 123
# Boolean:
# QC hit 7% yes, 92% no
# String:
# Databases isucondition
# Hosts isuconditi... (24927/36%)... 2 more
# Users isucon
# Query_time distribution
# 1us #
# 10us ################
# 100us ################################################################
# 1ms ##########
# 10ms ##
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucondition` LIKE 'isu_condition'\G
# SHOW CREATE TABLE `isucondition`.`isu_condition`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = 'b204f7a4-274b-4b82-93f7-f6f7ff0c59d3' ORDER BY timestamp DESC LIMIT 1\G
# Query 3: 230.44 QPS, 1.04x concurrency, ID 0xFFFCA4D67EA0A788813031B8BBC3B329 at byte 197003335
# Scores: V/M = 0.03
# Time range: 2021-08-21 13:05:00 to 13:06:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 14057
# Exec time 18 63s 3us 191ms 5ms 15ms 12ms 2ms
# 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
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 0 151.00k 11 11 11 11 0 11
# Query size 0 82.37k 6 6 6 6 0 6
# String:
# Databases isucondition
# Hosts isuconditi... (4720/33%)... 2 more
# Users isucon
# Query_time distribution
# 1us ######################
# 10us #############################
# 100us ###
# 1ms ################################################################
# 10ms ############
# 100ms #
# 1s
# 10s+
COMMIT\G
# Query 4: 2.94k QPS, 0.59x concurrency, ID 0xDA556F9115773A1A99AA0165670CE848 at byte 173302501
# Scores: V/M = 0.01
# Time range: 2021-08-21 13:05:00 to 13:06:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 31 179244
# Exec time 10 36s 19us 95ms 200us 515us 1ms 54us
# 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
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 3 88.35M 52 1.34k 516.87 652.75 284.46 621.67
# Query size 12 5.13M 30 30 30 30 0 30
# String:
# Databases isucondition
# Hosts isuconditi... (62523/34%)... 2 more
# Users isucon
# Query_time distribution
# 1us
# 10us ################################################################
# 100us ############
# 1ms ##
# 10ms #
# 100ms
# 1s
# 10s+
administrator command: Prepare\G
# Query 5: 132.30 QPS, 0.37x concurrency, ID 0xB8B32624C3268C0925657C305C0ED778 at byte 106678827
# Scores: V/M = 0.08
# Time range: 2021-08-21 13:05:01 to 13:06:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 7938
# Exec time 6 22s 90us 418ms 3ms 11ms 15ms 273us
# Lock time 5 1s 21us 27ms 137us 301us 591us 54us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Rows affecte 99 77.44k 2 10 9.99 9.83 0.23 9.83
# Bytes sent 0 412.24k 52 54 53.18 51.63 0 51.63
# Query size 27 11.72M 374 2.01k 1.51k 1.61k 99.93 1.46k
# String:
# Databases isucondition
# Hosts isuconditi... (2652/33%)... 2 more
# Users isucon
# Query_time distribution
# 1us
# 10us #
# 100us ################################################################
# 1ms ###############
# 10ms ####
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucondition` LIKE 'isu_condition'\G
# SHOW CREATE TABLE `isucondition`.`isu_condition`\G
INSERT INTO `isu_condition` (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `message`) VALUES ('b204f7a4-274b-4b82-93f7-f6f7ff0c59d3','2021-08-19 14:49:51',1,'is_dirty=false,is_overweight=false,is_broken=false','よいです。'),('b204f7a4-274b-4b82-93f7-f6f7ff0c59d3','2021-08-19 14:50:46',0,'is_dirty=false,is_overweight=false,is_broken=false','よいです。'),('b204f7a4-274b-4b82-93f7-f6f7ff0c59d3','2021-08-19 14:51:44',0,'is_dirty=false,is_overweight=false,is_broken=false','よいです。'),('b204f7a4-274b-4b82-93f7-f6f7ff0c59d3','2021-08-19 14:52:43',0,'is_dirty=false,is_overweight=false,is_broken=false','よいです。'),('b204f7a4-274b-4b82-93f7-f6f7ff0c59d3','2021-08-19 14:53:47',0,'is_dirty=false,is_overweight=false,is_broken=false','よいです。'),('b204f7a4-274b-4b82-93f7-f6f7ff0c59d3','2021-08-19 14:54:45',0,'is_dirty=false,is_overweight=false,is_broken=false','よいです。'),('b204f7a4-274b-4b82-93f7-f6f7ff0c59d3','2021-08-19 14:55:51',0,'is_dirty=false,is_overweight=false,is_broken=false','よいです。'),('b204f7a4-274b-4b82-93f7-f6f7ff0c59d3','2021-08-19 14:56:45',0,'is_dirty=false,is_overweight=false,is_broken=false','よいです。'),('b204f7a4-274b-4b82-93f7-f6f7ff0c59d3','2021-08-19 14:57:50',0,'is_dirty=false,is_overweight=false,is_broken=false','よいです。'),('b204f7a4-274b-4b82-93f7-f6f7ff0c59d3','2021-08-19 14:58:43',0,'is_dirty=false,is_overweight=false,is_broken=false','よいです。')\G
# Query 6: 329.21 QPS, 0.25x concurrency, ID 0x8155B89FFD74A9D523D19AC409FD97AF at byte 106612976
# Scores: V/M = 0.01
# Time range: 2021-08-21 13:05:00 to 13:06:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 20082
# Exec time 4 15s 11us 168ms 762us 3ms 3ms 119us
# Lock time 16 3s 0 30ms 148us 236us 985us 11us
# Rows sent 0 19.46k 0 1 0.99 0.99 0.09 0.99
# Rows examine 0 19.44k 0 1 0.99 0.99 0.09 0.99
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 0 14.09M 589 789 735.93 719.66 15.23 719.66
# Query size 5 2.39M 125 125 125 125 0 125
# Boolean:
# QC hit 0% yes, 99% no
# String:
# Databases isucondition
# Hosts isuconditi... (6783/33%)... 2 more
# Users isucon
# Query_time distribution
# 1us
# 10us #######################
# 100us ################################################################
# 1ms #########
# 10ms #
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucondition` LIKE 'isu_condition'\G
# SHOW CREATE TABLE `isucondition`.`isu_condition`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = 'c04ca3cb-30b8-4c19-b478-99b46487e4e4' ORDER BY `timestamp` DESC LIMIT 1\G
# Query 7: 355 QPS, 0.13x concurrency, ID 0x8C2BC651CBBBF3DB41D1CAD61AA0BD68 at byte 117930895
# Scores: V/M = 0.01
# Time range: 2021-08-21 13:05:01 to 13:06:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 21300
# Exec time 2 8s 12us 107ms 368us 1ms 2ms 52us
# Lock time 0 113ms 0 50ms 5us 0 343us 0
# Rows sent 1 66.82k 1 8 3.21 4.96 1.48 2.90
# Rows examine 0 31.88k 0 82 1.53 0 9.88 0
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 54 1.41G 5.44k 185.29k 69.22k 174.27k 44.46k 54.03k
# Query size 2 1.10M 51 57 54.36 56.92 2.95 51.63
# Boolean:
# QC hit 97% yes, 2% no
# String:
# Databases isucondition
# Hosts isuconditi... (7750/36%)... 2 more
# Users isucon
# Query_time distribution
# 1us
# 10us ################################################################
# 100us #################
# 1ms #####
# 10ms #
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucondition` LIKE 'isu'\G
# SHOW CREATE TABLE `isucondition`.`isu`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `isu` WHERE `character` = 'すなお'\G
# Query 8: 370.46 QPS, 0.09x concurrency, ID 0xADCA4F127A769A45A2D1B74705103105 at byte 117931902
# Scores: V/M = 0.01
# Time range: 2021-08-21 13:05:00 to 13:06:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 22598
# Exec time 1 6s 6us 106ms 246us 626us 2ms 16us
# Lock time 0 151ms 0 50ms 6us 0 555us 0
# Rows sent 0 22.07k 1 1 1 1 0 1
# Rows examine 0 42 0 1 0.00 0 0.04 0
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 0 1.53M 71 71 71 71 0 71
# Query size 3 1.44M 58 70 66.89 65.89 2.90 65.89
# Boolean:
# QC hit 99% yes, 0% no
# String:
# Databases isucondition
# Hosts isuconditi... (7552/33%)... 2 more
# Users isucon
# Query_time distribution
# 1us #
# 10us ################################################################
# 100us #####
# 1ms ##
# 10ms #
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucondition` LIKE 'user'\G
# SHOW CREATE TABLE `isucondition`.`user`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) FROM `user` WHERE `jia_user_id` = 'amazing_lovelace'\G
# Query 9: 44.07 QPS, 0.08x concurrency, ID 0x9C92D790D45C9F0EB13BB314E7013F0A at byte 157652479
# Scores: V/M = 0.01
# Time range: 2021-08-21 13:05:00 to 13:06:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 2688
# Exec time 1 5s 71us 97ms 2ms 8ms 4ms 490us
# Lock time 1 334ms 8us 23ms 124us 194us 921us 13us
# Rows sent 4 176.21k 0 154 67.13 107.34 28.62 69.19
# Rows examine 4 176.21k 0 154 67.13 107.34 28.62 69.19
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 1 26.76M 589 23.37k 10.19k 16.75k 4.13k 10.29k
# Query size 1 441.00k 168 168 168 168 0 168
# String:
# Databases isucondition
# Hosts isuconditi... (931/34%)... 2 more
# Users isucon
# Query_time distribution
# 1us
# 10us #
# 100us ################################################################
# 1ms ######################
# 10ms ###
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucondition` LIKE 'isu_condition'\G
# SHOW CREATE TABLE `isucondition`.`isu_condition`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = 'c8372e99-5faa-4297-b492-af252a2567c3' AND timestamp BETWEEN '2021-08-11' AND '2021-08-12' ORDER BY `timestamp` ASC\G
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment