How to resolve issues caused by excessive log files in a cluster - PolarDB

Large transactions can rapidly generate binary logs, redo logs, or undo logs and exhaust cluster storage space. This topic explains how to free storage by managing each log type and provides follow-up maintenance recommendations.

Note

After purging logs, the console may take time to reflect the updated storage usage.
If DML (Data Manipulation Language) operations on large objects are generating logs rapidly, scale out your storage space and investigate the root cause.
If purging log files does not free enough space, see Resolve full cluster storage caused by excessive data files.

Binary logs

Retention policies

If binary logging is enabled, binary log files are retained for three days by default. Binary log files whose retention period exceeds three days 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 (seven days) by default.

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

Modify the retention period

Important

Changing the retention period does not disrupt connections or require a cluster restart. However, purging a large volume of binary log files (for example, 10 TB) may cause brief write exceptions. To minimize impact, change the retention period during off-peak hours or progressively shorten it so that only a portion of logs is purged at a time. Deleted binary log files cannot be restored.

Modify the retention parameter for your cluster version:

Cluster version	Parameter	Unit	Valid values	Default
PolarDB for MySQL V5.6	`loose_expire_logs_hours`	Hours	0-2376	72
PolarDB for MySQL 5.7/8.0	`binlog_expire_logs_seconds`	Seconds	0-4294967295	259200

A value of 0 disables automatic deletion. For instructions, see Configure cluster and node parameters.

Trigger immediate purging

After changing the retention parameter, historical binary logs are not immediately purged. Use one of the following methods:

Wait for log rotation. When the active binary log file reaches the max_binlog_size limit, a new file is created and all historical binary log files are automatically deleted.
Flush binary logs. Run FLUSH BINARY LOGS with the privileged account.
Restart the cluster. Historical binary log files are purged when the cluster restarts.

Purge binary logs when binary logging is disabled

If binary logging is disabled and binary log files still occupy storage:

Enable binary logging.
Set loose_expire_logs_hours or binlog_expire_logs_seconds to a small value.
Wait for the logs to expire and be purged automatically.
Disable binary logging.

Redo logs

PolarDB for MySQL uses redo logs instead of binary logs for data synchronization between the primary node and read-only nodes.

Storage usage

Excluding log backups, local redo logs occupy 2 GB to 11 GB of storage:

Component	Size
8 redo logs in the buffer pool	8 GB
Active redo log (being written)	1 GB
Pre-created redo log	1 GB
Last redo log	1 GB

With log backups enabled, local redo logs are retained for about one hour after the backup completes. A high write speed (over 35 MB/s) may cause temporary accumulation.

Purge rules

Redo logs cannot be manually purged. They are automatically purged after a log backup completes.

Note

Adjust the log backup policy in the PolarDB console. The default retention period is 7 days.

Undo logs

In PolarDB, undo logs serve as historical versions for Multi-Version Concurrency Control (MVCC). An uncommitted transaction holding an old read view blocks undo log purging and causes accumulation.

Check for uncommitted transactions:

SELECT * FROM INFORMATION_SCHEMA.innodb_trx;

Because the primary node and read-only nodes share storage, uncommitted large transactions on read-only nodes also block undo log purging. After killing the corresponding thread, undo logs stop growing.

Reclaim undo log space

To reclaim space, first check whether the undo purge process is lagging:

SELECT COUNT FROM INFORMATION_SCHEMA.innodb_metrics WHERE name = 'trx_rseg_history_len';

If the value exceeds 1 million, or keeps increasing under sustained write pressure:

Increase innodb_purge_batch_size. No cluster restart required.
Increase innodb_purge_threads. Set it to match the number of CPU cores in your cluster specification. This requires a cluster restart -- perform it during off-peak hours. Wait for the undo history length to decrease.
Enable undo log truncation. Set innodb_undo_log_truncate to ON. PolarDB maintains 8 undo files. When a single file exceeds innodb_max_undo_log_size (default: 8 GB), truncation is triggered. If a file size exceeds 8 GB for a long time, write pressure is low, and you are using an early cluster version, you can upgrade the cluster to the latest minor version.
Important
Undo log truncation adds overhead during cluster switchover or restart. Disable it immediately after space is reclaimed, especially before minor version upgrades. Re-enable when needed.
Note
Some legacy versions have bugs related to undo log truncation and do not expose the innodb_undo_log_truncate parameter in the parameter modification interface. Perform a minor version upgrade to access this parameter.

Follow-up maintenance

PolarDB for MySQL uses a storage-compute separated architecture. To prevent storage issues from recurring, manually scale out or scale in storage space as needed.