Date: Thu, 31 Aug 2006 11:32:00 -0600
From: Michael Loftis <mloftis@wgops.com>
To: Cosimo Streppone <cosimo@streppone.it>,
	Pg Performance list <pgsql-performance@postgresql.org>
Subject: Re: High concurrency OLTP database performance tuning
Message-ID: <F0EDCD0D4A022F6C8652D6A0@dhcp-2-206.wgops.com>
In-Reply-To: <44F7040E.7040805@streppone.it>
References: <44F7040E.7040805@streppone.it>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline


--On August 31, 2006 5:45:18 PM +0200 Cosimo Streppone 
<cosimo@streppone.it> wrote:

> Good morning,

> - postgresql.conf, especially:
>       effective_cache_size (now 5000)
>       bgwriter_delay (500)
>       commit_delay/commit_siblings (default)

commit delay and siblings should be turned up, also you'll want to probably 
increase log_segments, unless you're not getting any warnings about it. 
also increase shared_buffers.  i'd also make sure write caching is on on 
the RAID arrays as long as they're battery backed caches.

> - start to use tablespaces for most intensive tables
> - analyze the locks situation while queries run
> - upgrade to 8.1.n
> - convert db partition filesystem to ext2/xfs?
>    (now ext3+noatime+data=writeback)
> - ???
>
> Server specs:
>     2 x P4 Xeon 2.8 Ghz
>     4 Gb RAM
>     LSI Logic SCSI 2x U320 controller
>      6 disks in raid 1 for os, /var, WAL
>     14 disks in raid 10 for db on FC connected storage
>
> Current config is now (the rest is like the default):
>    max_connections = 100
>    shared_buffers = 8192
>    work_mem = 8192
>    maintenance_work_mem = 262144
>    max_fsm_pages = 200000
>    max_fsm_relations = 1000
>    bgwriter_delay = 500
>    fsync = false
>    wal_buffers = 256
>    checkpoint_segments = 32
>    effective_cache_size = 5000
>    random_page_cost = 2
>
> Thanks for your ideas...
>
> --
> Cosimo
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>


--
"Genius might be described as a supreme capacity for getting its possessors
into trouble of all kinds."
-- Samuel Butler