Re: autovac hung/blocked

On Wednesday November 15 2006 6:30 am, Alvaro Herrera wrote:
>
> > The table in
> > question appears to be the pathological case for vacuum:
> > very large with lots of frequent UPDATEs. It's essentially
> > a log table.
>
> A big log table where the log entries are being updated?
> Certainly sounds like a recipe for vacuum headaches.

I'm curious to know how others are dealing with this problem, and
how pgsql might support this issue.

In our case, we have a 1.8GB OS cache, a 30GB DB cache, serving
around 200 transactions/second from a 110GB DB, and this
problematic table is 15GB on disk. So when it is vacuumed, I
suspect it essentially flushes the OS cache and half the DB
cache, severely impacting performance in an already
cpu-bottlenecked machine. I have attempted to adjusted autovac
to spread out its I/O impact, but then it takes so long to run
that other smaller frequently-updated tables are not
vacuumed/analyzed in the meantime and performance starts to
suffer.

Suppose there simply are no "off-hours" periods when you can
vacuum a very large table with many frequent updates. (There is
never a good time to flush the caches.) How do you manage such
a table in a 24x7 environment?

One idea would be to partition the table some how such that the
chunks getting vacuumed are much smaller and thus not such an
impact. On the app side, I suppose we could break the table
into multiple tables on some dimension (time) to make the vacuum
impacts smaller.

But a pgsql solution in the future would be nice. I don't know
the pgsql code well, but what if the freespace map was divided
into smaller sized sectors such that individual sectors could be
vacuumed without having to hit the entire table? Or even simply
breaking up the 15GB vacuum job into 1GB chunks with some
spacing between would help. Of course, it'd be important to
keep the smaller tables vacuumed/analyzed as needed in the
in-between time.

I don't know what the best answer is here, just groping for
ideas.

TIA.
Ed