Vertica query performance

naresh_way2

Hi,
I am using vertica for 3 months. At initial, I got the analytics very fast, but as days increases I am getting slower performance in vertica. I found that deleted row count shows the impact on my vertica analytics. So I decided to purge my deleted rows. I can do manually but it not feasible to me. So I want to set a purge policy which should purge my deleted rows when it reached 10% of my table total row count. How do I set configuration parameters? Can anybody explain?
Thanks in advance,
Naresh

Jim_Knicely

Hi,

Take a look at the PurgeMergeoutPercent configuration parameter...

dbadmin=> SELECT parameter_name, current_value, default_value, description FROM configuration_parameters WHERE parameter_name = 'PurgeMergeoutPercent';
    parameter_name    | current_value | default_value |                                       description
----------------------+---------------+---------------+------------------------------------------------------------------------------------------
 PurgeMergeoutPercent | 20            | 20            | Maximum % of rows that may be deleted before Tuple Mover purges the ROS through mergeout
(1 row)

dbadmin=> ALTER DATABASE test SET PurgeMergeoutPercent = 10;
ALTER DATABASE

See:
https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/AdministratorsGuide/ConfiguringTheDB/TupleMoverParameters.htm