Data management and cleaning - PolarDB - Alibaba Cloud Documentation Center

PolarDB for MySQLEach cluster specification has a maximum storage capacity. Storage space can fill up due to continuous growth of data, logs, and temporary files, which may cause the cluster to be locked in read-only mode and affect normal operations. This topic describes storage space composition and provides methods to view usage, clean up files, and reclaim space.

Storage space composition

By understanding PolarDB for MySQL storage space composition, you can manage it effectively, locate issues accurately, and take appropriate optimization measures.

Data files: Store your business data such as tables and indexes.
Log files: Include Binlog, Redo logs, and Undo logs. These log files can grow rapidly during large transactions or high-concurrency write operations.
Note
PolarDB for MySQLBinlog is disabled by default for PolarDB for MySQL clusters. A more efficient physical log (Redo Log) is used instead. If Binlog is not enabled for your cluster, you can ignore this section.
Temporary files: Generated during operations such as sorting (ORDER BY), grouping (GROUP BY), or join queries. Uncommitted large transactions also generate temporary Binlog cache files.
System files: Store core components required for database operation, such as data dictionaries, transaction information, and doublewrite buffers. These files are managed by the InnoDB engine and are critical for data consistency and cluster recovery. You cannot directly modify them.

View storage space usage

You can view the storage space usage of your cluster in either of the following ways:

Go to the PolarDB console, go to the Basic Information page of the target cluster, and check the Distributed Storage area to view the current storage capacity of the cluster.
Go to the PolarDB console, go to the Diagnostics and Optimization > Quick Diagnostics page of the target cluster, and check the Storage Analysis tab to view the storage space usage at a specific point in time.

Clean up data files and reclaim table space

PolarDB for MySQL storage space may be excessively consumed due to data files not being organized for a long time. In addition, after using the DELETE command to delete data, the system only marks the record positions or data pages as reusable and does not directly reduce the table file size. This results in a large amount of space fragmentation, causing storage space to remain occupied.

Note

When cleaning up data files, the console may take some time to reflect the updates. Please wait patiently.

Clean up files

For tables that are no longer needed, use the TRUNCATE TABLE or DROP TABLE command to immediately release all occupied space.

Note

Back up your data before performing operations to prevent data loss.

Reclaim table space

For tables with significant fragmentation that you need to retain, you can run the OPTIMIZE TABLE command during off-peak hours. This operation rebuilds the table, eliminates fragmentation, and reclaims free space. You can also use the DMS tool to perform this optimization. DMS supports rate limiting, which has less impact on business workloads but runs slower.

Precautions

When the fragmentation rate of the target table is low, running the OPTIMIZE TABLE command may not significantly reduce the tablespace size. You can check the information_schema.tables field in the DATA_FREE view to check the fragmentation rate of the target table.
When running the OPTIMIZE TABLE command, table data is copied to a new temporary table, which temporarily increases the storage space usage of the cluster.
For tables without full-text indexes, the OPTIMIZE TABLE statement is executed using Online DDL, which supports concurrent reads and writes.
Running the OPTIMIZE TABLE operation on large tables causes sudden I/O and buffer usage spikes, which may lead to table locks and resource contention. Running this operation during peak hours may cause cluster unavailability and monitoring gaps. We recommend running this operation during off-peak hours.

Comparison of methods

You can choose the appropriate reclamation method based on your actual business scenario.

Method	Concurrent reads/writes	Speed	Rate limiting	Use case
`OPTIMIZE TABLE` command	Yes	Fast	No	When the business workload is light and execution efficiency is a priority, the `OPTIMIZE TABLE` command can quickly reclaim table space and reduce storage overhead.
DMS table space reclamation	Yes	Slow	Yes	When the cluster workload is sensitive and execution efficiency is not a priority, the DMS tool can reclaim table space with minimal impact on business operations.

Procedure

Reclaim table space using OPTIMIZE TABLE

You can use the following command to reclaim table space.

OPTIMIZE TABLE [Database1].[Table1],[Database2].[Table2]

Note

[Database1] and [Database2] are database names, and [Table1] and [Table2] are table names.
When running the OPTIMIZE TABLE command in the InnoDB engine, the message Table does not support optimize, doing recreate + analyze instead is returned. This is a normal response. You can ignore this message and confirm that ok is returned. For more information about the OPTIMIZE TABLE statement, see OPTIMIZE TABLE Statement.

