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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

Reply via email to