Jim C. Nasby wrote:
On Tue, Feb 07, 2006 at 01:39:34PM +0100, Markus Schaber wrote:
Hi, Jim,
Jim C. Nasby wrote:
What we really need is a replacement for vacuum_delay that takes
PostgreSQL generated IO activity into account...
There are also other ideas which can make vacuum less painfull:
- Use a "delete"-map (like the free space map) so vacuum can quickly
find the pages to look at.
Already on TODO.
- Have vacuum end its transaction after a certain amount of work, and
restart at the same page later.
AFAIK this isn't possible with the current way vacuum works.
There was a patch posted for this in the 8.0 cycle, but it was said to
be not useful. I think it's possibly useful for large tables and with
autovac only.
- Have vacuum full search good candidates with non-stopping lock (and
usage of delete-map and fsm), then doing {lock, recheck, move, unlock}
in small amounts of data with delay between.
This isn't an issue of locks, it's an issue of long-running
transactions. It *might* be possible for vacuum to break work into
smaller transactions, but I'm pretty sure that would be a non-trivial
amount of hacking.
When tables are tracked individually for wraparound, the longest
transaction required for vacuuming will be one to vacuum one table.
With delete-map and other functions, the time for that transaction may
be reduced. Partial vacuum of large tables is an option, but again
requires some real smarts in the autovac code to track wraparound issues.
- Introducing some load measurement, and a pressure measurement (number
of deleted rows, TID wraparound etc.). Then start vacuum when load is
low or pressure is very high. Tune other parameters (like "certain
amount of work" depending on those measures.
Which is essentially what I was suggesting...
All of them are a lot of code to hack, but although I'm not a postgresql
core developer, I am keen enough to invite you to send patches. :-)
Well, if you know C then you're already 1 step closer to being able to
change these kinds of things than I am.
Regards
Russell Smith
