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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
