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

This topic provides answers to some frequently asked questions about the general query log feature of ApsaraDB RDS for MySQL.

Background information

By default, ApsaraDB RDS for MySQL stores general query logs to tables due to the following reasons:

General query logs stored in files cannot be directly queried because users are not allowed to access files in ApsaraDB RDS for MySQL.
The output format of general query logs and slow query logs is determined by the value of the log_output parameter. ApsaraDB RDS for MySQL rotates slow query logs, which requires the slow query logs to be stored in tables. As a result, general query logs must also be stored in tables.

General query logs occupy a large amount of storage

Problem description

The storage capacity of an ApsaraDB RDS for MySQL instance is exhausted. To check whether the error is caused by excessively large general query logs, perform the following steps:

Check the storage usage of the RDS instance. The sys_data_size file is excessively large. For more information, see View the monitoring information.
View the instance parameters. The general_log parameter is set to ON. For more information, see View the parameters of an ApsaraDB RDS for MySQL instance.
Connect to the RDS instance and execute the following statement. The output indicates that the size of general query logs is excessively large. For more information, see Connect to an ApsaraDB RDS for MySQL instance.
```
SELECT table_schema AS 'Database', table_name,SUM(data_length + index_length + data_free)/1024/1024 AS "Table size in MB",SUM(DATA_FREE)/1024/1024 AS "Fragment size in MB"
FROM information_schema.TABLES
WHERE table_name='general_log'
```
Note
- The SQL statement is used to query the data of the mysql.general_log table from the TABLES table in the information_schema database. The data size is displayed in MB.
- The data that is returned by this SQL statement is sample data. The actual data returned varies.

Causes

If you set the general_log parameter to ON, a table named mysql.general_log is generated and records all operations that are performed on the RDS instance and the operation details. The operations include select, insert, update, and delete operations. If a large number of queries are running or the mysql.general_log table is uncleared for a long period of time, the table consumes a large amount of storage resources. As a result, the storage capacity of the RDS instance is exhausted.

General query logs cause performance issues

Problem description

As the number of connections increases, the CPU utilization increases. After you execute the SHOW PROCESSLIST statement or query the innodb_trx table, the result indicates that a large number of connections are in the Waiting for table level lock state.

Causes

By default, the general query logs of your RDS instance are stored in a table. Each thread sequentially writes data to the table because the process requires Metadata Locks (MDLs) and table locks. In this case, connections enter the Waiting for table level lock state when multiple threads attempt to write concurrently to the table.

General query logs cause a longer RTO

Problem description

The recovery time objective (RTO) of your RDS instance increases. During the recovery period, the RDS instance is unavailable.

Causes

If your RDS instance shuts down unexpectedly, the crash marker of the general query log is true. In this case, the RDS instance initiates an automatic recovery process upon restarting. During recovery, if the size of the tables on the RDS instance is large, the time required to restore the tables can be extensive, and the RDS instance remains unavailable.

Solutions

Clear general query logs

To prevent new logs from being generated, set the general_log parameter to OFF. For more information, see Modify instance parameters.
Use the privileged account to connect to the RDS instance and execute the following statement to clear general query logs. For more information, see Connect to an ApsaraDB RDS for MySQL instance.
Note
You cannot execute the TRUNCATE statement to clear general query logs of the RDS instances that run MySQL 5.6. For more information, see Features.
```
TRUNCATE TABLE mysql.general_log;
```

What to do next

When you are debugging or tracing issues, we recommend that you set the general_log parameter to ON for a short period of time. After you resolve the issues, set the general_log parameter to OFF at the earliest opportunity.
We recommend that you enable the SQL Explorer and Audit feature. The feature can automatically record and analyze SQL statements from database kernels, the account that is used to execute the SQL statements, the IP address of the application from which queries are initiated, and the execution details of the SQL statements. The feature does not affect the performance of the RDS instance. For more information, see Use the SQL Explorer and Audit feature. Data that is generated by using the SQL Explorer and Audit feature is stored in Database Autonomy Service (DAS). The storage capacity of the RDS instance is not affected.
You can expand the storage capacity of an RDS instance. For more information, see Change instance specifications. You can also enable the automatic storage expansion feature for the RDS instance. When the storage capacity of an RDS instance reaches the specified threshold, the system automatically expands the storage capacity of the RDS instance. For more information, see Configure automatic storage expansion.