Date: Wed, 4 Apr 2007 19:44:48 +0200
From: Peter Schuller <peter.schuller@infidyne.com>
To: andrew@supernews.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Scaling SELECT:s with the number of disks on a stripe
Message-ID: <20070404174448.GA6918@hyperion.scode.org>
References: <20070330051645.GA78431@hyperion.scode.org>
	<slrnf0tpnk.2i67.andrew+nonews@atlantis.supernews.net>
	<20070402105355.GB57038@hyperion.scode.org>
	<slrnf12dlc.2i67.andrew+nonews@atlantis.supernews.net>
	<20070404060116.GA97698@hyperion.scode.org>
	<slrnf17508.2i67.andrew+nonews@atlantis.supernews.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="xHFwDpU9dbj6ez1V"
Content-Disposition: inline
In-Reply-To: <slrnf17508.2i67.andrew+nonews@atlantis.supernews.net>
User-Agent: Mutt/1.5.13 (2006-08-11)


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

Hello,

> I'd always do benchmarks with a realistic value of shared_buffers (i.e.
> much higher than that).
>=20
> Another thought that comes to mind is that the bitmap index scan does
> depend on the size of work_mem.
>=20
> Try increasing your shared_buffers to a reasonable working value (say
> 10%-15% of RAM - I was testing on a machine with 4GB of RAM, using a
> shared_buffers setting of 50000), and increase work_mem to 16364, and
> see if there are any noticable changes in behaviour.

Increasing the buffer size and work_mem did have a significant
effect. I can understand it in the case of the heap scan, but I am
still surprised at the index scan. Could pg be serializing the entire
query as a result of insufficient buffers/work_mem to satisfy multiple
concurrent queries?

With both turned up, not only is the heap scan no longer visibly CPU
bound, I am seeing some nice scaling in terms of disk I/O. I have not
yet benchmarked to the point of being able to say whether it's
entirely linear, but it certainly seems to at least be approaching the
ballpark.

Thank you for the help! I guess I made a bad call not tweaking
this. My thinking was that I explicitly did not want to turn it up so
that I could benchmark the raw performance of disk I/O, rather than
having things be cached in memory more than it would already be. But
apparantly it had other side-effects I did not consider.

Thanks again,

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


--xHFwDpU9dbj6ez1V
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (FreeBSD)

iD4DBQFGE+QPDNor2+l1i30RAmQFAJirlcUfDXJLT1f3UGwDOn54Y8tIAKDlhIt1
0CLAwL0LYwRgwHgRTg4fdg==
=r8KN
-----END PGP SIGNATURE-----

--xHFwDpU9dbj6ez1V--