Skip to main content

This content is closed to future replies and is no longer being maintained or updated.
Links may no longer function. If you have a similar request, please write a new post.

Question

 Kiran Kumar Sangaraju (KiranKS)
Ministry of Education Saudi Arabia
Kiran Kumar Sangaraju
Ministry of Education Saudi Arabia
SA
KiranKS Member since 2018 22 posts
Ministry of Education Saudi Arabia
Posted: Jan 21, 2020
Last activity: Jan 30, 2020
Posted: 21 Jan 2020 5:33 EST
Last activity: 30 Jan 2020 2:56 EST
Closed
Solved

DB Size is increasing rapidly

DB size is 477 GB now, increased 250GB in last 20 days, how to minimize data in the tables or is there a archival process and settings which will help us to clear data from some of the tables.

Pega: 7.3.1

Apache tomcat 8.5.5

DB: SQL Server

Any of the DSS / settings will help us to minimize the data storage?

Show More

DB size is 477 GB now, increased 250GB in last 20 days, how to minimize data in the tables or is there a archival process and settings which will help us to clear data from some of the tables.

Pega: 7.3.1

Apache tomcat 8.5.5

DB: SQL Server

Any of the DSS / settings will help us to minimize the data storage?

Pega-ProcessEngine pegarobotics/HeartbeatInterval 30
Pega-RulesEngine fua/minimumAgeToPurgeFromDatabase 30
Pega-RulesEngine fua/purgeAgeInDaysForRulesInLogUsage 30
Pega-RulesEngine fua/maximumApplicationsToPurge 1000
Pega-RulesEngine reporting/reportstatistics/minimumDaysToPurgeFromDatabase 30
Pega-RulesEngine database/databases/PegaRULES/TableStatisticsAgentInterval -1
Pega-RulesEngine database/databases/PegaDATA/TableStatisticsAgentInterval -1
Pega-RulesEngine database/databases/PegaRULES/BlobSizeAgentInterval -1
Pega-RulesEngine database/databases/PegaDATA/BlobSizeAgentInterval -1
Pega-UIEngine pyMaxrecentrecords 30
Pega-Engine prconfig/declaratives/declarepages/instancecountlimit/default 3000
Pega-Engine prconfig/fua/global/instancecountlimit 20000
Pega-Engine prconfig/fua/personal/instancecountlimit 20000
Pega-Engine prconfig/collections/mru/propertyreference/instancecountlimit 500000
Pega-Engine prconfig/declaratives/fragments/instancecountlimit/default 5000
Pega-Engine prconfig/datapages/mrucapacity/default 1000
Pega-Reporting reporting/cache/calculationstimeoutdays 7
Pega-Reporting reporting/cache/allmatchestimeoutdays 7
Pega-RulesEngine reporting/reportstatistics/cachesize 100
Pega-RULES indexing/selectedDayOfWeek 1
Pega-RULES indexing/frequency Hourly
Pega-RULES syslocks/defragIntervalInHours 24
Pega-RULES syslocks/defragHourOfDay 1
Pega-RULES syslocks/defragEnabled true
Pega-RULES syslocks/defragIntervalType DAILY
Pega-ProcessArchitect DecisionTableSizeThreshold 500
Pega-ProcessArchitect DecisionTableSizeThreshold2 50000
Pega-RulesEngine EventsRetentionPeriod 90

Please include if i miss any DSS / settings which will help us to increase application performance and also minimize data in DB tables.

We are trying to implement archival process to archive old resolved cases.

Attached scree shot with Top tables consuming more data.


Show Less
To see attachments, please log in.
System Administration
Data Integration

Accepted Solution

Posted: 5 years ago
Updated: 4 years ago
Posted: 22 Jan 2020 15:47 EST
Updated: 4 Feb 2020 6:56 EST
Brad Tainter (Br@dTainter_GCS)
PEGA
Fellow, Technical Support, Platform Service
Pegasystems Inc.
US

Hi,

I would suggest reviewing these articles as they relate to some of the large tables you have referenced:


https://community.pega.com/support/support-articles/system-cleaner-not-cleaning-perf-stats-table
https://collaborate.pega.com/question/when-will-pega-write-prlogreportstats
https://community.pega.com/support/support-articles/how-alter-retention-period-prperfstats-table-cleanup

To see attachments, please log in.
Posted: 5 years ago
Posted: 22 Jan 2020 2:44 EST
Kiran Kumar Sangaraju (KiranKS)
Ministry of Education Saudi Arabia
Kiran Kumar Sangaraju
Ministry of Education Saudi Arabia
SA

Any suggestion or recommendations to reduce the DB size?

To see attachments, please log in.

Accepted Solution

Posted: 5 years ago
Updated: 4 years ago
Posted: 22 Jan 2020 15:47 EST
Updated: 4 Feb 2020 6:56 EST
Brad Tainter (Br@dTainter_GCS)
PEGA
Fellow, Technical Support, Platform Service
Pegasystems Inc.
US

Hi,

I would suggest reviewing these articles as they relate to some of the large tables you have referenced:


https://community.pega.com/support/support-articles/system-cleaner-not-cleaning-perf-stats-table
https://collaborate.pega.com/question/when-will-pega-write-prlogreportstats
https://community.pega.com/support/support-articles/how-alter-retention-period-prperfstats-table-cleanup

To see attachments, please log in.
Posted: 5 years ago
Posted: 23 Jan 2020 4:41 EST
Kiran Kumar Sangaraju (KiranKS)
Ministry of Education Saudi Arabia
Kiran Kumar Sangaraju
Ministry of Education Saudi Arabia
SA

That was really helpful, i will check the settings and try to modify as required.

