MySQL and PostgreSQL speed compare - Mailing list pgsql-general

Hi,

I wrote a piece of benchmarking, just to test my classes, and was suprised
of the speed diffs.

So one more entry to the flame war (or the start of a new one) about which
one is faster, PostgreSQL or MySQL.

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.

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

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.

I do not claim that this is accurate, maybe my classes are broken or
something, or the test might be totally wrong. But *I think* it simulates
quite well a ordinary webserver running the dba locally (on the same server
as the www-server).

The setup is:

PII 450MHz with 256MByte memory.
Linux Redhat 6.0 (almost out of box).
MySQL, latest .rpm (a few weeks ago).
PostgreSQL, from CVS tree (HEAD, a few weeks ago).
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.
Used gcc to compile PostgreSQL, using only the --prefix when
./configur(ing).

If you like to run the test (or view the code), download DBA-Test and AFW
package from my site (www.comder.com). No fancy configure scripts exist so
you have to modify the code to make it run on your system.

Comments? Reasons for the result? What was wrong with the test?

I do not want to start a flame war. Just need help to get PostgreSQL up to
speed or MySQL to support sub-selects.

// Jarmo

PS. Posted this to MySQL and PostgreSQL list. Want to hear both sides. DS.