On Fri, 2009-07-10 at 14:25 -0500, Dan Armbrust wrote: > > Hm, I'm not sure I believe any of that except the last bit, seeing that > > he's got plenty of excess CPU capability. But the last bit fits with > > the wimpy-I/O problem, and it also offers something we could test. > > Dan, please see what happens when you vary the wal_buffers setting. > > (Note you need a postmaster restart to change that.) > > > Ok, I tried a few different values - 32kb, 64kb, 512kb, 2MB and 10MB. > > I'm not seeing any highly noticeable change in behaviour with any > setting - it wasn't a scientific test, but I seem to have about the > same size hiccup with each setting. The hiccup may be slightly > shorter with the 10MB setting, but barely, if it is. OK, so its not the "last bit". Let's look at the first idea again: In more detail, the explanation relates to a section of code in XLogInsert() in xlog.c that has a comment /* * Check to see if my RedoRecPtr is out of date. If so, may have to go * back and recompute everything. This can only happen just after a * checkpoint, so it's better to be slow in this case and fast otherwise. * * If we aren't doing full-page writes then RedoRecPtr doesn't actually * affect the contents of the XLOG record, so we'll update our local copy * but not force a recomputation. */ This causes us to queue for the WALInsertLock twice at exactly the time when every caller needs to calculate the CRC for complete blocks. So we queue twice when the lock-hold-time is consistently high, causing queue lengths to go ballistic. That also means that XLogFlush never achieves a piggy-back flush, so WALWriteLock hold times go up also. I bet that takes a significant amount of time to settle down once initiated, probably a function of the write transaction ratio and number of frequently accessed blocks. We haven't got *any* measurements that show how long traffic jams take to clear and I think we need them. Also think piggy-back writes works OK, but stops working when work begins to queue, which will cause a "stall" catastrophe, like in aerodynamics. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
