· 6 years ago · Aug 25, 2019, 10:18 PM
1Analyzing Slow Query Logs:
2
3# A software update is available:
4# * The current version for Percona::Toolkit is 3.0.5
5
6
7# 280ms user time, 30ms system time, 41.43M rss, 147.87M vsz
8# Current date: Mon Aug 12 17:40:03 2019
9# Hostname: dbserver-cc10651d.c.pantheon-dmz.internal
10# Files: mysqld-slow-query.log
11# Overall: 10 total, 4 unique, 0.00 QPS, 0.00x concurrency _______________
12# Time range: 2019-01-01 12:45:35 to 2019-07-30 18:05:23
13# Attribute total min max avg 95% stddev median
14# ============ ======= ======= ======= ======= ======= ======= =======
15# Exec time 894s 1s 595s 89s 130s 173s 11s
16# Lock time 1ms 0 498us 137us 287us 143us 76us
17# Rows sent 8 0 4 0.80 3.89 1.56 0
18# Rows examine 2.39k 0 1.22k 244.90 1.14k 480.80 0
19# Query size 3.17k 44 479 324.90 463.90 183.10 463.90
20
21# Profile
22# Rank Query ID Response time Calls R/Call V/M Item
23# ==== ================== ============== ===== ======== ===== ============
24# 1 0x64A0730403ECB7B9 752.6952 84.2% 6 125.4492 36... CREATE TABLE pantheon._pt_heartbeat `pantheon`.`_pt_heartbeat`
25# 2 0x55217AC13337017A 73.2818 8.2% 1 73.2818 0.00 INSERT pantheon._pt_heartbeat
26# 3 0x2C386E865C5162BE 61.8104 6.9% 1 61.8104 0.00 SHOW CREATE TABLE
27# MISC 0xMISC 6.1102 0.7% 2 3.0551 0.0 <1 ITEMS>
28
29# Query 1: 0.00 QPS, 0.00x concurrency, ID 0x64A0730403ECB7B9 at byte 3934
30# This item is included in the report because it matches --limit.
31# Scores: V/M = 365.91
32# Time range: 2019-01-01 12:45:35 to 2019-07-04 09:35:19
33# Attribute pct total min max avg 95% stddev median
34# ============ === ======= ======= ======= ======= ======= ======= =======
35# Count 60 6
36# Exec time 84 753s 1s 595s 125s 592s 214s 71s
37# Lock time 38 527us 42us 210us 87us 204us 54us 78us
38# Rows sent 0 0 0 0 0 0 0 0
39# Rows examine 0 0 0 0 0 0 0 0
40# Query size 88 2.81k 479 479 479 479 0 479
41# String:
42# Databases pantheon
43# Hosts 127.0.0.1
44# Users pantheon
45# Query_time distribution
46# 1us
47# 10us
48# 100us
49# 1ms
50# 10ms
51# 100ms
52# 1s ################################################################
53# 10s+ ################################################################
54# Tables
55# SHOW TABLE STATUS FROM `pantheon` LIKE '_pt_heartbeat'\G
56# SHOW CREATE TABLE `pantheon`.`_pt_heartbeat`\G
57CREATE TABLE IF NOT EXISTS `pantheon`.`_pt_heartbeat` (
58 ts varchar(26) NOT NULL,
59 server_id int unsigned NOT NULL PRIMARY KEY,
60 file varchar(255) DEFAULT NULL, -- SHOW MASTER STATUS
61 position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
62 relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS
63 exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
64 )\G
65
66# Query 2: 0 QPS, 0x concurrency, ID 0x55217AC13337017A at byte 2133 _____
67# This item is included in the report because it matches --limit.
68# Scores: V/M = 0.00
69# Time range: all events occurred at 2019-02-09 11:35:09
70# Attribute pct total min max avg 95% stddev median
71# ============ === ======= ======= ======= ======= ======= ======= =======
72# Count 10 1
73# Exec time 8 73s 73s 73s 73s 73s 0 73s
74# Lock time 4 56us 56us 56us 56us 56us 0 56us
75# Rows sent 0 0 0 0 0 0 0 0
76# Rows examine 0 0 0 0 0 0 0 0
77# Query size 2 81 81 81 81 81 0 81
78# String:
79# Databases pantheon
80# Hosts 127.0.0.1
81# Users pantheon
82# Query_time distribution
83# 1us
84# 10us
85# 100us
86# 1ms
87# 10ms
88# 100ms
89# 1s
90# 10s+ ################################################################
91# Tables
92# SHOW TABLE STATUS FROM `pantheon` LIKE '_pt_heartbeat'\G
93# SHOW CREATE TABLE `pantheon`.`_pt_heartbeat`\G
94INSERT INTO `pantheon`.`_pt_heartbeat` (ts, server_id) VALUES (NOW(), 1612276056)\G
95
96# Query 3: 0 QPS, 0x concurrency, ID 0x2C386E865C5162BE at byte 1157 _____
97# This item is included in the report because it matches --limit.
98# Scores: V/M = 0.00
99# Time range: all events occurred at 2019-01-10 15:27:43
100# Attribute pct total min max avg 95% stddev median
101# ============ === ======= ======= ======= ======= ======= ======= =======
102# Count 10 1
103# Exec time 6 62s 62s 62s 62s 62s 0 62s
104# Lock time 0 0 0 0 0 0 0 0
105# Rows sent 0 0 0 0 0 0 0 0
106# Rows examine 0 0 0 0 0 0 0 0
107# Query size 1 44 44 44 44 44 0 44
108# String:
109# Databases pantheon
110# Hosts 127.0.0.1
111# Users pantheon
112# Query_time distribution
113# 1us
114# 10us
115# 100us
116# 1ms
117# 10ms
118# 100ms
119# 1s
120# 10s+ ################################################################
121SHOW CREATE TABLE `pantheon`.`_pt_heartbeat`\G
122
123# A software update is available:
124# * The current version for Percona::Toolkit is 3.0.5
125
126
127# 290ms user time, 60ms system time, 41.44M rss, 147.88M vsz
128# Current date: Mon Aug 12 17:40:07 2019
129# Hostname: dbserver-c012afb0.c.pantheon-dmz.internal
130# Files: mysqld-slow-query.log
131# Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________
132# Time range: all events occurred at 2019-08-12 17:34:14
133# Attribute total min max avg 95% stddev median
134# ============ ======= ======= ======= ======= ======= ======= =======
135# Exec time 1s 1s 1s 1s 1s 0 1s
136# Lock time 114us 114us 114us 114us 114us 0 114us
137# Rows sent 4 4 4 4 4 0 4
138# Rows examine 1.22k 1.22k 1.22k 1.22k 1.22k 0 1.22k
139# Query size 125 125 125 125 125 0 125
140
141# Profile
142# Rank Query ID Response time Calls R/Call V/M Item
143# ==== ================== ============= ===== ====== ===== ===============
144# 1 0x490138CB77CD4D78 1.3708 100.0% 1 1.3708 0.00 SELECT information_schema.tables
145
146# Query 1: 0 QPS, 0x concurrency, ID 0x490138CB77CD4D78 at byte 0 ________
147# This item is included in the report because it matches --limit.
148# Scores: V/M = 0.00
149# Time range: all events occurred at 2019-08-12 17:34:14
150# Attribute pct total min max avg 95% stddev median
151# ============ === ======= ======= ======= ======= ======= ======= =======
152# Count 100 1
153# Exec time 100 1s 1s 1s 1s 1s 0 1s
154# Lock time 100 114us 114us 114us 114us 114us 0 114us
155# Rows sent 100 4 4 4 4 4 0 4
156# Rows examine 100 1.22k 1.22k 1.22k 1.22k 1.22k 0 1.22k
157# Query size 100 125 125 125 125 125 0 125
158# String:
159# Hosts 35.188.101.186
160# Users pantheon
161# Query_time distribution
162# 1us
163# 10us
164# 100us
165# 1ms
166# 10ms
167# 100ms
168# 1s ################################################################
169# 10s+
170# Tables
171# SHOW TABLE STATUS FROM `information_schema` LIKE 'tables'\G
172# SHOW CREATE TABLE `information_schema`.`tables`\G
173# EXPLAIN /*!50100 PARTITIONS*/
174SELECT table_schema, IFNULL(ROUND(sum(DATA_LENGTH) + sum(INDEX_LENGTH)), 0) AS size FROM information_schema.tables group by 1\G