Re: How to get higher tps - Mailing list pgsql-performance

Ron

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

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.

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

Thanks


-----Original Message-----
From: Ron [mailto:rjpeace@earthlink.net]
Sent: Tuesday, August 22, 2006 11:47 AM
To: Marty Jia
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How to get higher tps

At 04:45 PM 8/21/2006, 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

All of this comes =after= the Get the Correct HW (1) & OS (2) steps.
You are putting the cart before the horse.

>I believe all above have right size and values, but I just can not get
>higher tps more than 300 testd by pgbench

300tps on what HW?  and under what pattern of IO load?
300tps of OLTP on a small number of non-Raptor 10K rpm HD's may actually
be decent performance.
300tps on a 24 HD RAID 10 based on Raptors or 15Krpm HDs and working
through a HW RAID controller w/ >= 1GB of BB cache is likely to be poor.

>Here is our hardware
>
>
>Dual Intel Xeon 2.8GHz
>6GB RAM

Modest CPU and RAM for a DB server now-a-days.   In particular, the
more DB you can keep in RAM the better.
And you have said nothing about the most importance HW when talking
about tps:  What Does Your HD Subsystem Look Like?
.

>Linux 2.4 kernel
>RedHat Enterprise Linux AS 3
Upgrade to a 2.6 based kernel and examine your RHEL-AS3 install with a
close eye to trimming the fat you do not need from it.  Cent-OS ot Ca-Os
may be better distro choices.


>200GB for PGDATA on 3Par, ext3
>50GB for WAL on 3Par, ext3
Put WAL on ext2.  Experiment with ext3, jfs, reiserfs, and XFS for
pgdata.

Take a =close= look at the exact HW specs of your 3par.to make sure that
you are not attempting the impossible with that HW.
"3par" is marketing fluff.  We need HD specs and RAID subsystem config
data.

>With PostgreSql 8.1.4
>
>We don't have i/o bottle neck.
Prove it.  Where are the numbers that back up your assertion and how did
you get them?


>Whatelse I can try to better tps? Someone told me I can should get tps
>over 1500, it is hard to believe.
Did they claim your exact HW could get 1500tps?  Your exact HW+OS+pg
version+app SW?  Some subset of those 4 variables?
Performance claims are easy to make.  =Valid= performance claims are
tougher since they have to be much more constrained and descriptive.


Ron