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慢查詢配置
1. 慢查詢有什麼用?
它能記錄下所有執行超過long_query_time時間的SQL語句, 幫你找到執行慢的SQL, 方便我們對這些SQL進行優化.
2. 如何開啟慢查詢?
首先我們先查看MYSQL服務器的慢查詢狀態是否開啟.執行如下命令:
我們可以看到當前log_slow_queries狀態為OFF, 說明當前並沒有開啟慢查詢.
開啟慢查詢非常簡單, 操作如下:
Linux下找到mysql的配置文件my.ini, 在mysqld下方加入慢查詢的配置語句(注意:一定要在[mysqld]下的下方加入)
log-slow-queries: 代表MYSQL慢查詢的日志存儲目錄, 此目錄文件一定要有寫權限;
long_query_time: 最長執行時間. (如圖, MSYQL將記錄下所有執行時間超過2條的SQL語句, 此處為測試時間, 時間不應太小最好在5-10秒之內, 當然可以根據自己的標准而定);
配置好以後重新啟動一個MYSQL服務