欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

对MySQL慢查询日志进行分析的基本教程

程序员文章站 2024-02-25 11:02:34
0、首先查看当前是否开启慢查询: (1)快速办法,运行sql语句 show variables like "%slow%" (2)直接去my.con...

0、首先查看当前是否开启慢查询:

(1)快速办法,运行sql语句

show variables like "%slow%" 

(2)直接去my.conf中查看。

my.conf中的配置(放在[mysqld]下的下方加入)

[mysqld]

log-slow-queries = /usr/local/mysql/var/slowquery.log
long_query_time = 1 #单位是秒
log-queries-not-using-indexes


使用sql语句来修改:不能按照my.conf中的项来修改的。修改通过"show variables like "%slow%" "
语句列出来的变量,运行如下sql:

set global log_slow_queries = on;
set global slow_query_log = on;
set global long_query_time=0.1; #设置大于0.1s的sql语句记录下来

慢查询日志文件的信息格式:

# time: 130905 14:15:59   时间是2013年9月5日 14:15:59(前面部分容易看错哦,乍看以为是时间戳)
# user@host: root[root] @ [183.239.28.174] 请求mysql服务器的客户端ip
# query_time: 0.735883 lock_time: 0.000078 rows_sent: 262 rows_examined: 262 这里表示执行用时多少秒,0.735883秒,1秒等于1000毫秒

set timestamp=1378361759;  这目前我还不知道干嘛用的
show tables from `test_db`; 这个就是关键信息,指明了当时执行的是这条语句


1、mysql 慢查询日志分析
pt-query-digest分析慢查询日志

pt-query-digest –report slow.log

报告最近半个小时的慢查询:

pt-query-digest –report –since 1800s slow.log

报告一个时间段的慢查询:

pt-query-digest –report –since ‘2013-02-10 21:48:59′ –until ‘2013-02-16 02:33:50′ slow.log

报告只含select语句的慢查询:

pt-query-digest –filter ‘$event->{fingerprint} =~ m/^select/i' slow.log

报告针对某个用户的慢查询:

pt-query-digest –filter ‘($event->{user} || “”) =~ m/^root/i' slow.log

报告所有的全表扫描或full join的慢查询:

pt-query-digest –filter ‘(($event->{full_scan} || “”) eq “yes”) || (($event->{full_join} || “”) eq “yes”)' slow.log


2、将慢查询日志的分析结果可视化
query-digest-ui
其实,这是一个非常简单和直接的工具,浏览和统计mysql慢查询,基于ajax的web界面。
配置query-digest-ui:

下载:

wget https://nodeload.github.com/kormoc/query-digest-ui/zip/master
 unzip query-digest-ui-master.zip

查询分析结果可视化步骤如下:

(1)创建相关数据库表

-- 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)创建数据库账号

$ 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

修改数据库连接配置

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分析日志并将分析结果导入数据库

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