Re: MySQL and PostgreSQL speed compare

Just curious, what kind of tables did you set up in MySQL? My understanding is
that if you want to use the BerkeleyDB transaction support in the 3.23
beta/gamma/whatever, you need to create your tables as BerkeleyDB type rather
than ISAM. (And then the new row-level locking feature from NuSphere adds a
third table type, but that's another story...)

So if you wanted to do a true apples-to-apples comparison of databases that
supported transactions, you'd use BerkeleyDB tables in MySQL. If they were ISAM
tables, then we're just back at the same old "speed versus data integrity" flame
war that has always been the base of the MySQL/Postgres comparison.

Of course, if they *were* Berkeley tables and you still got those results, then
we'll need to dig a little deeper ;-)

Regards,
Ned

Jarmo Paavilainen wrote:

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