Slashdot Mirror
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)."
One of the major problems with a number of useful open source projects is that their names are terrible for the corporate environment. This is a perfect example of such a project. I sure wouldn't want to pitch the use of this software to a CTO or CIO, even if it was perfect for our database cluster, just because it is called 'Ultra Monkey'. That's just not a professional name, and this product will likely not be taken seriously.
This is for the MySQL Cluster storage engine, describing how to set up load balancing across two MYSQL servers that serve as front ends to it.
If you want code to set up a MYSQL cluster (cluster of MYSQL servers NOT using the Cluster storage engine) you might do something like looking at the PHP code in MediaWiki, which does application-level load balancing for reads (not writes) and has so far been tested to 25,000 or so queries per second. Or you could use the same director tools and skip using them for writes, implementing failover for the master server getting the writes.
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.
The MySQL Protocol is proprietary.
o tice.html
The MySQL Protocol is part of the MySQL Database Management System. As such, it falls under the provisions of the GNU Public License (GPL). A copy of the GNU Public License is available on MySQL's web site, and in the product download.
Because this is a GPL protocol, any product which uses it to connect to a MySQL server, or to emulate a MySQL server, or to interpose between any client and server which uses the protocol, or for any similar purpose, is also bound by the GPL. Therefore if you use this description to write a program, you must release your program as GPL. Contact MySQL AB if you need clarification of these terms or if you need to ask about alternative arrangements.
http://dev.mysql.com/doc/internals/en/licensing-n
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;
/etc/sysconfig/network-scripts/ifcfg-eth0 and ifcfg-lo:x
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
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.
"Don't let fools fool you. They are the clever ones."
> 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.
Seems backwards, but this is probably because mysql bought a product and has had to figure out how to bolt persistence onto it. A much simpler approach is to go the direction of most commercial databases: provide fine-tuned memory controls that easily ensure that the most-used data is kept in memory.
In this scenario (with a database like db2 or oracle) you simply organize tables into tablespaces, dedicate however much memory you want to each tablespace. If you want a table to live in memory - just give it enough memory and it will. If a different table is 100 GB in size, then give it as much memory as you want and the database will manage the memory cache accordingly.
With a solution like the above you don't need completely separate products, but instead see clustering, in-memory databases, and reliable persistence as all just different aspects of a robust and mature database.
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?
--
make install -not war
Cluster was designed for the telecoms case where you need to authenticate your subscriber and verify that they are entitled to make a call now even if they haven't made a call for a month, doing it in the same time window as the other calls. Also to scale out to as many servers as it takes to handle either load or data volume while delivering that predictable real-time performance. In a non-telecoms case, one user is capturing in real time performance data from thousands of vehicles for analysis, exploiting the write rate scaling and reliability.
You can get some of that by allocating data space to RAM on one server but that doesn't get you all of Cluster's properties. Cheaper if you can use the approach you gave, of course, just doesn't do the job for some situations. Same for the Memory engine approach I outlined - doesn't get you all Cluster provides, but is enough for some situations.
Like the other MySQL storage engines, you don't use the Cluster engine if you don't need its properties. It's a pick the right engine for the job situation.
I use a Monkey Management Technique for my mySQL servers. If one server goes down, I have a monkey that jumps up and down at the datacenter and gets real mad until someone fixes it.
.. Works like a charm!
I heard that you need to have enough RAM on the SQL nodes to hold your entire database on each cluster machine so that NDB will work. I did not see this info in the front page. I think it's critical to know.
> The mysql clustering is pretty much bunk anyways. All of the DBs need to live in RAM. What happens if you have a catastrophic UPS failure?
... unfortunately no one's seriously built up that infrastructure.
It was designed for read-only telecom use, with real time writes from "upstream" propogated down. Basically, a cache. Local writes aren't super-critical in this case, because whatever causes a failure to write probably takes out the rest of the node anyway.
It's not really a general purpose SQL database, it just happens to let mysql provide some front-end services. Personally I consider it a drag on an otherwise decent distributed cache engine (mysql isn't exactly synonymous with bulletproof) but I suppose it does gain some exposure.
Postgres provides a MUCH more flexible infrastructure than MySQL for doing real clustering and replication