I have some warm up questions on how MySQL handles LOBs internally and then some questions on high availability. Especially with HA I feel like I have heard all/most the solutions, but they all seem to lack in one area that really makes you hurt, but I keep stumbling over questions from people asking me which way to go. Finally I have a little backup question for extra credit at the end.

So for the LOB questions, I am wondering if MySQL (or any of the storage engines) store the actual LOB data on separate data pages or not. I am also wondering if the MySQL query cache has any special handling for LOBs, like ignoring all queries that fetch LOB data. I guess there is a setting to set the maximum size for a result set to go into the query cache that could be employed to prevent a few large LOBs to fill up the query cache, but then again you might block large but processing intensive result sets to make it into the query cache.

So now for the HA question. I frequently see setup's where you have 2 datacenters with a handful of frontend servers each. Most of the requests are read only. For the session stuff you probably want to use Memcache cluster per datacenter, which means you sessions need to be sticky on a co-location basis. The tricky bit is handling the non session related writes of course.

Option #1
Probably what you want is to prevent the frontend servers in one location to all have to read from a master in the other location. Same goes for writing, so ideally you would have master-master replication, with one master in each data center and automatic fail-over if one of the masters goes down. In this setup you keep the amount of data flowing between data centers to the absolute minimum and you also spare your frontend slaves from having to write to a master in another data center.

Option #2
If doing master-master replication requires too much changes to the application you might opt to instead turn one of the masters into a slave (from which the frontend slaves can read, instead of having to read from the master in the other data center), which would however have to be able to automatically promote itself to all frontend slaves as master, should the original master go down. With this setup you would still have automatic failover and you would lessen the read load on your master that now only needs to handle all the frontend servers in its data center, plus that one "masterslave" in the other data center.

So what are you guys using in terms of tools to make the above options possible? What solutions to you employ to handle automatic failover? If you just use a simple hearbeat solution to failover to the other master (or "masterslave") I presume the binarylog numbers could/will not match. I guess this is what you need MMM for?

Now the next topic in all of this is that in order to reduce the risk of a master going down I could use a clustering solution. For option #2 I might opt to only use a cluster in the "main" data center which has the default master. Why would want prefer MySQL Cluster over solutions like DRBD or Continuent Cluster? I assume that using DRBD and Continuent Cluster that if one of the servers in the cluster "decides" to write crap, then this is happily distributed across the cluster, right? Is this a real world problem? Also from what I heard the main issue with MySQL Cluster is that you need quite a lot of servers (3 in the cluster, 2 sql nodes and a management server in order to provide real HA) and that adding new servers to the cluster requires taking down the cluster. I also wonder how well all of the solutions handle single servers going down and coming back up, which would require re-syncing (I heard this is an issue with MemCache). AFAIK MySQL Cluster handles this nicely.

So now for the extra credit. I have actually not worried too much about backups myself in past projects. Mostly we could just accept some table locking while using mysqldump during a slow period. In others we just relied on a slave for backup purposes. Recently someone asked me if there is a way to prevent a situation where one would backup corrupt blocks when using a slave for backup which has written corrupt blocks. I never really worried about such a situation, but I guess it could happen, in which case you might be backing up copies from that slave onto CD that are partially corrupt. I guess one could run queries to fetch every single piece of data from the slave ..?

Ok so Roland told me on IRC that you do not need 3 data nodes after all. Actually the recommended is to use powers of 2 as the number of data nodes. What you do indeed need is an arbitrator, which however is a low level job that can easily be put on the management node.

Also MySQL Cluster seems to have pretty poor join performance, since the joining has to be done on the SQL node, while the data obviously resides on the data nodes. So it seems to me that MySQL Cluster is really not a performance choice for most setups. It provides automatic failover and once you successfully commit, you know for sure that it has distributed as many copies of your data as you configured for your cluster. MySQL Cluster is only fast for single table (primary key) lookups. You can also scale SQL nodes nicer than in a replication setup, since you do not bog down a master with an ever growing number of slaves as you scale up. But considering the performance issues with joins, I guess it really only helps for very niche type of applications.

So would you say that HA is better done by good old master slave replication?

Is there really such a big performance problem with MySQL Cluster?

The documentation reads nice. But without good performance I will have to think about some more time what solution I should set up... :-(

MySQL Cluster will be slow, especially considering the amount of hardware you have to throw at it (until 5.1 comes out all your data will have to be in memory).

The other day I stumbled upon an entry in the MySQL docs that explains how you can do master-master replication with autoincrement without having to do any changes to your application. This makes this kind of setup much more attractive.

This basically only means that there might be issues with "lag". One problem is that inserts on one master may not be immediately available on the other master in the other data center. However this is a non issue for the most part if you make your session stick to one data center, which is what most people do anyways. The other issue of lag is that slaves in the same data center might not see the data that was inserted through the web interface immediately either. The only way out from that is using stuff like the "oracle algorithm" to ensure that data makes it to the slaves quickly.

What I also like about the master-master setup is that you can build it up one by one. In step one you can just build up that second data center to use the same master as the first data center. In a next step you can at least add a slave that replicates frm the master in the first data center, that then will be used by the slaves in the second data center for replicating. Next you turn that slave into a hot slave that can take over if the original master goes down. In the final step you turn things into a true master-master setup.

Hi,

I have cluster setup. But problem is performance . In the normal setup , DML operation is very fast , but in clustering concept it is slow...so please give ur suggestion..

@Anish: As stated above, the issues you are seeing are just the nature of the beast. Getting your performance "back" will likely require changing your application, which may or may not be feasible. Apparently MySQL Cluster likes single table primary key lookups. So if you can get by with reimplementing joins in your middle tier with some caching love, you might end up being quite happy with the performance again, while benefiting from all the nice HA features of MySQL Cluster.