Mysql Slow Query settings

MySQL Slow query settings

The method of analyzing the performance of MySQL statement query in addition to using the EXPLAIN output execution plan, you can also let the MySQL record query over a specified time of the statement, we will exceed the specified time of the SQL statement query is called "Slow query."

=========================================================

Method One: This method I am using, hehe, compare like this kind of instant sex.

Versions above Mysql5.0 can support the recording of slower-performing SQL statements.

Mysql> Show variables like ' long% '; Note: This long_query_time is used to define a "slow query" that is slower than the number of seconds.
+-----------------+-----------+
| variable_name | Value |
+-----------------+-----------+
| Long_query_time | 10.000000 |
+-----------------+-----------+
1 row in Set (0.00 sec)

Mysql> set long_query_time=1; Note: I set 1, that is, the execution time of more than 1 seconds is a slow query.
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like ' slow% ';
+---------------------+---------------+
| variable_name | Value |
+---------------------+---------------+
| Slow_launch_time | 2 |
| Slow_query_log |           On | Note: If logging is turned on
| Slow_query_log_file |      /tmp/slow.log | Note: Where to set
+---------------------+---------------+
3 Rows in Set (0.00 sec)


mysql> set global slow_query_log= ' on ' Note: Turn on logging

Once the Slow_query_log variable is set to On,mysql it will start recording immediately.

The/ETC/MY.CNF can set the initial value of the MySQL global variable above.
Long_query_time=1
Slow_query_log_file=/tmp/slow.log

====================================================

Method Two:mysqldumpslow command

/path/mysqldumpslow-s c-t 10/tmp/slow-log
This outputs 10 SQL statements with the highest number of records, where:

• -S, is the way to indicate the sort, C, T, L, R are in accordance with the number of records, time, query time, the number of records returned to sort, AC, at, AL, AR, indicating the corresponding flashbacks;
• -T, which is the meaning of top N, which is to return the data of the previous number of bars;
• -G, you can write a regular matching pattern, the case is not sensitive;

Like what
/path/mysqldumpslow-s r-t 10/tmp/slow-log
Get up to 10 queries that return recordsets.
/path/mysqldumpslow-s t-t 10-g "left join"/tmp/slow-log
Get the query that contains the left connection in the first 10 lines sorted by time.

=====================================================

1, configuration on

Linux:

Added in MySQL config file my.cnf

Log-slow-queries=/var/lib/mysql/slowquery.log (Specify the log file location, can be empty, the system will give a default file Host_name-slow.log)
long_query_time=2 (record over time, default is 10s)
Log-queries-not-using-indexes (log down without using the indexed query, depending on the situation to decide whether to open)
Log-long-format (if set, all queries that do not use the index will also be logged)

Windows:

In My.ini [mysqld], add the following statement:
Log-slow-queries = E:\web\mysql\log\mysqlslowquery.log
Long_query_time = 2 (other parameters as above)

2, view mode

Linux:

Use MySQL to bring command Mysqldumpslow view

Common commands
-S ORDER what to sort by (t, at, L, AL, R, AR etc), "at" is default
-T NUM just show the top n queries
-G PATTERN Grep:only Consider stmts that include the This string

eg
s, is the order, the description is not detailed enough to write, I use down, including read the code, mainly have C,t,l,r and Ac,at,al,ar, respectively, according to the number of query, time, lock time and return records to sort, the front plus a when the reverse-T, is the meaning of top n , that is, to return the data in front of how many bars-g, you can write a regular matching pattern behind, case insensitive

Mysqldumpslow-s c-t Host-slow.log
Mysqldumpslow-s r-t Host-slow.log
The above command shows the 20 most visited SQL statements and 20 SQL that returns the maximum number of recordsets.

Mysqldumpslow-t 10-s t-g "left join" Host-slow.log This is the SQL statement that returns the first 10 lines containing the links in the previous period.

The MySQL slow query log is useful for tracking problematic queries and can analyze the resource-intensive SQL statements in the current program, so how do I turn on slow query logging for MySQL?
In fact, the slow query log to open MySQL is very simple, only need to in the MySQL configuration file (Windows system is My.ini,linux system is my.cnf) of the [mysqld] under the following code:

log-slow-queries=/var/lib/mysql/slowquery.log long_query_time=2

Note:
Log-slow-queries the log is written there, when empty, the system gives the host name to the slow query log and is appended with the Slow.log. /var/lib/mysql/slowquery.log the location of the files stored in the log, generally this directory to have MySQL running account of the writable permissions, generally set this directory as MySQL data storage directory

The 2 in long_query_time=2 indicates that the query is not logged for more than two seconds.

If the parameter Log-long-format is set, all queries that do not use the index will also be logged. Add the following line to the file my.cnf or My.ini to record these queries

This is a useful log. It has little impact on performance (assuming all queries are fast), and highlights those that are most important to note (missing indexes or indexes are not optimally applied)

# time:070927 8:08:52

# [email protected]: Root[root] @ [192.168.0.20]

# query_time:372 lock_time:136 rows_sent:152 rows_examined:263630
Select ID, name from manager where ID in (66,10135);
This is a slow query log, took 372 seconds, locked for 136 seconds, returned 152 lines, a total of 263630 lines

If the log content is many, with the eye one by one to see will be exhausted, MySQL comes with the tool of analysis, using the following methods:
Command line, enter the Mysql/bin directory, enter Mysqldumpslow–help or--help can see the parameters of this tool, mainly have
Usage:mysqldumpslow [OPTS ...] [LOGS ...]

Parse and summarize the MySQL slow query log. Options are

--verbose verbose

--debug Debug

--help write this text to standard output

-V Verbose

-D Debug

-S ORDER what to sort by (t, at, L, AL, R, AR etc), "at" is default

-R Reverse the sort order (largest last instead of first)

-T NUM just show the top n queries

-A don ' t abstract all numbers-N and strings to ' S '

-N NUM abstract numbers with at least n digits within names

-G PATTERN Grep:only Consider stmts that include the This string

-H HOSTNAME HOSTNAME of DB Server for *-slow.log filename (can be wildcard),

Default is ' * ', i.e. match all

-I name name of server instance (if using Mysql.server startup scrīpt)

-L don ' t subtract lock time from total time

-S, is the order, the description is not detailed enough to write, I use down, including read the code, mainly have
C,t,l,r and Ac,at,al,ar, respectively, are sorted by query count, time, lock time, and number of records returned, preceded by A's flashback
-T, which is the meaning of top N, which is to return the data of the previous number of bars
-G, you can write a regular match pattern behind, case insensitive

Mysqldumpslow-s c-t Host-slow.log
Mysqldumpslow-s r-t Host-slow.log

The above command shows the 20 most visited SQL statements and 20 SQL that returns the maximum number of recordsets.
Mysqldumpslow-t 10-s t-g "left join" Host-slow.log
This is the SQL statement that contains the left link in the first 10 lines.

Windows:

When you are the first to start the slow query of MySQL, will be in the directory you specified to create this record file, this article is mysqlslowquery.log, the content of this file is roughly the following (the first time you turn on MySQL slow query case)
E:\web\mysql\bin\mysqld, Version:5.4.3-beta-community-log (MySQL community Server (GPL)). Started with:
TCP port:3306, Named Pipe: (NULL)
Time Id Command Argument

You can view the number of records for a slow query by using the following command:

Mysql> show global status like '%slow% ';
+ ——————— + ——-+
| variable_name | Value |
+ ——————— + ——-+
| Slow_launch_threads | 0 |
| slow_queries | 0 |
+ ——————— + ——-+

Mysql Slow Query settings