db settings

Larry O'Leary loleary at redhat.com
Thu Oct 4 14:31:50 UTC 2012


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 at lists.fedorahosted.org
> > https://lists.fedorahosted.org/mailman/listinfo/rhq-devel
> > 
> _______________________________________________
> rhq-devel mailing list
> rhq-devel at lists.fedorahosted.org
> https://lists.fedorahosted.org/mailman/listinfo/rhq-devel

-- 
Larry O'Leary
https://plus.google.com/u/0/112645929986009801513



More information about the rhq-devel mailing list