Re: Vacuum Problems

Hello guys,
I tried to modify my vacuum routine, and started to only run vacuum verbose
analyze diary followed by a reindex weekly.
But I still having problems in my database. The uptime database is hard to
stay below 10.
I'm thinking that my hardware is not more good as it was sometime ago.

The machine is a: 2 x Intel Xeon Dual-Core 2.3 GHz, 2 Gb RAM. The load on
machine is about at 10000 transactions / m

Maybe I need more RAM memory?

2008/11/26 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>

> On Wed, Nov 26, 2008 at 12:54 PM, Matthew T. O'Connor <matthew(at)zeut(dot)net>
> wrote:
> > Rafael Domiciano wrote:
> >>
> >> I'm not using autovacuum. Regular vacuum goes ok.
> >> To see the last 10 lines of verbose i will need to run vacuum tonight
> >>
> >> If a run a reindex before the vacuum full, increase the "speed" of doing
> >> vacuum? I found something about it googling.
> >
> > It might help a bit, but by the end of VACUUM FULL you would need to run
> > reindex again as VACUUM FULL tends to cause a lot of index bloat. It is
> > normal for tables to have some slack space, so if you do a regular vacuum
> > every day (or let autovacuum) it's normal for the table to be a bit
> bigger
> > than after a VACUUM FULL, but they should ready steady state and stop
> > growing.
>
> But there are certain use cases that would be classified as
> pathological in nature, that you can't handle with regular vacuum.
> It's only when you've proven that that's your case, and you can't
> program around it, that you should start using vacuum full though.
> vacuum full is so expensive in terms of time the system is largely
> unusable combined with the need to run reindex or replace the whole
> thing with cluster, that if regular or autovacuum can handle the load,
> then that's what you do.
>
> I've only seen cases where things like large imports were using a
> shared table where it would get bloated insanely if three or four
> imports were running at the same time with the occasional update with
> no where clause. Since you can't truncate the table, because it's
> shared with other imports, you have to vacuum it, but if you bloat it
> by 10x or 100x normal size in 30 seconds, no amount of regular
> vacuuming will help.
>
> So, users need to understand why they're always asked if they're
> running autovacuum or not. It's like asking someone with a
> nonfunctioning dryer if they've cleaned the lint trap. It's just
> something we assume someone should try first unless there's a good
> reason not to. Because it does work so well most of the time. I run
> autovacuum. I also email myself the output from vacuum verbose every
> week, to look through and see how the tables are looking. A quick
> look near the end tells you if you're mostly ok, and quick
> investigation can find bloated tables pretty fast.
>
> So, for the OP, have you tried autovacuum, and why aren't you using
> it. Most of the time people aren't running it it's for erroneous
> reasons.
>
> Also, look into updating to 8.3 or above. With its HOT update
> mechanism, and autovacuum enabled by default it handles these
> situations quite easily.
>