14.2 Configuring Auto-Vacuum Settings for Tables in the PostgreSQL Database

In a large-scale datacenter, the tables in the Application Server PostgreSQL database can grow quite large. Performance can degrade significantly if stale and temporary data are not systematically removed. Vacuuming cleans up stale or temporary data in a table, and analyzing refreshes its knowledge of all the tables for the query planner.

PostgreSQL database tables are auto-vacuumed by default when 20% of the rows plus 50 rows are inserted, updated, or deleted. Tables are auto-analyzed when a threshold is met for 10% of the rows plus 50 rows. For example, a table with 10000 rows is not auto-vacuumed until 2050 rows are inserted, updated, or deleted. That same table is auto-analyzed when 1050 rows are inserted, updated, or deleted.

The default auto-vacuum analyze and vacuum settings are sufficient for a small deployment, but the percentage thresholds take longer to trigger as the tables grow larger. Performance degrades significantly before the auto-vacuum vacuuming and analyzing occurs.

NOTE:For larger installations (that is, thousands of workloads), we recommend that you customize the settings as follows on individual tables to ensure optimal performance for data access:

The scale factor should be set to zero for both vacuum and analyze auto-vacuum settings.
The threshold should be set to 1000 for both the vacuum and analyze threshold settings.

The following PostgreSQL statements will ensure that auto-vacuum vacuum and analyze are run every 1000 updates (inserts, modifies, or deletes) on the tables that are usually the biggest in the Cloud Manager Application Server installation.

ALTER TABLE novell.workload_cost SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE novell.workload_cost SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE novell.workload_cost SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE novell.workload_cost SET (autovacuum_analyze_threshold = 1000);

ALTER TABLE novell.cm_domain_obj SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE novell.cm_domain_obj SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE novell.cm_domain_obj SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE novell.cm_domain_obj SET (autovacuum_analyze_threshold = 1000);

ALTER TABLE novell.pso_object SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE novell.pso_object SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE novell.pso_object SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE novell.pso_object SET (autovacuum_analyze_threshold = 1000);

ALTER TABLE novell.disk_data SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE novell.disk_data SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE novell.disk_data SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE novell.disk_data SET (autovacuum_analyze_threshold = 1000);

ALTER TABLE wl_disk_data SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE wl_disk_data SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE wl_disk_data SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE wl_disk_data SET (autovacuum_analyze_threshold = 1000);

ALTER TABLE novell.nic_data SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE novell.nic_data SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE novell.nic_data SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE novell.nic_data SET (autovacuum_analyze_threshold = 1000);

ALTER TABLE novell.wl_nic_data SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE novell.wl_nic_data SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE novell.wl_nic_data SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE novell.wl_nic_data SET (autovacuum_analyze_threshold = 1000);

ALTER TABLE novell.workload SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE novell.workload SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE novell.workload SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE novell.workload SET (autovacuum_analyze_threshold = 1000);

ALTER TABLE novell.business_service SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE novell.business_service SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE novell.business_service SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE novell.business_service SET (autovacuum_analyze_threshold = 1000);

HINT:The following query provides information about the last time the Novell schema tables were vacuumed or analyzed:

select relname,last_vacuum, last_autovacuum, last_analyze, vacuum_count, autovacuum_count,
  last_autoanalyze from pg_stat_user_tables where schemaname = 'novell' order by relname ASC;