MySQL Load Balancing Cluster Finally?
I read an article in Slashdot pointing to an article on Setting up a MySQL Load Balancing Cluster. For the longest time, MySQL has been able to only provide secondary read-only slave clusters. This way writes are only made on one MySQL server. Having a load balancing cluster opens up more possibilities for open source high performance database computing. This is made possible because of the new MySQL Cluster Storage Engine.
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).
For all the RedHat/Fedora folk out there, here is a list of locations to look at for the required RPMs:
- Ultramonkey Stuff (LVS, arptables-noarp-addr, heartbeat, etc …) - http://www.ultramonkey.org/download/3/rh.el.3/SRPMS/
- ldirectord - http://rpmseek.com/rpm-pl/ldirectord.html?hl=com&cx=2111:L:0
Note that the Ultramonkey applications (Heartbeat and Linux Virtual Server) and ldirectord are not integral parts of MySQL. This solution is not quite turn key yet. These are responsbile for providing Heartbeat services (ability to detect which nodes are available and cause highjacking when nodes are not) and load balancing. It should possible to build this solution directly using LVS, Heartbeat and ldirectord. Ultramonkey just provides a nice place to grab them all.
December 20th, 2006 at 10:01 am
The big problem with MySQL Cluster is that
1) it is an in-memory implementation (which is not necessarily bad per-se)
2) it is a very naive implementation
MySQL basically went with the philosophy “let’s put everything in memory and mirror all transactions from one node to all the other nodes, and rely on the speed of the in-memory database to ensure consistency between nodes.”
In-memory implementations aren’t the greatest because.. your data set is limited by memory size. And, most IMDB’s were originally designed as disk-based, so they make many assumptions (such as, disk is slow, we need to have a buffer cache..) which are not necessary or vastly simplified in a pure in-memory implementation.
You can see the naivete of MySQL Cluster’s implementation, if you read their manual you need to provision about 3X to 4X the RAM for a given data store, e.g. if you have 1GB of data, you need 3G to 4G of RAM, accounting for all the overhead. This is quite inefficient even on 64-bit architectures, and is a side-effect of the fact MySQL Cluster’s in-memory DB technology was not designed from the beginning as an in-memory DB.
There are several commercial IMDB competitors: ANTs, SOLID, Gigaspaces. I would guess any one of them would at least match MySQL Cluster performance wise. For a price of course.
December 20th, 2006 at 12:51 pm
interesting…. that is probably the reason why it is fast! in-memory! whew! but not very fault tolerant i guess.
since you work for larry now. i guess you can hark the benefits of his solution?