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@xxxxxxxxxxxxx] Sent: Tuesday, August 22, 2006 11:47 AM To: Marty Jia Cc: pgsql-performance@xxxxxxxxxxxxxx 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
