Skip site navigation (1) Skip section navigation (2)

Re: MySQL+InnoDB vs. PostgreSQL test?

From: Adam Ruth <aruth(at)intercation(dot)com>
To: josh(at)agliodbs(dot)com
Cc: pgsql-advocacy(at)postgresql(dot)org, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: MySQL+InnoDB vs. PostgreSQL test?
Date: 2004-02-02 18:06:24
Message-ID: 83927B58-55AA-11D8-99C4-000A959D1424@intercation.com (view raw or flat)
Thread:
Lists: pgsql-advocacypgsql-performance
Josh,

I evaluated MySQL + InnoDB briefly for a project, once.  I didn't get 
very far because of some severe limitations in MySQL.

I had to import all of the data from an existing database (MS SQL).  
One of the tables was about 8 million rows, 10 fields, and had 5 
indexes.  I found it quite impossible to import into MySQL.  I would 
import the data into a table with no indexes, then perform a bunch of 
manipulation on it (I wasn't just converting from MS SQL, but also 
needed to alter quite a bit of the structure).  After the manipulation, 
I would drop some columns and build the indexes.  It took MySQL over 4 
days to do this!

What I found out was that any DDL changes to a table in MySQL actually 
does this:  create a new table, copy all of the data over, then drop 
the old table and rename the new one.  Whenever I added a new index, 
MySQL would go through the process of rebuilding each previous index.  
Same thing when adding or dropping columns.

I could not find a way to import all of the data in a reasonable amount 
of time.  For comparison, it took less that 45 minutes to import all of 
the data in to PostgreSQL (that's ALL of the data, not just that one 
table).

Needless to say (but I'll say it anyway :-), I didn't get any farther 
in my evaluation, there was no point.

One more thing that annoyed me.  If you started a process, such as a 
large DDL operation, or heaven forbid, a cartesian join (what?  I never 
do that!).  There's no way to cancel it with InnoDB.  You have to wait 
for it to finish.  Hitting ctrl+c in their command line tool only kills 
the command line tool, the process continues.  Even if you stop the 
database and restart it (including with a hard boot), it will pick 
right up where it left off and continue.  That proved to be way too 
much of a pain for me.

Disclaimer:  I'm not a real MySQL expert, or anything.  There could be 
ways of getting around this, but after two weeks of trying, I decided 
to give up.  It only took me a few hours to build the requisite 
PostgreSQL scripts and I never looked back.

Adam Ruth

On Feb 2, 2004, at 10:21 AM, Josh Berkus wrote:

> Folks,
>
> I've had requests from a couple of businesses to see results of 
> infomal MySQL
> +InnoDB vs. PostgreSQL tests.    I know that we don't have the setup 
> to do
> full formal benchmarking, but surely someone in our community has gone
> head-to-head on your own application?
>
> -- 
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
>
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>


In response to

Responses

pgsql-performance by date

Next:From: scott.marloweDate: 2004-02-02 18:30:36
Subject: Re: "Overlaping" indexes
Previous:From: Josh BerkusDate: 2004-02-02 18:06:23
Subject: Mainframe Linux + PostgreSQL

pgsql-advocacy by date

Next:From: Robert TreatDate: 2004-02-02 20:14:08
Subject: Re: MySQL+InnoDB vs. PostgreSQL test?
Previous:From: Josh BerkusDate: 2004-02-02 17:21:12
Subject: MySQL+InnoDB vs. PostgreSQL test?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group