DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=jsqjOtcT4n0XBPBby5ZYc40Grf+Bp2SkKdOSPGS6GlDQDmDIEeStpbsOMaLxn5PYQJT2O+QrHC6lKilCK/3Mhb+kmBm/K+6Rz9S+x/MTuWhweybiDI2be1z3rCB61uGrd1Z2vz0sPkZ3l6IXNh1DlLCrPHfToL4EeeTgyp6yt8k=
Message-ID: <4745D701.2090209@enterprisedb.com>
Date: Thu, 22 Nov 2007 19:22:41 +0000
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.14pre (X11/20071020)
MIME-Version: 1.0
To: giuseppe-r@tiscali.it
CC: pgsql-performance@postgresql.org
Subject: Re: tuning for TPC-C benchmark
References: <25297330.1195740592813.JavaMail.root@ps21>
In-Reply-To: <25297330.1195740592813.JavaMail.root@ps21>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

giuseppe-r@tiscali.it wrote:
> Hello all,
> I'm doing tests on various Database and in particular I'm running a 
> comparison between Oracle 10g and Postgres 8.1 on a dedicated server 
> with 2 processors Dual-Core AMD Opteron 2218  2.6 GHz, 4GB of memory 
> and Debian GNU / Linux version 2.6.18-5. Performance is very similar up 
> to 30 users, but from 40 onwards with Postgres fall quickly. That is 
> not what happens with Oracle that comes to 600 users. Can you help me 
> with the tuning ?

The fact that you didn't give any details on your I/O configuration 
tells me that you don't have much experience with TPC-C. TPC-C is 
basically limited by random I/O. That means that a good RAID controller 
and a lot of disks is a must. Looking at some of the results at 
www.tpc.org, systems with 4 cores have multiple RAID controllers and 
about a hundred hard drives.

You can of course run smaller tests, but those 4 cores are going spend 
all their time waiting for I/O. See for example these old DBT-2 results 
I ran to test the Load Distributed Checkpoints feature in 8.3.

Now that we got that out of the way, what kind of a test configuration 
are you using? How many warehouses? Are you using the think-times, per 
the spec, or are you running something like BenchmarkSQL which just 
pushes as many queries it can to the server?

I'm not sure what you mean by # of users, but you shouldn't use more 
than 10-30 connections on a test like that. More won't help, because 
they'll all have to queue for the same resources, whether it's I/O or CPU.

How long tests are you running? After some time, you'll need to run 
vacuums, which make a big difference.

8.3 will perform better, thanks to HOT which reduces the need to vacuum, 
varvarlen which reduces storage size, leading to better use of the cache 
and less I/O, and Load Distributed Checkpoints, which reduce the 
checkpoint spikes which otherwise throw you over the response time 
requirements.

And last but not least, why are you running the benchmark? It's going to 
be practically irrelevant for any real application. You should benchmark 
with your application, and your data, to get a comparison that matters 
for you.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com