Skip to content

Instantly share code, notes, and snippets.

@tatsuru

tatsuru/digest.sql Secret

Created October 3, 2020 02:46
Show Gist options
  • Save tatsuru/aab63808c8df4d4bdb1607c2b229921e to your computer and use it in GitHub Desktop.
Save tatsuru/aab63808c8df4d4bdb1607c2b229921e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
digest.sql
# A software update is available:
Reading from STDIN ...
# 26.1s user time, 170ms system time, 38.26M rss, 4.17G vsz
# Current date: Sat Oct 3 11:45:56 2020
# Hostname: hagyou.local
# Files: STDIN
# Overall: 260.81k total, 64 unique, 3.07k QPS, 2.27x concurrency ________
# Time range: 2020-10-03T02:42:59 to 2020-10-03T02:44:24
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 193s 1us 778ms 740us 2ms 12ms 60us
# Lock time 33s 0 743ms 127us 89us 5ms 0
# Rows sent 2.21M 0 320 8.89 9.83 43.99 0
# Rows examine 5.09M 0 640 20.45 69.19 88.95 0
# Query size 37.90M 6 2.35k 152.37 441.81 429.60 31.70
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================ ============= ====== ====== ===== ====
# 1 0xFFFCA4D67EA0A788813031B... 82.2615 42.6% 14494 0.0057 0.32 COMMIT
# 2 0x94DAA9B853A4194B236A188... 30.6197 15.8% 9267 0.0033 0.01 SELECT teams benchmark_jobs contestants
# 3 0x661D6D1358CF2B841B24BD3... 23.3827 12.1% 1069 0.0219 0.23 SELECT benchmark_jobs
# 4 0xA494778D1679D6A166F12FB... 19.0805 9.9% 1248 0.0153 0.18 INSERT notifications
# 5 0xDA556F9115773A1A99AA016... 10.0344 5.2% 71217 0.0001 0.00 ADMIN PREPARE
# 6 0x2ABC4FED164E43995860DC2... 6.9760 3.6% 9267 0.0008 0.00 SELECT benchmark_jobs
# 7 0x6C28D5AD2F9252CCFAD00FD... 3.6268 1.9% 3782 0.0010 0.28 UPDATE notifications
# 8 0x6CDF0F958A105B514036B56... 2.7281 1.4% 9815 0.0003 0.04 SELECT contestants
# 9 0x37B56D8F93E12D26C3984A0... 2.1931 1.1% 10294 0.0002 0.00 SELECT contest_config
# 10 0xA0B645FAC805F824A18DBE6... 2.1731 1.1% 11859 0.0002 0.00 SELECT benchmark_jobs
# 11 0x426CC7F7874CAD705DEA0E3... 1.2767 0.7% 47 0.0272 0.03 INSERT contestants
# MISC 0xMISC 8.8512 4.6% 118446 0.0001 0.0 <53 ITEMS>
# Query 1: 178.94 QPS, 1.02x concurrency, ID 0xFFFCA4D67EA0A788813031B8BBC3B329 at byte 7138647
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.32
# Time range: 2020-10-03T02:43:02 to 2020-10-03T02:44:23
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 5 14494
# Exec time 42 82s 14us 751ms 6ms 12ms 42ms 36us
# 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 84.93k 6 6 6 6 0 6
# String:
# Databases xsuportal
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us ################################################################
# 100us ####
# 1ms #########
# 10ms #####
# 100ms #
# 1s
# 10s+
COMMIT\G
# Query 2: 144.80 QPS, 0.48x concurrency, ID 0x94DAA9B853A4194B236A188CFDA83F98 at byte 45511071
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-10-03T02:43:19 to 2020-10-03T02:44:23
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 9267
# Exec time 15 31s 678us 65ms 3ms 11ms 4ms 2ms
# Lock time 4 1s 64us 11ms 157us 247us 318us 108us
# Rows sent 3 90.50k 10 10 10 10 0 10
# Rows examine 12 632.67k 30 70 69.91 69.19 1.87 69.19
# Query size 56 21.26M 2.35k 2.35k 2.35k 2.27k 0 2.27k
# String:
# Databases xsuportal
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us #
# 1ms ################################################################
# 10ms ###
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xsuportal` LIKE 'teams'\G
# SHOW CREATE TABLE `xsuportal`.`teams`\G
# SHOW TABLE STATUS FROM `xsuportal` LIKE 'benchmark_jobs'\G
# SHOW CREATE TABLE `xsuportal`.`benchmark_jobs`\G
# SHOW TABLE STATUS FROM `xsuportal` LIKE 'contestants'\G
# SHOW CREATE TABLE `xsuportal`.`contestants`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT
`teams`.`id` AS `id`,
`teams`.`name` AS `name`,
`teams`.`leader_id` AS `leader_id`,
`teams`.`withdrawn` AS `withdrawn`,
`team_student_flags`.`student` AS `student`,
(`best_score_jobs`.`score_raw` - `best_score_jobs`.`score_deduction`) AS `best_score`,
`best_score_jobs`.`started_at` AS `best_score_started_at`,
`best_score_jobs`.`finished_at` AS `best_score_marked_at`,
(`latest_score_jobs`.`score_raw` - `latest_score_jobs`.`score_deduction`) AS `latest_score`,
`latest_score_jobs`.`started_at` AS `latest_score_started_at`,
`latest_score_jobs`.`finished_at` AS `latest_score_marked_at`,
`latest_score_job_ids`.`finish_count` AS `finish_count`
FROM
`teams`
-- latest scores
LEFT JOIN (
SELECT
MAX(`id`) AS `id`,
`team_id`,
COUNT(*) AS `finish_count`
FROM
`benchmark_jobs`
WHERE
`finished_at` IS NOT NULL
-- score freeze
AND (`team_id` = 0 OR (`team_id` != 0 AND (0 = TRUE OR `finished_at` < '2020-10-03 02:43:59')))
GROUP BY
`team_id`
) `latest_score_job_ids` ON `latest_score_job_ids`.`team_id` = `teams`.`id`
LEFT JOIN `benchmark_jobs` `latest_score_jobs` ON `latest_score_job_ids`.`id` = `latest_score_jobs`.`id`
-- best scores
LEFT JOIN (
SELECT
MAX(`j`.`id`) AS `id`,
`j`.`team_id` AS `team_id`
FROM
(
SELECT
`team_id`,
MAX(`score_raw` - `score_deduction`) AS `score`
FROM
`benchmark_jobs`
WHERE
`finished_at` IS NOT NULL
-- score freeze
AND (`team_id` = 0 OR (`team_id` != 0 AND (0 = TRUE OR `finished_at` < '2020-10-03 02:43:59')))
GROUP BY
`team_id`
) `best_scores`
LEFT JOIN `benchmark_jobs` `j` ON (`j`.`score_raw` - `j`.`score_deduction`) = `best_scores`.`score`
AND `j`.`team_id` = `best_scores`.`team_id`
GROUP BY
`j`.`team_id`
) `best_score_job_ids` ON `best_score_job_ids`.`team_id` = `teams`.`id`
LEFT JOIN `benchmark_jobs` `best_score_jobs` ON `best_score_jobs`.`id` = `best_score_job_ids`.`id`
-- check student teams
LEFT JOIN (
SELECT
`team_id`,
(SUM(`student`) = COUNT(*)) AS `student`
FROM
`contestants`
GROUP BY
`contestants`.`team_id`
) `team_student_flags` ON `team_student_flags`.`team_id` = `teams`.`id`
ORDER BY
`latest_score` DESC,
`latest_score_marked_at` ASC\G
# Query 3: 21.82 QPS, 0.48x concurrency, ID 0x661D6D1358CF2B841B24BD3B020A8902 at byte 7139480
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.23
# Time range: 2020-10-03T02:43:19 to 2020-10-03T02:44:08
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 1069
# Exec time 12 23s 47us 743ms 22ms 38ms 70ms 7ms
# Lock time 69 23s 8us 743ms 22ms 34ms 70ms 7ms
# Rows sent 0 320 0 1 0.30 0.99 0.45 0
# Rows examine 0 320 0 1 0.30 0.99 0.45 0
# Query size 0 77.95k 73 75 74.67 72.65 0 72.65
# String:
# Databases xsuportal
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us ##############################
# 100us #################################
# 1ms ###############################################################
# 10ms ################################################################
# 100ms #######
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xsuportal` LIKE 'benchmark_jobs'\G
# SHOW CREATE TABLE `xsuportal`.`benchmark_jobs`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT 1 FROM `benchmark_jobs` WHERE `id` = 13 AND `status` = 0 FOR UPDATE\G
# Query 4: 25.47 QPS, 0.39x concurrency, ID 0xA494778D1679D6A166F12FBF42CC3832 at byte 18862628
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.18
# Time range: 2020-10-03T02:43:19 to 2020-10-03T02:44:08
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 1248
# Exec time 9 19s 3ms 778ms 15ms 19ms 52ms 9ms
# Lock time 0 277ms 12us 20ms 221us 93us 1ms 25us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 0 193.12k 154 159 158.46 158.58 2.35 158.58
# String:
# Databases xsuportal
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ################################################################
# 10ms ##########################################################
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xsuportal` LIKE 'notifications'\G
# SHOW CREATE TABLE `xsuportal`.`notifications`\G
INSERT INTO `notifications` (`contestant_id`, `encoded_message`, `read`, `created_at`, `updated_at`) VALUES ('DZROJSFPwWqE', 'GgIIcQ==', FALSE, NOW(6), NOW(6))\G
# Query 5: 847.82 QPS, 0.12x concurrency, ID 0xDA556F9115773A1A99AA0165670CE848 at byte 143159
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-10-03T02:43:00 to 2020-10-03T02:44:24
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 27 71217
# Exec time 5 10s 20us 37ms 140us 366us 470us 69us
# Lock time 0 21ms 0 16ms 0 0 60us 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 5 2.04M 30 30 30 30 0 30
# String:
# Databases xsuportal
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us ################################################################
# 100us #########################
# 1ms #
# 10ms #
# 100ms
# 1s
# 10s+
administrator command: Prepare\G
# Query 6: 144.80 QPS, 0.11x concurrency, ID 0x2ABC4FED164E43995860DC28DCB5BAE4 at byte 57598914
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-10-03T02:43:19 to 2020-10-03T02:44:23
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 9267
# Exec time 3 7s 141us 27ms 752us 2ms 1ms 403us
# Lock time 1 509ms 12us 17ms 54us 76us 320us 28us
# Rows sent 93 2.06M 0 320 233.25 284.79 64.78 258.32
# Rows examine 81 4.17M 0 640 471.47 592.07 131.43 511.45
# Query size 9 3.44M 389 391 389.02 381.65 0 381.65
# String:
# Databases xsuportal
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms #######
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xsuportal` LIKE 'benchmark_jobs'\G
# SHOW CREATE TABLE `xsuportal`.`benchmark_jobs`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT
`team_id` AS `team_id`,
(`score_raw` - `score_deduction`) AS `score`,
`started_at` AS `started_at`,
`finished_at` AS `finished_at`
FROM
`benchmark_jobs`
WHERE
`started_at` IS NOT NULL
AND (
`finished_at` IS NOT NULL
-- score freeze
AND (`team_id` = 0 OR (`team_id` != 0 AND (0 = TRUE OR `finished_at` < '2020-10-03 02:43:59')))
)
ORDER BY
`finished_at`\G
# Query 7: 64.10 QPS, 0.06x concurrency, ID 0x6C28D5AD2F9252CCFAD00FDB6F5C9080 at byte 61010156
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.28
# Time range: 2020-10-03T02:43:09 to 2020-10-03T02:44:08
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 3782
# Exec time 1 4s 75us 590ms 958us 839us 16ms 214us
# Lock time 7 2s 12us 590ms 657us 66us 16ms 26us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 1 68.15k 0 52 18.45 40.45 14.85 18.53
# Query size 0 360.09k 93 98 97.50 97.36 1.40 97.36
# String:
# Databases xsuportal
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us #
# 100us ################################################################
# 1ms ##
# 10ms #
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xsuportal` LIKE 'notifications'\G
# SHOW CREATE TABLE `xsuportal`.`notifications`\G
UPDATE `notifications` SET `read` = TRUE WHERE `contestant_id` = 'RcbWlPlfHBrb' AND `read` = FALSE\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select `read` = TRUE from `notifications` where `contestant_id` = 'RcbWlPlfHBrb' AND `read` = FALSE\G
# Query 8: 116.85 QPS, 0.03x concurrency, ID 0x6CDF0F958A105B514036B56CB7B5F15F at byte 160137
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.04
# Time range: 2020-10-03T02:43:00 to 2020-10-03T02:44:24
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 9815
# Exec time 1 3s 41us 127ms 277us 316us 3ms 93us
# Lock time 4 2s 11us 127ms 159us 63us 3ms 23us
# Rows sent 0 9.58k 1 1 1 1 0 1
# Rows examine 0 9.58k 1 1 1 1 0 1
# Query size 1 596.64k 56 63 62.25 62.76 2.27 62.76
# String:
# Databases xsuportal
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us ################################################################
# 100us ##########################################################
# 1ms #
# 10ms #
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xsuportal` LIKE 'contestants'\G
# SHOW CREATE TABLE `xsuportal`.`contestants`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `contestants` WHERE `id` = 'CWD10wv19PY5' LIMIT 1\G
# Query 9: 124.02 QPS, 0.03x concurrency, ID 0x37B56D8F93E12D26C3984A089C0C44E6 at byte 32553280
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-10-03T02:43:00 to 2020-10-03T02:44:23
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 10294
# Exec time 1 2s 73us 56ms 213us 366us 744us 138us
# Lock time 3 1s 39us 56ms 122us 185us 657us 76us
# Rows sent 0 10.05k 1 1 1 1 0 1
# Rows examine 0 10.05k 1 1 1 1 0 1
# Query size 11 4.45M 453 453 453 453 0 453
# String:
# Databases xsuportal
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us ##########
# 100us ################################################################
# 1ms #
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xsuportal` LIKE 'contest_config'\G
# SHOW CREATE TABLE `xsuportal`.`contest_config`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT *, NOW(6) AS `current_time`, CASE WHEN NOW(6) < `registration_open_at` THEN 'standby' WHEN `registration_open_at` <= NOW(6) AND NOW(6) < `contest_starts_at` THEN 'registration' WHEN `contest_starts_at` <= NOW(6) AND NOW(6) < `contest_ends_at` THEN 'started' WHEN `contest_ends_at` <= NOW(6) THEN 'finished' ELSE 'unknown' END AS `status`, IF(`contest_starts_at` <= NOW(6) AND NOW(6) < `contest_freezes_at`, 1, 0) AS `frozen` FROM `contest_config`\G
# Query 10: 197.65 QPS, 0.04x concurrency, ID 0xA0B645FAC805F824A18DBE6A05ED8451 at byte 69154129
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-10-03T02:43:09 to 2020-10-03T02:44:09
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 4 11859
# Exec time 1 2s 62us 14ms 183us 247us 433us 138us
# Lock time 1 346ms 10us 6ms 29us 49us 59us 23us
# Rows sent 0 1.04k 0 1 0.09 0.99 0.28 0
# Rows examine 0 12.38k 0 38 1.07 4.96 4.46 0
# Query size 2 822.25k 71 71 71 71 0 71
# String:
# Databases xsuportal
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us ######
# 100us ################################################################
# 1ms #
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xsuportal` LIKE 'benchmark_jobs'\G
# SHOW CREATE TABLE `xsuportal`.`benchmark_jobs`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `benchmark_jobs` WHERE `status` = 0 ORDER BY `id` LIMIT 1\G
# Query 11: 5.22 QPS, 0.14x concurrency, ID 0x426CC7F7874CAD705DEA0E3BE88FDF2F at byte 173664
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.03
# Time range: 2020-10-03T02:43:00 to 2020-10-03T02:43:09
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 47
# Exec time 0 1s 113us 122ms 27ms 91ms 26ms 18ms
# Lock time 1 498ms 26us 116ms 11ms 78ms 25ms 63us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 0 7.97k 169 174 173.68 166.51 0 166.51
# String:
# Databases xsuportal
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us ##
# 1ms ######################
# 10ms ################################################################
# 100ms ######
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `xsuportal` LIKE 'contestants'\G
# SHOW CREATE TABLE `xsuportal`.`contestants`\G
INSERT INTO `contestants` (`id`, `password`, `staff`, `created_at`) VALUES ('BSxMoOfLJAoI', '9202626a79aed176a975225e43293b3f245c8e380f73c23e4a6fc45ecc6fcab8', FALSE, NOW(6))\G
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment