A basic tutorial on MySQL slow query log analysis,

A basic tutorial on MySQL slow query log analysis,

0. First, check whether slow query is Enabled:

(1) quick way to run SQL statements

show VARIABLES like "%slow%" 

(2) go to my. conf to view the information.

Configuration in my. conf (add it below [mysqld)

[Mysqld] log-slow-queries =/usr/local/mysql/var/slowquery. loglong_query_time = 1 # Unit: log-queries-not-using-indexes

Use an SQL statement to modify the statement: it cannot be modified according to the items in my. conf. Modified by "show VARIABLES like" % slow % ""
Run the following SQL statement:

Set global log_slow_queries = ON; set global slow_query_log = ON; set global long_query_time = 0.1; # set the SQL statement that exceeds s to record

Slow query log file information format:

# Time: 130905 14:15:59 the Time is September 5, 2013 14:15:59 (the previous part is easy to see incorrectly. At first glance, it is a timestamp) # User @ Host: root [root] @ [183.239.28.174] request the ip address of the mysql Server Client # Query_time: 0.735883 Lock_time: 0.000078 Rows_sent: 262 Rows_examined: 262 The number of seconds and 0.735883 seconds of execution, 1 second equals 1000 milliseconds

SET timestamp = 1378361759; I still don't know why
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 analyze slow query logs

pt-query-digest –report slow.log

Report slow queries in the last 30 hours:

pt-query-digest –report –since 1800s slow.log

Report slow queries within a period of time:

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

Slow queries with only select statements in the report:

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

Slow query of a user in the report:

pt-query-digest –filter ‘($event->{user} || “”) =~ m/^root/i' slow.log

Report all slow queries for full table scans or full join:

pt-query-digest –filter ‘(($event->{Full_scan} || “”) eq “yes”) || (($event->{Full_join} || “”) eq “yes”)' slow.log

2. Visualize the analysis results of slow query logs
Query-Digest-UI
In fact, this is a very simple and direct tool, browsing and counting Mysql slow queries, AJAX-based Web interface.
Configure Query-Digest-UI:

Download:

wget https://nodeload.github.com/kormoc/Query-Digest-UI/zip/master unzip Query-Digest-UI-master.zip

To visualize the query and analysis results, follow these steps:

(1) Create related database tables

-- install.sql-- Create the database needed for the Query-Digest-UIDROP DATABASE IF EXISTS slow_query_log;CREATE DATABASE slow_query_log;USE slow_query_log; -- Create the global query review tableCREATE 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 tableCREATE 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) 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'@'%' IDENTIFIED BY '123456';"

(3) Configure Query-Digest-UI

Modify database connection Configuration

cd Query-Digest-UIcp config.php.example config.phpvi 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) Use pt-query-digest to analyze logs and import the analysis results to 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

Articles you may be interested in:

• How to start and use a common query log instance in MySQL
• How to query logs and slow query logs in MySQL
• How to use the general query log in MySQL to find the statement with the most queries