| Variable_name | Value |
|---|---|
| long_query_time | 1.000000 |
| slow_query_log | ON |
| slow_query_log_file | D: / MySQL/MySQL – 5.6.31 – winx64 \ data \ zhoupq_sh – missile. The log |
An overview of the
Database query speed is a major factor affecting project performance, for the database, we in addition to optimize SQL, more important is to find the NEED to optimize SQL. How to find inefficient SQL is the main purpose of this article.
The MySQL database has a “slow query log” feature, which records SQL queries that take longer than a certain set point. This greatly helps us to locate the problem quickly so that we can take appropriate measures. As for how slow the query time is, each project and business has different requirements. The software of traditional enterprises allows the query time to be higher than a certain value, but if this standard is put on Internet projects or websites with large visits, it is estimated to be a bug, and may even be upgraded to a functional defect.
To avoid misleading readers, the discussion of this article is limited to Win 64-bit + MySQL 5.6. I have not tried other platforms or database types and versions, so I will not repeat them.
Setting the Log Function
The following parameters are involved in slow query logs:
- Slow_query_log: Specifies whether to enable the slow query log function (mandatory)
- Long_query_time: if the value is exceeded, it will be considered a slow query and recorded in a slow query log file (required)
- Log-slow-queries: slowly queries log files (cannot be filled). A [hostname]-slow.log file is automatically created in \data\
In other words, the slow query function can only be turned on or off correctly if the above three conditions are met.
The command
Set the parameters on the command line without restarting the MySQL service. Note the permissions and scope of the parameters:
Mysql > set global slow_query_log = ON; ERROR 1227 (42000): Access denied; You need (at least one of) the SUPER privilege(s) for this operation # MySQL: mysql-mysql-5.6.31-winx64 \bin> MySQL -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.31- Log MySQL Community Server (GPL) Copyright (C) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help; 'or '\h' for help.type '\c' to clear the current input statement. Mysql > # slow_query_log slow_query_log = ON; ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL mysql> set global slow_query_log = ON; Query OK, 0 rows affected (0.04 SEC) # mysql> set GLOBAL long_query_time = 1; Query OK, 0 rows affected (0.00 SEC)Copy the codeThe configuration file
To set the configuration file, restart the MySQL service. Under [mysqld] in my.ini:
Slow_query_log = ON long_query_time = 1Copy the codeLog path
The system automatically creates \data\[hostname]-slow.log. I defined the log query path in various forms in the configuration file, all of which reported an error:
# zhoupq_sh.err [ERROR] MySQL: unknown variable 'log-slow-queries=/data/mysql-slow.log' [ERROR] MySQL: unknown variable 'log-slow-queries=\data\mysql-slow.log' [ERROR] MySQL: Unknown variable 'log - missile - the queries = D: / MySQL/MySQL - 5.6.31 - winx64 \ data \ MySQL - missile. The log'Copy the codeMaybe I didn’t find the right way, but customizing the slow query log file is not an option for me. In fact, I don’t need to customize this file.
inspection
mysql> show variables like '%query%'; +------------------------------+------------------------------------------------------+ | Variable_name | Value | +------------------------------+------------------------------------------------------+ | binlog_rows_query_log_events | OFF | | ft_query_expansion_limit |20 |
| have_query_cache | YES |
| long_query_time | 1.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | D:\MySQL\mysql5.631.-winx64\data\zhoupq_sh-slow.log |
+------------------------------+------------------------------------------------------+
13 rows in set (0.00 sec)Copy the codeAs you can see from the result, both slow_query_log and long_query_time are set to update, while slow_query_log_file is not specified but generated automatically.
The slow_query_log_file has no data at this point, except for some system information. Next, we’ll test to see if “slow query logging” meets our requirements.
A slow query is logged
I have written several SQL statements that are preset to log queries that take more than 1s to query.
SELECT t.* FROM subscribe t limit 0.1;
SELECT t.product, t.vendor, COUNT(DISTINCT t.id) AS vulcnt FROM temp t WHERE 1 = 1 GROUP BY t.vendor, t.product ORDER BY vulcnt DESC;Copy the codeFirst SQL execution time 0.001s:
SQL > execute time > 2s
Only the second SQL is logged by the slow query log:
# User@Host: root[root] @localhost [127.0.0.1] Id: 2 # Query_time: 2.133122 Lock_time: 0.106006 Rows_sent: 23878 Rows_examined: 160312 use testDB; SET timestamp=1491547463; SELECT t.product, t.vendor, COUNT(DISTINCT t.id) AS vulcnt FROM temp t WHERE 1 = 1 GROUP BY t.vendor, t.product ORDER BY vulcnt DESC;Copy the codesummary
-
Remember three parameters:
- slow_query_log
- long_query_time
- log-slow-queries
-
Log-slow-queries cannot be customized
- In the production environment, disable slow Log Query to save space
- The MySQL service does not need to be restarted in command mode, but needs to be restarted in configuration file mode
My blog
MySQL slow query log – let “slow” no hiding