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.
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:-
# * 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” :-
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.