We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Increase query_requests storing deepth — Vertica Forum

Increase query_requests storing deepth

Hello.

 

Now only queries posted last 6 hours are stored in query_requests, i want increase depth up to 1 week.

How it can be done? Is there any data collector analog?

 

Thanx!

Comments

  • Hi

     

    There is functionality to adjust dc table retention via parameter settings. For example query_requests table uses dc_requests_issued and dc_requests_completed so you can change the retention policy to increase the number of records retained by size. For Vertica 7.0 we retain by file size only; Vertica 7.1 adds ability to specify by time. Please note these tables are in the catalog directory so increasing the size will take more disk space and in a constrained file system could potentially create issues. Please ensure free space before increasing the retention. Systematically increasing the size until a retention time is reached would be a good way to address this request.

     

    We recommend reviewing all information in our documentation on the subject and then running select data_collector_help(); from a vsql prompt for additional information.

     

    https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/AdministratorsGuide/Monitoring/Vertica/ConfiguringDataRetentionPolicies.htm%3FTocPath%3DAdministrator's%2520Guide%7CMonitoring%2520Vertica%7CRetaining%2520Monitoring%2520Information%7C_____3

     

    https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/DataCollection/SET_DATA_COLLECTOR_TIME_POLICY.htm?Highlight=SET_DATA_COLLECTOR_POLICY

    Data Collector settings to capture specific information can be tailored to customers needs. Information and examples to get customers started on using the data collector tables follows.


    dbadmin=> SELECT DISTINCT table_name, component, description FROM data_collector where table_name ilike '%request%';
    table_name | component | description
    -----------------------+-------------------+------------------------------------------------------
    dc_lock_requests | LockRequests | History of lock requests
    dc_requests_retried | RequestsRetried | History of all SQL requests issued that were retried
    dc_requests_issued | RequestsIssued | History of all SQL requests issued
    dc_requests_completed | RequestsCompleted | History of all SQL requests completed
    (4 rows)

    dbadmin=> SELECT get_data_collector_policy('RequestsCompleted');
    get_data_collector_policy
    -----------------------------------------------------------------------------
    2000KB kept in memory, 50000KB kept on disk. Time based retention disabled.
    (1 row)


    dbadmin=> SELECT get_data_collector_policy('RequestsIssued');
    get_data_collector_policy
    -----------------------------------------------------------------------------
    2000KB kept in memory, 50000KB kept on disk. Time based retention disabled.
    (1 row)

     

     

    Hope this helps!!

     

    Regards

    Rahul

     

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file