In-Reply-To: <44F7040E.7040805@streppone.it>
References: <44F7040E.7040805@streppone.it>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <321C8D0A-243F-48A1-AF90-C4EE37E408B4@fastcrypt.com>
Cc: Pg Performance list <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: 7bit
From: Dave Cramer <pg@fastcrypt.com>
Subject: Re: High concurrency OLTP database performance tuning
Date: Thu, 31 Aug 2006 14:13:40 -0400
To: Cosimo Streppone <cosimo@streppone.it>


On 31-Aug-06, at 11:45 AM, Cosimo Streppone wrote:

> Good morning,
>
> I'd like to ask you some advice on pg tuning in a high
> concurrency OLTP-like environment.
> The application I'm talking about is running on Pg 8.0.1.
> Under average users load, iostat and vmstat show that iowait stays
> well under 1%. Tables and indexes scan and seek times are also good.
> I can be reasonably sure that disk I/O is not the *main* bottleneck
> here.
>
> These OLTP transactions are composed each of 50-1000+ small  
> queries, on
> single tables or 2/3 joined tables. Write operations are very  
> frequent,
> and done concurrently by many users on the same data.
>
> Often there are also queries which involve record lookups like:
>
>   SELECT DISTINCT rowid2 FROM table
>   WHERE rowid1    IN (<long_list_of_numerical_ids>) OR
>         refrowid1 IN (<long_list_of_numerical_ids>)
>
> These files are structured with rowid fields which link
> other external tables, and the links are fairly complex to follow.
> SQL queries and indexes have been carefully(?) built and tested,
> each with its own "explain analyze".
>
> The problem is that under peak load, when n. of concurrent  
> transactions
> raises, there is a sensible performance degradation.
> I'm looking for tuning ideas/tests. I plan to concentrate,
> in priority order, on:
>
> - postgresql.conf, especially:
>      effective_cache_size (now 5000)
>      bgwriter_delay (500)
>      commit_delay/commit_siblings (default)
> - 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
way too low, shared buffers should be 50k
>   work_mem = 8192
>   maintenance_work_mem = 262144

>   max_fsm_pages = 200000
why ?
>   max_fsm_relations = 1000
>   bgwriter_delay = 500
>   fsync = false
you will lose data with this!
>   wal_buffers = 256
>   checkpoint_segments = 32
>   effective_cache_size = 5000
way too low should be on the order of 300k
>   random_page_cost = 2
again why ?
>
> Thanks for your ideas...
>
> -- 
> Cosimo
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>