Re: tuning for TPC-C benchmark

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



giuseppe-r@xxxxxxxxxx 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
      message can get through to the mailing list cleanly

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux