Skip to content

Instantly share code, notes, and snippets.

@mactkg

mactkg/pt-query.txt

Last active July 28, 2019 07:08
Show Gist options
  • Save mactkg/1fbf5e826e9fc0ab3314c7c794b1553e to your computer and use it in GitHub Desktop.
Save mactkg/1fbf5e826e9fc0ab3314c7c794b1553e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
pt-query.txt
# 43.2s user time, 160ms system time, 44.84M rss, 107.73M vsz
# Current date: Sun Jul 28 15:06:18 2019
# Hostname: iZ6we6ismi6srrazihvganZ
# Files: /var/log/mysql/slow.log
# Overall: 341.45k total, 56 unique, 295.89 QPS, 0.06x concurrency _______
# Time range: 2019-07-28T06:46:53 to 2019-07-28T07:06:07
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 70s 5us 12s 204us 260us 20ms 76us
# Lock time 11s 0 32ms 31us 54us 258us 28us
# Rows sent 350.83k 0 300 1.05 0.99 7.76 0
# Rows examine 8.03M 0 12.16k 24.64 0.99 144.89 0
# Query size 170.03M 8 1.47M 522.16 62.76 18.56k 56.92
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== =========================== ============= ====== ======= ===== ====
# 1 0x2229826778431E8B171E93... 16.0175 22.9% 2454 0.0065 0.27 INSERT message
# 2 0x91FDB098FE1F1D3EB7F196... 11.7275 16.8% 1 11.7275 0.00 SELECT isubata.image
# 3 0xB36405B8C0D2F0C74866C9... 11.4133 16.3% 70503 0.0002 0.00 SELECT message
# 4 0xDF664753A9FC79D32CF41F... 7.4802 10.7% 70600 0.0001 0.00 SELECT haveread
# 5 0x23E986FD32D530D2691DE3... 5.1202 7.3% 47932 0.0001 0.00 SELECT user
# 6 0x98584C6216E0B67546AAE7... 4.9884 7.1% 944 0.0053 0.26 INSERT UPDATE haveread
# 7 0xE1F097E42A11604ECA5E81... 3.5587 5.1% 255 0.0140 0.00 INSERT image
# 8 0xE8390778DC20D4CC04FE01... 2.2455 3.2% 138064 0.0000 0.00 ADMIN PING
# 9 0x5C15E821AA89B226061694... 1.9274 2.8% 260 0.0074 0.30 INSERT user
# 10 0x086EF2D69E0CA10C16A139... 1.3496 1.9% 4 0.3374 0.02 SELECT image
# MISC 0xMISC 4.0764 5.8% 10438 0.0004 0.0 <46 ITEMS>
# Query 1: 46.30 QPS, 0.30x concurrency, ID 0x2229826778431E8B171E93102FB2256E at byte 85478252
# Scores: V/M = 0.27
# Time range: 2019-07-28T06:47:00 to 2019-07-28T06:47:53
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 2454
# Exec time 22 16s 547us 722ms 7ms 9ms 42ms 2ms
# Lock time 1 178ms 25us 8ms 72us 103us 171us 60us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 0 1.03M 200 1.41k 438.82 652.75 132.25 400.73
# String:
# Databases isubata
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us ##
# 1ms ################################################################
# 10ms ##
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isubata` LIKE 'message'\G
# SHOW CREATE TABLE `isubata`.`message`\G
INSERT INTO message (channel_id, user_id, content, created_at) VALUES ('528', 25, '聞いてるかねみんな聞いてるよ。でその下へ行水盥を出しましてね。そのくらいな事はいかな苦沙弥でも心得ているはずですが。', NOW())\G
# Query 2: 0 QPS, 0x concurrency, ID 0x91FDB098FE1F1D3EB7F19670FE9A1696 at byte 247582989
# Scores: V/M = 0.00
# Time range: all events occurred at 2019-07-28T06:56:31
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 1
# Exec time 16 12s 12s 12s 12s 12s 0 12s
# Lock time 0 147us 147us 147us 147us 147us 0 147us
# Rows sent 0 300 300 300 300 300 0 300
# Rows examine 0 300 300 300 300 300 0 300
# Query size 0 64 64 64 64 64 0 64
# String:
# Databases isubata
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `isubata` LIKE 'image'\G
# SHOW CREATE TABLE `isubata`.`image`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `isubata`.`image` ORDER BY `id` LIMIT 300 OFFSET 0\G
# Query 3: 1.22k QPS, 0.20x concurrency, ID 0xB36405B8C0D2F0C74866C96BA295C56E at byte 28204678
# Scores: V/M = 0.00
# Time range: 2019-07-28T06:46:56 to 2019-07-28T06:47:54
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 20 70503
# Exec time 16 11s 68us 36ms 161us 366us 278us 113us
# Lock time 27 3s 16us 11ms 41us 57us 91us 33us
# Rows sent 19 68.85k 1 1 1 1 0 1
# Rows examine 93 7.53M 0 1.01k 111.92 964.41 297.37 0
# Query size 2 3.89M 56 60 57.83 56.92 0.82 56.92
# String:
# Databases isubata
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us ##################
# 100us ################################################################
# 1ms #
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isubata` LIKE 'message'\G
# SHOW CREATE TABLE `isubata`.`message`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) as cnt FROM message WHERE channel_id = 8\G
# Query 4: 1.28k QPS, 0.14x concurrency, ID 0xDF664753A9FC79D32CF41F0982B38E37 at byte 28041909
# Scores: V/M = 0.00
# Time range: 2019-07-28T06:46:59 to 2019-07-28T06:47:54
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 20 70600
# Exec time 10 7s 52us 23ms 105us 144us 298us 84us
# Lock time 29 3s 18us 5ms 44us 63us 63us 36us
# Rows sent 0 703 0 1 0.01 0 0.10 0
# Rows examine 0 703 0 1 0.01 0 0.10 0
# Query size 2 4.22M 59 64 62.65 62.76 1.24 62.76
# String:
# Databases isubata
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us ################################################################
# 100us ##########################
# 1ms #
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isubata` LIKE 'haveread'\G
# SHOW CREATE TABLE `isubata`.`haveread`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM haveread WHERE user_id = 468 AND channel_id = 544\G
# Query 5: 826.41 QPS, 0.09x concurrency, ID 0x23E986FD32D530D2691DE3FA84054868 at byte 192764230
# Scores: V/M = 0.00
# Time range: 2019-07-28T06:46:56 to 2019-07-28T06:47:54
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 14 47932
# Exec time 7 5s 53us 11ms 106us 152us 189us 89us
# Lock time 18 2s 16us 3ms 41us 60us 54us 35us
# Rows sent 13 46.81k 1 1 1 1 0 1
# Rows examine 0 46.81k 1 1 1 1 0 1
# Query size 1 2.88M 61 64 62.91 62.76 0.84 62.76
# String:
# Databases isubata
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us ################################################################
# 100us ################################
# 1ms #
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isubata` LIKE 'user'\G
# SHOW CREATE TABLE `isubata`.`user`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT name, display_name, avatar_icon FROM user WHERE id = 768\G
# Query 6: 17.16 QPS, 0.09x concurrency, ID 0x98584C6216E0B67546AAE7D349F5E3D9 at byte 85476601
# Scores: V/M = 0.26
# Time range: 2019-07-28T06:46:59 to 2019-07-28T06:47:54
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 944
# Exec time 7 5s 564us 734ms 5ms 7ms 37ms 2ms
# Lock time 0 78ms 36us 5ms 82us 119us 162us 66us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 0 184.42k 192 203 200.05 192.76 0.87 192.76
# String:
# Databases isubata
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us ##
# 1ms ################################################################
# 10ms #
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isubata` LIKE 'haveread'\G
# SHOW CREATE TABLE `isubata`.`haveread`\G
INSERT INTO haveread (user_id, channel_id, message_id, updated_at, created_at)
VALUES (919, '523', 13498, NOW(), NOW())
ON DUPLICATE KEY UPDATE message_id = 13498, updated_at = NOW()\G
# Query 7: 4.64 QPS, 0.06x concurrency, ID 0xE1F097E42A11604ECA5E8124C2622472 at byte 104084683
# Scores: V/M = 0.00
# Time range: 2019-07-28T06:46:58 to 2019-07-28T06:47:53
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 255
# Exec time 5 4s 3ms 46ms 14ms 30ms 8ms 12ms
# Lock time 16 2s 935us 24ms 7ms 16ms 5ms 6ms
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 90 153.76M 110.42k 1.47M 617.43k 1.26M 314.76k 562.03k
# String:
# Databases isubata
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ######################################
# 10ms ################################################################
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isubata` LIKE 'image'\G
# SHOW CREATE TABLE `isubata`.`image`\G
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment