Performance Deterioration Due to Data Bloat

Symptom

Data bloat causes disk space to be insufficient, thus deteriorating performance.

Possible Causes

You can run the VACUUM/VACUUM FULL command on the management plane to periodically reclaim space.

• Frequent table creation and deletion can lead to table bloating. To free up space, you can run the VACUUM command on system catalogs.
• Frequently update and delete operations can lead to table bloating. To free up space, you can run the VACUUM or VACUUM FULL command on system catalogs.
  

  Only 8.1.3 and later cluster versions support this function.

Handling Procedure

1. Log in to the GaussDB(DWS) management console.
2. Click the name of the target cluster.
3. In the navigation pane, choose Intelligent O&M.
4. Click the O&M Plan tab. Click Add O&M Task.

   

5. The Add O&M Task page is displayed.
   • Select Vacuum for O&M Task.
   • Set Scheduling Mode to Auto. GaussDB(DWS) automatically scans tables that require VACUUM operation.
   • Select System catalogs or User tables for Autovacuum.
     • If there are a large number of UPDATE and DELETE operations, select the User tables.
     • If there are a large number of CREATE and DELETE operations, select System catalogs.

       

6. Click Next: Configure Schedule to configure the schedule and Vacuum type. You are advised to select Periodic for Task Type. The GaussDB(DWS) automatically executes VACUUM in your selected time windows.

   

   

   For automatic Vacuum O&M tasks, the system uses the VACUUM FULL operation to process user tables. VACUUM FULL holds a level 8 lock, which blocks other transactions. Other transactions will be in lock waiting during VACUUM FULL execution. After 20 minutes, a timeout error is reported. Therefore, do not perform other transactions in the configured time window.

7. After confirming that the information is correct, click Next to complete the configuration.