
But if you are looking to keep records for some period of time, for example the last 6 days, it would be very difficult to calculate how much memory or disk space is needed.
Good news is you don’t have to worry about that! Simply set a “time capacity” for individual Data Collector tables on all nodes using the SET_DATA_COLLECTOR_TIME_POLICY function!
Example:
dbadmin->mydb@sandbox1=> SELECT node_name, component, description, interval_set, interval_time
dbadmin->mydb@sandbox1--> FROM data_collector
dbadmin->mydb@sandbox1--> WHERE table_name = 'dc_requests_issued'
dbadmin->mydb@sandbox1--> ORDER BY 1;
node_name | component | description | interval_set | interval_time
----------------+----------------+------------------------------------+----------
v_mydb_node0001 | RequestsIssued | History of all SQL requests issued | f | 0
v_mydb_node0002 | RequestsIssued | History of all SQL requests issued | f | 0
v_mydb_node0003 | RequestsIssued | History of all SQL requests issued | f | 0
(3 rows)
dbadmin->mydb@sandbox1=>* SELECT set_data_collector_time_policy('RequestsIssued', '6 days'::interval);
set_data_collector_time_policy
--------------------------------
SET
(1 row)
dbadmin->mydb@sandbox1=> SELECT node_name, component, description, interval_set, interval_time
dbadmin->mydb@sandbox1--> FROM data_collector
dbadmin->mydb@sandbox1--> WHERE table_name = 'dc_requests_issued'
dbadmin->mydb@sandbox1--> ORDER BY 1;
node_name | component | description | interval_set | interval_time
----------------+----------------+------------------------------------+----------
v_mydb_node0001 | RequestsIssued | History of all SQL requests issued | t | 6
v_mydb_node0002 | RequestsIssued | History of all SQL requests issued | t | 6
v_mydb_node0003 | RequestsIssued | History of all SQL requests issued | t | 6
(3 rows)
Helpful Link:https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/DataCollection/SET_DATA_COLLECTOR_TIME_POLICY.htm
Have fun!