Date: Fri, 7 Nov 2008 00:04:01 +0100
From: Peter Schuller <peter.schuller@infidyne.com>
To: Greg Smith <gsmith@gregsmith.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: lru_multiplier and backend page write-outs
Message-ID: <20081106230400.GA95142@hyperion.scode.org>
References: <20081105122830.GA23242@hyperion.scode.org>
	<Pine.GSO.4.64.0811051500120.2523@westnet.com>
	<20081106101918.GA70379@hyperion.scode.org>
	<Pine.GSO.4.64.0811061632220.15452@westnet.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="+QahgC5+KEYLbs62"
Content-Disposition: inline
In-Reply-To: <Pine.GSO.4.64.0811061632220.15452@westnet.com>
User-Agent: Mutt/1.5.18 (2008-05-17)


--+QahgC5+KEYLbs62
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

> > no table was ever large enough that 256k buffers would ever be filled b=
y=20
> > the process of vacuuming a single table.
>=20
> Not 256K buffers--256K, 32 buffers.

Ok.=20

> > In addition, when I say "constantly" above I mean that the count
> > increases even between successive SELECT:s (of the stat table) with
> > only a second or two in between.
>=20
> Writes to the database when only doing read operations are usually relate=
d=20
> to hint bits:  http://wiki.postgresql.org/wiki/Hint_Bits

Sorry, I didn't mean to imply read-only operations (I did read the
hint bits information a while back though). What I meant was that
while I was constantly generating the insert/delete/update activity, I
was selecting the bg writer stats with only a second or two in
between. The intent was to convey that the count of backend written
pages was systematically and constantly (as in a few hundreds per
handful of seconds) increasing, in spite of no long running vacuum and
the buffer cache not being close to full.

> > On this topic btw, was it considered to allow the administrator to
> > specify a fixed-size margin to use when applying the JIT policy?
>=20
> Right now, there's no way to know exactly what's in the buffer cache=20
> without scanning the individual buffers, which requires locking their=20
> headers so you can see them consistently.  No one process can get the big=
=20
> picture without doing something intrusive like that, and on a busy system=
=20
> the overhead of collecting more data to know how exactly far ahead the=20
> cleaning is can drag down overall performance.  A lot can happen while th=
e=20
> background writer is sleeping.

Understood.

> One next-generation design which has been sketched out but not even=20
> prototyped would take cleaned buffers and add them to the internal list o=
f=20
> buffers that are free, which right now is usually empty on the theory tha=
t=20
> cached data is always more useful than a reserved buffer.  If you=20
> developed a reasonable model for how many buffers you needed and padded=
=20
> that appropriately, that's the easiest way (given the rest of the buffer=
=20
> manager code) to get close to ensuring there aren't any backend writes.=
=20
> Because you've got the OS buffering writes anyway in most cases, it's har=
d=20
> to pin down whether that actually improved worst-case latency though. And=
=20
> moving in that direction always seems to reduce average throughput even i=
n=20
> write-heavy benchmarks.

Ok.

> The important thing to remember is that the underlying OS has its own rea=
d=20
> and write caching mechanisms here, and unless the PostgreSQL ones are=20
> measurably better than those you might as well let the OS manage the=20
> problem instead.

The problem though is that though the OS may be good in the common
cases it is designed for, it can have specific features that are
directly counter-productive if your goals do not line up with that of
the commonly designed-for use case (in particular, if you care about
latency a lot and not necessarily about absolute max throughput).

For example, in Linux up until recently if not still, there is the
1024 per-inode buffer limit that limited the number of buffers written
as a result of expiry, which means that when PostgreSQL does its
fsync(), you may end up having a lot more to write out than what would
have been the case if the centisecs_expiry had been enforced,
regardless of whether PostgreSQL was tuned to write dirty pages out
sufficiently aggressively. If the amount built up exceeds the capacity
of the RAID controller cache...

I had a case where I suspect this was exaserbating the
situation. Manually doing a 'sync' on the system every few seconds
noticably helped (the theory being, because it forced page write-outs
to happen earlier and in smaller storms).

>  It's easy to demonstrate that's happening when you give=20
> a decent amount of memory to shared_buffers, it's much harder to prove=20
> that's the case for an improved write scheduling algorithm.  Stepping bac=
k=20
> a bit, you might even consider that one reason PostgreSQL has grown as=20
> well as it has in scalability is exactly because it's been riding=20
> improvements the underlying OS in many of these cases, rather than trying=
=20
> to do all the I/O scheduling itself.

Sure. In this case with the backend writes, I am nore interesting in
understanding better what is happening and having better indications
of when backends block on I/O, than necessarily having a proven
improvement in throughput or latency. It makes it easier to reason
about what is happening when you *do* have a measured performance
problem.

Thanks for all the insightful information.

--=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


--+QahgC5+KEYLbs62
Content-Type: application/pgp-signature
Content-Disposition: inline

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

iEYEARECAAYFAkkTd+AACgkQDNor2+l1i31nZgCfdHJAtPkyAXr0uMI8a7VRssQu
4WIAn2pGgJ0PBLIFsCUVtctGQ/5/qWhl
=SyCo
-----END PGP SIGNATURE-----

--+QahgC5+KEYLbs62--