VACUUM FULL Is Slow

Updated on 2025-01-06 GMT+08:00

View PDF

Common scenarios and troubleshooting methods for slow execution of VACUUM FULL are as follows:

Scenario 1: VACUUM FULL Is Executed Slowly Due to Lock Wait

If the cluster version is 8.1.x or later, perform the following steps:

Query the pgxc_lock_conflicts view to check lock conflicts.
1

SELECT * FROM pgxc_lock_conflicts;
- As shown in the following figure, if the value of granted is f, the VACUUM FULL statement is waiting for another lock. If granted is t, the INSERT statement holds the lock. nodename indicates the CN or DN where the lock is generated, for example, cn_5001. Execute 2.
- If 0 rows is displayed in the command output, no lock conflict occurs. In this case, check other scenarios.
Based on the statement content, determine whether to run VACUUM FULL immediately after terminating the lock-holding statement or run VACUUM FULL during off-peak hours.

To terminate a lock-holding statement, run the following statement: In the preceding command, pid is obtained from step 1, and cn_5001 is the node name queried out.
1

execute direct on (cn_5001) 'SELECT PG_TERMINATE_BACKEND(pid)';
After the statement is terminated, run VACUUM FULL again.
1

VACUUM FULL table_name;

For 8.0.x and earlier versions:

Run the following statement in the database to obtain the value of query_id corresponding to the VACUUM FULL operation:

      
         SELECT * FROM pgxc_stat_activity WHERE query LIKE '%vacuum%'AND waiting = 't';

Click to enlarge

Run the following statement to check whether lock wait exists (Use the obtained query_id):
1

SELECT * FROM pgxc_thread_wait_status WHERE query_id = {query_id};
- If acquire lock is displayed in wait_status in the command output, lock wait exists. Check the value of node_name and record it, for example, cn_5001 or dn_600x_600y, then go to 3.
- If in the query result, wait_status does not contain acquire lock, there is no lock wait. Check other scenarios.

Run the following statement to check the lock waited by the VACUUM FULL operation in pg_locks: The following uses cn_5001 as an example. If the lock wait is on a DN, change it to the corresponding DN name. pid is obtained in 2.

Record the value of relation in the command output.

      
         execute direct on (cn_5001) 'SELECT * FROM pg_locks WHERE pid = {tid} AND granted = ''f''';

Click to enlarge

View in pg_locks the PID of the transaction holding the lock based on the value of relation . The value of relation is obtained in 3.

      
         execute direct on (cn_5001) 'SELECT * FROM pg_locks WHERE relation = {relation} AND granted = ''t''';

Click to enlarge

Run the following statement to query the corresponding SQL statement based on the PID: The value of pid is obtained in 4.

      
         execute direct on (cn_5001) 'SELECT query FROM pg_stat_activity WHERE pid ={pid}';

Click to enlarge

Based on the statement content, determine whether to run VACUUM FULL immediately after terminating the lock-holding statement or run VACUUM FULL during off-peak hours.

To terminate a lock-holding statement, run the following statement: In the preceding command, pid is obtained from 4, and cn_5001 is the node name queried in 4.
1

execute direct on (cn_5001) 'SELECT PG_TERMINATE_BACKEND(pid)';
After the statement is terminated, run the VACUUM FULL statement.
1

VACUUM FULL table_name;

Scenario 2: Transactions Cannot Be Committed Due to I/O or Network Problems

Solution: Run the CRETAE TABLE statement. If the execution is still slow, there might be issues with the I/O or network. In such cases, check the I/O and network conditions.

Scenario 3: VACUUM FULL Is Executed Slowly Due to Large System Catalogs

Once the I/O or network problems are resolved, perform a VACUUM FULL on empty tables. If the VACUUM FULL operation is still slow, even on empty tables, it indicates that the system catalogs are too large. Executing VACUUM FULL on any table requires scanning the system catalogs pg_class, pg_partition, and pg_proc. If the three system catalogs are too large, VACUUM FULL will be executed slowly.

Solution: GaussDB(DWS) supports executing VACUUM FULL on system catalogs. However, the execution holds an eight-level lock, and services related to system catalogs will be blocked. Clear system catalogs during off-peak hours or when services are stopped and no DDL operation is performed.

Scenario 4: Slow VACUUM FULL on a Column-store Table Using Partial Clustering (PCK)

When VACUUM FULL is performed on a column-store table, if PCK exists, all records in PARTIAL_CLUSTER_ROWS are loaded to the memory and then sorted. If the table is large or psort_work_mem is set to a small value, data will spill to disks during PCK sorting (the database stores temporary results to disks). This increases the time consumption.

Solution: Adjust the values of PARTIAL_CLUSTER_ROWS and psort_work_mem based on the tuple length of the data in the table.

Run the following statement to view the table definition. If PARTIAL CLUSTER KEY is displayed in the command output, the table contains PCKs.
1

SELECT * FROM pg_get_tabledef('table name');
Log in to the GaussDB(DWS) management console and choose Clusters on the left.
Click the cluster name to go to the cluster details page.
Click Parameters on the left, enter psort_work_mem in the search box, increase the values on both CNs and DNs, and click Save.
Run VACUUM FULL again.