Business Directory Security_privileges table is bloating( in GB)

Business Directory Security_privileges table is bloating( in GB)

book

Article ID: KB0080544

calendar_today

Updated On:

Products Versions
TIBCO Data Virtualization 7.0.x

Description

Business Directory Security_privileges table is bloating( in GB)
 

Issue/Introduction

Business Directory Security_privileges table is bloating( in GB)

Resolution

Please follow below mentioned Steps:
  • Shut down BD
  • In /repository/data/postgresql.conf :
    • turn on autovacuum (uncomment this line):
autovacuum = on
  • Uncomment the following lines:
vacuum_cost_page_hit = 1 
vacuum_cost_page_miss = 10 
vacuum_cost_page_dirty = 20 
log_autovacuum_min_duration = -1    # -1 disables, 0 logs all actions
Note: Uncomment the line above and set to 0 to see the autovacuum logging. Re-comment it and restart the repo to stop the logging that may slow the repository down.
  • ​Using psql, run these commands. If needed, run these for every table that is bloating:
alter table bdrepo.security_privileges set(autovacuum_enabled=true);
alter table bdrepo.security_privileges set(fillfactor=100);
alter table bdrepo.security_privileges set(autovacuum_analyze_scale_factor=0.2);
alter table bdrepo.security_privileges set(autovacuum_vacuum_cost_limit = 1000);
alter table bdrepo.security_privileges set(autovacuum_vacuum_scale_factor = 0);
alter table bdrepo.security_privileges SET (autovacuum_vacuum_threshold = 10000);
alter table bdrepo.security_privileges set(autovacuum_vacuum_cost_delay = 10);

To verify that all the settings have been added, run
select relname, reloptions, pg_namespace.nspname
from pg_class
join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where relname like 'security_privileges';
  • Restart the BD repository
  • After some time, monitor the cs_repository-<weekday>.log. It should show autovacuum removing rows from a busy table.
  • Check to see if there are idle transactions holding a lock on the repository. This is what was preventing autovacuum from working on some busy tables before the fix in 7.0.6.00.02. It should be fixed in 7.0.6.00.02 and greater.
SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';
If they haven't upgraded to 7.0.6.00.02 yet, setting below mentioned pool size configurations in "/apps/server/server_defs-<most recent version>.xml" to 0 may help. However, there are some importat fixes that went into Pool manager implementation that is only available in 7.0.6 patch 2. 
Pool Minimum Size (Current)
Pool Minimum Size (On Server Restart)
Monitor daemon Pool Minimum Size (Current)
Monitor daemon Pool Minimum Size (Current)
Powered by Wolken Software