Enabling the slow query log in MySQL

Maxoq

Well-known member
Registered
Joined
Feb 25, 2015
Messages
520
Points
28
If you are using MySQL version v5.6, you can do changes in your file my.cnf on your VPS to aneb enable Slow Query Log for your MySQL. In order to enable this for MySQL on your VPS or hosting servers, you should do as follows

Open my.cnf

Code:
nano /etc/my.cnf
When you opened your my.cnf file, you can add these lines under [mysqld]

Code:
[mysqld]
 
### Slow Query Log Configuration ###
### Only Avaiable on MySQL v5.6  ###
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/yourserver.name.slow_queries.log
long_query_time = 5
log_output = FILE
Then create the file yourserver.name.slow_queries.log correspond to which you set in my.cnf

Explain:
long_query_time = [value] :
This value will stipulated that if 1 query time-consuming to implement than the time that you specified in this section, the MySQL configuration will log the information related to that query.

slow_query_log = [0/1]
- If the value is "0", the slow query log is off, and "1" this function is activated. The default is to disable this feature.

slow_query_log_file = file_name
- This value specifies the name of the log file will be saved to file, this file will be created in the MySQL data directory unless you give the absolute path to another directory.

log_output = [FILE|TABLES|NONE]
- This configuration is to specify the type of log that we will save the information down, namely 1 file or database table in the MySQL service.

Restart MySQL

Code:
 /etc/init.d/mysql restart
Note:
If your hosting system is using MySQL 5.5 or lesser, there is no configuration exists called "slow_query_log" and it has been renamed from "log-slow-queries' to the new. So if you configured on the system, it will use the old MySQL configuration names are:

Code:
log-slow-queries = 1
Sign into MySQL command-line and execute the set of values corresponding GLOBAL configuration as follows:

Code:
# mysql -uroot -p
mysql> SET GLOBAL slow_query_log_file = '/var/lib/mysql/yourserver.name.slow_queries.log';
mysql> SET GLOBAL long_query_time = 1; 
mysql> SET GLOBAL slow_query_log = 1;
mysql> FLUSH LOGS;

Check again if the values are set
Code:
mysql> SHOW GLOBAL VARIABLES LIKE 'slow\_%';
+---------------------+-----------------------------------------------------+
| Variable_name       | Value                                               |
+---------------------+-----------------------------------------------------+
| slow_query_log      | ON                                                  |
| slow_query_log_file | /var/lib/mysql/yourserver.name.slow_queries.log      |
+---------------------+-----------------------------------------------------+
Code:
mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 5.000000  |
+-----------------+-----------+
 

CraigM

New member
Registered
Joined
Sep 6, 2016
Messages
9
Points
0
thanks for great tips, I tried to configure these commands on my hosting server but I don't see any slow queries in my log file.
I don't know it is working or not. Is there a way to check it is already storing slow queries from my websites?
 

LJSHost

Well-known member
Hosting Provider
Registered
Joined
Jul 5, 2016
Messages
1,031
Points
63
If the log file is empty it's off.

Check your /etc/my.cnf for the following

slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log


make sure the value is 1 and the log path is correct
 
Recommended Threads

Latest Hosting OffersNew Reviews

Sponsors

Tag Cloud

You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.

Top