vacuuming slow

From: Joe Maldonado <jmaldonado(at)webehosting(dot)biz>
To: pgsql-general(at)postgresql(dot)org
Subject: vacuuming slow
Date: 2005-02-22 15:38:55
Message-ID: 421B520F.1010006@webehosting.biz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 ?

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.

To speed this up I have changed vacuum_mem from 8MB to 1Gb. This did help.
I'm still collecting data to see how much it has improved it by. But I
suspect this isn't the end.

The other thing I do not understand is as follows: I looked at
pg_stat_all_tables and obtained n_tup_ins, upd, del etc on a production
machine to reproduce the long vacuum times on my local system. But when I
simulated inserts/updates/deletes to get at the same stats for the table,
vacuum ran surprisingly fast (production took 1.5hrs, local system 3mins).
On my local system though I had turned off other application processes.
But I do not find my results reasonable since these processes are not I/O
intensive.

Thus, I'm trying to either (1) fix my application if it is inevitable, so
I don't do as many updates/min or (2) understand vacuuming better and fix
something I've missed.

Thanks all,

Joe

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2005-02-22 15:43:58 Re: FW: execute dynamic strings. need help.
Previous Message Tom Lane 2005-02-22 15:29:13 Re: rows and array