I didn't find much information about pr4_log_rule_usage_details, can you please share any details about this table? After Work table this is the biggest table consuming more space. Is there a way to minimize data in this table and what will be the impact if we do it?

To see attachments, please log in.
Posted: 5 years ago
Posted: 23 Jan 2020 9:15 EST
Bhavya Tyagi (Bhavya)
Coforge DPA
Senior Technical Lead
Coforge DPA
GB

Hi

For the pr4_log_rule_usage_details table refer: https://community.pega.com/sites/default/files/help_v83/procomhelpmain.htm#/pxadminstudio/howto/howto2/rule-usage-report-tsk.htm

 

For the work table,the best option would be to archive the old cases if possible. You can make use of Pega OOTB Purge and Archive wizard or any other archiving methods.

 

Regards

Bhavya

To see attachments, please log in.
Posted: 5 years ago
Posted: 23 Jan 2020 10:02 EST
Rachit Agarwal (Rachit_Agarwal)
PEGA
Principal Technical Support Engineer
Pegasystems Inc.
US

I could see that the application-specific tables pc_MoE_Safeer_Work is approximately 168 GB and pc_History_MoE_Safeer_Work is approx 80GB. 

I recommend we should use  - Purge/Archive wizards that enable us to use an agent to automatically delete old work item data from the respective tables or store them in archive files.

Purging and archiving old work items and their related history and attachment records helps maintain system performance by reducing database processing demands.

https://community.pega.com/sites/default/files/help_v73/tools/purgearchive/aboutpurgearchivewizard.htm

Hope it helps.

To see attachments, please log in.
Posted: 4 years ago
Posted: 30 Jan 2020 2:54 EST
Kiran Kumar Sangaraju (KiranKS)
Ministry of Education Saudi Arabia
Kiran Kumar Sangaraju
Ministry of Education Saudi Arabia
SA

Thanks for all your response here is what we changed and stats before and after changes in lower environments.

Prconfig Changes:

<env name="usage/retentionperiod" value="6" /> 

Note: The number of days retained is the number of days plus one. For example, if set to 1, the system keeps two days of data, today and yesterday.

DSS Changes:

Owning Ruleset: Pega-Rules

EnginePurpose: management/systemdetails/maxdays

Value: 7 (New DSS)

Owning Ruleset: Pega-RulesEngine

Purpose: reporting/reportstatistics/minimumDaysToPurgeFromDatabase

Value: 7 (Exisiting Value 30)

Owning Ruleset: Pega-RulesEngine

EnginePurpose: fua/purgeAgeInDaysForRulesInLogUsage

Value: 7 (Exisiting Value 30)

Owning Ruleset: Pega-RulesEngine

EnginePurpose: fua/minimumAgeToPurgeFromDatabase

Value: 7 (Exisiting Value 30)

Owning Ruleset: Pega-UIEngine

EnginePurpose: pyMaxrecentrecords

Value: 5 (Exisiting Value 30)

DB Query:

PegaDATA.pr_data_recents : Run query to delete all the entries whith counter more than 5 for each user.

DB Size details before & after changes.

Show More

Thanks for all your response here is what we changed and stats before and after changes in lower environments.

Prconfig Changes:

<env name="usage/retentionperiod" value="6" /> 

Note: The number of days retained is the number of days plus one. For example, if set to 1, the system keeps two days of data, today and yesterday.

DSS Changes:

Owning Ruleset: Pega-Rules

EnginePurpose: management/systemdetails/maxdays

Value: 7 (New DSS)

Owning Ruleset: Pega-RulesEngine

Purpose: reporting/reportstatistics/minimumDaysToPurgeFromDatabase

Value: 7 (Exisiting Value 30)

Owning Ruleset: Pega-RulesEngine

EnginePurpose: fua/purgeAgeInDaysForRulesInLogUsage

Value: 7 (Exisiting Value 30)

Owning Ruleset: Pega-RulesEngine

EnginePurpose: fua/minimumAgeToPurgeFromDatabase

Value: 7 (Exisiting Value 30)

Owning Ruleset: Pega-UIEngine

EnginePurpose: pyMaxrecentrecords

Value: 5 (Exisiting Value 30)

DB Query:

PegaDATA.pr_data_recents : Run query to delete all the entries whith counter more than 5 for each user.

DB Size details before & after changes.

TableName

SchemaName

RowCounts

TotalSpaceKB

TotalSpaceMB

UsedSpaceKB

UsedSpaceMB

UnusedSpaceKB

UnusedSpaceMB

Before Changes

pr_perf_stats

pegadata

91696

434752

424.56

394120

384.88

40632

39.68

pr4_log_rule_usage_details

pegadata

81132

160512

156.75

150528

147

9984

9.75

pr_log_reportstats

pegadata

102060

154464

150.84

130920

127.85

23544

22.99

pr_data_recents

pegadata

29070

50720

49.53

49376

48.22

1344

1.31

After Changes

pr_perf_stats

pegadata

16044

164096

160.25

94008

91.8

70088

68.45

pr4_log_rule_usage_details

pegadata

46124

113856

111.19

92376

90.21

21480

20.98

pr_log_reportstats

pegadata

36185

106272

103.78

62520

61.05

43752

42.73

pr_data_recents

pegadata

5445

49888

48.72

24680

24.1

25208

24.62

We are hoping that there wont be any performance impact due to these changes.


Show Less
To see attachments, please log in.
Posted: 4 years ago
Posted: 30 Jan 2020 2:56 EST
Kiran Kumar Sangaraju (KiranKS)
Ministry of Education Saudi Arabia
Kiran Kumar Sangaraju
Ministry of Education Saudi Arabia
SA

For Work table we are proceeding with Purge/Archieve process.

To see attachments, please log in.

We'd prefer it if you saw us at our best.

Pega Collaboration Center has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice