On Sun, Jun 8, 2014 at 9:51 AM, Kevin Grittner <kgri...@ymail.com> wrote: > Amit Kapila <amit.kapil...@gmail.com> wrote: >> I have improved the patch by making following changes: >> a. Improved the bgwriter logic to log for xl_running_xacts info and >> removed the hibernate logic as bgwriter will now work only when >> there is scarcity of buffer's in free list. Basic idea is when the >> number of buffers on freelist drops below the low threshold, the >> allocating backend sets the latch and bgwriter wakesup and begin >> adding buffer's to freelist until it reaches high threshold and then >> again goes back to sleep. > > The numbers from your benchmarks are very exciting, but the above > concerns me. My tuning of the bgwriter in production has generally > *not* been aimed at keeping pages on the freelist,
Just to be clear, prior to this patch, the bgwriter has never been in the business of putting pages on the freelist in the first place, so it wouldn't have been possible for you to tune for that. > Essentially I was able to tune the bgwriter so that a dirty page > was always push out to the OS cache within three seconds, which led > to a healthy balance of writes between the checkpoint process and > the bgwriter. Backend processes related to user connections still > performed about 30% of the writes, and this work shows promise > toward bringing that down, which would be great; but please don't > eliminate the ability to prevent write stalls in the process. I think, as Amit says downthread, that the crucial design question here is whether we need two processes, one to populate the freelist so that regular backends don't need to run the clock sweep, and a second to flush dirty buffers, or whether a single process can serve both needs. In favor of a single process, many people have commented that the background writer doesn't seem to do much right now. If the process is mostly sitting around idle, then giving it more responsibilities might be OK. In favor of having a second process, I'm a little concerned that if the background writer gets busy writing a page, it might then be unavailable to populate the freelist until it finishes, which might be a very long time relative to the buffer allocation needs of other backends. I'm not sure what the right answer is. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers