Support Article
Index table pr_index_reference keep growing
SA-30994
Summary
User noticed that the server has performance issues and upon restarting noticed pr_index_reference file keep growing for 2,700,000+ records in server.
Error Messages
Not Applicable
Steps to Reproduce
Not Applicable
Root Cause
A defect in Pegasystems’ code or rules.
There is a bug in PRPC where the Out-of-the-box (OOTB) developer tools (as refactor rulesets) is used to delete a ruleset do not clean up the pr_index_reference table.
Resolution
Follow the instructions below to clean up the system of old rules and shrink the pr_index_reference table.
Approach # 1:
1. Delete all application rulesets that are no longer needed.
2. Use the dev tools to remove the ruleset(s). See #2 for an alternate approach.
Approach # 2:
Step 1:Use the cleanup.bat/cleanup.sh script available in the ../scripts dir of each build to generate SQL that deletes all of the old PRPC rules.
All rules prior to the current major release will be deleted. The generated script can be cut and pasted into SQL dev. Change the ruleset name and version as appropriate to delete old application rules via the DB.
Step 2: Using SQL dev or a similar application run the generated script to delete the old PRPC rules. Issue the commit command after the deletion.
Step 3: Truncate the <rules schema>.pr_index_reference table and rebuild all indexes for this table. See example below:
- truncate table <rules schema>."PR_INDEX_REFERENCE";
- alter index <rules schema>."INDEX_REFPORT" rebuild // do this for each index
Step 4:. Rebuild the pr_index_reference table using prpcUtils. PrpcUtils is a script available with every build (PRPC media) and can be found in the ../scripts dir.
- In the .../scripts dir update the prpcUtils.properties file.
- Set the DB connection and schema details as appropriate for your system. See example below
- Update the "Settings for Expose Tool" section to include the Rule- class and its decedents. See example below.
- Run prpcUtils.bat or prpcUtils.sh with the expose option. For example. prpcUtils.bat expose
This will resave all of the rules in the system triggering a rebuild of the pr_index_reference data. This will take about 30 minutes.
Step 5: Rebuild the stats on <rules schema>.pr_index_reference table.
Published May 17, 2017 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.