Re: Just-in-time Background Writer Patch+Test Results

It was suggested to me today that I should clarify how others should be 
able to test this patch themselves by writing a sort of performance 
reviewer's guide; that information has been scattered among material 
covering development.  That's what you'll find below.  Let me know if any 
of it seems confusing and I'll try to clarify.  I'll be checking my mail 
and responding intermitantly while I'm away, just won't be able to run any 
tests myself until next week.

The latest version of the background writer code that I've been reporting 
on is attached to the first message in this thread:

http://archives.postgresql.org/pgsql-hackers/2007-09/msg00214.php

I haven't found any reason so far to update that code, the existing 
exposed tunables still appear sufficient for all the situations I've 
found.

Track Buffer Allocations and Cleaner Efficiency
-----------------------------------------------

First you apply the patch inside buf-alloc-2.patch.gz , which adds several 
entries to pg_stat_bgwriter; it applied cleanly to HEAD at the point when 
I generated it.  I'd suggest testing that one to collect baseline 
information with the current background writer, and to confirm that the 
overhead of tracking the buffer allocations by itself doesn't cause a 
performance hit, before applying the second patch.  I keep two clusters 
going on the same port, one with just buf-alloc-2, one with both patches, 
to be able to make such comparisions, only having one active at a time. 
You'll need to run initdb to create a database with the new stats in it 
after applying the patch.

What I've been doing to test the effectiveness of any LRU background 
writer method using this patch is take a before/after snapshot of 
pg_stat_bgwriter.  Then I compute the delta during the test run in order 
to figure what percentage of buffers were written by the background writer 
vs. the client backends; that's the number I'm reporting as cleaner_pct in 
my tests.  Here is an example of how to compute that against all 
transactions in pg_stat_bgwriter:

select round(buffers_clean * 10000 / (buffers_backend + buffers_clean)) / 
100 as cleaner_pct from pg_stat_bgwriter;

You should also monitor maxwritten_clean to make sure you've set 
bgwriter_lru_maxpages high enough that it's not limiting writes.  You can 
always turn the background writer off by setting maxpages to 0 (it's the 
only way to do so after applying the below patch).

For reference, the exact code I'm using to save the deltas and compute 
everything is available within pgbench-tools-0.2 at 
http://www.westnet.com/~gsmith/content/postgresql/pgbench-tools.htm

The code inside the benchwarmer script uses a table called test_bgwriter 
(schema in init/resultdb.sql), populates it before the test, then computes 
the delta afterwards.  bufsummary.sql generates the results I've been 
putting in my messages.  I assume there's a cleaner way to compute just 
these numbers by resetting the statistics before the test instead, but 
that didn't fit into what I was working towards.

New Background Writer Logic
---------------------------

The second patch in jit-cleaner.patch.gz applies on top of buf-alloc-2. 
It modifies the LRU background writer with the just-in-time logic as I 
described in the message the patches were attached to.  The main tunable 
there is bgwriter_lru_multiplier, which replaces bgwriter_lru_percent. 
The effective range seems to be 1.0 to 3.0.  You can take an existing 8.3 
postgresql.conf, rename bgwriter_lru_percent to bgwriter_lru_multiplier, 
adjust the value to be in the right range, and then it will work with this 
patched version.

For comparing the patched vs. original BGW behavior, I've taken to keeping 
definitions for both variables in a common postgresql.conf, and then I 
just comment/uncomment the one I need based on which version I'm running:

bgwriter_lru_multiplier = 1.0
#bgwriter_lru_percent = 5

The main thing I've noticed so far is that as you decrease bgwriter_delay 
from the default of 200ms, the multiplier has needed to be larger to 
maintain the same cleaner percentage in my tests.

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