A basic tutorial on MySQL slow query log analysis,

Source: Internet
Author: User
Tags mysql slow query log percona

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.