On Wed, Jul 18, 2012 at 3:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> In short, then, the background writer process is entirely useless for
> any database that fits completely into shared buffers.
Or to phrase that a bit more positively, there's no reason to do a
bunch of unnecessary writes if we are lucky enough to encounter the
happy situation where the database fits in shared buffers. The
background writer's reason for existence is to make buffer eviction
faster by cleaning buffers that will soon be evicted, so if we're not
going to evict any buffers then we needn't clean them either (except
at checkpoint time).
> So that raises two independent sets of questions:
>
> 1. Do we like the fact that the bgwriter isn't doing anything in this
> situation? It seems arguably OK for writes to happen only for
> checkpointing purposes if there is no memory pressure. But having the
> bgwriter wake up 5 times a second to decide it has nothing to do seems
> a bit wasteful. I'm inclined to think maybe it should go into the
> recently added "hibernation mode" anytime the buffer freelist isn't
> empty. Or maybe you could argue that this scenario isn't worth any
> optimization effort, but with many-gig RAM becoming more and more
> common, I don't think I agree.
I feel like the hibernation behavior ought to be tied to buffer
eviction, not the freelist. When there's no buffer eviction
happening, the background writer should hibernate, because there's no
need to clean buffers in preparation for future eviction in that case.It is true that when the freelist is non-empty,
there'sno buffer
eviction occurring, but that will typically only happen at start-up.
It's not uncommon to have a database that is larger than
shared_buffers but whose active portion is smaller than
shared_buffers. In that case you expect the freelist to converge to
empty (since the only things that put buffers back on the freelist
after startup are relation or database drops) but yet you probably
don't need the background writer working.
Another consideration is that we might actually want to arrange things
so that the free-list remains non-empty on an ongoing basis. Right
now buffer eviction is a major scalability bottleneck. Maybe we'll
find some other way to fix that, but then again maybe we won't.
> 2. It's rather disturbing that a fairly large swath of functionality
> just stopped getting tested at all by the buildfarm. Do we want to
> rethink the shared_buffers increase? Or artificially bloat the
> regression database to make it larger than 128MB? Or do something else
> to ensure we still exercise the DB-bigger-than-buffers case?
It seems like it could be useful to test with a variety of
shared_buffers settings. Maybe we should even have one or two
buildfarm animals that run with a REALLY small shared_buffers setting,
like 1MB, just to see if that breaks anything.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
