MySQL Slow query logging method

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

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.

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

Call ~ ~ ~ ~ after finishing.

MySQL Slow query logging method