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 to retain more historical data. The default value of undo_retention_time is 0. The value ranges from 0 to 3 days.

   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 of undo_retention_time that is more suitable for your service model:

   new_val = 0.5 x (undo_space_limit_size x 0.8 – curr_used_undo_size)/avg_space_increse_speed, where avg_space_increse_speed is the recent average growth speed of the undo space and curr_used_undo_size is the current undo space and both of them can be queried in the gs_stat_undo view.

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 gs_stat_undo(), 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.

   To accurately set this parameter to achieve optimal performance, you are advised to determine the new value by using the following methods:

   • undo_space_limit_size: new_val = 86400 x 30 x avg_space_increse_speed + curr_used_undo_size, where avg_space_increse_speed and curr_used_undo_size can be queried in the gs_stat_undo view.
   • undo_limit_size_per_transaction: new_val = 10 x max_xact_space, where max_xact_space indicates the maximum undo space occupied by a single transaction and can be queried in the gs_stat_undo() view in the 503.2 version.

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, assume that undo_space_limit_size is set to 1GB, and undo_retention_time is set to 900s. If the size of historical data generated within 900s is less than 1 GB x 0.8, the system recycles the data generated within 900s. If the data exceeds 1 GB x 0.8 generated within 900s, only 1 GB x 0.8 data will be recycled. 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.