DMS table space reclamation

Log on to the database: Go to the PolarDB console, click the target cluster Basic Information page, and click the Log on to Database button in the upper-right corner to log on to the PolarDB for MySQL cluster through DMS.
Reclaim table space: In the left-side navigation pane, select the target cluster ID, double-click the target database, right-click any table name, and then select Batch Operations On Tables. On the Batch Operations On Tables page, select the tables for which you want to release space, and then click Table Maintenance>Optimize Table

Clean up log files

PolarDB for MySQL clusters may rapidly generate Binlog, Redo logs, or Undo logs due to large transaction processing, which can result in excessive storage space consumption or full storage.In this case, we recommend that you first expand the storage capacity and then investigate the cause of rapid log file generation.

Note

When cleaning up Binlog, Undo logs, or Redo logs, the console may take some time to reflect the updates. Please wait patiently.

Binlog

PolarDB for MySQL clusters have Binlog disabled by default and use the more advanced physical log (Redo Log) as a replacement. If Binlog is not enabled for your cluster, refer to the Redo log and Undo log cleanup sections.

Retention policy

Binlog files have the following retention policies:

When Binlog is enabled, files are retained for 3 days by default. Files older than 3 days are automatically deleted.
Note
- For PolarDB for MySQL clusters purchased before November 23, 2023, the default Binlog retention period is two weeks (14 days).
- For PolarDB for MySQL clusters purchased before January 17, 2024, the default Binlog retention period is one week (7 days).
After Binlog is disabled, existing Binlog files are retained indefinitely and are not automatically deleted.
Note
To delete Binlog files, you need to set the Binlog retention period parameter (loose_expire_logs_hours or binlog_expire_logs_seconds) to a small value while Binlog is enabled. After the files expire and are automatically deleted, you can then disable Binlog.

Modify the retention period

Important

Modifying the Binlog retention period does not cause connection flashes or require a cluster restart.
If modifying the retention period results in a large number of Binlog files to be purged (for example, 10 TB), short-term database write exceptions may occur during the purge process. Therefore, when Binlog files are large, we recommend performing this operation during off-peak hours and shortening the Binlog retention period in multiple steps, purging a portion of Binlog data each time.
Purged Binlog files cannot be recovered after deletion.

You can modify the Binlog retention period as follows:

MySQL 5.6: You can modify the loose_expire_logs_hours parameter (value range: 0 to 2376, in hours, default value: 72) to set the Binlog retention period. A value of 0 means Binlog files are not automatically deleted.
MySQL 5.7MySQL 8.0: You can modify the binlog_expire_logs_seconds parameter (value range: 0 to 4294967295, in seconds, default value: 259200) to set the Binlog retention period. A value of 0 means Binlog files are not automatically deleted.

Clean up historical files

After modifying the Binlog retention period parameter (loose_expire_logs_hours or binlog_expire_logs_seconds), historical Binlog files in the cluster are not immediately purged. To purge historical files, you can use one of the following three methods:

Wait for automatic purge: When the last Binlog file in the cluster reaches the maximum storage size (parameter max_binlog_size), the system switches to a new Binlog file, and these historical Binlog files are automatically purged.
Manual purge: Use a privileged account to run the flush binary logs command to immediately trigger a Binlog file switch and purge expired Binlog files.
Restart the cluster: After the cluster restarts, the system automatically purges historical Binlog files.

Undo logs

PolarDB for MySQL Undo logs serve as historical versions in multiversion concurrency control (MVCC). When uncommitted transactions (on either read-only nodes or read/write nodes) hold old read views, the undo log cleanup process is blocked, resulting in continuous space accumulation.

Identify and terminateuncommitted transactions

Log on to the database: Go to the PolarDB console, click the target cluster Basic Information page, and click the Log on to Database button in the upper-right corner to log on to the PolarDB for MySQL cluster through DMS.
Find uncommitted transactions: Run the following command to check for long-running uncommitted transactions.
```
SELECT * FROM INFORMATION_SCHEMA.innodb_trx;
```
Focus on transactions where trx_started (transaction start time) is significantly old, or where trx_state (transaction state) has been in the RUNNING state for an extended period. Record the trx_mysql_thread_id (thread ID) value.
Terminate the transaction: After confirming that it does not affect your business, run the KILL command to terminate the target transaction.
```
kill [Thread ID];
```

