I have started trying out Galera Cluster[1] for MySQL, to replace a single MySQL server node with 3-4 nodes, all synchronously replicated. This should hopefully solve the problem with having to split writes to the master and reads to the slaves, and provide redundancy in case of a server going down.
Does anyone have any experience with Galera in a production environment? Is the setup in this article preferable to that?
I have some experience with Galera. It's pretty slick, but you probably won't want to split writes. It technically works, though you can run into some unexpected deadlocks due to optimistic locking on remote nodes vs. pessimistic locking on the write node[1]. What you really don't have to worry about is replication being behind and loosing data if you lose a host.
On the other hand, your overall throughput is constrained by the network, since all commits must at least ping all of the nodes.
Which setup are you referring to? MySQL 5.5, or MySQL NDB cluster?
Thanks, I greatly appreciate the link! The deadlock scenario does not look good, however I think it won't be a problem in this particular application. But since a single server easily handles the load, I will definitely take the safe route and not split the writes, and just use the other db nodes as failover and for reading.
I was initially set on using NDB cluster to provide failover, but the table constraints (tables can't be too wide) means that there would have to be code changes. I recently set up and tried Galera, and everything "just worked". The application currently uses standard MySQL 5 with Innodb, and going with Galera would mean that no code change would be necessary.
It just sounded too good to be true though, so I just wanted to know that people actually use this in production. The application handles payments, so corrupted or rows not committing on all nodes would be a huge problem.
Comments
I have started trying out Galera Cluster[1] for MySQL, to replace a single MySQL server node with 3-4 nodes, all synchronously replicated. This should hopefully solve the problem with having to split writes to the master and reads to the slaves, and provide redundancy in case of a server going down.
Does anyone have any experience with Galera in a production environment? Is the setup in this article preferable to that?
[1] http://codership.com/content/using-galera-cluster
I have some experience with Galera. It's pretty slick, but you probably won't want to split writes. It technically works, though you can run into some unexpected deadlocks due to optimistic locking on remote nodes vs. pessimistic locking on the write node[1]. What you really don't have to worry about is replication being behind and loosing data if you lose a host.
On the other hand, your overall throughput is constrained by the network, since all commits must at least ping all of the nodes.
Which setup are you referring to? MySQL 5.5, or MySQL NDB cluster?
[1]http://www.mysqlperformanceblog.com/2012/08/17/percona-xtrad...
Thanks, I greatly appreciate the link! The deadlock scenario does not look good, however I think it won't be a problem in this particular application. But since a single server easily handles the load, I will definitely take the safe route and not split the writes, and just use the other db nodes as failover and for reading.
I was initially set on using NDB cluster to provide failover, but the table constraints (tables can't be too wide) means that there would have to be code changes. I recently set up and tried Galera, and everything "just worked". The application currently uses standard MySQL 5 with Innodb, and going with Galera would mean that no code change would be necessary.
It just sounded too good to be true though, so I just wanted to know that people actually use this in production. The application handles payments, so corrupted or rows not committing on all nodes would be a huge problem.