Master to Master Replication with MySQL / MaxScale

[envo]

Hi guys, hope someone in here has some knowledge of this. Was wondering, I heard that master to master replication and using Maxscale from MariaDB to split the read/write loads would help in terms of load and reliability.

I've seen some feedback where this messes with sessions, since my own CMS doesn't use sessions, but Wordpress might, and I'd like to host my friends and my own servers.

Was wondering if anyone had first hand experience in this?

 

[itareanlnotani]

shared memcache probably going to be an idea. memcache on one node.

eg php settings somewhat like
session.save_handler = memcache
session.save_path = "tcp://envo's.memcached.server:11211"

Not sure on your setup though.
Assuming standard cluster setup, of load balancer -> node / node... within a single data center or vm?

WordPress doesn’t use PHP session storage though, it uses cookies to manage sessions.
May be the odd plugin that "does it differently" though.


Suggest read some of the clustering links for a better overview. They're mostly oriented to master /slave topologies though.

eg

http://docs.jelastic.com/wordpress-cluster
http://www.markomedia.com.au/scaling-wordpress-in-amazon-cloud/ (ECS oriented, but some useful bits in there).
http://blog.clusterfoundry.com/p/highly-available-load-balanced-wordpress-cluster/


Probably better you explain your setup a little more so that someone can give you a little more detail on how to do what you need.
Without that, you're only going to get a generic style answer.

L.

 

[envo]

I have no setup. So can't explain something that doesn't exist. "I heard" & "I've seen" <> My setup

I'm busy designing a setup of scalable MySQL servers. Somewhere where I can split the writes from the reads using something like MariaDB's Maxscale possibly using a Master - Master setup to begin with, adding more master's or slaves as loads increase.

The thing I've seen is one caveat of the Master - Master setup is sessions. Which I generally don't understand why that would be an issue since the PHP session would be handled on the Nginx server?

Wondering how best to approach this issue as it needs to scale.

I can't very well invest $1000's in a cluster as I only have 3 people interested in using my server (which is 1 MySQL database and an nginx server, but since I saw potential that more people might be interested in hosting with me, need to think about scalability and backups)

 

[itareanlnotani]

I can't very well invest $1000's in a cluster as I only have 3 people interested in using my server (which is 1 MySQL database and an nginx server, but since I saw potential that more people might be interested in hosting with me, need to think about scalability and backups)

Why invest $1000's in a cluster? Doesn't cost anywhere near that much.

Talking about a Master / Master setup with Maxscale would heavily imply you have multiple servers in an HA config, in the same data centre or possibly vps, or if you have enough ip's then docker or similar vm's.

Why wouldn't you cluster, especially if you want it scalable?

And pricing? Its not that expensive. I personally use digitalocean as a provider.
Its fairly cheap to setup a small cluster there - eg one load balancer, and then multiple nodes can be done fairly cheaply - 15-20$ a month if size reqs are smallish. They also offer backups and snapshots.

I'm fairly happy with them, I have a few small sized servers with them.

If you want to signup you can use my aff link if you're feeling generous


EC2 is also another option, you can script new nodes on the fly. More expensive than Digital Ocean though.



--

The thing I've seen is one caveat of the Master - Master setup is sessions. Which I generally don't understand why that would be an issue since the PHP session would be handled on the Nginx server?