Monitor cleanup progress

After cleaning up the threads corresponding to the transactions, you need to check the current Undo history progress. If the Undo history length continues to grow rapidly, you need to tune the background cleanup (purge) performance.

Note

When write pressure is high, the PolarDB strategy prioritizes current write performance. This may cause undo log cleanup to lag behind.

Monitor cleanup progress: Run the following command to observe the Undo history length.
```
SELECT COUNT FROM INFORMATION_SCHEMA.innodb_metrics WHERE name = 'trx_rseg_history_len';
```
If the value exceeds 1 million or continues to rise over several minutes while the current pressure is indeed high, the cleanup speed cannot keep up with the write speed.
Adjust cleanup parameters: Improve cleanup efficiency.
1. Increase the innodb_purge_batch_size parameter to make each cleanup batch larger.
2. Increase the innodb_purge_threads parameter to add more cleanup threads. We recommend setting this to the number of CPU cores in the cluster specification.
  Note
  This operation restarts the cluster. We recommend performing this during off-peak hours.

Reclaim occupied space

After the Undo history length decreases and stabilizes, if you want to clean up the space occupied by undo logs, you can enable the Undo truncate feature.

Enable the feature: Set the innodb_undo_log_truncate parameter to ON.
Trigger mechanism: Truncation is triggered when a single undo file exceeds innodb_max_undo_log_size, the Undo truncate is triggered.
Version limitations: Some earlier versions have known bugs related to Undo truncate. If modifying this parameter is not permitted on your cluster, you need to upgrade to the latest minor version.
Disable promptly: This feature adds overhead during cluster switchovers and restarts. We recommend setting this parameter back to OFF immediately after space reclamation is complete, especially before maintenance operations such as a upgrade to the latest minor version. Enable this feature only when needed.

Redo logs

PolarDB for MySQL clusters use Redo logs instead of Binlog for data synchronization between the primary node and read-only nodes.

Without log backups, local Redo logs occupy 2 GB to 11 GB of storage space, with a maximum of 11 GB. This includes 8 Redo logs in the buffer pool (8 GB), the Redo log being written (1 GB), pre-created Redo logs (1 GB), and the last Redo log (1 GB).
With log backups, local Redo logs are retained for approximately one hour after backup completion. If the write speed is high (for example, exceeding 35 MB/s), temporary Redo log accumulation may occur.

Cleanup rules

Redo logs do not support manual cleanup. They are automatically cleaned up after log backup completion and require no manual intervention.

Note

You can adjust the PolarDB console cluster's log backup policy (default: 7 days) in the PolarDB console.

Clean up temporary files

PolarDB for MySQL clusters may generate a large number of temporary files due to complex queries or large transactions, resulting in excessive storage consumption or full storage. In this case, error messages may appear, such as: error: 1114 The table '/home/mysql/log/tmp/#sqlxxx_xxx_xxx' is full

You can handle this using either of the following methods:

Terminate sessions

Terminate sessions with a large amount of Copy to tmp table or Sending data messages.

Log on to the database: Go to the PolarDB console, click the target cluster Basic Information page, and click the Log on to Database button in the upper-right corner to log on to the PolarDB for MySQL cluster through DMS.
View session status: Run the following command to view session status.
```
SHOW PROCESSLIST;
```
Find the target session: In DMS, click the State column in the query results to sort the data, and check whether there are a large number of Copy to tmp table or Sending data messages. Record the session ID.
Terminate sessions: Run the following command to terminate the target session.
```
kill [Session ID];
```
Important
Before terminating a session, make sure it does not affect running business operations.

If the preceding steps do not release enough storage space, you can restart each node in the cluster to release the storage space occupied by temporary files.

Adjust parameters

Go to the PolarDB console, go to the Settings and Management > Parameters page, and modify the tmp_table_size and max_heap_table_size parameters to increase the temporary table space size.

FAQ

Why doesn't storage space decrease after I run theDELETEcommand?

DELETE only marks record positions or data pages as reusable internally and does not reduce the physical file size. This results in a large amount of fragmented space. You need to run the OPTIMIZE TABLE command on the table during off-peak hours to fully reclaim this fragmented space.