How to enable MySQL Log Slow Queries on Linux

0

MySQL

Sometimes MySQL is a resource hog, and most of the time, it’s not a server related problem. 90% of the MySQL performance issues are related to inneficient queries / bad SQL programming. When we face this scenario, a simple top -c commands can reveal the big CPU or RAM usage, but sometimes we have to go deeper and know which queries are causing the bad performance.

MySQL offers a great tool to find out which are the most slow queries running in your server. So, today I will show you how to enable mysql log slow queries on any Linux server.

Enabling MySQL Log Slow Queries

First, let’s check if MySQL log slow queries is not already active:

mysqladmin var -u root -ppassword | grep log_slow

You should see something like this:

 [[email protected]:~]mysqladmin var -u root -ppassword | grep slow_log -i | log_slow_queries | OFF | [[email protected]:~]

Replace “password” with your real root password and you are done. On most cPanel servers you can use it without -ppassword option. Now we know slow queries are off, so it’s time to activate this option. For that we will edit MySQL configuration file, normally located at /etc/my.cnf

 nano -w /etc/my.cnf

Under the [mysqld] section add this:

 log-slow-queries=/var/log/mysql-slow-queries.log long_query_time = 1

Final output:

 [mysqld] log-slow-queries=/var/log/mysql-slow-queries.log long_query_time = 1

Some SysAdmins also add this:

 log-queries-not-using-indexes

But in my experience, if you activate loggin queries without no indexes it will also log faster queries below 1 seconds, so, that’s not the main objetive here, however, be aware that queries using no indexes can be a major SQL problem too.

Explanation of each directive

 log-slow-queries: sets the log path. long_query_time: sets the seconds a query must be running to be considered "slow". log-queries-not-using-indexes: will log the queries that are not using indexes.

Once you finish editing /etc/my.cnf file, now let’s create the mysq-slow-queries file and set proper permissions.

 touch /var/log/mysql-slow-queries.log chown mysql.root /var/log/mysql-slow-queries.log

Restart MySQL server to apply changes:

 /etc/init.d/mysqld restart

That’s all. Wait a couple of hours to let the log fill with lot of information about queries. Then once you got enough logged queries, you can filter the top 10 slow queries using mysqldumpslow utility.

For example, this command will reveal the top 10 most slow queries on your server:

 mysqldumpslow -t 10 /var/log/mysql-slow-queries.log

Run mysqldumpslow –help for more information about how mysqldumpslow can help you on the log analysis.