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
[mysqld] log-slow-queries=/var/log/mysql-slow-queries.log long_query_time = 1
Some SysAdmins also add this:
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:
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.