Skip to content

Instantly share code, notes, and snippets.

@tatsuru

tatsuru/12:48 slow.sql Secret

Created August 21, 2021 03:48
Show Gist options
  • Save tatsuru/1ef1f8d1a17ef473d251b20e9657001e to your computer and use it in GitHub Desktop.
Save tatsuru/1ef1f8d1a17ef473d251b20e9657001e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
12:48 slow.sql
# A software update is available:
# 46.7s user time, 940ms system time, 43.07M rss, 4.16G vsz
# Current date: Sat Aug 21 12:46:54 2021
# Hostname: hagyou.lan
# Files: /tmp/slow.log
# Overall: 373.02k total, 95 unique, 4.55k QPS, 1.84x concurrency ________
# Time range: 2021-08-21 12:43:32 to 12:44:54
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 151s 0 266ms 403us 2ms 2ms 33us
# Lock time 4s 0 27ms 9us 18us 145us 0
# Rows sent 4.63M 0 1.70k 13.02 0.99 99.00 0
# Rows examine 4.55M 0 1.70k 12.80 0 98.06 0
# Rows affecte 73.08k 0 618 0.20 0.99 1.05 0
# Bytes sent 1.05G 0 646.20k 2.94k 2.27k 17.44k 12.54
# Merge passes 0 0 0 0 0 0 0
# Tmp tables 9 1 1 1 1 0 1
# Tmp disk tbl 0 0 0 0 0 0 0
# Tmp tbl size 1.36M 124.33k 162.47k 154.63k 158.07k 13.02k 158.07k
# Query size 31.96M 6 790.36k 89.84 258.32 1.31k 31.70
# Boolean:
# Filesort 13% yes, 86% no
# Full scan 89% yes, 10% no
# QC hit 7% yes, 92% no
# Tmp table 3% yes, 96% no
# Profile
# Rank Query ID Response time Calls R/Call V/M
# ==== =================================== ============= ====== ====== ===
# 1 0x931A992E852C61FC6D46141A39DEF4FE 50.3797 33.5% 9552 0.0053 0.01 SELECT isu_condition
# 2 0xFFFCA4D67EA0A788813031B8BBC3B329 31.6737 21.0% 8718 0.0036 0.01 COMMIT
# 3 0xB8B32624C3268C0925657C305C0ED778 20.7541 13.8% 74050 0.0003 0.00 INSERT isu_condition
# 4 0x9C6C682008AE0D08F3E2A0049B030C70 19.0186 12.6% 2546 0.0075 0.01 SELECT isu_condition
# 5 0xDA556F9115773A1A99AA0165670CE848 17.4250 11.6% 118362 0.0001 0.00 ADMIN PREPARE
# 6 0x5F580A12ADA1633C9634298BE5BD9422 4.5879 3.0% 677 0.0068 0.01 SELECT isu_condition
# MISC 0xMISC 6.7246 4.5% 159120 0.0000 0.0 <89 ITEMS>
# Query 1: 159.20 QPS, 0.84x concurrency, ID 0x931A992E852C61FC6D46141A39DEF4FE at byte 92881780
# Scores: V/M = 0.01
# Time range: 2021-08-21 12:43:54 to 12:44:54
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 9552
# Exec time 33 50s 10us 90ms 5ms 20ms 7ms 2ms
# Lock time 14 504ms 0 21ms 52us 57us 415us 12us
# Rows sent 62 2.89M 0 1.70k 317.36 1.14k 389.33 107.34
# Rows examine 62 2.84M 0 1.70k 311.62 1.14k 384.11 107.34
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 41 445.87M 589 267.13k 47.80k 174.27k 58.54k 16.75k
# Query size 3 1.05M 115 115 115 115 0 115
# Boolean:
# QC hit 2% yes, 97% no
# String:
# Databases isucondition
# Hosts localhost
# 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` = '61838011-bcdf-4888-b232-cec0be847d23' ORDER BY timestamp DESC\G
# Query 2: 142.92 QPS, 0.52x concurrency, ID 0xFFFCA4D67EA0A788813031B8BBC3B329 at byte 118034332
# Scores: V/M = 0.01
# Time range: 2021-08-21 12:43:53 to 12:44:54
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 8718
# Exec time 21 32s 5us 266ms 4ms 9ms 5ms 3ms
# 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 93.65k 11 11 11 11 0 11
# Query size 0 51.08k 6 6 6 6 0 6
# String:
# Databases isucondition
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us ##
# 10us ####
# 100us ###
# 1ms ################################################################
# 10ms ###
# 100ms #
# 1s
# 10s+
COMMIT\G
# Query 3: 1.23k QPS, 0.35x concurrency, ID 0xB8B32624C3268C0925657C305C0ED778 at byte 92869985
# Scores: V/M = 0.00
# Time range: 2021-08-21 12:43:54 to 12:44:54
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 19 74050
# Exec time 13 21s 19us 57ms 280us 1ms 1ms 57us
# Lock time 74 3s 4us 27ms 35us 38us 264us 13us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Rows affecte 98 72.31k 1 1 1 1 0 1
# Bytes sent 0 949.01k 13 14 13.12 13.83 0.42 12.54
# Query size 56 18.07M 225 302 255.83 271.23 13.03 246.02
# String:
# Databases isucondition
# Hosts localhost
# 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 ('aa538db5-3cca-4a08-95ad-27a414b5be5b', '2021-08-24 16:41:15', 0, 'is_dirty=true,is_overweight=false,is_broken=true', '…痛いしベタつく')\G
# Query 4: 41.74 QPS, 0.31x concurrency, ID 0x9C6C682008AE0D08F3E2A0049B030C70 at byte 96791634
# Scores: V/M = 0.01
# Time range: 2021-08-21 12:43:53 to 12:44:54
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 2546
# Exec time 12 19s 93us 68ms 7ms 23ms 8ms 5ms
# Lock time 4 143ms 7us 12ms 56us 60us 401us 13us
# Rows sent 27 1.26M 0 1.64k 517.82 1.09k 341.54 463.90
# Rows examine 27 1.26M 0 1.64k 517.82 1.09k 341.54 463.90
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 18 193.73M 589 250.15k 77.92k 174.27k 51.60k 68.96k
# Query size 1 390.35k 157 157 157 157 0 157
# String:
# Databases isucondition
# Hosts localhost
# 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` = 'e9f2c6b9-1a8a-4117-bf22-87692d9570b5' AND `timestamp` < '2021-08-24 21:39:07' ORDER BY `timestamp` DESC\G
# Query 5: 1.79k QPS, 0.26x concurrency, ID 0xDA556F9115773A1A99AA0165670CE848 at byte 113659594
# Scores: V/M = 0.00
# Time range: 2021-08-21 12:43:48 to 12:44:54
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 31 118362
# Exec time 11 17s 13us 47ms 147us 490us 627us 42us
# 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 2 26.19M 52 681 232.01 621.67 173.76 158.58
# Query size 10 3.39M 30 30 30 30 0 30
# String:
# Databases isucondition
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us ################################################################
# 100us ########
# 1ms #
# 10ms #
# 100ms
# 1s
# 10s+
administrator command: Prepare\G
# Query 6: 11.28 QPS, 0.08x concurrency, ID 0x5F580A12ADA1633C9634298BE5BD9422 at byte 74035212
# Scores: V/M = 0.01
# Time range: 2021-08-21 12:43:53 to 12:44:53
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 677
# Exec time 3 5s 14us 42ms 7ms 21ms 7ms 4ms
# Lock time 0 22ms 0 3ms 32us 54us 159us 12us
# Rows sent 9 453.94k 12 1.39k 686.61 1.20k 387.59 652.75
# Rows examine 9 453.53k 0 1.39k 685.99 1.20k 388.57 652.75
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 6 68.60M 2.13k 219.50k 103.76k 192.13k 59.18k 97.04k
# Query size 0 76.69k 116 116 116 116 0 116
# Boolean:
# QC hit 1% yes, 98% no
# String:
# Databases isucondition
# Hosts localhost
# 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` = 'ebf1fd1a-2422-4c2e-b206-8d3f2be38d30' ORDER BY `timestamp` ASC\G
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment