Re: High concurrency OLTP database performance tuning

--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