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@postgresql.org
[mailtogsql-performance-owner@postgresql.org] On Behalf Of Olivier
Andreotti
Sent: den 18 maj 2006 11:57
To: pgsql-performance@postgresql.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/postg...rformance.html
http://www.varlena.com/GeneralBits/T...ed_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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"Mikael Carneholm" <Mikael.Carneholm@WirelessCar.com> writes:
> 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.

Yeah, checkpoint_segments is a really critical number for any
write-intensive situation. Pushing it up to 30 or more can make a big
difference. You might want to set checkpoint_warning to a large value
(300 or so) so you can see in the log how often checkpoints are
happening. You really don't want checkpoints to happen more than about
once every five minutes, because not only does the checkpoint itself
cost a lot of I/O, but there is a subsequent penalty of increased WAL
traffic due to fresh page images getting dumped into WAL.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Hello everybody !

Thanks for all the advices, iI will try all theses new values, and
i'll post my final values on this thread.

About the benchmark and the results, i dont know if can publish values
about Oracle performance ? For MySQL and PostgreSQL, i think there is
no problems.

Just a last question about the pg_xlog : i understand that the
directory must be moved but i have just 3 disks for the database :
disk 1 and 2 for the data, disk 3 for the indexes, where can i put the
pg_xlog ?

OA.

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

"Olivier Andreotti" <olivier.andreotti@gmail.com> writes:
> Just a last question about the pg_xlog : i understand that the
> directory must be moved but i have just 3 disks for the database :
> disk 1 and 2 for the data, disk 3 for the indexes, where can i put the
> pg_xlog ?

If you have three disks then put the xlog on one of them and everything
else on the other two. Separating out the indexes is way less important
than getting xlog onto its very own spindle (at least for
write-intensive cases).

regards, tom lane

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