slow purge_table statement

John_Y

currently we have an ETL process which runs a "purge_table" statement, this statement was running ok in the before (<1 mins), now all of a sudden it is running for more than 30 minutes, what could be the reason for it and how to fix it? thank you

Prasanta_Pal

Have you deleted more data recently?

John_Y

yes, we did have another ETL process which deletes data much more often

Rahul_Choudhary

Hi John

As you said you are performing deletes quiet often so just wanted to inform you that normally the deleted records should be purged as mergeout occurs, but if a mass delete was executed after mergeout had consolidated containers into a large container in an upper tuple mover stratum, the deletes remain un-purged because Mergeout will not consider the large containers. 

The PurgeMergeoutPercent config parameter (shows up in the system table configuration_parameters) was not effective with its default value of 40%.  So a large ROS container gets stuck in the high stratum with little chance of ever getting purged. 

In scenarios like this you can force the purge using the process below. 

=> select make_ahm_now(); 
=> select set_config_parameter('MaxMrgOutROSSizeMB', 1024); 
=> select set_config_parameter('PurgeMergeoutPercent',1); 

Now let the ROSes get purged through Tuple Mover Mergout operations after deletes have been purged, 

=> select set_config_parameter('MaxMrgOutROSSizeMB', -1); 
=> select set_config_parameter('PurgeMergeoutPercent',40); 

If large deletes of old data can occur again following this operation, check periodically for the need to re-run the process. 

Use the following queries to find projections and nodes with the highest count of ROS containers and delete vectors. 

=> select count(*), node_name, projection_name from storage_containers group by 2,3 order by 1 desc limit 20; 
=> select count(*), node_name, projection_name from delete_vectors group by 2,3 order by 1 desc limit 20;


Regards
Rahul