Question
Increasing the retention period of pr_perf_stats table
Hi,
we have a requirement from the Client IT Ops team to increase the retention period of the pr_perf_stats table from the current default value of 30 days to 180 days. As far as I know this table is much like the History table for the application work entities, and it grows exponentially in case of single/multi node systems. Is there any possible impact of such a change on the eventual performance of the Database and/or the application like transaction logs getting full or system performance getting degraded?
Pegasystems
IN
pr_perf_stats table contains PAL data for every requestor on every node connected to that rulebase. More details can be found here - https://pdn.pega.com/sites/pdn.pega.com/files/help_v719/procomhelpmain.htm#howto/howto2/logusagetable.htm along with how to change retention days.
There is a lot of data in this table and this could impact performance if you use the reports that go against this table or your nodes are being monitored by AES (https://pdn.pega.com/products/autonomic-event-services-enterprise-edition) or PDC (https://pdn.pega.com/products/pega-predictive-diagnostic-cloud).
Cognizant Technology Solutions
IN
HI Rajiv
Thanks for the quick response. Unfortunately we don't have AES installed in our current production set up so the impact with AES is discarded for now. As for the reports run against this table, the main motive is to monitor this table and generate reports against it for some performance metrics. But I think 180 days in place of the default 30 days as retention parameter for our system where we have 4 nodes active is a bit too much in terms of occupying DB space for this table.
Is there any recommended option for such changes in production based on the available hardware/memory and other parameters?
Pegasystems
IN
Hi Anoojit,
There are no specific recommendations available based on the environment (hardware / memory or other parameters). In general a large table which had inserts and deletes causes fragmentation and poor performance. De-fragmentation on a regular basis should help there. Depending on how your reports are configured, partitioning might be a good idea.
General performance recommendations including pr_perf_stats are mentioned here - https://pdn.pega.com/performance/performance-guidance-for-production-applications-database
-Rajiv
Cognizant Technology Solutions
IN
Hi Rajiv,
Thanks for the inputs and suggestions.