Keep Data Collector Information for a Set Interval: Quick Tip

The Data Collector retains history of important system activities and records essential performance and resource utilization counters. You probably know that you can set a size restraint (memory and disk space in kilobytes) for the specified Data Collector table on all nodes.

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!