How to Enable and Configure MySQL and PHP Slow Logs for Performance Optimization

Overview of Slow Logs

Every SQL query in a MySQL database consumes a certain amount of time. To identify and locate performance bottlenecks, it is necessary to find the SQL statements that take longer to execute. MySQL’s slow query log feature records all SQL statements that exceed a specified execution time threshold.

By analyzing the slow logs, database administrators and developers can pinpoint slow queries and their execution times, enabling targeted performance optimization.

Steps to Enable MySQL Slow Query Log

Modify MySQL Configuration File

Edit the MySQL configuration file my.cnf to enable the slow query log by adding the following parameters:

slow_query_log = 1               # Enable slow query log
slow_query_log_file = /var/log/mysql/mysql-slow.log
log_queries_not_using_indexes = 1   # Log queries that do not use indexes

Parameter explanations:

• slow_query_log: Whether to enable slow query logging; 1 means enabled, 0 means disabled.
• slow_query_log_file: Path to the slow query log file.
• log_queries_not_using_indexes: Whether to log queries that don’t use indexes; 1 means log them.

Restart MySQL Service

After modifying the configuration file, restart MySQL to apply the changes:

service mysql restart

View Slow Query Log

After restarting, you can monitor the slow query log in real-time with:

cd /var/log/mysql/
tail -f mysql-slow.log

Steps to Enable PHP Slow Log

Modify php.ini Configuration

Add or update the following parameters in the php.ini file to enable PHP slow logging:

slowlog = /var/log/php/php-slow.log
request_slowlog_timeout = 10  # Timeout in seconds; requests longer than this will be logged

Parameter explanations:

• slowlog: Path to the slow log file.
• request_slowlog_timeout: Timeout threshold in seconds; requests exceeding this time are logged.

Restart php-fpm Service

After updating the configuration, restart the PHP service:

sudo service php-fpm restart

View PHP Slow Log

After restarting, you can view the PHP slow log with:

cd /var/log/php/
tail -f php-slow.log

Summary

Enabling slow logs for MySQL and PHP helps quickly identify performance bottlenecks in queries and scripts, allowing focused optimizations to improve overall system responsiveness and stability.