Bug 863169 - DB settings and indexes
DB settings and indexes
Status: NEW
Product: RHQ Project
Classification: Other
Component: Database (Show other bugs)
Unspecified Linux
unspecified Severity low (vote)
: ---
: ---
Assigned To: Heiko W. Rupp
Mike Foley
Depends On:
  Show dependency treegraph
Reported: 2012-10-04 11:14 EDT by vlad crc
Modified: 2013-07-01 06:49 EDT (History)
2 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Last Closed:
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)
db report (621.08 KB, text/html)
2012-10-04 11:14 EDT, vlad crc
no flags Details

  None (edit)
Description vlad crc 2012-10-04 11:14:19 EDT
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:
   There's an index missing on this column.

   Further missing index:

Attached is a db monitoring report.

Best regards,
Comment 1 John Mazzitelli 2012-10-04 11:40:25 EDT
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.
Comment 2 Heiko W. Rupp 2013-07-01 06:49:18 EDT
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)
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.

Note You need to log in before you can comment on or make changes to this bug.