slow purge_table statement

Comments

• Options

  Prasanta_Pal - Select Field - Employee

  November 2014

  Have you deleted more data recently?
  

  0
• Options

  John_Y

  November 2014

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

  0
• Options

  Rahul_Choudhary

  November 2014

  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

  0