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

2020-04-05 作者:计算机教程   |   浏览(119)

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

快速办法,运行sql语句

show VARIABLES like "%slow%" 

直接去my.conf中查看。

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

[mysqld]log-slow-queries = /usr/local/mysql/var/slowquery.loglong_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

www.2003.com,报告最近半个小时的慢查询:

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  unzip Query-Digest-UI-master.zip

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

创建相关数据库表

-- install.sql-- Create the database needed for the Query-Digest-UIDROP DATABASE IF EXISTS slow_query_log;CREATE DATABASE slow_query_log;USE slow_query_log; -- Create the global query review tableCREATE 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 tableCREATE 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;

创建数据库账号

$ 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';"

配置Query-Digest-UI

修改数据库连接配置

cd Query-Digest-UIcp config.php.example config.phpvi 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 #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 #cmdoption-pt-query-digest--review-history 'history_table' = 'global_query_review_history',);

使用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

本文由www.2003.com发布于计算机教程,转载请注明出处:对MySQL慢查询日志进行分析的基本教程www.2003.co

关键词: