Skip site navigation (1) Skip section navigation (2)

Re: High concurrency OLTP database performance tuning

From: Michael Loftis <mloftis(at)wgops(dot)com>
To: Cosimo Streppone <cosimo(at)streppone(dot)it>,Pg Performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: High concurrency OLTP database performance tuning
Date: 2006-08-31 17:32:00
Message-ID: F0EDCD0D4A022F6C8652D6A0@dhcp-2-206.wgops.com (view raw or flat)
Thread:
Lists: pgsql-performance

--On August 31, 2006 5:45:18 PM +0200 Cosimo Streppone 
<cosimo(at)streppone(dot)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

In response to

pgsql-performance by date

Next:From: Merlin MoncureDate: 2006-08-31 17:42:06
Subject: Re: High concurrency OLTP database performance tuning
Previous:From: Guillaume SmetDate: 2006-08-31 17:06:29
Subject: Re: High concurrency OLTP database performance tuning

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group