Slow queries in MySQL can significantly impact the performance of your web applications and waste server resources. By enabling the slow query log, you can identify long-running or inefficient SQL queries, allowing you to optimize them for better performance. This guide will walk you through the steps to enable the slow query log on various Linux-based systems, including Ubuntu, CentOS, RHEL, Debian, and AlmaLinux.
Step-by-Step Guide to Enable Slow Query Log
Step 1: Connect to Your VPS via SSH
Use an SSH client such as Terminal (macOS/Linux) or PuTTY (Windows) to log in to your VPS.
For Terminal (macOS/Linux) or WSL:
# ssh -p <ssh_port> your_username@your_server_ip
Replace ssh_port, your_username, and your_server_ip with your actual credentials.
Step 2: Next, you’ll need to modify the MySQL configuration file:
On Ubuntu/Debian:
# sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
On CentOS/RHEL/AlmaLinux:
# sudo vi /etc/my.cnf
Step 3: Add Slow Query Settings
In the configuration file, find the [mysqld] section and add or update the following lines:
Then add or update the following lines:
slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 3 log_queries_not_using_indexes = 1
What do these do:
- slow_query_log = 1: Enables slow query logging.
- slow_query_log_file: Specifies the file where logs will be saved.
- long_query_time = 3: Logs queries that take longer than 3 seconds.
- log_queries_not_using_indexes = 1: (Optional) Logs queries that do not use indexes.
Save and exit the file:
- In nano: Press Ctrl + O, then Enter, and Ctrl + X to exit.
- In vi: Type: wq and press Enter.
Step 4: Create the Log Directory and File
Ensure that the log directory exists and that MySQL has permission to write to it:
# sudo mkdir -p /var/log/mysql # sudo touch /var/log/mysql/mysql-slow.log # sudo chown mysql:mysql /var/log/mysql/mysql-slow.log
Step 5: Restart MySQL
To apply your changes, restart the MySQL service:
On Ubuntu/Debian/CentOS/RHEL:
# sudo systemctl restart mysql # sudo systemctl status mysql
Step 6: Confirm Slow Query Log Is Working
After MySQL has been running for a while and your application has executed some queries, check the log file to see if slow queries are being recorded:
# sudo tail -f /var/log/mysql/mysql-slow.log
If everything is set up correctly, you should see slow queries being logged in real time.
Conclusion:
Enabling the slow query log on your Linux VPS/Server is a crucial step in monitoring and optimizing your MySQL database performance. Whether you're using Ubuntu, Debian, CentOS, or RHEL, this setup will help you identify and address inefficient SQL queries over time.
