Re: Bgwriter strategies - Mailing list pgsql-hackers

I just got my own first set of useful tests of using the new "remember 
where you last scanned to" BGW implementation suggested by Tom.  What I 
did was keep the exiting % to scan, but cut back the number to scan when 
so close to a complete lap ahead of the strategy point that I'd cross it 
if I scanned that much.  So when the system was idle, it would very 
quickly catch up with the strategy point, but if the %/max numbers were 
low it's possible for it to fall behind.

My workload was just the UPDATE statement out of pgbench with a database 
of scale 25 (~400MB, picked so most operations were in memory), which 
pushes lots of things in and out of the buffer cache as fast as possible.

Here's some data with no background writer at all:

clients    tps      buf_clean buf_backend buf_alloc
1    1340    0    72554    96846
2    1421    0    73969    88879
3    1418    0    71452    86339
4    1344    0    75184    90187
8    1361    0    73063    88099
15    1348    0    71861    86923

And here's what I got with the new approach, using 10% for the scan 
percentage and a maximum of 200 buffers written out.  I picked those 
numbers after some experimentation because they were the first I found 
where the background writer was almost always riding right behind the 
strategy point; with lower numbers, when the background writer woke up it 
often found it had already fallen behind the stategy point and had to 
start cleaning forward the old way instead, which wasn't what I wanted to 
test.

clients    tps      buf_clean buf_backend buf_alloc
1    1261    122917    150    105655
2    1186    126663    26    97586
3    1154    127780    21    98077
4    1181    127685    19    98068
8    1076    128597    2    98229
15    1065    128399    5    98143

As you can see, I achieved the goal of almost never having a backend write 
its own buffer, so yeah for that.  That's the only good thing I can say 
about it though.  The TPS results take a moderate dive, and there's about 
10% more buffer allocations.  The big and obvious issues is that I'm 
writing almost 75% more buffers this way--way worse even than the 10% 
extra overhead Heikki was seeing.  But since I've going out of my way to 
find a worse-case for this code, I consider mission accomplished there.

Anyway, will have more detailed reports to post after I collect some more 
data; for now I just wanted to join Heikki in confirming that the strategy 
of trying to get the LRU cleaner to ride right behind the strategy point 
can really waste a whole lot of writes.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD