Re: vacuuming slow

Joe Maldonado <jmaldonado(at)webehosting(dot)biz> writes:
> Can concurrent updates/deletes slow down vacuum when it is progressing ? I
> mean to ask if vacuum would have to redo or stall its work because of the
> updates/deletes. Is it even possible that it goes into a long loop while
> such updates occur ?

vacuum has to take an exclusive lock at the page level on any page it is
trying to remove tuples from. The code is such that any ordinary
operations on the same page will block the vacuum; vacuum doesn't get
the lock until no one else is interested in the page. Given
sufficiently heavy concurrent activity on the table, I suppose it could
take quite a while for vacuum to finish.

> The reason for my question is that I'm seeing vacuuming take several hours
> on a big table (~1million rows) that is frequently being updated (1000
> updates/min). They are run around 2 times a day and each time it takes ~3
> hrs. There are various other processes on the box using the database, but
> the I/O load isn't very high. When vacuum runs, it causes various I/O
> tasks to run very slowly.

However, if the scenario I described were your problem, the vacuum would
be spending most of its time just blocked waiting for page locks; it
therefore wouldn't have much effect on I/O. What I suspect is that your
machine has no I/O bandwidth to spare and the extra demands of the
vacuum are just saturating your disk. You might look at iostat or
vmstat output to see what's going on. Also check with ps or top to
see if the vacuuming backend spends most of its time in "S" (sleep)
or "D" (disk IO) state.

If you are using PG 8.0 you could experiment with vacuum_cost_delay and
associated parameters. This would slow down vacuum even more in terms
of elapsed time to finish, but it should reduce the impact on concurrent
processing.

regards, tom lane