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