mysql慢查詢分析

標籤：http   os   io   使用   ar   strong   for   資料   2014   

mysql慢查詢分析

Posted: 29. 08. 2014 | Author: zdz | Category: mysql

MySQL 慢查詢日誌分析

 

1. pt-query-digest分析慢查詢日誌
pt-query-digest –report slow.log

2. 報告最近半個小時的慢查詢：
pt-query-digest –report –since 1800s slow.log

3. 報告一個時間段的慢查詢：
pt-query-digest –report –since ‘2013-02-10 21:48:59‘ –until ‘2013-02-16 02:33:50‘ slow.log

4. 報告只含select語句的慢查詢:
pt-query-digest –filter ‘$event->{fingerprint} =~ m/^select/i‘ slow.log

5. 報告針對某個使用者的慢查詢:
pt-query-digest –filter ‘($event->{user} || "") =~ m/^root/i‘ slow.log

6. 報告所有的全表掃描或full join的慢查詢:
pt-query-digest –filter ‘(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")‘ slow.log

 

 

將慢查詢日誌的分析結果可視化
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

 

git clone https:

//github.com/kormoc/Query-Digest-UI.git

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慢查詢分析