Re: tuning for TPC-C benchmark

On Nov 22, 2007 9:09 AM, giuseppe-r(at)tiscali(dot)it <giuseppe-r(at)tiscali(dot)it> wrote:
> 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

As Bill said, do not publish any part of the Oracle result anywhere.

> 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 ?

I'm not sure which TPC-C kit you're using, but you should probably use DBT-2.

http://sourceforge.net/project/showfiles.php?group_id=52479&package_id=54389&release_id=485705
http://oss.oracle.com/projects/olt/

As for parameters, I'd start with:

- Make sure wal and data are split and their RAIDs (if any) are
configured properly.

shared_buffers = 98304 (this may need to stay at your current one
depending on the cost of checkpoints)
max_prepared_transactions = 5 (this doesn't have anything to do with
what it sounds like)
max_fsm_relations = 1000
bgwriter_delay = 500
wal_sync_method = open_sync (or try open_datasync)
wal_buffers = 256
checkpoint_segments = 256 (if you have the space)
checkpoint_timeout = 1800
checkpoint_warning = 1740
effective_cache_size = 346030
default_statistics_target = 100

I'm not sure whether DBT-2 supports it out-of-the-box, but you should
also look at changing default_transaction_isolation to serializable.
Keep in mind that DBT-2 has several bugs in it. Though, I'm not sure
whether Oracle fixed them on their version either.

It also looks like you have fsync turned off, which means commits are
not guaranteed (unlike your Oracle configuration). If you want
apples-to-apples, you need to turn fsync on.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/