Track Slow MYSQL Queries Choking Your Server

MYSQL is the most used database, especially for PHP applications all around the world. Companies hire DBA’s to administer them and maintain uptime. But not everyone is that qualified or able to spend that much extra on his/her web application maintenance.

Slow MYSQL Server

To solve the slow database issue we need to find the choke points, i.e. the queries that are used much more resources that others.

People code and code without even thinking about the amount of limited resources they have, which makes life of Server admins a bit tough.

This tutorial is for both the Linux admins and common peoples having some command skills.

Fire up your favorite text editor and open mysql config file:-

root@server:~# vim /etc/mysql/my.cnf

Now Scroll down to section [mysqld], You will seee something like this:-

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql

Here at the end of this section add following lines by pressing key “i” :-

long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
log-queries-not-using-indexes

 

press ESC and type :wq! to exit the vim editor.

We have sucessfully edited the mysql config file. Now create an empty text file to store the slow log

root@server:~# touch /var/log/mysql/log-slow-queries.log

assign proper permissions:-

root@server:~# chown mysql.mysql -R /var/log/mysql

And Restart the mysql server:-

root@server:~# service mysql restart

Now run your application and check the log file by typing:-

root@server:~# tailf /var/log/mysql/log-slow-queries.log

Now you will be able to pin point the choke points which are causing your database go slow.

Thanks for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *