Re: patch: improve SLRU replacement algorithm - Mailing list pgsql-hackers

On Wed, Apr 4, 2012 at 1:11 PM, Greg Stark <stark@mit.edu> wrote:
> On Wed, Apr 4, 2012 at 1:00 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> , everybody's next few CLOG requests hit some other
>> buffer but eventually the long-I/O-in-progress buffer again becomes
>> least recently used and the next CLOG eviction causes a second backend
>> to begin waiting for that buffer.
>
> This still sounds like evidence that the slru is just too small for
> this transaction rate. Otherwise there would be some other buffer that
> would be accessed similarly infrequently.
>
> Your fix sounds right to me but I would hope it should be fixing
> something that would only happen rarely, not every time theres a
> write. It sounds like the slru is thrashing quite a bit more than the
> code anticipates.

Yes, the SLRU is thrashing heavily.  In this configuration, there are
32 CLOG buffers.  I just added an elog() every time we replace a
buffer.  Here's a sample of how often that's firing, by second, on
this test (pgbench with 32 clients):
  4191 19:54:21  4540 19:54:22  4295 19:54:23  3931 19:54:24  4294 19:54:25   478 19:54:26    72 19:54:27   818
19:54:28  876 19:54:29  1498 19:54:30  3526 19:54:31  1874 19:54:32   551 19:54:35  3746 19:54:36  3846 19:54:37  3803
19:54:38 3593 19:54:39  3016 19:54:40  3233 19:54:41  3190 19:54:42  3291 19:54:43  5068 19:54:44  3877 19:54:45     2
19:54:46 1678 19:54:47  1005 19:54:48   947 19:54:49  1007 19:54:50   921 19:54:51   931 19:54:52   147 19:54:53  1103
19:54:54  898 19:54:55   674 19:54:56   274 19:54:57  1081 19:54:58  1874 19:54:59  1067 19:55:00   328 19:55:01  1507
19:55:02 3735 19:55:03   138 19:55:04     1 19:55:05  2667 19:55:09  5373 19:55:10  5175 19:55:11  5062 19:55:12
 

So, yes, we're thrashing CLOG pretty hard.  But, I think that's a
mostly separate issue.  Reworking the SLRU code so that it can
efficiently handle a larger number of buffers is probably a good thing
to do, but unless we're planning to make the CLOG SLRU so large that
we NEVER have multiple people trying to replace buffers at the same
time, this fix is still necessary and appropriate.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

diff --git a/src/backend/access/transam/slru.c
b/src/backend/access/transam/slru.c
index 3049e01..6f92679 100644
--- a/src/backend/access/transam/slru.c
+++ b/src/backend/access/transam/slru.c
@@ -390,6 +390,9 @@ SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,                       return slotno;
        }
 

+               elog(LOG, "SLRU %d reading page %d", shared->ControlLock,
+                       pageno);
+               /* We found no match; assert we selected a freeable slot */
Assert(shared->page_status[slotno]== SLRU_PAGE_EMPTY ||                          (shared->page_status[slotno] ==
SLRU_PAGE_VALID&&