Increase query_requests storing deepth

jazzmanov

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!

Rahul_Choudhary

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