There was a time when a log was set to query SQL statements in MySQL that are slower than 1 seconds. Just suddenly recalled the method of setting, there are several parameters of the name of the dead and alive, so re-organize, make a note.
The easiest problem to find and solve for troubleshooting a performance bottleneck is MySQL's slow query and the queries that are not indexed.
OK, start figuring out the SQL statements that are not "cool" in MySQL.
=========================================================
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.
Slow query analysis for MySQL