Data Collector Policy not working

ersaurabhex

Hi,
I have set up data collector policy 5 days ago using below command.
SELECT set_data_collector_time_policy('v_monitor.query_requests', '5 days'::interval);
It's also reflecting correctly in the database using below command.
select GET_DATA_COLLECTOR_POLICY ('RequestsIssued');
2000KB kept in memory, 5000000KB kept on disk. 5 days kept on disk.
However, I am still not able to see all historical queries of last 5 days. It just showing me last two days queries only.
select distinct substr(query_start, 1, 10 ) from query_profiles;
--2019-11-08
select distinct trunc(start_timestamp) from query_requests;
--2019-11-08 00:00:00
Please help me fixing this issue. Thanks, Saurabh

Jim_Knicely

Fyi... Per the documentation of the SET_DATA_COLLECTOR_TIME_POLICY( ['component',] 'interval' ) function...

If you specify interval, Vertica enforces the setting that is exceeded first (size or time).

See: https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/DataCollection/SET_DATA_COLLECTOR_TIME_POLICY.htm

chaima

Hi,
You should change the size retention policy of all the data_collector tables used in those views . If the disk size is not enough, the time constraint won't be enforced,
For example, for query_requests: RequestsIssued,RequestsIssued, RequestsIssued, ResourceAcquisitions.
Can you check also how many days you have in dc_requests_issued?
SELECT
node_name,
MIN(TIME),
MAX(TIME)
FROM
dc_requests_issued
GROUP BY
node_name;

Ps: increasing the retention time /size will affect performance on those system tables

Chaima