程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 對MySQL慢查詢日記停止剖析的根本教程

對MySQL慢查詢日記停止剖析的根本教程

編輯:MySQL綜合教程

對MySQL慢查詢日記停止剖析的根本教程。本站提示廣大學習愛好者:(對MySQL慢查詢日記停止剖析的根本教程)文章只能為提供參考,不一定能成為您想要的結果。以下是對MySQL慢查詢日記停止剖析的根本教程正文


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(
// WordStr 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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved