Enable logging slow queries, it is a very easy step to do even for a beginner and you will have access to a file, that gathers queries that tool longer than 10 seconds set by default. You may optionally decrease this number down to 1, which is minimum.
The slow query log consists of SQL statements that took more than long_query_time seconds to execute. The minimum value is 1 second and the default value of long_query_time is 10 seconds of execution time, this means, that if your query takes more than 10 seconds to execute, it will be written into this log. This value is measured in real time, not CPU time, so a query that is under the threshold on a lightly loaded system might be above the threshold on a heavily loaded one.
In /etc/mysql/my.cnf which is a MySQL server’s main config file, under [mysqld] add this line:
log-slow-queries = /var/log/mysql/slow.logTo use log-slow-queries properly, you may need to consider setting up the following to ON, if your server does use queries, for example cron server that uses a lot of optimizing queries like OPTIMIZE, DROP, … or if you use queries, that select all rows.
log-slow-admin-statements = OFF (Boolean OFF by default, turn ON to include admin statement queries in the log). What is an administrative statement? Administrative statements are ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.
log-queries-not-using-indexes = OFF (Boolean OFF by default, turn ON to include queries that are expected to retrieve all rows are logged. For example, a query that uses a full index scan uses an index but would be logged because the index would not limit the number of rows.)
When your slow.log is empty with 10 seconds, it is time to harden the optimization by lowering the number gradually to 1 – the minimum, just the theory, but you may lover the number by setting
long_query_time = 8
This means, that from the time, you will restart the MySQL server, only queries over 8 seconds of execution time will be added to /var/log/mysql/slow.log file.
All these settings start to apply after MySQL server restart, to restart MySQL use:
$ sudo service mysql reloadBeware of changing more than one parameter in my.cnf file an once, always change one parameter, reload the service and if it is loaded without any error, then continue in adding or altering more parameters.