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
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 |