对MySQL慢查询日志进行分析的基本教程
作者:zdz 时间:2024-01-22 20:32:35
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,慢查询,日志
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
详细介绍Scrapy shell的使用教程
2022-04-18 03:35:39
python中的pygame实现接球小游戏
2021-10-21 13:33:50
python OpenCV图像金字塔
2021-11-03 04:00:48
![](https://img.aspxhome.com/file/2023/3/107483_0s.png)
Python Django 数据库的相关操作详解
2024-01-26 21:02:46
![](https://img.aspxhome.com/file/2023/8/112548_0s.png)
Python中函数的参数定义和可变参数用法实例分析
2023-10-04 00:26:30
Python中的集合介绍
2022-09-10 03:49:52
![](https://img.aspxhome.com/file/2023/1/101111_0s.png)
解决pyCharm中 module 调用失败的问题
2022-11-27 23:21:19
![](https://img.aspxhome.com/file/2023/7/82617_0s.jpg)
.Net行为型设计模式之迭代器模式(Iterator)
2024-06-05 09:27:47
![](https://img.aspxhome.com/file/2023/5/122815_0s.png)
Python之根据输入参数计算结果案例讲解
2022-08-27 07:44:50
![](https://img.aspxhome.com/file/2023/9/69499_0s.jpg)
jQuery页面滚动浮动层智能定位实例代码
2024-04-22 22:22:16
TFRecord文件查看包含的所有Features代码
2023-07-26 15:45:08
Python asyncio异步编程常见问题小结
2023-10-04 23:13:56
![](https://img.aspxhome.com/file/2023/8/68418_0s.jpg)
Python实现连接FTP并下载文件夹
2021-02-28 01:56:26
![](https://img.aspxhome.com/file/2023/1/120551_0s.png)
python列表去重的二种方法
2022-06-02 05:21:41
Python如何判断字符串是否仅包含数字
2023-12-23 08:16:04
![](https://img.aspxhome.com/file/2023/3/124413_0s.png)
php文件上传类完整实例
2023-11-22 04:32:28
python验证身份证信息实例代码
2022-11-19 13:36:24
![](https://img.aspxhome.com/file/2023/9/75259_0s.gif)
整理Python中常用的conda命令操作
2022-07-17 16:53:33
![](https://img.aspxhome.com/file/2023/6/84986_0s.png)
五种方法解决 Web2.0设计中的匹配度
2007-09-22 10:58:00
详解Python中的__new__、__init__、__call__三个特殊方法
2023-03-31 00:28:52