MySQL 慢查询日志分析
1. pt-query-digest分析慢查询日志
1
|
pt
-
query
-
digest
--
report
slow
.
log
|
2. 报告最近半个小时的慢查询:
1
|
pt
-
query
-
digest
--
report
--
since
1800s
slow
.
log
|
3. 报告一个时间段的慢查询:
1
|
pt
-
query
-
digest
--
report
--
since
'2013-02-10 21:48:59'
--
until
'2013-02-16 02:33:50'
slow
.
log
|
4. 报告只含select语句的慢查询:
1
|
pt
-
query
-
digest
--
filter
'$event->{fingerprint} =~ m/^select/i'
slow
.
log
|
5. 报告针对某个用户的慢查询:
1
|
pt
-
query
-
digest
--
filter
'($event->{user} || "") =~ m/^root/i'
slow
.
log
|
6. 报告所有的全表扫描或full join的慢查询:
1
|
pt
-
query
-
digest
--
filter
'(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")'
slow
.
log
|
更多filter的事件属性
将慢查询日志的分析结果可视化
使用pt-query-digest分析慢查询日志并将查询分析数据保存到MySQL数据库表中.然后使用Query-Digest-UI来展示分析结果.
由于Query-Digest-UI是基于PHP的Web应用程序,因此需要LAMP环境的支持.
查询分析结果可视化步骤如下:
1)创建相关数据库表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
--
install
.
sql
--
Create
the
database
needed
for
the
Query
-
Digest
-
UI
DROP
DATABASE
IF
EXISTS
slow_query_log
;
CREATE
DATABASE
slow_query_log
;
USE
slow_query_log
;
--
Create
the
global
query
review
table
CREATE
TABLE
`
global_query_review
`
(
`
checksum
`
bigint
(
20
)
unsigned
NOT
NULL
,
`
fingerprint
`
text
NOT
NULL
,
`
sample
`
longtext
NOT
NULL
,
`
first_seen
`
datetime
DEFAULT
NULL
,
`
last_seen
`
datetime
DEFAULT
NULL
,
`
reviewed_by
`
varchar
(
20
)
DEFAULT
NULL
,
`
reviewed_on
`
datetime
DEFAULT
NULL
,
`
comments
`
text
,
`
reviewed_status
`
varchar
(
24
)
DEFAULT
NULL
,
PRIMARY
KEY
(
`
checksum
`
)
)
ENGINE
=
InnoDB
DEFAULT
CHARSET
=
utf8
;
--
Create
the
historical
query
review
table
CREATE
TABLE
`
global_query_review_history
`
(
`
hostname_max
`
varchar
(
64
)
NOT
NULL
,
`
db_max
`
varchar
(
64
)
DEFAULT
NULL
,
`
checksum
`
bigint
(
20
)
unsigned
NOT
NULL
,
`
sample
`
longtext
NOT
NULL
,
`
ts_min
`
datetime
NOT
NULL
DEFAULT
'0000-00-00 00:00:00'
,
`
ts_max
`
datetime
NOT
NULL
DEFAULT
'0000-00-00 00:00:00'
,
`
ts_cnt
`
float
DEFAULT
NULL
,
`
Query_time_sum
`
float
DEFAULT
NULL
,
`
Query_time_min
`
float
DEFAULT
NULL
,
`
Query_time_max
`
float
DEFAULT
NULL
,
`
Query_time_pct_95
`
float
DEFAULT
NULL
,
`
Query_time_stddev
`
float
DEFAULT
NULL
,
`
Query_time_median
`
float
DEFAULT
NULL
,
`
Lock_time_sum
`
float
DEFAULT
NULL
,
`
Lock_time_min
`
float
DEFAULT
NULL
,
`
Lock_time_max
`
float
DEFAULT
NULL
,
`
Lock_time_pct_95
`
float
DEFAULT
NULL
,
`
Lock_time_stddev
`
float
DEFAULT
NULL
,
`
Lock_time_median
`
float
DEFAULT
NULL
,
`
Rows_sent_sum
`
float
DEFAULT
NULL
,
`
Rows_sent_min
`
float
DEFAULT
NULL
,
`
Rows_sent_max
`
float
DEFAULT
NULL
,
`
Rows_sent_pct_95
`
float
DEFAULT
NULL
,
`
Rows_sent_stddev
`
float
DEFAULT
NULL
,
`
Rows_sent_median
`
float
DEFAULT
NULL
,
`
Rows_examined_sum
`
float
DEFAULT
NULL
,
`
Rows_examined_min
`
float
DEFAULT
NULL
,
`
Rows_examined_max
`
float
DEFAULT
NULL
,
`
Rows_examined_pct_95
`
float
DEFAULT
NULL
,
`
Rows_examined_stddev
`
float
DEFAULT
NULL
,
`
Rows_examined_median
`
float
DEFAULT
NULL
,
`
Rows_affected_sum
`
float
DEFAULT
NULL
,
`
Rows_affected_min
`
float
DEFAULT
NULL
,
`
Rows_affected_max
`
float
DEFAULT
NULL
,
`
Rows_affected_pct_95
`
float
DEFAULT
NULL
,
`
Rows_affected_stddev
`
float
DEFAULT
NULL
,
`
Rows_affected_median
`
float
DEFAULT
NULL
,
`
Rows_read_sum
`
float
DEFAULT
NULL
,
`
Rows_read_min
`
float
DEFAULT
NULL
,
`
Rows_read_max
`
float
DEFAULT
NULL
,
`
Rows_read_pct_95
`
float
DEFAULT
NULL
,
`
Rows_read_stddev
`
float
DEFAULT
NULL
,
`
Rows_read_median
`
float
DEFAULT
NULL
,
`
Merge_passes_sum
`
float
DEFAULT
NULL
,
`
Merge_passes_min
`
float
DEFAULT
NULL
,
`
Merge_passes_max
`
float
DEFAULT
NULL
,
`
Merge_passes_pct_95
`
float
DEFAULT
NULL
,
`
Merge_passes_stddev
`
float
DEFAULT
NULL
,
`
Merge_passes_median
`
float
DEFAULT
NULL
,
`
InnoDB_IO_r_ops_min
`
float
DEFAULT
NULL
,
`
InnoDB_IO_r_ops_max
`
float
DEFAULT
NULL
,
`
InnoDB_IO_r_ops_pct_95
`
float
DEFAULT
NULL
,
`
InnoDB_IO_r_bytes_pct_95
`
float
DEFAULT
NULL
,
`
InnoDB_IO_r_bytes_stddev
`
float
DEFAULT
NULL
,
`
InnoDB_IO_r_bytes_median
`
float
DEFAULT
NULL
,
`
InnoDB_IO_r_wait_min
`
float
DEFAULT
NULL
,
`
InnoDB_IO_r_wait_max
`
float
DEFAULT
NULL
,
`
InnoDB_IO_r_wait_pct_95
`
float
DEFAULT
NULL
,
`
InnoDB_IO_r_ops_stddev
`
float
DEFAULT
NULL
,
`
InnoDB_IO_r_ops_median
`
float
DEFAULT
NULL
,
`
InnoDB_IO_r_bytes_min
`
float
DEFAULT
NULL
,
`
InnoDB_IO_r_bytes_max
`
float
DEFAULT
NULL
,
`
InnoDB_IO_r_wait_stddev
`
float
DEFAULT
NULL
,
`
InnoDB_IO_r_wait_median
`
float
DEFAULT
NULL
,
`
InnoDB_rec_lock_wait_min
`
float
DEFAULT
NULL
,
`
InnoDB_rec_lock_wait_max
`
float
DEFAULT
NULL
,
`
InnoDB_rec_lock_wait_pct_95
`
float
DEFAULT
NULL
,
`
InnoDB_rec_lock_wait_stddev
`
float
DEFAULT
NULL
,
`
InnoDB_rec_lock_wait_median
`
float
DEFAULT
NULL
,
`
InnoDB_queue_wait_min
`
float
DEFAULT
NULL
,
`
InnoDB_queue_wait_max
`
float
DEFAULT
NULL
,
`
InnoDB_queue_wait_pct_95
`
float
DEFAULT
NULL
,
`
InnoDB_queue_wait_stddev
`
float
DEFAULT
NULL
,
`
InnoDB_queue_wait_median
`
float
DEFAULT
NULL
,
`
InnoDB_pages_distinct_min
`
float
DEFAULT
NULL
,
`
InnoDB_pages_distinct_max
`
float
DEFAULT
NULL
,
`
InnoDB_pages_distinct_pct_95
`
float
DEFAULT
NULL
,
`
InnoDB_pages_distinct_stddev
`
float
DEFAULT
NULL
,
`
InnoDB_pages_distinct_median
`
float
DEFAULT
NULL
,
`
QC_Hit_cnt
`
float
DEFAULT
NULL
,
`
QC_Hit_sum
`
float
DEFAULT
NULL
,
`
Full_scan_cnt
`
float
DEFAULT
NULL
,
`
Full_scan_sum
`
float
DEFAULT
NULL
,
`
Full_join_cnt
`
float
DEFAULT
NULL
,
`
Full_join_sum
`
float
DEFAULT
NULL
,
`
Tmp_table_cnt
`
float
DEFAULT
NULL
,
`
Tmp_table_sum
`
float
DEFAULT
NULL
,
`
Filesort_cnt
`
float
DEFAULT
NULL
,
`
Filesort_sum
`
float
DEFAULT
NULL
,
`
Tmp_table_on_disk_cnt
`
float
DEFAULT
NULL
,
`
Tmp_table_on_disk_sum
`
float
DEFAULT
NULL
,
`
Filesort_on_disk_cnt
`
float
DEFAULT
NULL
,
`
Filesort_on_disk_sum
`
float
DEFAULT
NULL
,
`
Bytes_sum
`
float
DEFAULT
NULL
,
`
Bytes_min
`
float
DEFAULT
NULL
,
`
Bytes_max
`
float
DEFAULT
NULL
,
`
Bytes_pct_95
`
float
DEFAULT
NULL
,
`
Bytes_stddev
`
float
DEFAULT
NULL
,
`
Bytes_median
`
float
DEFAULT
NULL
,
UNIQUE
KEY
`
hostname_max
`
(
`
hostname_max
`
,
`
checksum
`
,
`
ts_min
`
,
`
ts_max
`
)
,
KEY
`
ts_min
`
(
`
ts_min
`
)
,
KEY
`
checksum
`
(
`
checksum
`
)
)
ENGINE
=
InnoDB
DEFAULT
CHARSET
=
utf8
;
|
2) 创建数据库账号
1
2
|
$
mysql
-
uroot
-
p
-
h
192.168.1.190
<
install
.
sql
$
mysql
-
uroot
-
p
-
h
192.168.1.190
-
e
"grant ALL ON slow_query_log.* to 'slowlog'@'%' IDENTIFIED BY '123456';"
|
3) 配置Query-Digest-UI
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
git
clone
https
:
//github.com/kormoc/Query-Digest-UI.git
cd
Query
-
Digest
-
UI
cp
config
.
php
.
example
config
.
php
vi
config
.
php
$
reviewhost
=
array
(
// Replace hostname and database in this setting
// use host=hostname;port=portnum if not the default port
'dsn'
=
>
'mysql:host=192.168.1.190;port=3306;dbname=slow_query_log'
,
'user'
=
>
'slowlog'
,
'password'
=
>
'123456'
,
// See http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html#cmdoption-pt-query-digest--review
'review_table'
=
>
'global_query_review'
,
// This table is optional. You don't need it, but you lose detailed stats
// Set to a blank string to disable
// See http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html#cmdoption-pt-query-digest--review-history
'history_table'
=
>
'global_query_review_history'
,
)
;
|
4)使用pt-query-digest分析日志并将分析结果导入数据库
1
|
pt
-
query
-
digest
--
user
=
slowlog
--
password
=
123456
--
review
h
=
192.168.1.190
,
D
=
slow_query_log
,
t
=
global_query_review
--
review
-
history
h
=
192.168.1.190
,
D
=
slow_query_log
,
t
=
global_query_review_history
--
no
-
report
--
limit
=
0
%
--
filter
=
" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\""
/
usr
/
local
/
mysql
/
data
/
slow
.
log
|
5)访问web界面查看可视化结果
转自:http://www.zrwm.com/?p=2668