Re: MySQL and PostgreSQL speed compare

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

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Gordan Bobic" <gordan(at)freeuk(dot)com>
To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)org>
Sent: Friday, December 29, 2000 7:31 AM
Subject: Re: [GENERAL] MySQL and PostgreSQL speed compare

> > Well I expected MySQL to be the faster one, but this much.
> >
> > Inserts on MySQL : 0.71sec/1000 rows
> > Inserts on PostgreSQL: 10.78sec/1000 rows (15 times slower?)
> > Inserts on PostgreSQL*: 1.59sec/1000 rows (2 times slower?)
> >
> > Modify on MySQL : 0.67sec/1000 rows
> > Modify on PostgreSQL: 10.20sec/1000 rows (15 times slower?)
> > Modify on PostgreSQL*: 1.61sec/1000 rows (2 times slower?)
> >
> > Delete on MySQL : 1.04sec/1000 rows
> > Delete on PostgreSQL: 20.40sec/1000 rows (almost 20 times slower?)
> > Delete on PostgreSQL*: 7.20sec/1000 rows (7 times slower?)
> >
> > Search were almost the same (MySQL were faster on some, PostgreSQL on
> some),
> > sorting and reading sorted entries from dba was the same. But
> > insert/modify/delete.
>
> 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
> queries. Read the docs on how to do this, and what the implications are.
> And in case you cannot be bothered, add the "-o -F" parameters (IIRC) to
> your postgres startup line in the postgres startup script in
> /etc/rc.d/init.d.
>
> Then try running the benchmark again and re-post the results. On a machine
> with that much memory, allowing proper caching will make a huge
difference.
> I think MySQL does that by default, but PostgreSQL tries to be cautious
and
> flushes the it's disk cache bufferes after every query. This should even
> things out quite a lot.
>
> > "PostgreSQL*" is postgres whith queries inside transactions. But as long
> as
> > transactions are broken in PostgreSQL you cant use them in real life (if
> a
> > query fails inside a transactions block, PostgreSQL "RollBack"s the
whole
> > 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.
> But if you are sticking to good programming (and this applies to more than
> just SQL) practices, you should make sure that your code behaves properly
> and checks for things before going in head long. It can be slower, but it
> is a lot cleaner. In the same way you check for a zero-return when using
> malloc in C, and clean up all compiler warnings, or run-time warnings in
> perl, you sould consider doing, for example, a SELECT query to make sure
> that the records are/aren't already there before inserting them or
updating
> them.
>
> Just MHO. Yes it is slightly slower, but it does work, and it is a lot
> neater than fillijg up the error logs with all sorts of garbage.
>
> > Then I thought that maybe it would even up if I made more than one
simul.
> > call. So I rewrote the utility so that it forked itself several times.
> With
> > PostgreSQL I could not try the test with transactions activated
> > (transactions are broken in PostgreSQL, and the test shows it clearly).
> > PostgreSQl maxed out my CPU with 5 connections, MySQL used around 75%
> with
> > 20 connections. At five connections MySQL was 5 times faster, with 20
> > connections it was 4 times faster.
>
> [snip]
>
> > MySQL on a SCSI disk.
> > PostgreSQL on a IDE disk. I moved the "data" dir to the SCSI disk and
> > tested. Suprise suprise it was slower! Well PostgreSQL was as nice as
> > MySQL towards the CPU when it was on the SCSI disk.
>
> I thought the CPU hit was strange. This exaplains it...
>
> Re-try the test with the fsync() disabled and re-post the results. I'm
> interested to learn of your findings.