TDV Postgres Auto Vacuum configuration

Products	Versions
TIBCO Data Virtualization	7.0.x

Description

We have seen TDV Postgres tables sizes are getting bloated.

Issue/Introduction

Environment

Windows/Linux

Resolution

If your TDV installation is on 7.0.6.00.01 or earlier, autovacuum might not work until you upgrade; however, a manual VACUUM FULL should work to bring your tables down to a manageable level until you can upgrade.

Shut down TDV

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 cisrepo.security_privileges set(autovacuum_enabled=true);
- alter table cisrepo.security_privileges set(fillfactor=100);
- alter table cisrepo.security_privileges set(autovacuum_analyze_scale_factor=0.2);
- alter table cisrepo.security_privileges set(autovacuum_vacuum_cost_limit = 1000);
- alter table cisrepo.security_privileges set(autovacuum_vacuum_scale_factor = 0);
- alter table cisrepo.security_privileges SET (autovacuum_vacuum_threshold = 10000);
- alter table cisrepo.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 TDV 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 you 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)

Welcome to "KB Articles"