X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id B3058B439EF for ; Thu, 3 Jul 2003 23:32:46 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 48127-02 for ; Thu, 3 Jul 2003 20:32:36 -0300 (ADT) Received: from perrin.int.nxad.com (internal.ext.nxad.com [69.1.70.251]) by svr1.postgresql.org (Postfix) with ESMTP id 1A480B439EC for ; Thu, 3 Jul 2003 20:32:36 -0300 (ADT) Received: by perrin.int.nxad.com (Postfix, from userid 1001) id D0F5220F00; Thu, 3 Jul 2003 16:32:38 -0700 (PDT) Date: Thu, 3 Jul 2003 16:32:38 -0700 From: Sean Chittenden To: Rod Taylor Cc: Postgresql Performance Subject: Re: Moving postgresql.conf tunables into 2003... Message-ID: <20030703233238.GU72567@perrin.int.nxad.com> References: <20030703190502.GR72567@perrin.int.nxad.com> <1057263840.31736.22.camel@jester> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="v9Ux+11Zm5mwPlX6" Content-Disposition: inline In-Reply-To: <1057263840.31736.22.camel@jester> X-PGP-Key: finger seanc@FreeBSD.org X-PGP-Fingerprint: 3849 3760 1AFE 7B17 11A0 83A6 DD99 E31F BC84 B341 X-Web-Homepage: http://sean.chittenden.org/ User-Agent: Mutt/1.5.4i X-Archive-Number: 200307/39 X-Sequence-Number: 2332 --v9Ux+11Zm5mwPlX6 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable > > What are the odds of going through and revamping some of the > > tunables in postgresql.conf for the 7.4 release? I was just > > working with someone on IRC and on their 7800 RPM IDE drives, > > their random_page_cost was ideally suited to be 0.32: a far cry > > from 4. >=20 > I find it very very hard to believe a random read was cheaper than a > sequential read. Something is shifty in your testing. This is the procedure used to zero in on the number: SET random_page_cost =3D 3; [run query three times] SET random_page_cost =3D 2; [run query three times] SET random_page_cost =3D 1; [run query three times] SET random_page_cost =3D 0.01; -- verify that this tunable would make -- a difference eventually [run query three times] SET random_page_cost =3D 0.5; [run query three times] SET random_page_cost =3D 0.2; -- this was the 1st query that didn't -- do a seq scan [run query three times] SET random_page_cost =3D 0.4; -- back to a seq scan [run query three times] SET random_page_cost =3D 0.3; -- idx scan, how high can I push the rpc? [run query three times] SET random_page_cost =3D 0.35; -- interesting, the query time jumped to -- about 0.2s... better than 40s, but not as -- nice as the 0.25ms when the rpc was at 0.3 [run query three times] SET random_page_cost =3D 0.32; -- Sweet, 0.25ms for the query [run query three times] SET random_page_cost =3D 0.33; -- Bah, back up to 0.2s [run query three times] SET random_page_cost =3D 0.31; -- Down to 0.25ms, too low [run query three times] SET random_page_cost =3D 0.33; -- Double check that it wasn't an errant -- performance at 0.33 [run query three times] SET random_page_cost =3D 0.32; -- Double check that 0.32 is the magic numb= er [run query three times] [edit postgresql.conf && killall -SIGHUP postmaster] -sc --=20 Sean Chittenden --v9Ux+11Zm5mwPlX6 Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Comment: Sean Chittenden iD8DBQE/BL0W3ZnjH7yEs0ERAijwAJ9UCE17D4AbscQJFfm0BdYcxL2zZQCeL3c9 xTXEsej5iClj3TGdwfJIy2o= =DHzM -----END PGP SIGNATURE----- --v9Ux+11Zm5mwPlX6--