分享好友 数据库首页 频道列表

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

MySQL教程  2016-01-31 10:050

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

查看更多关于【MySQL教程】的文章

展开全文
相关推荐
反对 0
举报 0
评论 0
图文资讯
热门推荐
优选好物
更多热点专题
更多推荐文章
mysql下如何执行sql脚本 执行SQL脚本
1.编写sql脚本,假设内容如下:  create database dearabao;  use dearabao;  create table niuzi (name varchar(20));  保存脚本文件,假设我把它保存在F盘的hello world目录下,于是该文件的路径为:F:\hello world\niuzi.sql2.执行sql脚本,可以有2种方法: 

0评论2023-02-10699

MySQL 5.7版本sql_mode=only_full_group_by问题
用到GROUP BY 语句查询时com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'col_user_6.a.START_TIME' which is not functionally dependent on colu

0评论2023-02-10973

Oracle迁移到MySQL性能下降的注意点 oracle数据库迁移需要注意的问题
背景：最近有较多的客户系统由原来由Oracle改造到MySQL后出现了性能问题CPU 100%，或是后台的CRM系统复杂SQL在业务高峰的时候出现堆积导致业务故障。在我的记忆里面淘宝最初从Oracle迁移到MySQL期间也遇到了很多SQL的性能问题，记忆最为深刻的子查询，当初的

0评论2023-02-10580

MySQL与Oracle 差异比较之六触发器
触发器编号类别ORACLEMYSQL注释1创建触发器语句不同create or replace trigger TG_ES_FAC_UNIT  before insert or update or delete on ES_FAC_UNIT  for each rowcreate trigger `hs_esbs`.`TG_INSERT_ES_FAC_UNIT` BEFORE INSERT on `hs_esbs`.`es_fac_u

0评论2023-02-10914

mysql where条件：某时间字段为今天的sql语句
1.查询：注册时间为今天的所有用户数：select count(*) from customer where TO_DAYS(createtime) = TO_DAYS(NOW())2.获取当前时间到凌晨24点还有多长时间：（Java中可用于判断某时间是否为今天）final Calendar cal = Calendar.getInstance();    ca

0评论2023-02-10717

mysql中的sql
变量用户变量: 在用户变量前加@系统变量: 在系统变量前加@@运算符算术运算符有: +(加), -(减), * (乘), / (除) 和％ (求模) 五中运算位运算符有:(位于), | (位或), ^ (位异或), ~ (位取反),(位右移),(位左移)比较运算符有: = (等于),(大于),(小于), = (大

0评论2023-02-10936

Oracle、MySql、Sql Server比对
MySql:廉价(部分免费)：当前，MySQL採用双重授权(DualLicensed)，他们是GPL和MySQLAB制定的商业许可协议。假设你在一个遵循GPL的***（开源）项目中使用MySQL，那么你能够遵循GPL协议免费使用MySQL。否则，你须要购买MySQLAB制定的那个商业许可协议。Windows $

0评论2023-02-10441

MySQL与Oracle的区别之我见 mysql oracle 区别
1. 大的方面（宏观）Oracle为商用数据库，行业中占据相当的地位：市场占比2012年为40%。开发、管理资源相当丰富，有自己的metalink，我也曾用过，有什么问题，都能在那里得到较快速度的解决。开发用了近10年，虽然有些功能用起来挺鸡肋的（像分页），但它在OL

0评论2023-02-10801

一条SQL语句在MySQL中如何执行的 mysql执行语句的过程
本篇文章会分析一个 sql 语句在 MySQL 中的执行流程，包括 sql 的查询在 MySQL 内部会怎么流转，sql 语句的更新是怎么完成的。在分析之前我会先带着你看看 MySQL 的基础架构，知道了 MySQL 由那些组件组成以及这些组件的作用是什么，可以帮助我们理解和解决这

0评论2023-02-10684

sql mysql和sqlserver存在就更新,不存在就插入的写法(转)
转自：http://hi.baidu.com/tidy0608/item/ff930fe2436f2601560f1dd9sqlsever数据存在就更新,不存在就插入的两种方法两种经常使用的方法:1. Update, if @@ROWCOUNT = 0 then insertUPDATETable1 SETColumn1 = @newValue WHEREId = @idIF@@ROWCOU

0评论2023-02-10605

更多推荐