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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
