How To Set Up A Load-Balanced MySQL Cluster

hausmasta writes "This tutorial shows how to configure a MySQL 5 cluster with three nodes: two storage nodes and one management node. This cluster is load-balanced by a high-availability load balancer that in fact has two nodes that use the Ultra Monkey package which provides heartbeat (for checking if the other node is still alive) and ldirectord (to split up the requests to the nodes of the MySQL cluster)."

sounds cool, but MAJOR drawbacks!

[Anonymous Coward]

from the article:

"All data is stored in RAM! Therefore you need lots of RAM on your cluster nodes. The formula how much RAM you need on ech node goes like this:

(SizeofDatabase × NumberOfReplicas × 1.1 ) / NumberOfDataNodes

So if you have a database that is 1 GB of size, you would need 1.1 GB RAM on each node!

- The cluster management node listens on port 1186, and anyone can connect. So that's definitely not secure"

Re:sounds cool, but MAJOR drawbacks!

[Jamesday]

The sort of thing people use the MySQL Cluster storage engine for include session management and telecoms. That data tends to be of modest size, even for a company with many millions of customers. If you don't mind losing the data if there's a power failure longer than your UPS life you can use the Memory (Heap) storage engine instead of Cluster. You can often back that up wth triggers and reload in a startup script.

The RAM only bit will go away once MySQL 5.1 is released. It's currently in early beta. It doesn't mean you lose your data if there's a power failure; the data is backed up to disk regularly.

This isn't very elegant lb'ing MySQL cluster

[layer3switch]

No MySQL sessions are carried over from failed node, so all MySQL connections will be dropped and need to re-establish again. For sessionless Apache or other fast and short connections may make this very useful, but for application service with shared connection pool, a DNS RR with node check makes better choice rather than elaborate setup using lb nodes in front of MySQL cluster due to +2 lb nodes administration overhead. And also because of arp problem due to MAC on loopback broadcasting, the article forgets to mention;

net.ipv4.conf.all.arp_announce = 2
net.ipv4.conf.all.arp_ignore = 1

Should take care of all interface arp announce/ignore. Doesn't need to set net.ipv4.conf.eth0.arp_ seperately as the article provides. Or easier way to control it is to set ARP=no on /etc/sysconfig/network-scripts/ifcfg-eth0 and ifcfg-lo:x

Personally I rather have Active/Active lb nodes in front and carry over sessions from failed node to active node transparently by using shared memory space to replicate all session info. OpenMosix comes to mind.

Without wishing to start a flamewar...

[m50d]

Anyone have a guide for doing this kind of thing with postgresql?

MyPostgres?

[Doc Ruby]

Anyone have an equivalent HowTo for Postgres clusters?

And while I'm getting everything in life I casually ask for, where's the SW that automatically swaps out a MySQL install and replaces it with Postgres, including revising source code that calls/queries the DB, or just uses a MySQL installation as a proxy replica for Postgres nodes in a mixed cluster?

Re:'Ultra Monkey'?

[RookKilla]

I'm with you on that one. Well, i'm a CTO, and i don't care about the names, but sometimes the initiative starts "from the bottom", so to speak, and sometimes needs to be run by not-so-open-minded people who happen to write the check. Unless the person wants to start company-wide project of opening minds prior to initiating something like "Ultra Monkey", he/she has to do something about the name. In this very case, i'd call the software "UM", i guess, if i were working in a "good old corporation" and had to submit an official project proposal, and stick to "UM" in all latter documentation. The problem with projects like this is the fact that they are relatively unknown. While Google, RedHat and others are names you can find all over the WSJ and NYTimes, projects like this are not known to general public, including the gray-haired conservative guys in financial departments that are writing the checks. At least those a my thoughts.