Re: Bgwriter strategies

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD