One of the most important steps in improving and adjusting MySQL database is to identify the queries that are causing problems. How can we find out what queries are taking a long time to complete? How can we check what queries are slowing down the MySQL database server? MySQL has the answer for us and we only need to know where to look for it.
Today in this article we will show you to identify which MySQL database creating the problem on your server.
First let’s make sure that the MySQL server will log ‘slow’ queries and for this we have to check if slow query logging enabled on MySQL server:
mysqladmin var |grep log_slow_queries | log_slow_queries | OFF
If log_slow_queries is ON then its already enabled. The MySQL variable long_query_time (default 1) defines what is considered as a slow query. In the default case, any query that takes more than 1 second will be considered a slow query.
In order to enable the MySQL slow query log:
Login to the server as root.
Open your MySQL server config file /etc/my.cnf with your editor.
nano /etc/my.cnf
In the [mysqld] section, add the line as below:
[mysqld] long_query_time = 1 log-slow-queries = /var/log/mysql/mysql-slow.log
This configuration will log all queries that take more than 1 sec in the file /var/log/mysql/mysql-slow.log.
To investigate the MySQL slow queries log, we can look inside the log file for each slow query that was executed by the server.
tail -f /var/log/mysql-slow.log
This information allows us to see what queries need to be optimized.