Subject: Re: Postgres performance
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: mauro <bertolima@yahoo.it>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <d64dda1e.0503010252.67c2312d@posting.google.com>
References: <d64dda1e.0502230229.3deaf864@posting.google.com>
	<421D8ABC.2030601@archonet.com>
	<d64dda1e.0503010252.67c2312d@posting.google.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1109775613.30529.359.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Wed, 02 Mar 2005 09:00:14 -0600

On Tue, 2005-03-01 at 04:52, mauro wrote:
> > Not always, AFAICT. The four most common reasons why PG tests slower 
> > than Mysql are:
> > 1. You haven't configured or have misconfigured PostgreSQL.
> > 2. You are testing a MySQL-tuned application (lots of small, simple 
> > queries, no views, no subselects etc)
> > 3. You are only testing one connection (try 10,20,50 simultaneous users 
> > and see who wins then).
> > 4. You are not testing the transaction-safe storage systems in MySQL
> > 
> > See if you can answer some of the questions above and I'm sure we'll be 
> > able to get your database server running smoothly.
> Hi, 
>   I've used the benchmark
> http://genecensus.org/yeast/cluster/database/mysql/sql-bench/ (ok,
> it's without bench on views, sub-select, transaction,..)
> The database files are in stripe (RAID 0) on two SATA hd (transfer
> rate 50Mb), the filesystem is reiserfs (3.6 format - with checks off),
> no optimation on I/O scheduler, DBMS are in default configuration (so
> I don't benefit nobody). Total time:
> Pgsql: 7h 20'
> MySQL: 14' (!!)

Why is a dragster faster than a freight train?  Because it only has to
run for 5 or 6 seconds and you expect the engine to implode on ever
fourth run.  The freight train, on the other hand, has to run day after
day and deliver its cargo without damage.

The reason MySQL can be so fast is that it's not really a database in
the classical sense.  It does floating point maths on exact numeric
types.  It does almost no error checking, and if you lose power during
updates all your data could quite easily be gone.

While it's a fine storage system for certain content management tasks,
it's not reliable enough for things like accounting or where the answers
have to be right.

The reason PostgreSQL is slower is because it (and by extension the team
behind it) cares about your data. 

Here's a list of the things MySQL will gladly do wrong:

http://sql-info.de/mysql/gotchas.html

I wouldn't trust such a database for an kind of mission critical system
that handled important data, and anyone who does is gambling against the
house odds.