1.直接分析慢查询
pt-query-digest slow.log>slow.sql
2.分析半个小时的慢查询
pt-query-digest --since 1800s slow.log>slow.sql
3.分析一段时间范围的慢查询
pt-query-digest --since '2013-02-10 21:48:59' --until '2013-02-16 02:33:50' slow.log>slow.sql
4.分析指含有select语句的慢查询
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log>slow.sql
5.针对某个用户的慢查询
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log>slow.sql
6.查询所有所有的全表扫描或full join的慢查询
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' slow.log>slow.sql
7.把查询保存到query_review中,注意query_review表的表结构必须先建好,表结构如下:
CREATE TABLE query_review (
checksum BIGINT UNSIGNED NOT NULL PRIMARY KEY,
fingerprint TEXT NOT NULL,
sample TEXT NOT NULL,
first_seen DATETIME,
last_seen DATETIME,
reviewed_by VARCHAR(20),
reviewed_on DATETIME,
comments TEXT
);
pt-query-digest --user=root --password=xxxx --review h=localhost,D=test,t=query_review /data/dbdata/localhost-slow.log
阅读(3263) | 评论(0) | 转发(0) |