X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 57F4FD1B510 for ; Mon, 13 Oct 2003 21:35:07 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 42774-06 for ; Mon, 13 Oct 2003 18:34:36 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id BD928D1B53C for ; Mon, 13 Oct 2003 18:34:35 -0300 (ADT) Received: from [216.135.165.74] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3763672; Mon, 13 Oct 2003 14:34:59 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: David Griffiths , pgsql-performance@postgresql.org Subject: Re: Any issues with my tuning... Date: Mon, 13 Oct 2003 14:32:17 -0700 User-Agent: KMail/1.4.3 References: <039801c391c2$4b83d600$6501a8c0@griffiths2> In-Reply-To: <039801c391c2$4b83d600$6501a8c0@griffiths2> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200310131432.17735.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/435 X-Sequence-Number: 4183 David, > shared_buffers =3D 96000 # min max_connections*2 or 16, 8KB each This seems a little high to me, even for 2gb RAM. What % of your availabl= e=20 RAM does it work out to? > effective_cache_size =3D 6000 # typically 8KB each This is very, very low. Given your hardware, I'd set it to 1.5GB. > Note that I've played with all these values; shared_buffers has been as > low as 5000, and effective_cache_size has been as high as 50000. Sort > mem has varied between 1024 bytes and 4096 bytes. wal_buffers have been > between 16 and 128. If large updates are slow, increasing checkpoint_segments has the largest= =20 effect on this. > Tied up in all this is my inability to grasp what shared_buffers do >=20=20 > From " http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html > ": >=20=20 > "shbufShared buffers defines a block of memory that PostgreSQL will use > to hold requests that are awaiting attention from the kernel buffer and > CPU." and "The shared buffers parameter assumes that OS is going to > cache a lot of files and hence it is generally very low compared with > system RAM." This is correct. Optimal levels among the people on this list who have=20 bothered to do profiling have ranged btw. 6% and 12% of available RAM, but= =20 never higher. > From " http://www.lyris.com/lm_help/6.0/tuning_postgresql.html > " >=20=20 > "Increase the buffer size. Postgres uses a shared memory segment among > its subthreads to buffer data in memory. The default is 512k, which is > inadequate. On many of our installs, we've bumped it to ~16M, which is > still small. If you can spare enough memory to fit your whole database > in memory, do so." This is absolutely incorrect. They are confusing shared_buffers with the= =20 kernel cache, or perhaps confusing PostgreSQL configuration with Oracle=20 configuration. I have contacted Lyris and advised them to update the manual. > Our database (in Oracle) is just over 4 gig in size; obviously, this > won't comfortably fit in memory (though we do have an Opteron machine > inbound for next week with 4-gig of RAM and SCSI hard-drives). The more > of it we can fit in memory the better. This is done through increasing the effective_cache_size, which encourages = the=20 planner to use data kept in the kernel cache. > What about changing these costs - the doc at > http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html > l> doesn't go into a lot of detail. I was thinking that maybe the > optimizer decided it was faster to do a sequential scan rather than an > index scan based on an analysis of the cost using these values. >=20=20 > #random_page_cost =3D 4 # units are one sequential page fetch > cost > #cpu_tuple_cost =3D 0.01 # (same) > #cpu_index_tuple_cost =3D 0.001 # (same) > #cpu_operator_cost =3D 0.0025 # (same) That's because nobody to date has done tests on the effect of tinkering wit= h=20 these values on different machines and setups. We would welcome your=20 results. On high-end machines, random_page_cost almost inevatibly needs to be lowere= d=20 to 2 or even 1.5 to encourage the use of indexes. --=20 -Josh Berkus Aglio Database Solutions San Francisco