Thousands of entries being written to pr_sys_ruleset_index
Hi,
We have a bix process running hourly and every time it runs it inserts hundreds of entries into pr_sys_ruleset_index table. By the end of the day there around 5000 new entries of the same hash.
This is causing various issues during runtime as there are thousands of duplicate hashed ruleset entries.
Any one had anything similar before?
Or can anyone advise where these entries are being inserted from?
All the usual has been attempted; server file system temp dirs wiped, database cached tables truncated, server restarts, static assembler, new App rule imported, BIX operator deleted & recreated manually. But nothing stops this issue happening.
Cognizant
IN
@StevenY7 after performing skimming or upgrade or major releases by binding multiple versions the ootb table pr_sys_ruleset_index will get pile up so its better to truncate below tables to avoid performance issue while opening rules or while engine performing assembling when we testing ..
system cleaner perform the clean up but if we don't do defragmentation using truncate scripts in long run data block in DB will not be free and cause SQL inserts/ selects as long running transactions. DB index will not help us
so please truncate below along with ootb cache tables and perform restart by flushing pega temp in file system
truncate table pr_sys_app_ruleset_index; truncate table pr_sys_appcache_entry; truncate table pr_sys_appcache_dep; truncate table pr_sys_appcache_shortcut; truncate table pr_sys_app_hierarchy_flat; truncate table pr_sys_class_ancestors;
truncate table pr_sys_cache_entry; truncate table pr_sys_cache_impl; truncate table pr_sys_cache_dep; truncate table pr4_rule_sysgen; truncate table pr_assembledclasses; Truncate table PR4_Log_Rule_Usage; Truncate table PR4_Log_Rule_Usage_Details;
Truncate table PR_Log; Truncate table PR_SYS_STATUSDETAILS; Truncate table PR_LOG_REPORTSTATS;
the log tables actually have historical information for bix extracts these also need to flush
Regards
Bhagath
@StevenY7 after performing skimming or upgrade or major releases by binding multiple versions the ootb table pr_sys_ruleset_index will get pile up so its better to truncate below tables to avoid performance issue while opening rules or while engine performing assembling when we testing ..
system cleaner perform the clean up but if we don't do defragmentation using truncate scripts in long run data block in DB will not be free and cause SQL inserts/ selects as long running transactions. DB index will not help us
so please truncate below along with ootb cache tables and perform restart by flushing pega temp in file system
truncate table pr_sys_app_ruleset_index; truncate table pr_sys_appcache_entry; truncate table pr_sys_appcache_dep; truncate table pr_sys_appcache_shortcut; truncate table pr_sys_app_hierarchy_flat; truncate table pr_sys_class_ancestors;
truncate table pr_sys_cache_entry; truncate table pr_sys_cache_impl; truncate table pr_sys_cache_dep; truncate table pr4_rule_sysgen; truncate table pr_assembledclasses; Truncate table PR4_Log_Rule_Usage; Truncate table PR4_Log_Rule_Usage_Details;
Truncate table PR_Log; Truncate table PR_SYS_STATUSDETAILS; Truncate table PR_LOG_REPORTSTATS;
the log tables actually have historical information for bix extracts these also need to flush
Regards
Bhagath
Certified J2EE Architect / Pega CLSA
AI/ML expertise @ IIIT-H
NAG
GB
Hi Bhagath,
Thanks very much for your detailed response above.
Finally got round to trying the above as mentioned, unfortunately the issue still persists after truncating db cache tables and clearing temp directories.
Thanks for your help.
We have raised Service Request now and will see if Pega can help advise anything further.
Pegasystems Inc.
GB
@StevenY7 would you be able to provide us with the Incident number you have submitted?
This will help us track the issue and document the final solution.
My research found the following;
The problem is caused by the function pxAllowsRecordInRuleSet when its build the ruleset version list tries to retrieve all version of given ruleset ( rsvlist.addAll(pega_rules_default.GetRuleSetVersions(rsFromName, true))) and this in turn calls getRulesetPrerequiste and dependentrulesetlistpoolimpl to insert the dependentrulesetlist for every version of given ruleset.
See support article:
This issue is already in BUG-248449 and BUG-248452 from 7.2.1
Also from Pega version 8.4.4 or later, purge was enhanced to removed unnecessary rows. in earlier releases you would need to run the delete queries to remove dead branches and that brought down the size of this table to a reasonable size.
Issue is caused by function pxAllowsRecordInRuleSet when its build the ruleset version list tries to retrieve all version of given ruleset
• rsvlist.addAll(pega_rules_default.GetRuleSetVersions(rsFromName, true));
@StevenY7 would you be able to provide us with the Incident number you have submitted?
This will help us track the issue and document the final solution.
My research found the following;
The problem is caused by the function pxAllowsRecordInRuleSet when its build the ruleset version list tries to retrieve all version of given ruleset ( rsvlist.addAll(pega_rules_default.GetRuleSetVersions(rsFromName, true))) and this in turn calls getRulesetPrerequiste and dependentrulesetlistpoolimpl to insert the dependentrulesetlist for every version of given ruleset.
See support article:
This issue is already in BUG-248449 and BUG-248452 from 7.2.1
Also from Pega version 8.4.4 or later, purge was enhanced to removed unnecessary rows. in earlier releases you would need to run the delete queries to remove dead branches and that brought down the size of this table to a reasonable size.
Issue is caused by function pxAllowsRecordInRuleSet when its build the ruleset version list tries to retrieve all version of given ruleset
• rsvlist.addAll(pega_rules_default.GetRuleSetVersions(rsFromName, true));
Later in this function Since we are retrieving all the version for given ruleset later in this function we iterate over this rsvlist and call getRulesetPrerequiste which calls dependentrulesetlistpoolimpl and inserts the dependentrulesetlist for every version of given ruleset.
I would also like to check with you: there is a 7.1.9 hotifx for a similar issue:
Hfix-27767.
Here we added check to avoid adding locked rulesets to final ruleset list in pxAllowsRecordInRuleSet RUF
The table PR_SYS_RULESET_INDEX has to be truncated to discard the uneeded (or in the case of the above support article, duplicated) items.
With this Hfix applied, only the unlocked versions for ruleset is retrieved. But since “save-as” on a rule fetches and populates the ruleset and ruleset versions that this rule can be saved into, we recommend following best practices to avoid inserting too many records into the table.
- Lock RSV that are not updated frequently.
- Instead of Re-save into new RS version, consider Branch ruleset
Can you provide us with the support ticket ID?