On Mon, Apr 2, 2012 at 12:58 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> I can't help thinking that the "background hinter" I had ideas about
> writing would prevent many of the reads of old CLOG pages, taking a
> lot of pressure off of this area. It just occurred to me that the
> difference between that idea and having an autovacuum thread which
> just did first-pass work on dirty heap pages is slim to none.
Yeah. Marking things all-visible in the background seems possibly
attractive, too. I think the trick is to figuring out the control
mechanism. In this case, the workload fits within shared_buffers, so
it's not helpful to think about using buffer eviction as the trigger
for doing these operations, though that might have some legs in
general. And a simple revolving scan over shared_buffers doesn't
really figure to work out well either, I suspect, because it's too
undirected. I think what you'd really like to have is a list of
buffers that were modified by transactions which have recently
committed or rolled back. Given that, your chance of finding useful
work to do are extremely high. But it's not clear to me how to make
it happen. You could have backends remember the last few buffers
they've modified and kick that information over to the background
process via some sort of request queue at commit time, but that seems
more like a nasty benchmarking kludge that something that's likely to
solve real-world problems.
> I know how much time good benchmarking can take, so I hesitate to
> suggest another permutation, but it might be interesting to see what
> it does to the throughput if autovacuum is configured to what would
> otherwise be considered insanely aggressive values (just for vacuum,
> not analyze). To give this a fair shot, the whole database would
> need to be vacuumed between initial load and the start of the
> benchmark.
If you would like to provide a chunk of settings that I can splat into
postgresql.conf, I'm happy to run 'em through a test cycle and see
what pops out.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
