ScaleScaleScaleScale

Tips / Nginx


How to enable MySQL Log Slow Queries on Linux

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:

[root@server.myserver.com:~]mysqladmin var -u root -ppassword | grep slow_log -i
| log_slow_queries | OFF |
[root@server.myserver.com:~]

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.

Popular search terms:

  • enable the slow query log to troubleshoot bad queries
  • how to check slow my sql slow queries in linux com
  • how to see slow queries mysql is on or off
  • slow query log mysql linux every week
profile

Esteban Borges

Linux Geek, Webperf Addict, Nginx Fan. CTO @Infranetworking