Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I enable MySQL slow query log on my server?

Tags:

mysql

How do I enable slow query log on my server? I have enabled it on my local host by adding log-slow-queries =[path] in my.ini file, but don't know how to add this on my server. My server is Linux-based and has PHP version 5.2.16.

like image 912
Juice Avatar asked Jul 23 '12 05:07

Juice


People also ask

How do I create a slow query log?

To enable the slow query log, type the following command at the mysql> prompt: Copy SET GLOBAL slow_query_log = 'ON'; There are additional options that you can set for the slow query log: By default, when the slow query log is enabled, it logs any query that takes longer than 10 seconds to run.

How do I find the slow query log path in MySQL?

By default, the slow query log file is located at /var/lib/mysql/hostname-slow. log. We can also set up another location as shown in listing 03 using the slow_query_log_file parameter.

What is MySQL slow log?

The MySQL slow query log is where the MySQL database server registers all queries that exceed a given threshold of execution time. This can often be a good starting place to see which queries are slowest and how often they are slow. MySQL on your server is configured to log all queries taking longer than 0.1 seconds.

How do I turn off slow query log?

To disable or enable the slow query log or change the log file name at runtime, use the global slow_query_log and slow_query_log_file system variables. Set slow_query_log to 0 to disable the log or to 1 to enable it. Set slow_query_log_file to specify the name of the log file.


2 Answers

Enabling slow query log has nothing to do with PHP version. You have to enable it in the MySQL server. You can enable in two ways

  1. In runtime
  2. During the server start

If your server is above 5.1.6 you can set the slow query log in the runtime itself. For which you have to execute this queries.

set global log_slow_queries = 1; set global slow_query_log_file = <some file name>; 

Or alternatively you can set the this options in the my.cnf/my.ini option files

log_slow_queries = 1;  slow_query_log_file = <some file name>; 

Where the option file is changed, the MySQL server need to be restarted.

Location of the mysql option file can be found here http://dev.mysql.com/doc/refman/4.1/en/mysql-config-wizard-file-location.html

FYI : log_slow_queries was removed in MySQL 5.6.1 and slow_query_log is used instead. http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_log-slow-queries

But for performance you can set the log output (option log_output) to TABLE. Also you can have a look other slow query log options like long_query_time, log-queries-not-using-indexes

like image 52
Rituparna Kashyap Avatar answered Sep 25 '22 19:09

Rituparna Kashyap


You can set it temporarily, by running the following commands:

set global slow_query_log = 1; set global slow_query_log_file = '/var/log/mysql-slow.log'; 

but your changes will be undone when mysql is restarted.

You can set it permanently, by adding the following to your my.cnf file:

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

The location of my.cnf varies by OS, but is often found in /etc/my.cnf, or /etc/mysql/my.cnf:

After saving your changes, you will need to restart MySql. This can vary by OS, but here are some common examples:

sudo /etc/init.d/mysqld restart 

and

sudo service mysqld restart 
like image 39
Ross Smith II Avatar answered Sep 22 '22 19:09

Ross Smith II