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
>
