query_consumption and query_requests retention for different resource pools

dimitri_p

If we run the following query on our Vertica v9.2:

SELECT qc.user_name,qc.resource_pool,count(*) AS rec_count,max(start_timestamp) AS latest_rec,min(start_timestamp) AS earliest_rec FROM
     query_consumption qc
 JOIN
     query_requests qr
 ON
     qc.transaction_id = qr.transaction_id
     AND qc.statement_id = qr.statement_id
  GROUP BY 1,2

sysquery and general pools go more than 24 hours back whereas the other pools - from 1 to 6 hours - and have much fewer records each. How is it configured? How can I make sure that the other pools keep at least 24 hours worth of history?

Vertica_Curtis

https://docs.vertica.com/25.1.x/en/sql-reference/functions/management-functions/data-collector-functions/set-data-collector-policy/

query_requests is a view. You can query vs_system_views to see which DC tables are used to create that view. Then you can use set_data_collector_policy to apply a time-based retention policy on those components.

Also, if you're on 9.2, you should seriously consider upgrading. 9.2 is like 6-7 years old.

dimitri_p

thanks Curtis, my problem is that right now on that system both general and sysquery resource pools show tens of thousands of records and 24+ hours worth of history. Whereas the rest of the pools only show less than 100 records combined. Which means if I were to just increase retention for all those underlying DC tables - it would probably be all consumed by sysquery and general pools again and the other pools would get almost nothing (cannibalized by sysquery and general), like it is today. And I am interested in those other pools.