MySQL Cluster, is it worth it?

4

I’ve been trying to figure out if a MySQL Cluster would make sense in our environment and if it’s worth the complexity. I think so but I can’t find enough information online to answer some nagging questions.

Nearly any “dynamic” web application we have is backed by MySQL and we have several “clusters” using MySQL’s replication . This gives a master read-write node and a read-only slave (and in some cases, multiple slaves) and all the apps need to know about this split. This works but has some nasty side effects – kernel upgrades require downtime, overloaded masters/slaves delay replication and impacts other databases on that pair.

And this doesn’t scale without end especially in instances were my gut says we could use more read-write heads to the same database or where we need a new slave right now but can’t take the downtime hit to rsync all of /var/lib/mysql (or whatever).

What I want is to be able to throw more machines into a cluster to increase I/O, CPU or number of client connections & queries I can handle without a lot of effort. On paper, MySQL Cluster looks like it’ll do that but I’m left with a bunch of questions:

  1. What do I do if disk I/O is my contention? CPU?
  2. If I need more capacity on my read-write heads, do I just add more SQL nodes? Do my apps just need to know of multiple heads?
  3. How are simultaneous database writes (updates/inserts) handled? Will I run into locking issues?
  4. How critical is the management node? If it stops running, what breaks?

I’m hoping someone out there’s been through these issues and can point me in the right directions. Ping me offline – mrz at mozlla .

Categories: Mozilla

4 responses

  1. Callek wrote on :

    mrz, I’d be interested in what you find out about this, whether you update this post, make a new post, or simply add a comment (if you add a comment, I would appreciate a ping so I can know to read it).

    Thanks in advance.
    ~Justin Wood (Callek)

  2. Archon810 wrote on :

    Hi mrz. Bret Reckard pointed me to this post after finding out that somewhere in the depths of beer planet I was writing a MySQL cluster setup guide.

    I’ll try to answer some of your questions in the post, but feel free to read this modest article: http://beerpla.net/2007/11/05/setting-up-a-mysql-cluster/.

    1 and 2. I think that in general, if you do a lot of CPU intensive selects, you should add more SQL nodes. If, however, you do a lot of inserts/updates, you should consider adding more data nodes. Another important point is to try to isolate the cluster and put it onto its own switch and subnet as network latency is a big factor of how fast the cluster is going to perform.

    3. The cluster is fully A.C.I.D. compliant, unlike replication, where a slave can fall off and stupidly get behind. It fully supports transactions, which I’m a big fan of. If you’re having problems with transactions deadlocking, consider tweaking the transaction related cluster properties.

    4. is answered in the guide. In short, nothing breaks, but no new nodes can (re)join the cluster if the management node isn’t up.

    Let me know if there is anything in the guide that can be more clear, or if there are any mistakes or omissions.

    Respect to Mozilla what everything you’re doing.
    Archon810.

  3. Archon810 wrote on :

    s/Mozilla what/Mozilla for/

  4. yasso wrote on :

    If amdinistrator node failed, the standby node can becomes the administrator node of the cluster, you can look at MYSQL_Cluster_and_Lexst_cluster_comparison