MySQL Slow query analysis

MySQL Slow query analysis

Posted: 29. 08.2014 | Author: Zdz | Category: MySQL

MySQL Slow Query log analysis

1. Pt-query-digest Analysis Slow query log
Pt-query-digest–report Slow.log

2. Report a slow query for the last half hour:
Pt-query-digest–report–since 1800s Slow.log

3. 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

4. Report a slow query with only the SELECT statement:
Pt-query-digest–filter ' $event->{fingerprint} =~ m/^select/i ' Slow.log

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

6. Report all full-table scans or slow queries for the complete join:
Pt-query-digest–filter ' (($event->{full_scan} | | "") eq "yes") | | (($event->{full_join} | | "") eq "yes") ' Slow.log

Visualize analysis results for slow query logs
Query-digest-ui

In fact, this is a very simple and straightforward tool to browse and statistics MySQL slow query, AJAX-based web interface.

Configuration Query-digest-ui:

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

The following steps are visualized for query analysis results:

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,

' Fingerprint ' text is 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 NULL,

' Db_max ' varchar (+) DEFAULT NULL,

' Checksum ' bigint () 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) 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 on slow_query_log.* to ' slowlog ' @ ' percent ' identified by ' 123456 ';"

3) configuration 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 and 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) 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

MySQL Slow query analysis