Re: Benchmarck PostgreSQL 8.1.4 MySQL 5.0.20 and Oracle 10g2

What filesystem are you using - ext2/etx3/xfs/jfs/...? Does the SCSI
controller have a battery backed cache? For ext3, mounting it with
data=writeback should give you quite a boost in write performance.

What benchmark tool are you using - is it by any chance BenchmarkSQL?
(since you mention that it is JDBC and prepared statements).

Just to let you know, I've tested PostgreSQL 8.1.3 against a well-known
proprietary DB (let's call it RS for "Rising Sun") on similar hardware
(single Xeon CPU, 6Gb Ram, single SCSI disk for tables+indexes+pg_xlog)
using BenchmarkSQL and found that Postgres was capable of handling up to
8 times (yes, 8 times) as many transactions per minute, starting at 2
times as many for a single user going to 8 times as many at 10
concurrent users, consistent all the way up to 100 concurrent users.
BenchmarkSQL stops at 100 users ("terminals") so I don't know what it
looks like with 200, 300 or 500 users.

Heck, the single disk Postgres instance did even beat our RS production
system in this benchmark, and in that case the RS instance has a fully
equipped EMC SAN. (although low-end)

I personally don't care about MySQL as I don't consider it to be a DBMS
at all (breaking the consistency and durability ACID rules disqualifies
it hands-down). That company/product is one of the reasons I'm ashamed
of being swedish..

Btw, check you logfile for hints regarding increasing max_fsm_pages, and
consider increasing checkpoint_segments as well. You could also play
with more aggressive bgwriter_* params to reduce the risk for long
vacuum pauses.

- Mikael

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Olivier
Andreotti
Sent: den 18 maj 2006 11:57
To: pgsql-performance(at)postgresql(dot)org
Subject: [PERFORM] Benchmarck PostgreSQL 8.1.4 MySQL 5.0.20 and Oracle
10g2

Hello,

I'm running a benchmark with theses 3 databases, and the first results
are not very good for PostgreSQL.

PostgreSQL is 20% less performance than MySQL (InnoDB tables)

My benchmark uses the same server for theses 3 databases :
Dell Power edge - Xeon 2.8 Ghz - 2 Go Ram - 3 SCSI disks - Debian Sarge
- Linux 2.6

The transactions are a random mix of request in read (select) and write
(insert, delete, update) on many tables about 100 000 to 15 000 000
rows.

Transactions are executed from 500 connections.

For the tunning of PostgreSQL i use official documentation and theses
web sites :

http://www.revsys.com/writings/postgresql-performance.html
http://www.varlena.com/GeneralBits/Tidbits/annotated_conf_e.html

Some important points of my postgresql.conf file :

max_connections = 510
shared_buffer = 16384
max_prepared_transactions = 510
work_mem = 1024
maintenance_work_mem = 1024
fsync = off
wal_buffers = 32
commit_delay = 500
checkpoint_segments = 10
checkpoint_timeout = 300
checkpoint_warning = 0
effective_cache_size = 165 000
autovaccuum = on
default_transaction_isolation = 'read_committed'

What do you think of my tunning ?

Best regards.

O.A

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings