SV: MySQL and PostgreSQL speed compare

Hi,

Ive got a few tips on what to do (turn off fsync(), could be broken
PostgreSQL from cvs). And few hints on whats wrong with MySQL (transactions
not enabled by default). Ill check these out and return to the list.

But first I want to comment a few things (marked with >>> from different
emails).

>>>Actually, if he ran Postgresql with WAL enabled, fsync shouldn't
>>>make much of a difference.

WAL seems to be enabled by default. What WAL is good for I do not know. But
if I start PostgreSQL without the -S I see a lot of info about WAL this and
WAL that.

...
> But isn't it recommended to run the server with fsync? If so,
> you shouldn't disable it on a benchmark then.

I run both MySQL and PostgreSQL as they are (minimum switches, no tuning, as
default as it can be). That is MySQL as the .rpm installed it
(--datadir --pid-file --skip-locking) and PostgreSQL with -i -S -D. Thats
the way most people would be running them anyway. And default should be good
enought for this test (simple queries, few rows (max 1000) per table).

...
> > > Well I expected MySQL to be the faster one, but this much.
...
> > To me, all this is pointing toward the possibility that you haven't
> > switched of fsync. This will make a MASSIVE difference to insert/update

The idea was to run as recomended and as default as possible. But with the
latest (alpha/beta/development) code.

...
> > And in case you cannot be bothered, add the "-o -F" parameters (IIRC) to
...
> > flushes the it's disk cache bufferes after every query. This should even
> > things out quite a lot.

Ill test that. Even thou it feels like tweaking PostgreSQL away from what
its considered safe by PostgreSQL developers. If it would be safe it would
be default.

...
> > > transaction block, and thats broken. You can not convince me of
anything else).
...
> > They are not as functionally complete as they could be, I'll give you
that.

Thanks, I think ;-)

What if I do a SELECT to check for a row. Then I do a INSERT. But between
SELECT and INSERT someone else inserted a row. NO I do not think that "good
programming" will solve this.

>>> Sir, thanks for sharing this with us. However, unless you can explain
>>> why queries inside of transactions run faster than queries outside of
>>> transactions, I would be inclined to mistrust the test. I haven't

I was suprised too. But the only difference is that I do a "BEGIN" before I
start inserting/modifying/deleting and then when Im done I do a "COMMIT".
Everything between those are exactly the same. Ive been told that MySQL does
not support transactions (by default) so there the test is broken. And with
PostgreSQL, well something inside PostgreSQL is broken (it cant be right
that with transaction PostgreSQL is 10 times faster than without).

...
> > interested to learn of your findings.

Ill update from cvs and rebuild PostgreSQL, and (try to) locate cvs of MySQL
and build it locally. And make the recomended tweaking (no fsync() but with
WAL). Ill also make sure that transactions are supported. Ill also add a
test of rollback to my test program.

// Jarmo