Re: tuning for TPC-C benchmark

giuseppe-r(at)tiscali(dot)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