Basic tutorials for the analysis of Mysql slow query log _mysql

0, first to see whether the current open slow query:

(1) Quick way to run SQL statements

Show VARIABLES like "%slow%" 

(2) directly to the my.conf to view.

Configuration in my.conf (add under [mysqld] below)

[Mysqld]

Log-slow-queries =/usr/local/mysql/var/slowquery.log
long_query_time = 1 #单位是秒
Log-queries-not-using-indexes

Use SQL statements to modify: cannot be modified according to the items in my.conf. Modified by "show VARIABLES like"%slow% ""
Statement to run the following SQL:

Set global log_slow_queries = on;
Set global Slow_query_log = on;
Set global long_query_time=0.1; #设置大于0.1s SQL statements are recorded

Slow query log file Information format:

# time:130905 14:15:59   time is September 5, 2013 14:15:59 (the front part is easy to see wrong oh, at first thought is the time stamp)
# User@host:root[root] @ [183.239.28.174] Request the MySQL server's client IP
# query_time:0.735883 lock_time:0.000078 rows_sent:262 rows_examined:262 here indicates how many seconds of execution, 0.735883 seconds , 1 seconds equals 1000 milliseconds.

SET timestamp=1378361759; Well, I don't know what to do with it right now.
Show tables from ' test_db '; This is the key information, indicating that the statement was executed at that time.

1, MySQL slow query log analysis
pt-query-digest Analysis Slow query log

Pt-query-digest–report Slow.log

Report a slow query for the last half hour:

Pt-query-digest–report–since 1800s Slow.log

Report a slow query for a time period:

Pt-query-digest–report–since ' 2013-02-10 21:48:59′–until ' 2013-02-16 02:33:50′slow.log

Report a slow query that contains only SELECT statements:

Pt-query-digest–filter ' $event->{fingerprint} =~ m/^select/i ' Slow.log

Report a slow query for a user:

Pt-query-digest–filter ' ($event->{user} | | "") =~ m/^root/i ' Slow.log

Report all table scans or full join slow queries:

Pt-query-digest–filter ' (($event->{full_scan} | | "") eq "yes") | | (($event->{full_join} | | "") eq "yes") ' Slow.log

2, the slow query log analysis results visualization
Query-digest-ui
In fact, this is a very simple and straightforward tool for browsing and counting MySQL slow queries, based on the AJAX web interface.
Configure Query-digest-ui:

Download:

wget https://nodeload.github.com/kormoc/Query-Digest-UI/zip/master
 Unzip Query-digest-ui-master.zip

The results of the query analysis are visualized in the following steps:

(1) Create related database tables

--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 unsigned not NULL, ' F  Ingerprint ' text not NULL, ' sample ' longtext not null, ' First_seen ' datetime default NULL, ' Last_seen ' datetime default  NULL, ' reviewed_by ' varchar default NULL, ' reviewed_on ' datetime default NULL, ' comments ' text, ' Reviewed_status '
 
varchar () 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 () not N ull, ' db_max ' varchar ($) DEFAULT null, ' checksum ' bigint not NULL, ' sample ' unsigned NOT NULL, ' Longtext ' DateTime NOT NULL default ' 0000-00-00 00:00:00 ', ' Ts_max ' datetime NOT NULL default ' 0000-00-00 00:00:00 ', ' ts_cnt ' Floa T DEFAULT Null, ' query_time_sum ' float default null, ' query_time_min ' float default null, ' Query_time_max ' float default null, ' Q Uery_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_p Ct_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 D Efault null, ' rows_examined_min ' float default null, ' Rows_examined_max ' float default null, ' Rows_examined_pct_95 ' Flo At DEFAULT null, ' Rows_examined_stddev ' float default null, ' Rows_examined_median ' float default null, ' rows_affected_s
Um ' float DEFAULT NULL, ' rows_affected_min ' float default null, ' Rows_affected_max ' float default null, ' rows_affected_pct_95 ' float default NU  LL, ' 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, ' M Erge_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 N ull, ' 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, ' Inno Db_io_r_wait_min ' floatDefault 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 ' Floa t default NULL, ' innodb_rec_lock_wait_min ' float default null, ' Innodb_rec_lock_wait_max ' float default null, ' Innodb_r Ec_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, ' in Nodb_queue_wait_pct_95 ' float default null, ' Innodb_queue_wait_stddev ' float default null, ' Innodb_queue_wait_median ' F Loat default NULL, ' innodb_pages_distinct_min ' float default null, ' Innodb_pages_distinct_max ' float default null, ' Inn Odb_pages_distinct_pct_95 ' floatDefault 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 ' Floa t default NULL, ' full_join_cnt ' float default null, ' full_join_sum ' float default null, ' tmp_table_cnt ' float default N ull, ' tmp_table_sum ' float default null, ' filesort_cnt ' float default null, ' filesort_sum ' float default null, ' Tmp_ta ble_on_disk_cnt ' float default null, ' tmp_table_on_disk_sum ' float default null, ' filesort_on_disk_cnt ' float default NU LL, ' 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 DEF Ault NULL, UNIQUE key ' Hostname_max ' (' Hostname_max ', ' checksum ', ' ts_min ', ' Ts_max '), key ' Ts_min ' (' ts_min '), key ' check Sum ' (' checksum ') EngiNe=innodb DEFAULT Charset=utf8;
 

(2) Create a database account

$ mysql-uroot-p-H 192.168.1.190 < Install.sql
$ mysql-uroot-p-H 192.168.1.190-e "Grant all" Slow_query_log . * to ' slowlog ' @ '% ' identified by ' 123456 ';

(3) Configure Query-digest-ui

Modify Database Connection Configuration

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 '   => ' m Ysql:host=192.168.1.190;port=3306;dbname=slow_query_log ',
 ' user '   => ' slowlog ',
 ' password  ' => ' 123456 ',
//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 your lose detailed stats
//Set to a blank string to disable
// /doc/percona-toolkit/2.1/pt-query-digest.html#cmdoption-pt-query-digest--review-history
 ' history_table ' = > ' global_query_review_history ',
);

(4) Use Pt-query-digest to analyze logs and import analysis results into the database

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