On Thu, 2012-10-04 at 09:33 -0400, John Mazzitelli wrote:
I thought there was an option in Administration>SystemSettings that lets you tell the server not to perform daily DB maintenance (which is that vacuum).
If RHQ is running vacuum, that is a VERY SCARY thought! Database maintenance should be left up to the DBA and they should have full control over what happens to their production databases and when. The PostgreSQL documentation also states this. If the user wants more aggressive vacuuming, then they should modify their PostgreSQL vacuum daemon or the manual jobs run by cron. Or they can issue the command themselves from a PostgreSQL admin console. Although vacuuming is designed to run in parallel with a production database, vacuum does create a disk and CPU bottleneck on the entire database -- not just the RHQ tables -- which can cause poor performance for other active sessions.
If vacuuming is happening in RHQ on its own, this sounds like a pretty big bug and from the code, it appears that this may have been introduced a year ago. Ouch!
As for indexes, I believe we purposefully chose not to add that index on RHQ_AVAILABILITY to limit the affect on writes. I can't remember the details. We have performed some DB analysis and opted to not put indexes on some tables, but its been a while and I can't remember all that was decided.
Do we have any of the results from this analysis posted anywhere? I couldn't find it on the wiki and was curious. I would imagine that an index on a time stamp column would be ideal if it is what we primarily use in criteria. I do know that in cases where the table may only contain a few thousand rows, an index can add a few milliseconds to writes but once you exceed tens of thousands or rows, the performance gains become favorable.
You should write up a Bugzilla issue and document the indexs you think we should need to perhaps some data reports like query plans that you've seen. That could help.
----- Original Message -----
Hello,
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
- You should check all the join conditions for missing indexes too.
What do you think about it ?
Best regards, Vlad Craciunoiu - 1&1 Internet AG
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://lists.fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://lists.fedorahosted.org/mailman/listinfo/rhq-devel