The mysql.slow_log table in ApsaraDB RDS for MySQL records SQL statements that exceed a specified running time threshold. These records are known as slow queries. By analyzing these logs, you can identify performance bottlenecks and optimize SQL statements to improve overall system performance. This topic describes the collection policy for slow query logs in RDS for MySQL and explains how to view them to better understand their behavior and usage.
Slow query log collection policy
RDS for MySQL uses a logagent component to retrieve slow query logs from the mysql.slow_log table every minute. The logs are then synced to a backend system for processing. After the sync, logagent clears the mysql.slow_log table using the TRUNCATE command. This process reduces tablespace usage and avoids impacting the performance of your production environment.
View slow query log details
Method 1: View in the Log Management page
The Log Management page only supports viewing the details of slow query logs. To export or analyze slow query logs, see Method 2: View slow query logs on the Slow SQL page.
Method 2: View in the Slow SQL page
On the Slow SQL page, you can view and export slow query logs, analyze SQL statements that exceed a specified running time threshold, and find corresponding administration solutions. These solutions help you quickly locate and resolve database performance issues and improve system stability and reliability.
FAQ
Q: Why is the slow_log table in my RDS for MySQL database empty? Is it cleared every minute after collection?
A: Yes, it is. The mysql.slow_log table is a system table. Frequent write and query operations can affect database performance. For this reason, RDS periodically clears the data from this table to reduce tablespace usage and avoid impacting the performance of the production environment. You cannot manually change the retention period. You can use the DescribeSlowLogRecords - View slow log details API operation to retrieve slow query logs in batches. If a single export is limited by the number of logs, you can perform the operation multiple times to retrieve all records.
References
Enable the automatic administration feature of Database Autonomy Service (DAS) to automatically optimize slow SQL statements as they occur in your database instance.