對(duì)MySQL慢查詢?nèi)罩具M(jìn)行分析的基本教程
0、首先查看當(dāng)前是否開啟慢查詢:
(1)快速辦法,運(yùn)行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中的項(xiàng)來修改的。修改通過"show VARIABLES like "%slow%" "
語句列出來的變量,運(yùn)行如下sql:
set global log_slow_queries = ON; set global slow_query_log = ON; set global long_query_time=0.1; #設(shè)置大于0.1s的sql語句記錄下來
慢查詢?nèi)罩疚募男畔⒏袷剑?/p>
# Time: 130905 14:15:59 時(shí)間是2013年9月5日 14:15:59(前面部分容易看錯(cuò)哦,乍看以為是時(shí)間戳) # User@Host: root[root] @ [183.239.28.174] 請(qǐng)求mysql服務(wù)器的客戶端ip # Query_time: 0.735883 Lock_time: 0.000078 Rows_sent: 262 Rows_examined: 262 這里表示執(zhí)行用時(shí)多少秒,0.735883秒,1秒等于1000毫秒
SET timestamp=1378361759; 這目前我還不知道干嘛用的
show tables from `test_db`; 這個(gè)就是關(guān)鍵信息,指明了當(dāng)時(shí)執(zhí)行的是這條語句
1、MySQL 慢查詢?nèi)罩痉治?br />
pt-query-digest分析慢查詢?nèi)罩?br />
pt-query-digest –report slow.log
報(bào)告最近半個(gè)小時(shí)的慢查詢:
pt-query-digest –report –since 1800s slow.log
報(bào)告一個(gè)時(shí)間段的慢查詢:
pt-query-digest –report –since ‘2013-02-10 21:48:59′ –until ‘2013-02-16 02:33:50′ slow.log
報(bào)告只含select語句的慢查詢:
pt-query-digest –filter ‘$event->{fingerprint} =~ m/^select/i' slow.log
報(bào)告針對(duì)某個(gè)用戶的慢查詢:
pt-query-digest –filter ‘($event->{user} || “”) =~ m/^root/i' slow.log
報(bào)告所有的全表掃描或full join的慢查詢:
pt-query-digest –filter ‘(($event->{Full_scan} || “”) eq “yes”) || (($event->{Full_join} || “”) eq “yes”)' slow.log
2、將慢查詢?nèi)罩镜姆治鼋Y(jié)果可視化
Query-Digest-UI
其實(shí),這是一個(gè)非常簡單和直接的工具,瀏覽和統(tǒng)計(jì)Mysql慢查詢,基于AJAX的Web界面。
配置Query-Digest-UI:
下載:
wget https://nodeload.github.com/kormoc/Query-Digest-UI/zip/master unzip Query-Digest-UI-master.zip
查詢分析結(jié)果可視化步驟如下:
(1)創(chuàng)建相關(guān)數(shù)據(jù)庫表
-- 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)創(chuàng)建數(shù)據(jù)庫賬號(hào)
$ 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
修改數(shù)據(jù)庫連接配置
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分析日志并將分析結(jié)果導(dǎo)入數(shù)據(jù)庫
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
相關(guān)文章
詳解MySQL安裝及MySQL8.0新密碼認(rèn)證方式
這篇文章主要介紹了詳解MySQL安裝及MySQL8.0新密碼認(rèn)證方式,小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧2018-09-09MySQL復(fù)合查詢(多表查詢、子查詢)的實(shí)現(xiàn)
MySQL復(fù)合查詢是指在一個(gè)SQL語句中使用多個(gè)查詢條件,以過濾和檢索數(shù)據(jù),本文主要介紹了MySQL復(fù)合查詢(多表查詢、子查詢)的實(shí)現(xiàn),具有一定的參考價(jià)值,感興趣的可以了解一下2023-12-12Mysql啟動(dòng)與數(shù)據(jù)庫的創(chuàng)建方法[圖文]
Mysql啟動(dòng)與數(shù)據(jù)庫的創(chuàng)建方法,mysql數(shù)據(jù)庫入門2012-03-03mysql數(shù)據(jù)庫備份及恢復(fù)命令 mysqldump,source的用法
mysql數(shù)據(jù)庫備份及恢復(fù)命令 mysqldump,source的用法,需要的朋友可以參考下。2011-02-02重置MySQL中表中自增列的初始值的實(shí)現(xiàn)方法
這篇文章主要介紹了重置MySQL中表中自增列的初始值的實(shí)現(xiàn)方法的相關(guān)資料,需要的朋友可以參考下2017-03-03關(guān)于Mysql中current_time/current_date()與now()區(qū)別
這篇文章主要介紹了關(guān)于current_time/current_date()與now()區(qū)別,在Mysql中 current_time函數(shù)是顯示當(dāng)前時(shí)間的,而其他兩個(gè)函數(shù)有何不同呢, 接下來我們就一起來看看吧2023-04-04