How do I clear the log files that are occupying a large amount of storage? - PolarDB

This topic describes what to do when your storage is exhausted by a large number of log files.

Description

According to storage analysis results, a large amount of storage in a PolarDB for MySQL cluster is occupied by binary log files, redo log files, or undo log files, as shown in the following figure.

The log files may have been generated during large transactions.

Solution

Note

The result of clearing binary log files, redo log files, or undo log files is not updated in real time on the PolarDB console. Wait for a while before you view the result again.
A large number of binary log files, redo log files, or undo log files are generated within a short period of time when you perform DML operations, especially those that involve large fields. In this case, we recommend that you scale up the storage of the cluster and troubleshoot the rapid increase of log files.
If the storage of your cluster is still insufficient after you clear log files by using the following solutions, you can clear other data files to release more storage space. For more information, see How do I release storage by deleting data files?

Clear binary log files

Retention policy

There are two retention policies for binary log files:

If the binary logging feature is enabled, binary logs are retained for three days by default, after which they are automatically deleted.
Note
- For a PolarDB for MySQL cluster that was purchased before November 23, 2023, binary log files are retained for two weeks (14 days) by default.
- For a PolarDB for MySQL cluster that was purchased before January 17, 2024, binary log files are retained for one week (7 days) by default.

If the binary logging feature is disabled, existing binary log files are permanently retained.

Retention period modification

Important

Changing the retention period of binary logs does not disrupt connections or require a cluster restart.

However, if a large volume of binary log files (such as 10 TB) is to be purged due to a changed retention period, brief exceptions may occur during write operations. Therefore, if a large volume of binary logs exists, we recommend changing the retention period during off-peak hours. Also, you can shorten the retention period in a progressive manner, so that only a part of binary logs is cleared each time, mitigating the impact on your business operations.

If binary logging has been enabled for your cluster, change the retention period of binary logs as follows:
- PolarDB for MySQL V5.6 cluster: modify the value of the loose_expire_logs_hours parameter. The value range of this parameter is [0, 2376], measured in hours. Its default value is 72. A value of 0 indicates that binary log files are not automatically deleted.
- PolarDB for MySQL cluster V5.7 or V8.0 cluster: modify the value of the binlog_expire_logs_seconds parameter. The value range of this parameter is [0, 4294967295], measured in seconds. Its default value is 259200. A value of 0 indicates that binary log files are not automatically deleted.
  Note
  After you modify the values of these two parameters, historical binary logs in your cluster are not cleared immediately. You can use one of the following methods to trigger immediate purging:
  - Wait for the rotation of binary log files. When the active binary log file reaches the maximum size set by max_binlog_size, a new binary log file is created and all older binary log files are automatically deleted.
  - Use the privileged account to execute the flush binary logs command.
  - Restart the cluster. Historical binary log files are cleared after the cluster is restarted.
If binary logging is not enabled for your cluster and you want to delete binary log files, do the following:
1. Enable binary logging for your cluster.
2. Set the loose_expire_logs_hours or binlog_expire_logs_seconds parameter to a smaller value.
3. Wait for binary logs to expire and be automatically purged.
4. Disable the binary logging feature.

Clear redo log files

In most cases, redo log files are automatically cleared after backup. However, the cache pool of redo log files occupies huge storage space, 11 GB at maximum. This includes 8 GB of eight log files, 1 GB of a log file that is being written, and 1 GB of the most recent log file. You can configure the loose_innodb_polar_log_file_max_reuse parameter to specify the number of cached redo log files to reduce the storage usage of log files. However, if loads on the cluster are heavy, periodic performance fluctuations may occur.

Clear undo log files

Before you clear undo log files, check whether uncommitted old transactions exist. Undo log files in PolarDB serve as historical versions in Multi-Version Concurrency Control (MVCC). Therefore, when an uncommitted transaction holds an outdated read view, undo log files cannot be cleared, leading to the waste of storage. You can execute the following statement to check whether large transactions exist:

SELECT * FROM INFORMATION_SCHEMA.innodb_trx;

Uncommitted large transactions on read-only nodes can also prevent undo log files from being cleared, because storage resources are shared between the primary node and read-only nodes of a PolarDB cluster. After the threads of large transactions are killed, the storage usage of the log files stops increasing. To clear undo log files, first perform the following operations to check the progress of the undo history list.

Consider postponing the clearing of undo log files when write loads are heavy. PolarDB prioritizes guaranteeing current write performance, heavy write loads may delay the clearing of undo log files. You can execute the following statement to check the length of the current undo history list:
```
SELECT COUNT FROM INFORMATION_SCHEMA.innodb_metrics WHERE name = 'trx_rseg_history_len';
```
If the length is greater than 1 million, or if the length keeps increasing within a few minutes, and you cluster is under heavy workloads, you can adjust the following parameters:
1. Increase the value of the innodb_purge_batch_size parameter. This operation does not restart the cluster.
2. Set the innodb_purge_threads parameter to the number of cores defined in the cluster specifications. This operation restarts the cluster. Therefore, we recommend that you perform this operation during off-peak hours. After the length of the undo history list decreases, the storage usage of undo log files stops increasing. If you want to release the storage occupied by undo log files, you can enable the undo log truncation feature.
To enable the undo log truncation feature, set the innodb_undo_log_truncate parameter to ON. After you enable this feature, switching or restarting a cluster consumes additional resources. We recommend that you disable the feature immediately after storage is released, and make sure it is disabled before you initiate tasks such as minor version updates. You can enable the undo log truncation feature again as needed. PolarDB maintains eight undo log files. When the size of a single file exceeds the value of the innodb_max_undo_log_size parameter, an operation is triggered to truncate undo log files. The default value of the innodb_max_undo_log_size parameter is 8 GB. If undo log files have long been greater than 8 GB and you are using an older cluster version, consider updating the cluster to the latest minor version. Make sure you conduct the update when write loads are not heavy.
Note
In some old versions of PolarDB, the innodb_undo_log_truncate parameter is prohibited from modification and thus not displayed on the parameter modification page. In this case, you need to update the minor version of your PolarDB cluster to the latest.

Next steps

Scale up the storage of your cluster. PolarDB for MySQL uses an architecture that separates storage resources from computing resources. You can manually scale up the storage of your cluster. For more information, see Manually scale up or scale down the storage capacity