Solutions to a full instance caused by the general query log - ApsaraDB RDS

When the general query log is enabled, it records all user operations, including the execution details of each SQL statement. If your instance handles high traffic or you do not clean the general query log file for a long time, the file consumes a large amount of storage space and can cause the storage to be exhausted. This topic describes common issues and solutions related to the general query log.

Background information

ApsaraDB RDS for MySQL stores general query logs in the TABLE format by default for the following reasons:

You cannot query or directly download logs stored in the FILE format because you cannot directly access the files of an ApsaraDB RDS for MySQL instance.
The `log_output` parameter affects both general query logs and slow query logs. ApsaraDB RDS for MySQL uses a rotation mechanism to collect slow query logs, which requires storing them in the TABLE format. Therefore, general query logs must also be stored in the TABLE format.

General query logs occupy a large amount of storage space

Problem description

The storage space of your ApsaraDB RDS for MySQL instance is full. You can view the storage usage of the instance. If you find that `general_log_size` is too large, the issue is caused by an oversized general query log file.

Cause

When the general query log is enabled for an ApsaraDB RDS for MySQL instance, the log file records all user operations. This includes the details of every SQL statement, such as queries, inserts, updates, and deletes. If the instance handles high traffic or the general query log file is not cleaned for a long time, the file grows continuously. If not handled promptly, the file will eventually exhaust the instance's storage space.

General query logs cause performance issues

Problem description

The number of connections and CPU utilization increase. When you run SHOW PROCESSLIST or query the `innodb_trx` table, you can see that many connections are in the `Waiting for table level lock` state.

Cause

ApsaraDB RDS for MySQL uses the TABLE format as the default storage for the general query log. Threads write to the general query log serially. Writing to the log requires a metadata lock (MDL) and a table-level lock. This table-level lock causes connections to enter the `Waiting for table level lock` state.

General query logs cause a longer RTO

Problem description

The instance crash recovery time increases, which results in a longer Recovery Time Objective (RTO). During this period, the instance is unavailable.

Cause

If an instance shuts down unexpectedly, the crash mark for the general query log is set to true. This triggers an automatic recovery process when the instance restarts. If the table is large, recovery takes a long time. The instance is unavailable during this process.

Solution

Clean the general query log file

Disable the general query log by setting the `general_log` parameter to OFF. This prevents new logs from being generated. For more information, see Set instance parameters.
Use a privileged account to connect to the ApsaraDB RDS for MySQL instance. Then, run the following statement to clean the general query log file. After a period of time, you can check the storage usage of the general query log on the instance monitoring page.
Note
You cannot run the `TRUNCATE` command to clean the general query log file on an ApsaraDB RDS for MySQL 5.6 instance. To clean the file, you must contact us.
```
TRUNCATE TABLE mysql.general_log;
```

Disable the general query log during normal database use. Enable it only temporarily for debugging or tracking issues. After you are finished, clean and disable the log promptly. This prevents log file accumulation and storage exhaustion that can be caused by high traffic or by not cleaning the log for a long time. To view and analyze the execution details of SQL statements, you can choose one of the following two methods:

(Recommended) Enable SQL Explorer and Audit. The system automatically records and analyzes executed SQL statements. The resulting insight and audit data is stored in Database Autonomy Service (DAS). This method does not use the storage space of your RDS instance or affect its performance.
Temporarily enable the general query log. You can temporarily enable the general query log to debug or track issues. Use the following command to query the execution of SQL statements. After you finish debugging, disable and clean the general query log file.
```
SELECT * FROM mysql.general_log;
```

Subsequent maintenance

You can manually expand the instance storage space or enable the automatic storage expansion feature. If you enable automatic storage expansion, the system automatically expands the storage space when the storage usage reaches the specified threshold.