How long does data stay in the DC tables?

[usli06]

Hi,

How long does data stay in the data collector tables? It seems like it is less than two days

[id10t]

Hi!

Look for GET_DATA_COLLECTOR_POLICY. \

Code: Select all

=> SELECT DATA_COLLECTOR_HELP();
   Usage Data Collector
   The data collector retains history of important system activities.
   This data can be used as a reference of what actions have been taken 
     by users, but it can also be used to locate performance bottlenecks, 
     or identify potential improvements to the Vertica configuration.
   This data is queryable via Vertica system tables.
   The list of data collector components, and some statistics, can be found using:
   SELECT * FROM v_monitor.data_collector;
   The amount of data retained can be controlled with:
      set_data_collector_policy(<component>,
                            <memory retention (KB)>,
                            <disk retention (KB)>);
   The current retention policy for a component can be queried with:
      get_data_collector_policy(<component>);
   Data on disk is kept in the "DataCollector" directory under the Vertica
   catalog path. This directory also contains instructions on how to load
   the monitoring data into another Vertica database.
   Additional commands can be used to affect the data collection logs.
   The log can be cleared with:
     clear_data_collector([<optional component>]);
   The log can be synchronized with the disk storage using:
     flush_data_collector([<optional component>]);

[scutter]

Note that the data collector retention policies are based on size of the collected data, so the duration of time that the data covers will be variable and dependent on system load. It's unfortunate that the policies aren't based on time. You can bump up the policies, but dc tables can be slow to query.

I prefer to schedule cron jobs to dump data form DC tables into real tables and then control the retention policy based on time. From that I get I nice historical view of 2 weeks or 4 weeks or 6 months. Then I also get faster queries since the data is stored in optimized projections.

--Sharon

[zvika]

I agree with Sharon as you can profile a query and until you check the query profile or execution engine tables it get purged.
This is the first step to create an oracle AWR ...