How Can I Configure the Size of Rollback Segments

Generally, use the default size of rollback segments. To achieve optimal performance, you can adjust the parameters related to the rollback segment size in some scenarios. The scenarios and corresponding configurations are as follows:

1. Historical data within a specified period needs to be retained.

   To use flashback or locate faults, you can change the value of undo_retention_time (this GUC parameter can be modified in the gaussdb.conf file) to retain more historical data. The default value of undo_retention_time is 0. The value ranges from 0 to 3 days. The valid unit is second, minute, hour, or day.

   You are advised to set it to 900s. Note that a larger value of undo_retention_time indicates more undo space usage and data space bloat, which further affects the data scanning and update performance. When flashback is not used, you are advised to set undo_retention_time to a smaller value to reduce the disk space occupied by historical data and achieve optimal performance. You can use the following method to determine the new value that is more suitable for your service model:

   Use the system function gs_stat_undo for undo statistics. If the input parameter is false, check the recommended undo_retention_time parameter in the info column of the output. For details about the parameter values, see "SQL Reference > Functions and Operators > Undo System Functions" in Developer Guide.

2. Historical data within a specified size needs to be retained.

   If long transactions or large transactions exist in your service, undo space may bloat. In this case, you need to increase the value of undo_space_limit_size. The default value of undo_space_limit_size is 256GB, and the value ranges from 800 MB to 16 TB.

   If the disk space is sufficient, you are advised to double the value of undo_space_limit_size. In addition, a larger value of undo_space_limit_size indicates more disk space occupation and deteriorated performance. If no undo space bloat is found by querying curr_used_undo_size of the gs_stat_undo() view, you can restore the value to the original value.

   After adjusting the value of undo_space_limit_size, you can increase the value of undo_limit_size_per_transaction, which ranges from 2 MB to 16 TB. The default value is 32GB. It is recommended that the value of undo_limit_size_per_transaction be less than or equal to that of undo_space_limit_size, that is, the threshold of the undo space allocated to a single transaction be less than or equal to the threshold of the total undo space.

3. The parameter adjustment priority is retained for historical data.

   If any of undo_retention_time, undo_space_limit_size, and undo_limit_size_per_transaction is reached, the corresponding restriction is triggered.

   

   For example, the undo_space_limit_size parameter is set to 1 GB, and the undo_retention_time parameter is set to 900s. If the historical version data generated within 900s is less than 1 GB multiplied by 0.8, data recycling is performed every 900s. Otherwise, data recycling is performed when the volume of generated data reaches 1 GB multiplied by 0.8. In this case, if the disk space is sufficient, you can increase the value of undo_space_limit_size. If not, decrease the value of undo_retention_time.