If the general query log feature is enabled for your ApsaraDB RDS for MySQL instance, the general query log file records all user operations, including the execution details of each SQL statement. If a large number of access requests need to processed or the general query log file is not cleaned up for a long period of time, a large number of storage resources are consumed. As a result, the storage capacity is exhausted. 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 or downloaded 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 RDS instance is exhausted. To check whether the error is probably 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.
Causes
If the general query log feature is enabled for your RDS instance, the general query log file records all user operations, including the execution details of each SQL statement, such as SELECT, INSERT, UPDATE, or DELETE. If a large number of access requests need to processed or the general query log file is not cleaned up for a long period of time, the size of the general query log file continuously increased. If you do not resolve the issue at the earliest opportunity, the storage capacity 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 concurrently write data 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, a long period of time is required to restore the tables and the RDS instance remains unavailable.
Solution
You can clear the general logs to resolve this issue.
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 the general query log file. After a period of time, you can view the storage resources occupied by the general query log file on the instance monitoring page.
NoteYou cannot execute the TRUNCATE statement to clear the general query log files of the RDS instances that run MySQL 5.6. For more information, see Features. If you want to clear the general query log file, contact the technical support.
TRUNCATE TABLE mysql.general_log;
We recommend that you disable the general query log feature for your RDS instance and temporarily enable the general query log feature during debugging or issue tracking. After the debugging or issue tracking is complete, you must promptly disable the feature and clear general query logs. This prevents file accumulation and storage capacity exhaustion caused by a large number of access requests or general query logs not cleared for a long period of time. If you want to check and analyze the execution of SQL statements, you can use one of the following methods:
Enable the SQL Explorer and Audit feature: We recommend that you use this method. After you enable this feature, the system automatically records and analyzes executed SQL statements. The data related to the feature is stored in Database Autonomy Service (DAS) and does not occupy the storage capacity of your RDS instance. Therefore, this feature does not affect instance performance. For more information, see Use the SQL Explorer and Audit feature.
Temporarily enable the general query log feature: You can temporarily enable the general query log feature to debug and track issues. After you enable the general query log feature, execute the following statement to check the execution of SQL statements. After the debugging is complete, you can disable the general query log feature and clear the general query log file. For more information about how to enable general logs, see Modify instance parameters.
SELECT * FROM mysql.general_log;
What to do next
You can manually expand the storage capacity or enable the automatic storage expansion feature for the RDS instance. When the storage usage of an RDS instance reaches the specified threshold, the system automatically expands the storage capacity of the RDS instance. For more information, see Change instance specifications and Configure automatic storage expansion.