X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4C93244E465 for ; Mon, 27 Dec 2004 18:07:24 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94071-10 for ; Mon, 27 Dec 2004 18:06:56 +0000 (GMT) Received: from quasar.skima.is (quasar.skima.is [212.30.200.205]) by svr1.postgresql.org (Postfix) with ESMTP id 9E2FF44E488 for ; Mon, 27 Dec 2004 18:06:54 +0000 (GMT) Received: from gnari ([157.157.175.183] [157.157.175.183]) by quasar.skima.is with ESMTP; Mon, 27 Dec 2004 18:06:52 Z Subject: Re: Howto Increased performace ? From: Ragnar =?ISO-8859-1?Q?Hafsta=F0?= To: postuser@spr.og.th Cc: Iain , pgsql-performance@postgresql.org In-Reply-To: <50978.192.168.2.17.1104161471.squirrel@192.168.2.17> References: <2452.192.168.2.6.1103621509.squirrel@192.168.2.6> <00cb01c4ebb3$94aebf10$7201a8c0@mst1x5r347kymb> <45410.192.168.2.17.1104139293.squirrel@192.168.2.17> <003a01c4ebf7$503de6a0$7201a8c0@mst1x5r347kymb> <50978.192.168.2.17.1104161471.squirrel@192.168.2.17> Content-Type: text/plain Date: Mon, 27 Dec 2004 18:08:39 +0000 Message-Id: <1104170919.23792.16.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200412/298 X-Sequence-Number: 9673 On Mon, 2004-12-27 at 22:31 +0700, Amrit Angsusingh wrote: > [ iain@mst.co.jp ] > > > > These are some settings that I am planning to start with for a 4GB RAM > > dual > > opteron system with a maximum of 100 connections: > > > > > > shared_buffers 8192 (=67MB RAM) > > sort_mem 4096 (=400MB RAM for 100 connections) > > effective_cache_size 380000(@8KB =3.04GB RAM) > > vacuum_mem 32768 KB > > wal_buffers 64 > > checkpoint_segments 8 > > > > In theory, effective cache size is the amount of memory left over for the > > OS > > to cache the filesystem after running all programs and having 100 users > > connected, plus a little slack. > I reduced the connection to 160 and configured as below there is some > improvement in speed . > shared_buffers = 27853 [Should I reduce it to nearly as you do and what > will happen?] at some point, more shared buffers will do less good than leaving the memory to the OS to use as disk buffers. you might want to experiment a bit with different values to find what suits your real-life conditions > sort_mem = 8192 > vacuum_mem = 16384 > effective_cache_size = 81920 [Should I increase it to more than 200000 ?] as Iain wrote, this value is an indication of how much memory will be available to the OS for disk cache. when all other settings have been made, try to see how much memory your OS has left under normal conditions, and adjust your setting accordingly, if it differs significantly. I have seen cases where an incorrect value (too low) influenced the planner to use sequential scans instead of better indexscans, presumably because of a higher ratio of estimated cache hits. > Thanks for any comment again. > > NB. There is a huge diaster in my country "Tsunamies" and all the people > over the country include me felt into deep sorrow. my condolescences. > Amrit Angsusingh > Thailand gnari