The issue is that php sessions are stored locally on the cluster node, so you need to use memcache or database based sessions in order to share between all nodes, or configure nginx to use ip-hashing (see http://nginx.org/en/docs/http/load_balancing.html )
Again, Wordpress uses session cookies, so thats not really an issue for wordpress per se.


To be honest though, from your answer, I'm not sure you actually need to do this, unless you want to learn how to.
Going to assume you've read this -
https://www.percona.com/blog/2015/0...ool-to-solve-your-mysql-scalability-problems/


Lastly, less snide remarks, and more thank you for trying to help you ask the questions you need to ask yourself.
My questions are things you should know as it impacts setup / configuration / structure...

 

[envo]

No snide remarks, I thought I was pretty clear that I'm thinking about/reading up on it. So if I came across as ungrateful for the insight, then I apologize. I think your questions asked me stuff I wouldn't know how to answer as I'm still trying to find a possible solution. So there's not even a picture in my head how a setup like this would work. I'm no dba, and this is doubling as a learning curve as well as something I want to grow as a side-line perhaps.

The only reason I was considering using Maxscale to split read/writes to a Master-Master-Slave setup is because someone mentioned how expensive setting up a Cluster would be vs this. But if you say it's quite cheap to do from the get-go, I can't see why not do it the cluster way.

Would the session issue also be a problem with a Cluster setup? Because then I can use my existing MariaDB and scale outwards (if that's easy enough to do)

Can you also do cross-geo clusters? Like have one in San Francisco talking to one in New York talking to one in London?

*edit

Thanks for being the only one who seems to know what I want to do more or less

 

[envo]

Looking at this again, I see that I'm running MariaDB 10.0.22 which (as I understand) is Galera as well, I just need to setup the Cluster, so the potential for growing is already there, the only other thing I'd need is haproxy or some kind of load balancer, but I'm thinking I should use MaxScale on the application server itself as that can also scale out horizontally if I need it to without having to change much (if any) code. I would, however, need a load balancer in front of my application servers if I do intend to do that, so busy enquiring with my cloud partner if their IP addresses are able to move from one to another without impacting existing DNS setups, that way I can easily move over to a load balancer and scale, and the IP will be served by 2 or more balancers

 

[Ben_Dover]

Looking at this again, I see that I'm running MariaDB 10.0.22 which (as I understand) is Galera as well, I just need to setup the Cluster, so the potential for growing is already there, the only other thing I'd need is haproxy or some kind of load balancer, but I'm thinking I should use MaxScale on the application server itself as that can also scale out horizontally if I need it to without having to change much (if any) code. I would, however, need a load balancer in front of my application servers if I do intend to do that, so busy enquiring with my cloud partner if their IP addresses are able to move from one to another without impacting existing DNS setups, that way I can easily move over to a load balancer and scale, and the IP will be served by 2 or more balancers

We've had some funky things happen to us when using Galera cluster. When it breaks, it _really_ breaks bad.
With a Master-Master Setup, your best option (if it's possible) is to setup the read/write in code. What we do on our setup is create 2 vrrp ip addresses with the master of each address being each mysql node. Then in code we have a read mysql connection and a write mysql connection pointing to each vrrp ip. This will give you redundancy if a node goes down and allow you to split read/writes.

As for MaxScale, it will act as a proxy for your connections and the best howto i've found on it has been - https://mariadb.com/kb/en/mariadb-e...le-connection-routing-with-mysql-replication/

The reason we use the "in code" method instead of something like Maxscale is because it's one more thing which can fail between your code and your DB.

 

[envo]

We've had some funky things happen to us when using Galera cluster. When it breaks, it _really_ breaks bad.
With a Master-Master Setup, your best option (if it's possible) is to setup the read/write in code. What we do on our setup is create 2 vrrp ip addresses with the master of each address being each mysql node. Then in code we have a read mysql connection and a write mysql connection pointing to each vrrp ip. This will give you redundancy if a node goes down and allow you to split read/writes.

As for MaxScale, it will act as a proxy for your connections and the best howto i've found on it has been - https://mariadb.com/kb/en/mariadb-e...le-connection-routing-with-mysql-replication/

The reason we use the "in code" method instead of something like Maxscale is because it's one more thing which can fail between your code and your DB.

Thanks for your feedback.

Can you give me a use-case of why/when Galera broke and how you ended up fixing it? (disaster recovery etc.)

What makes MaxScale attractive is, that if you don't have access to change the source code, that it can give you Cluster ability without having the source to be ready. Something like hosting Wordpress where you don't want to customize the code to use separate read/write connections.

However, this may make sense for my own custom CMS I wrote. I think I've decided against a Master-Master setup, but a Cluster instead (requiring minimum 3 nodes). I'm busy investigating slave-lag and how it would affect performance if the cluster is spread over several geographically different data centers. One thing I'm keeping in mind (and is probably a pipe dream for now) is the ability to route traffic to the closest geographical location of the user, much like Cloudflare, but keeping the data consistent across all nodes.

I have some big plans, but need to start small and need to keep in mind my overall plans as to not shoot myself in the foot as I scale and implement these features

 

[Ben_Dover]

Thanks for your feedback.

Can you give me a use-case of why/when Galera broke and how you ended up fixing it? (disaster recovery etc.)

What makes MaxScale attractive is, that if you don't have access to change the source code, that it can give you Cluster ability without having the source to be ready. Something like hosting Wordpress where you don't want to customize the code to use separate read/write connections.

However, this may make sense for my own custom CMS I wrote. I think I've decided against a Master-Master setup, but a Cluster instead (requiring minimum 3 nodes). I'm busy investigating slave-lag and how it would affect performance if the cluster is spread over several geographically different data centers. One thing I'm keeping in mind (and is probably a pipe dream for now) is the ability to route traffic to the closest geographical location of the user, much like Cloudflare, but keeping the data consistent across all nodes.

I have some big plans, but need to start small and need to keep in mind my overall plans as to not shoot myself in the foot as I scale and implement these features

We had issues where by nodes would randomly detach themselves from the cluster, in a 3 node setup, you need at least 2 nodes connected in order to re-attach a node, provided there has been no data writes to the detached node. If you have 2 nodes detach you end up with a split brain situation and none of them will reattach. You then need to pick 1 server, bring down the entire cluster and start it up from scratch and sync data from the elected node to the other members.

We then tried a 4 node setup and found we kept running into state snapshot transfer failures and when this happens, the node which has detached can not reattach to the cluster without you either restarting the cluster or doing a mysqldump and shifting it across to the detached node.

In the end it proved to be more problems than it was worth. Master-master replication has worked well for us and has been running problem free for more than a year since inception. We use Percona for this because XtraBackup is a god send. Admittedly we haven't tried percona's XtraDB Cluster, which is based on Galera, you might want to check it out.

For geographical routing you would need to look at a split horizon DNS implementation - http://www.caraytech.com/geodns/

 

[oldBastard]

We had issues where by nodes would randomly detach themselves from the cluster, in a 3 node setup, you need at least 2 nodes connected in order to re-attach a node, provided there has been no data writes to the detached node. If you have 2 nodes detach you end up with a split brain situation and none of them will reattach. You then need to pick 1 server, bring down the entire cluster and start it up from scratch and sync data from the elected node to the other members.

We then tried a 4 node setup and found we kept running into state snapshot transfer failures and when this happens, the node which has detached can not reattach to the cluster without you either restarting the cluster or doing a mysqldump and shifting it across to the detached node.

In the end it proved to be more problems than it was worth. Master-master replication has worked well for us and has been running problem free for more than a year since inception. We use Percona for this because XtraBackup is a god send. Admittedly we haven't tried percona's XtraDB Cluster, which is based on Galera, you might want to check it out.

For geographical routing you would need to look at a split horizon DNS implementation - http://www.caraytech.com/geodns/

I can vouch for that. Percona has been a God send and I can't imagine doing a mysqldump of TB's of data vs xtrabackup.

 

[envo]

Thanks for the recommendations.

When did you try Galera? Did you hear back from the team at MariaDB as to why this seemed to happen so frequently (or even why it happened to begin with)? As I understand their response is quite good as well as the releases and features they bring out.

How much data did you put on your Cluster (now Master-Master) and is all that data on one DB or several?

As I'm planning to host several wordpress sites, their DB's shouldn't grow much at all (depending on plugins of course), but it would be good to note how much data and/or if this issue was addressed in a newer release yet.

Do you have an effective way to replicate your issue so I can test on my test-setup I have going?

I've heard good things about Percona, at the moment leaning towards MariaDB instead though. Of course, your experience speaks volumes. So still in 2 minds about this.

 

[gkm]

I am also surprised by what Ben said. My experience with Percona Cluster (Galera cluster based) has been that things worked very smoothly. There were no problems with nodes leaving and joining the cluster. I am not with the company any more, but last I heard it was still running.

One thing that is however clearly stated in the docs is that one must always run a cluster with an uneven number of nodes (3, 5, 7, 9 etc), otherwise one can end up with a split brain situation (1 vs 1 split or 2 vs 2 split etc) in which case the cluster stops, since no side can elect a majority, which is the correct behaviour for this kind of situation. So, a 4 server cluster is a big no-no.

I would recommend using the tools from Severalnines to help to set up the cluster. It includes a nice UI to monitor the cluster etc.

Master-Master replication is much simpler, however then one can only have one writable master, so if that fails, one has to safely switch to the other master, which can be tricky. Any RO slaves that was replicating from the dead master is then also dead and often has to be rebuilt, so long term in my opinion Master-Master replication can end up being more work. One then also has to implement some form of heart-beating to be able to detect slaves replicating from a dead master, so that one's application can automatically stop using them etc.