Re: Size for vacuum_mem

On Fri, Dec 06, 2002 at 02:56:47PM -0500, Tom Lane wrote:
> "David Blood" <david(at)matraex(dot)com> writes:
> > A "lazy vacuum" can hurt If you have lots of i/o. If we try to run it
> > during the day it kills us. This is because to vacuum all the tables
> > postgres has to read them from the disk. While it doesn't not lock rows
> > it does block other rows from reading/writing to/from the disk.
>
> On the other hand, I have watched people lazy-vacuum production
> databases in 7.2.* and not seen any visible hit on system load
> (as far as top or vmstat could show, anyway).
>
> I think it may be a matter of whether you have disk bandwidth to
> spare. If the disk farm is marginal, the extra demand from a vacuum
> may push you over the knee of the performance curve. But that's just
> a guess. It would be interesting if some folks from the "it doesn't
> hurt" and the "it does hurt" camps could compare notes and try to
> understand the reason for the difference in their results.

I'm firmly in the "devastating to performance" camp.

7.2.3, reasonably well-tuned on a not overspecced, but adequate
Solaris box. (Built with a non-Solaris qsort, though I doubt that's
relevant).

Several large-ish (hundreds of thousands to millions of rows), fairly
heavily updated tables, with some text fields large enough to push
data out to toast.

Vacuumed pretty much continuously while data is being updated, so it
didn't get too far out to lunch.

Then the process updating the table were shut down, so the system was
basically idle, and the tables were vacuumed. Simple selects (from
some small tables, via psql) slowed to a crawl - tens of seconds to
get any response. There was a lot of I/O but also high CPU usage -
including a fair fraction of system time.

It felt like the system was i/o-starved, yet it would run a very
intensive DB app quite happily if it wasn't vacuuming.

(I finally rewrote the algorithm to avoid UPDATE, instead storing
deltas in a daily table, then every night reading all the deltas and
all the archived data and inserting the merged data into a new archive
table (then indexing it and renaming to replace the old archived data
table). Ugly, and offended my SQL sensibilities, but it avoided having
to keep that table vacuumed.)

Cheers,
Steve