Bug 863169

Summary: DB settings and indexes
Product: [Other] RHQ Project Reporter: vlad crc <vlad.craciunoiu>
Component: DatabaseAssignee: Heiko W. Rupp <hrupp>
Status: NEW --- QA Contact: Mike Foley <mfoley>
Severity: low Docs Contact:
Priority: unspecified    
Version: 4.4CC: hrupp, mazz
Target Milestone: ---   
Target Release: ---   
Hardware: Unspecified   
OS: Linux   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---
Attachments:
Description Flags
db report none

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:
   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
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.


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.