Re: MySQL and PostgreSQL speed compare - Mailing list pgsql-general

> 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.