Re: High concurrency OLTP database performance tuning

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