Created attachment 621694 [details] db report We run RHQ 4.4 in our company and our db admin checked the server's activity on the database. He advised the followings: - regular reindex and manual VACUUM runs are not required. There's no reason to run reindex on a every day basis; reindex is flooding the Backup. And there's an autovacuum Daemon since Postgres 8.3. - create missing indexes would help much more The query below is run very often: DELETE FROM RHQ_AVAILABILITY WHERE END_TIME < '1317423602458'; There's an index missing on this column. Further missing index: rhq_measurement_sched.definition Attached is a db monitoring report. Best regards, Vlad
I just looked at the code, there doesn't seem to be a way via the UI to disable this. Note that we do this vacuum because years ago, we supported postgres versions that did not have auto-vacuum, so we just vacuumed nightly. Now that all postgres versions we support has auto-vacuum (I think this is true), we can disable this by default. See org.rhq.enterprise.server.scheduler.jobs.DataPurgeJob.performDatabaseMaintenance(SystemManagerLocal, Properties) for the code that needs to change.
Reindex can be turned off in the admin screen - I think we should at least disable it by default. Vacuum is indeed no longer needed and our supported 8.4 will be EOLd once 9.3 is out ( I guess you will still get 8.4 support inside RHEL for a few years, but as this has autovacuum, we are ok). The availability table already has a (partial; PG only) index. Indexes: "rhq_availability_pkey" PRIMARY KEY, btree (id) "rhq_avail_resource_end_idx" btree (resource_id, end_time) WHERE end_time IS NULL "rhq_avail_resource_start_idx" btree (resource_id, start_time) Foreign-key constraints: "rhq_availability_resource_id_fkey" FOREIGN KEY (resource_id) REFERENCES rhq_resource(id) This could potentially be turned into a full index. One would still need to investigate the cost for insertions of availabilities.