Skip to main content
Question

query_consumption and query_requests retention for different resource pools

  • February 25, 2025
  • 2 replies
  • 7 views
dimitri_p
Forum|alt.badge.img+2

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?

    2 replies

    Vertica_Curtis
    Forum|alt.badge.img+1
    • Vertica_Curtis
    • Participating Frequently
    • February 25, 2025

    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
    Forum|alt.badge.img+2
    • dimitri_p
    • Author
    • Participating Frequently
    • February 25, 2025

    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.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement