Bug 863169
Summary: | DB settings and indexes | ||||||
---|---|---|---|---|---|---|---|
Product: | [Other] RHQ Project | Reporter: | vlad crc <vlad.craciunoiu> | ||||
Component: | Database | Assignee: | Heiko W. Rupp <hrupp> | ||||
Status: | NEW --- | QA Contact: | |||||
Severity: | low | Docs Contact: | |||||
Priority: | unspecified | ||||||
Version: | 4.4 | CC: | 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: | --- | Target Upstream Version: | |||||
Embargoed: | |||||||
Attachments: |
|
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. |
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