Introduction:
MySQL’s slow query log makes it easy to track SQL queries that take more than a specific time for execution. This allows you to find inefficient SQL queries that can be optimized to improve database performance.
Prerequisite:
- A user with sudo privileges
- Mysql Database must be installed
- Mysql credentials (if secured)
Implementation:
Step 1: SSH the server with the user having sudo privileges
| $ ssh user@IP |
Step 2: Open the mysqld.cnf file from the path /etc/mysql/mysql.conf.d/ with vi editor or nano editor
| $ cd /etc/mysql/mysql.conf.d/$ vi mysqld.cnf |
Step 3: Search for the parameter slow_query_log and edit the existing setup as mentioned
| slow_query_log = 1slow-query_log_file = /var/log/mysql/mysql-slow.loglong_query_time = 2 ====== Change the long query time as per the requirement in seconds. ====== |
Step 4: Create the mysql-slow.log file in the path as mentioned in the parameter slow-query_log_file in step 3 and set its user as the mysql user
| $touch /var/log/mysql/mysql-slow.log $chown mysql:mysql /var/log/mysql/mysql-slow.log |
Step 5: Restart MySQL service
| $ /usr/local/cpanel/scripts/restartsrv_mysql |
Step 6: To save all slow queries that the system previously recorded in the mysql-slow.log file
| $ mysqldumpslow -a /var/log/mysql/mysql-slow.log |
Step 7: To verify this setup log in to MySQL database server
| $ mysql -u root -p ==== Enter the root user password ==== |
Step 8: Run the below query to verify whether the slow query log has been enabled or not
| SHOW GLOBAL VARIABLES LIKE ‘slow_query_log%’; |
If the slow_query_log value is ON, we can confirm that the slow query log has been enabled successfully
Conclusion:
By enabling the slow query log for MySQL we can diagnose the performance and efficiency issues that affect the server.
By identifying queries that are particularly slow in their execution, we can address them while restructuring the application.