Re: Re: VACUUM and 24/7 database operation

> With best regards.
> Sanjay.
>
> At 05:53 PM 1/23/01 , Tom Lane wrote:
> >Thomas.Favier@accelance.fr writes:
> >> - Is 2 minutes a standard time for vacuuming a 500.000 rows table ?
> >> - Can it be reduced ?
> >> - In a far future, what are the problems we can run into not vacuuming
> >> that table ? We have already seen that after a month, some transactions
> >> involving where id >= some_value take forever, so we supressed them.
> >
> >If it takes a month before query performance gets bad, then perhaps you
> >could vacuum the table only once a month.  However, that vacuum would
> >probably take longer than two minutes, so it's a tradeoff...
> >
> >We have plans for 7.2 to reduce the need for periodic vacuums, but that
> >won't help you much now.
> >
> >There are patches available for a "lazy vacuum" process on 7.0.3,
> >which can be a win if vacuum only needs to get rid of a few rows.
> >But they're not very thoroughly tested IMHO.  See
> >http://people.freebsd.org/~alfred/vacfix/
> >

We've been running them since I released them with only a single
problem that has never resurfaced.  I would say they are pretty stable.

It's not "just a few rows" by the way, it's several thousand and up to
probably 50,000 rows we get about a 20-40x speedup in the time taken
to vacuum (10-15 minutes to 13-40 seconds).  This is on tables that
are over 300megabytes and indecies that are even larger (multiple
column indicies).

It's a shame this still hasn't made it into 7.1 :(

* Sanjay Arora <sk@pobox.com> [010123 12:10] wrote:
> Tom,
>
> Shouldn't it be possible to build vacuum as an ongoing internal PG process,
> instead of a seperate operation? How does Oracle byepass this? Must be some
> way that can be implemented.
>
> Any pointers to further reading to brush up my theory in this regard please?

Follow the long trail of my messages on the lists about it, I'd
say about 1/3 of my posts have to do with the problems we were
facing before contracting Vadim to do the patches available at:

  http://people.freebsd.org/~alfred/vacfix/

>
> IAC, regarding the actual inquiry, wouldn't be a replicated database on a
> second server be more cheaper than Oracle, if the party is satisfied with
> PG performance? I browsed some PG commercial organization site that told
> about a Replication Server being available for PG. I am about to look into
> that next month. Is it any good like PG? Will provide failover too..rather
> than using Oracle.

It should, but I havne't read up on it much.

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."