Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

slow purge_table statement

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

Comments

  • Have you deleted more data recently?
  • yes, we did have another ETL process which deletes data much more often
  • 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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.