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

Problem description

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

The log files may have been generated during large transactions.

Solutions

Binary log files

• By default, binary logs are retained for three days, 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.

  You can use one of the following methods to modify the retention period of binary log files:

  • If your PolarDB cluster runs MySQL 5.6, you can adjust the loose_expire_logs_hours parameter to configure a proper retention period for your binary logs. The value range of this parameter is [0, 2376], measured in hours. Its default value is 72. The value 0 indicates that binary log files are not automatically deleted.

  • If your PolarDB cluster runs MySQL 5.7 or 8.0, you can adjust the binlog_expire_logs_seconds parameter to configure a proper retention period for your binary logs. The value range of this parameter is [0, 4294967295], measured in seconds. Its default value is 259200. The value 0 indicates that binary log files are not automatically deleted.

  Note

  After you set the retention period of binary log files by modifying the loose_expire_logs_hours or binlog_expire_logs_seconds parameter, historical binary log files in the cluster are not automatically deleted. To clear historical binary log files, you can use one of the following methods:

  • Historical binary log files in the cluster are cleared after a new binary log file is used when the last binary log file in the cluster reaches the max_binlog_size value.

  • You can also execute the flush binary logs statement by using the privileged account to immediately use a new binary log file and clear expired binary log files.

  • You can restart the cluster. Historical binary log files are cleared after the cluster is restarted.

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

Note

To delete the binary log files, you can enable the feature and set the loose_expire_logs_hours or binlog_expire_logs_seconds parameter to a smaller value. After the retention period is reached, binary log files are automatically deleted. Then, you can disable the binary logging feature.

Redo log files

In most cases, redo log files are automatically cleared after backup. You do not need to manually clear the log files. However, the redo log cache pool occupies a maximum of 11 GB of storage, including 8 GB of storage occupied by eight log files, 1 GB of storage occupied by a log file that is being written, 1 GB of storage occupied by a log file that is created in advance, and 1 GB of storage occupied by 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 for reducing the storage usage of log files. However, if the loads on the cluster are heavy, periodic performance fluctuations may occur.

Undo log files

Check whether uncommitted old transactions exist. Undo log files in PolarDB are used for historical versions in Multi-Version Concurrency Control (MVCC). Therefore, when an uncommitted transaction holds an outdated read view, the 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;

The primary node and read-only nodes of a PolarDB cluster share storage resources. If uncommitted large transactions exist on the read-only nodes, undo log files cannot be cleared either. After the threads of the transactions are killed, the storage usage of the log files stops increasing. If you want to clear undo log files, you can perform the following operations to determine the progress of the undo history list in advance.

1. If the write loads are heavy, you need to determine whether to postpone the clearing of the undo log files. PolarDB preferentially guarantees the current write performance. As a result, a delay may occur when you clear the 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 clear the log files.

2. To enable the undo log truncation feature, set the innodb_undo_log_truncate parameter to ON. After you enable the undo log truncation feature, additional resources are consumed when a cluster is switched or restarted. We recommend that you disable the feature immediately after storage is released, especially 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 the size of a single file exceeds 8 GB for a long period of time, you can adjust the value of the innodb_max_undo_log_size parameter. If this parameter is not available, you may be using an old version of PolarDB. You can update the minor engine version of your cluster to the latest version during off-peak hours and try modifying the parameter value again.

   Note

   In some old versions of PolarDB, the innodb_undo_log_truncate parameter is not displayed on the parameter modification page because the related permissions are prohibited. In this case, you need to update the minor version of your PolarDB cluster to the latest version.

What to do next

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 storage capacity.