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

数据运维 2023-04-25 大白菜程序猿手机阅读

0、首先查看当前是否开启慢查询：（1）快速办法，运行sql语句 show VARIABLES like "%slow%" （2）直接去my.conf中查看。 my.conf中的配置(放在[mysqld]下的下方加入) [mysqld]log-slow-queries = /usr/loca

                        <p>0、首先查看当前是否开启慢查询：</p>

（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