Identifying Slow MySQL queries

Article by Aashish

MySQL can sometimes create big problems on a server when you have users abusing it.
This article will teach you how to correctly identify the queries that are creating a problem for your server.

MySQL can log those queries that are taking longer then X seconds but this future is not turned on by default.
Here’s how you turn it on:
Login to your server as root
Open my.cnf with your favorite editor. Example:
vim /etc/my.cnf

Into the [mysqld] section add the fallowing lines
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 3

This is just an example. You can use any file name that you want and you can modify the long_query_time to any value. In this example I will be logging to /var/log/mysql-slow.log any queries that are taking longer then 3 seconds.

Go ahead and save the configuration.
For vim: CTRL+X and YES

Now we have to actually create the log file.
touch /var/log/mysql-slow.log

Now we are changing the owner of the file so that mysql and actually write to it.
chown mysql.root /var/log/mysql-slow.log

Now we restart mysql
service mysql restart

It should restart successfully. If it doesn’t check that you didn’t brake my.cnf by examining the error file in your data directory.

Wait a few minutes and then examine the slow queries log
A few examples on how to do it:

cat /var/log/mysql-slow.log
tail /var/log/mysql-slow.log
tail -50 /var/log/mysql-slow.log

After you have identified the offending query go ahead and optimize or remove it.
Again test the results by looking at your server load and the mysql slow queries log.

After you fixed all the problems go ahead and comment the slow queries logging as it will slow your server a bit if you leave it on. my.cnf should now look similar to this:

#log-slow-queries = /var/log/mysql-slow.log
#long_query_time = 3

And don’t forget to restart MySQL after this.

service mysql restart

Hope this helps !

Install MySQL Performance Tuning Primer Script

Tuning the performance of MySQL can be a really hard job to do.
There are many things to consider and no two servers are identical so there is no universal solution.
Tuning Primer is a script that will help you tune your mysql installation by providing very healthy recommendations based on past mysql records.
For the script to be efficient you must run the mysql server for at least 48 hours.
Installation is extremely simple:

Download the script
wget http://day32.com/MySQL/tuning-primer.sh

Change the permissions for the file
chmod 755 tuning-primer.sh

Run it
./tuning-primer.sh

Apply the sugesttions

Enjoy!

Related Posts:

• No Related Posts