Configure slow query
The default my. cnf file is in the/etc/directory.
Vim/etc/my. cnf
# Slow_query
Slow_query_log = 1 # enable slow query
Slow_query_log_file =/var/lib/mysql/mysql-slow.log # set the slow query path
Long_query_time = 0.01 # set the slow query time
The configuration takes effect only after the mysql service is restarted.
Query whether the slow query configuration is successful
Test slow query
Use information_schema;
Select * from tables;
The statement execution time took 1.1 seconds and 314 rows of records were returned;
Viewing slow query files: cat mysql-slow.log
The slow query log file records the slow query records every day. Each record starts with Time:. It records the logon information, query Time, and lock Time, number of returned rows, number of scanned records, and executed statements.
Generate slow query log files online
1. Disable global slow query
SET global slow_query_log = 0
Whether to disable global slow query
Show variables like '% query_log % ';
Set a new slow query file
SET global slow_query_log_file = '/var/lib/mysql/mysql-slow_new.log'
Enable slow query
SET global slow_query_log = 1;
Show variables like '% query_log % ';
A new slow query file is generated in the slow query path.
The slow query information is recorded in the new log file,
At this time, we can archive the slow query files before mv.
Restart mysql service
Service mysql restart
Show variables like '% query_log % ';
After the mysql service is restarted, the slow log file will be changed to the previous one in my. cnf file, so if you want to restart the service after the slow log file or just set the mysql-slow_new.log, you need to modify the global settings while modifying my. cnf file to ensure that the file is modified after restart.
Summary
Configuring slow queries is helpful for collecting statements with poor performance at ordinary times, and many tools are dedicated to analyzing slow query logs. percona-toolkit is a good tool for analyzing slow queries, if you are not using it, you can check it out.
Mysql correctly and securely clears the online slow query log slow log
1. see the slow log status;
Mysql> show variables like '% slow % ';
+ --------------------- + ------------------------------------------ +
| Variable_name | Value |
+ --------------------- + ------------------------------------------ +
| Log_slow_queries | ON |
| Slow_launch_time | 2 |
| Slow_query_log | ON |
| Slow_query_log_file |/mysqllog/slow_log/slow_queries_3306.log |
+ --------------------- + ------------------------------------------ +
4 rows in set (0.00 sec)
2. stop the slow log server.
Mysql> set global slow_query_log = 0;
Query OK, 0 rows affected (0.27 sec)
Mysql> show variables like '% slow % ';
+ --------------------- + ------------------------------------------ +
| Variable_name | Value |
+ --------------------- + ------------------------------------------ +
| Log_slow_queries | OFF |
| Slow_launch_time | 2 |
| Slow_query_log | OFF |
| Slow_query_log_file |/mysqllog/slow_log/slow_queries_3306.log |
+ --------------------- + ------------------------------------------ +
4 rows in set (0.00 sec)
Mysql>
Mysql> show variables like '% slow %'; -- check slow log status
+ --------------------- + ------------------------------------------ +
| Variable_name | Value |
+ --------------------- + ------------------------------------------ +
| Log_slow_queries | OFF |
| Slow_launch_time | 2 |
| Slow_query_log | OFF |
| Slow_query_log_file |/mysqllog/slow_log/slow_queries_3306.log |
+ --------------------- + ------------------------------------------ +
4 rows in set (0.00 sec)
3, reset the new path of slow log
Mysql> set global slow_query_log_file = '/mysqllog/slow_log/slow_queries_3306_new.log ';
Query OK, 0 rows affected (0.03 sec)
4. start the slow log server
Mysql>
Mysql>
Mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.01 sec)
Mysql> show variables like '% slow % ';
+ --------------------- + ---------------------------------------------- +
| Variable_name | Value |
+ --------------------- + ---------------------------------------------- +
| Log_slow_queries | ON |
| Slow_launch_time | 2 |
| Slow_query_log | ON |
| Slow_query_log_file |/mysqllog/slow_log/slow_queries_3306_new.log |
+ --------------------- + ---------------------------------------------- +
4 rows in set (0.00 sec)
5. check the slow SQL in the new slow log file.
Mysql> select sleep (10) as a, 1 as B;
+ --- +
| A | B |
+ --- +
| 0 | 1 |
+ --- +
1 row in set (10.00 sec)
Mysql>
[Mysql @ xxx-xxx ~] $ More/mysqllog/slow_log/slow_queries_3306_new.log
......
Time Id Command Argument
# Time: 140213 6:44:24
# User @ Host: root [root] @ localhost []
# Query_time: 10.000365 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp = 1392273864;
Select sleep (10) as a, 1 as B;
6. backup the old big slow log file to other directory.
Mv/mysqllog/slow_log/slow_queries_3306.log/mysqlbackup/slow_log/slow_queries_3306.log.bak.20140213