Red Hat Bugzilla – Bug 863169
DB settings and indexes
Last modified: 2013-07-01 06:49:18 EDT
Created attachment 621694 [details]
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:
Attached is a db monitoring report.
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.
"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)
"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.