Date: Wed, 5 Nov 2008 13:28:30 +0100
From: Peter Schuller <peter.schuller@infidyne.com>
To: pgsql-performance@postgresql.org
Subject: lru_multiplier and backend page write-outs
Message-ID: <20081105122830.GA23242@hyperion.scode.org>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="TB36FDmn/VVEgNH/"
Content-Disposition: inline
User-Agent: Mutt/1.5.18 (2008-05-17)


--TB36FDmn/VVEgNH/
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Hello,

I've had the feeling for a while that the pg_stat_bgwriter statistics
doesn't work quite the way I have understood it (based on the
excellent [1] and the pg docs).

I am now monitoring a database that has an lru_multiplier of 4.0, a
delay of 200ms and a maxpages of 1000. Current stats:

postgres=3D# select * from pg_stat_bgwriter;
 checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean |=
 maxwritten_clean | buffers_backend | buffers_alloc=20
-------------------+-----------------+--------------------+---------------+=
------------------+-----------------+---------------
               241 |              17 |              72803 |             0 |=
                0 |           81015 |         81708
(1 row)

This is while the database is undergoing continuous activity (almost
exclusively writing), but at a rate that does not saturate underlying
storage (caching raid controller, all write ops are fast, cache is
never filled).

In addition, PostgreSQL is not even close to even filling it's buffer
cache. The buffer cache is configured at 1 GB, and the resident size
of the PostgreSQL process is only 80-90 MB so far. So even
independently of any lru multplier setting, delays and whatever else,
I don't see why any backend would ever have to do its own writeouts in
order to allocate a page from the buffer cache.

One theory: Is it the auto vacuum process? Stracing those I've seen
that they very often to writes directly to disk.

In any case, the reason I am fixating on buffers_backend is that I am
after a clear indication whether any "normal" backend (non-autovacuum
or anything like that) is ever having to block on disk writes, other
than WAL fsync:s.

Is a non-zero buffers_backend consistent with expected behavior?

[1] http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm

--=20
/ Peter Schuller

PGP userID: 0xE9758B7D or 'Peter Schuller <peter.schuller@infidyne.com>'
Key retrieval: Send an E-Mail to getpgpkey@scode.org
E-Mail: peter.schuller@infidyne.com Web: http://www.scode.org


--TB36FDmn/VVEgNH/
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (FreeBSD)

iEYEARECAAYFAkkRkW4ACgkQDNor2+l1i31qdQCgyNHxIqWibDZBGiLbm8+iUfjU
GrsAnRnydcn5PuAgfQnb9I6H30sCbZKH
=pY+6
-----END PGP SIGNATURE-----

--TB36FDmn/VVEgNH/--