On Wed, Jul 18, 2012 at 3:59 PM, Tom Lane <t...@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's no 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 -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers