MySQL Load Balancing between 2 or more servers?

[mds1256]

Hi

 

Just messing about with MySQL and would like to try and set up (for educational purposes) a MySQL Load Balancing environment.

 

Just like you do with Apache where you round robin requests between two or more servers for the web app, I want to also set up this for the database setup.

 

I have looked on the internet and havent found much help or answers but surely this is possible.

 

I am guessing from setting up the apache load balancer that you will need shared storage (for the database files / config files) and then also two nodes each being a DB server which is connected to the shared storage.

 

If this is not possible then how do people set up environments that need many servers due to load?

 

Thanks

[Psycho]

I have looked on the internet and havent found much help or answers but surely this is possible.

 

 

Really? I did a Google search for "mysql load balancing" and the second result was to the MySQL manual on how to set it up: http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-j2ee-concepts-managing-load-balanced-connections.html

 

It may not have all the information you need, but there should be enough to get started.

[mds1256]

Really? I did a Google search for "mysql load balancing" and the second result was to the MySQL manual on how to set it up: http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-j2ee-concepts-managing-load-balanced-connections.html

 

It may not have all the information you need, but there should be enough to get started.

 

That is more for Java connections rather than PHP. Will have a look through the manual for PHP and see if it offers the same but if anyone has any suggestions then please post here

[Psycho]

OK, this link is from the PHP manual: http://www.php.net/manual/en/intro.mysqlnd-ms.php. Perhaps this will help.

 

 

The mysqlnd replication and load balancing plugin (mysqlnd_ms) adds easy to use MySQL replication support to all PHP MySQL extensions that use mysqlnd.

Edited January 14, 2014 by Psycho

[gizmola]

MySQL really doesn't support load balancing for writes.  It does support slaves/replication for reads.  So the idea of putting a load balancer in front of a set of mysql servers isn't used from what I've seen.

 

I think what you're really looking for is:  http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

 

You can google for mysql replication and get more information. 

[mds1256]

MySQL really doesn't support load balancing for writes.  It does support slaves/replication for reads.  So the idea of putting a load balancer in front of a set of mysql servers isn't used from what I've seen.

 

I think what you're really looking for is:  http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

 

You can google for mysql replication and get more information. 

 

I see that you can do Master to Master replication and then adjust the increment offset to stop PK collisions.

 

I have also read that an idea is to have multiple web servers connected to their own MySQL db server so that said web server node will have its own dedicated db server which has a master to master replication so any updates will populate to the others.

 

Not sure if this is a good idea or not, just seems that most sites I have looked at recommend Load balancing the web servers and then they all point to ONE db server which seems a little daft as you may have a bottle neck if the DB server is overloaded.....

 

I can see how Master - Slave replication can work when most times when people are reading then it can always provide additional nodes for reading from (slaves) but not much information about a busy website that needs to perform lots of writes.......

[kicken]

Not sure if this is a good idea or not, just seems that most sites I have looked at recommend Load balancing the web servers and then they all point to ONE db server which seems a little daft as you may have a bottle neck if the DB server is overloaded.....

It's not daft, it's just the typical scenario.

 

Typically your DB server is big and beefy where as your web-servers are smaller. Your DB server, if setup properly, should easily be able to handle requests from multiple different web servers.

 

If you are at the point that a single DB server cannot handle it, then you need to determine what exactly the load is so you can address the problem. Most often the load is read-only stuff like SELECT statements. In those cases you setup one master which handles all write statements and then replicate to one or more slaves and the slaves handle all the read statements.

 

If your app is write heavy then you'll need to explore ways of addressing that. This is not an area I am familiar with so I can't say anything to specific about it. It's a situation that is not typical and few have to deal with.

Edited January 16, 2014 by kicken

[mds1256]

It's not daft, it's just the typical scenario.

 

Typically your DB server is big and beefy where as your web-servers are smaller. Your DB server, if setup properly, should easily be able to handle requests from multiple different web servers.

 

If you are at the point that a single DB server cannot handle it, then you need to determine what exactly the load is so you can address the problem. Most often the load is read-only stuff like SELECT statements. In those cases you setup one master which handles all write statements and then replicate to one or more slaves and the slaves handle all the read statements.

 

If your app is write heavy then you'll need to explore ways of addressing that. This is not an area I am familiar with so I can't say anything to specific about it. It's a situation that is not typical and few have to deal with.

 

Thanks, I see what you mean, I guess you probably do read more than you write when thinking about it.

 

Next question is how do you set up the environment where all reads are sent to the slaves automatically or do you have to programatically have to tell it to connect to the slaves when running a select and then swap connections to the master when doing an insert / update?

[kicken]

You handle it programmatically. You'd have one connection for handling reads, and a separate connection for handling writes. In you're code you'd use which ever connection is needed at the time.