Slow query analysis for MySQL

The slow query analysis day was originally used to capture relatively "slow" queries, and in the mysql5.1 + version, the functionality of the slow query was enhanced to capture all queries by setting Long_query_time to zero, and the response time of the query could be subtle.

--- in the current version of MySQL, the slow query log is the least expensive and most accurate tool for measuring query time. If you're still worried about the extra I/O overhead of turning on slow queries, you can rest assured that we have tested in I/O intensive scenarios and the overhead of slow queries is negligible (actually the impact of CPU-intensive scenarios is slightly larger)

More worrying is that logs can consume a lot of disk space, so don't open full log queries for long periods of time [high-performance MySQL]

Below we use the Slow query log query statement execution efficiency

First, the implementation of show variables like '%quer% '; Whether the query has a slow query turned on

You can see that the status of Binlog_rows_query_log_events is off, indicating that the log function has not been turned on

There are two ways to turn on the slow log feature

(1.1) Open directly at startup:

Add the following information in the Default-ini:

[Mysqld]

Log-slow-queries= "D:\softpackage\mysql\data\cuiyw-slow.log"--The output directory of the query log

Long_query_time = 4--threshold value of the query log

Log-queries-not-using-indexes--Log output with no indexes

(1.2) Use the command to open temporarily:

Use DatabaseName;
Set global Slow_query_log = 1;
Set global long_query_time = 1;
Set global log_output = ' FILE ';
Set global General_log = 1;
Set long_query_time = 1;

I'm using the second way, after execution, execute show variables like '%quer% ' to determine if it started successfully:

The discovery parameter value becomes on, indicating that the start was successful. Let's look at the files in the data directory

Second, Slow_query_log

Log_slow_queries is actually no longer in use, the current MySQL5.6 version of the parameter is Slow_query_log, the parameter meaning is as follows:

(2.1), command-line parameters:

--log-slow-queries

Specify the log file location, can be empty, the system will give a default file Host_name-slow.log

(2.2), System variables

Log_slow_queries

Specify the log file location, can be empty, the system will give a default file Host_name-slow.log

Slow_query_log

Slow quere log switch, when the value is 1, turn on slow query.

Slow_query_log_file

Specify the log file location, can be empty, the system will give a default file Host_name-slow.log

Long_query_time

Record over time, default is 10s

Log_queries_not_using_indexes

Log down does not use the index of query, depending on the circumstances to decide whether to open

Third, restart MySQL (re-record the MySQL startup and shutdown method)

(3.1), starting mode

1. Start with service: Service mysqld start
2. Start with mysqld script:/etc/inint.d/mysqld start
3. Use Safe_mysqld to start:safe_mysqld&

(3.2), stop

1. Start with service: Service mysqld stop
2. Start with mysqld script:/etc/inint.d/mysqld stop
3, mysqladmin shutdown

(3.3), restart
1. Start with service: Service mysqld restart
2. Start with mysqld script:/etc/inint.d/mysqld restart

Slow query analysis for MySQL