Re: How to get higher tps

Hi, Mark

Thanks, here is our hardware info:

RAID 10, using 3Par virtual volume technology across ~200 physical FC
disks. 4 virtual disks for PGDATA, striped with LVM into one volume, 2
virtual disks for WAL, also striped. SAN attached with Qlogic SAN
surfer multipathing to load balance each LUN on two 2GBs paths. HBAs
are Qlogic 2340's. 16GB host cache on 3Par.

Detailed major config values

shared_buffers = 80000
fsync = on
max_fsm_pages = 350000
max_connections = 1000
work_mem = 65536
effective_cache_size = 610000
random_page_cost = 3

Marty

-----Original Message-----
From: Mark Lewis [mailto:mark(dot)lewis(at)mir3(dot)com]
Sent: Monday, August 21, 2006 5:47 PM
To: Marty Jia
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] How to get higher tps

Not much we can do unless you give us more info about how you're testing
(pgbench setup), and what you've done with the parameters you listed
below. It would also be useful if you told us more about your drive
array than just "3Par". We need to know the RAID level, number/speed of
disks, whether it's got a battery-backed write cache that's turned on,
things like this.

Like Jeff just said, it's likely that you're waiting for rotational
latency, which would limit your maximum tps for sequential jobs based on
the number of disks in your array. For example, a 2-disk array of 10k
RPM disks is going to max out somewhere around 333 tps. (2*10000/60).

-- Mark Lewis

On Mon, 2006-08-21 at 16:45 -0400, Marty Jia wrote:
> I'm exhausted to try all performance tuning ideas, like following
> parameters
>
> shared_buffers
> fsync
> max_fsm_pages
> max_connections
> shared_buffers
> work_mem
> max_fsm_pages
> effective_cache_size
> random_page_cost
>
> I believe all above have right size and values, but I just can not get

> higher tps more than 300 testd by pgbench
>
> Here is our hardware
>
>
> Dual Intel Xeon 2.8GHz
> 6GB RAM
> Linux 2.4 kernel
> RedHat Enterprise Linux AS 3
> 200GB for PGDATA on 3Par, ext3
> 50GB for WAL on 3Par, ext3
>
> With PostgreSql 8.1.4
>
> We don't have i/o bottle neck.
>
> Whatelse I can try to better tps? Someone told me I can should get tps

> over 1500, it is hard to believe.
>
> Thanks
>
> Marty
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster