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)."
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 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
PostgreSQL supports these capabilities natively, without needing to resort to third-party packages. The best place to learn about setting it all up is from the PostgreSQL docs. Between the FAQ and the manuals, you should have more than enough to get you started.
> 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.
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.
Right now there is no equivalent for Postgres. Best you can do is use Slony for creating read only replicas and then send any write queries to the master database. Heres a link (though slightly old) explaining the situation.
e plication.html.
http://brianray.chipy.org/postgreSQL/cluster_vs_r
Not much has changed since then. I do think there are some 3rd party commercial apps that handle master-master replication or even shared storage but thats about it.
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 text I posted is a direct copy from the official MySQL site. If you click the underlined text it will take you to their site.
It isn't my fault that MySQL believe it.
> 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
If you have a catastrophic UPS failure you wait for the power to come back up and reload from the disk copy. MySQL Cluster regularly saves the data to disk. Or you use two places that are on different power setups and UPS systems. Or you shut down while you stil have UPS power. Or you switch to your alternate data center and its copy of the cluster.