A blogger's "Five Months With MySQL Cluster"

"We discovered early on with cluster that we would have to redesign our application. Our DB was highly relational. Almost no data could be put on the site without data from other tables. We used a lot of joins. We learned (later) that joins in the cluster are not a good idea. Neither are sub-selects."
[runs away]

To be fair, he did then say: "So, we wrote some proof of concept scripts for our application. We were very happy. Very few issues were found. Nothing anywhere near show stopping."

I'm just not too keen on working around those little things called foreign keys, joins and such.

http://doughboy.wordpress.com/2007/06/07/five-months-with-mysql-cluster/

Yikes...

I am evaluating database clustering solutions. At the moment I am reading the MySQL documentation. I'm very skeptical but hey, I want to give it a fair chance.

The more I read though, the scarier it looks. Here are some of the "unsupported or missing features":

# Foreign key constraints. The foreign key construct is ignored, just as it is in MyISAM tables.
# Savepoints and rollbacks. Savepoints and rollbacks to savepoints are ignored as in MyISAM.
# Durability of commits. Commits are replicated, but there is no guarantee that logs are flushed to disk on commit.

http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations-unsupported-missing.html

On other pages we have such gems as:

# Online adding or dropping of data nodes is not currently possible. In such cases, the entire cluster must be restarted.

But they also claim MySQL gives you "99.999%" uptime, or "five 9s" uptime. How they arrived at this number is beyond me, especially if you can't add nodes (what if one machine dies and you need to replace it?).

Like I said...yikes.

Small Post About Transactions or "Why, Postgres, Why??"

Anyone who knows RDBMSs knows the importance of transactions. It's often good to use them even if you're running a few administrative DML commands to fix a bug. In Oracle, you're pretty much forced to use them (unless you do something silly like run all queries from JDBC with autocommit enabled). Postgres has good transaction support, but you must explicitly "begin transaction" from psql (command line app).

Occasionally I run into this very frustrating issue. If you get an error, ANY error, Postgres automatically brings your transaction into an error state - not even rolled back - in which your transaction is dead, kaput.

So, today I was running a big delete followed by two big inserts. Beforehand I ran BEGIN TRANSACTION in case of boo-boos. After a while the inserts finished. Let's check out the results before committing.......

test_stage=>
select * from securityid limit 100 order by random();
ERROR: syntax error at or near "order"
LINE 1: select * from securityid limit 100 order by random();

Whoopsie, I reversed the position of the limit and order by clauses. Let's fix that right up...

test_stage=>
select * from securityid order by random() limit 100;
ERROR: current transaction is aborted, commands ignored until end of transaction block

NOOOOOOO!!! At this point nothing but rollback will work. Why, Postgres, why? It was just a typo - a syntax error! Sigh...

A bit of silliness

On AskTom, there's a huge thread that's been running for 5+ years called Comparison between Oracle and Others. As one might expect, the debates get a bit "religious" and some posters try to put down the Oracle database. That's fine, but a few sites are linked to that make ridiculous claims about Oracle, really only proving that the authors don't understand the database. Some claims are unsubstantiated, some make zero sense. For example, Oracle is too expensive and has a high TCO, or you "no real numeric data types". (???) I've seen similarly ridiculous trashings on Slashdot such as, this post that claims Oracle focuses only on "ruining successful companies"(because destroying customers is a good business model, you know) - http://slashdot.org/comments.pl?sid=208928&cid=17040380

Anyway, I decided to have a little fun and post my own list of Oracle gripes. Total joke, though some people didn't seem to get it. Perhaps my satire is too subtle. :) Here it is:

Major flaws in Oracle that I've found:

1) I tried installing Oracle on my Commodore 64 and it crashed. I reported the problem to support and they said I had to upgrade. Those jerks.
2) The alert log keeps getting bigger. This is a security flaw because eventually the disk will get full and Oracle will hang.
3) The "undo tablespace" is a worthless feature that just slows down the database. Support said I should try putting it on another disk. I'm really starting to hate those guys.
4) No support for null primary keys.
5) I need a table with 1001 columns and Oracle only supports 1000. Tom Kyte says there's something wrong with my design. What an insult.
6) I deleted all the data in the table and it still took up space.
7) Because TOAD exists, every GUI tool Oracle offers is horrible.
8) Everything you do is logged, whether you like it or not, wasting an average of 12.7499 GB/min according to www.microsoft.com/fairandbalanced/whyoracleisevil.php
9) Oracle charges you $300 per row returned. One time I accidentally cross joined instead of inner joined and I had to file for bankruptcy.
10) In SQL*Plus, you can't run Java commands unless you create a "Java Stored Procedure" and even then you're forced to call a static method. Static methods are so 80s. They went out with The Culture Club and velcro sneakers. Get with the times, Oracle.
11) I can't get dirty reads with Oracle. It should be a standard database feature for me to be able to read your incomplete transaction. If you're transferring $1000 from your savings to checking account, I should be able to see your savings down by $1000 before checking is increased by $1000. This way the check I'm trying to clear bounces even though you have the money. Oracle arrogantly breaks this standard. Stay away from this awful software.
12) Data types are all screwed up. Dates are stored as numbers and you can't specify precision or format, and you can't even cast a long raw to a rowid.
13) In fact, VARCHAR3 and 4 don't even work AT ALL.
14) I tried modifying Oracle data files in Windows Notepad and the database broke. Support told me you can't do that with Oracle...Obviously the software is inflexible in its methods of data access.
15) It's slow even when I index every column.
16) Try returning a hash table of 2D arrays of cursors pointing to user passwords in Oracle. Can't do it.
17) No one would ever use Oracle for a serious business. Sure Blizzard uses Oracle for its 8.5 million user MMORPG, but that's the exception, not the rule. They should have used MySQL and saved money.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1886476148373#236100400346362272

Quote from a new guy

At work we've got a relatively new developer, call him Rob, who has been working on a Postgres database for about a month now. The other day, Rob groaned in frustration:

"I've worked with Oracle for nine years, and only in the last year did I find a few things I had to work around. I've been working with Postgres for one month, and I've already had to write more workarounds."

Rob said this after he found he could not create an array of composite types in Postgres. Tom Lane post that says you can't create such an array.

I had to smile because his complaint resonated with me. It reminded me of how I felt about a year ago when I started on Postgres. One workaround after another, or a feature that I had grown accustomed to in Oracle was nowhere to be found in Postgres. Now that I've been working with Postgres for a good year I can appreciate that it is a pretty damn good open source database. I'll write more about what I like about Postgres another time.

First Post

Wow, it's been a year since I created this blog, and I still haven't written anything. I've been meaning to compose some brilliant post with a great big mindblowing database conclusion, such as why Hibernate is no good, or deeply compare Oracle to Postgres. Instead I am going to just start writing what comes to mind... I would like to keep a log of ideas, experiments and work experiences. Hopefully any discussion in comments will be insightful and free of name-calling.

Mark