ApsaraDB RDS:FAQ about the general query log feature of ApsaraDB RDS for MySQL

Why does RDS store general query logs in a table?

ApsaraDB RDS for MySQL stores general query logs in a database table rather than a file by default, for two reasons:

• Users cannot directly access files on an RDS instance, so file-based logs cannot be queried or downloaded.

• The log_output parameter controls the output format for both general query logs and slow query logs. Because RDS rotates slow query logs and requires them to be stored in tables, general query logs must also be stored in tables.

Why does the general query log exhaust storage?

Each SQL statement executed on the instance generates a log entry. When the general query log is enabled for an extended period or the instance handles high query volumes, the mysql.general_log table grows continuously. If you don't periodically clear the table, it fills the instance's storage.

To confirm the general query log is causing the storage issue:

1. Check storage usage on the instance monitoring page. If sys_data_size is unusually large, the table data is consuming storage. For details, see View the monitoring information.

2. View the instance parameters. If general_log is set to ON, the feature is active.

Why does the general query log cause performance issues?

When the general query log is enabled, every thread that executes a SQL statement must write a row to the mysql.general_log table. This write operation requires both metadata locks (MDLs) and table locks, so threads write to the table sequentially rather than in parallel.

Under high connection volumes, many threads compete for these locks simultaneously, leaving connections in the Waiting for table level lock state. As connection count grows, so does CPU utilization.

To confirm: run SHOW PROCESSLIST or query the innodb_trx table. If many connections show Waiting for table level lock, the general query log is contributing to the lock contention.

Why does the general query log increase recovery time?

If an RDS instance shuts down unexpectedly, the general query log's crash marker is set to true. On restart, the instance initiates an automatic recovery process. If the size of the tables on the RDS instance is large, restoring them takes a long time, during which the instance remains unavailable. This directly extends your recovery time objective (RTO).

Solution

1. Set general_log to OFF to stop new log entries from being written. See Modify instance parameters.

2. Connect to the instance using a privileged account. See Connect to an ApsaraDB RDS for MySQL instance.

3. Clear the log table:

   The TRUNCATE statement is not supported on instances running MySQL 5.6. For those instances, contact technical support to clear the log. For the full list of version-specific feature support, see Features.

   TRUNCATE TABLE mysql.general_log;

After you clear the log, check the instance monitoring page to confirm that storage usage has decreased.

Alternatives for SQL analysis

Keep the general query log disabled during normal operations. Enable it only for short debugging sessions, then disable it and clear the table immediately after.

For ongoing SQL analysis, use one of these approaches instead:

• SQL Explorer and Audit (recommended): Enable this feature to automatically record and analyze SQL statements. Data is stored in Database Autonomy Service (DAS) and does not consume your RDS instance's storage, so it has no impact on instance performance. See Use the SQL Explorer and Audit feature.

• Temporary general query log: If you need to debug a specific issue, enable the general query log, then query the log table to inspect SQL activity:

  SELECT * FROM mysql.general_log;

  Disable the feature and clear the table as soon as debugging is complete.

What to do if storage is already exhausted

If the instance ran out of storage before you could clear the log, expand storage capacity:

• Manual expansion: Change the instance specifications to increase storage. See Change instance specifications.

• Automatic expansion: Configure automatic storage expansion so the system expands storage when usage reaches a specified threshold. See Configure automatic storage expansion.