Topic: MySQL Load Balancing Clusters or Replication Masters Slaves

[C0nw0nk]

Download Link : http://dev.mysql.com/downloads/

So this is gonna be a fun topic for those of us on Windows, Since we all know the bottle neck for slow site speeds majority of the time is MySQL, If you have ever witnessed High MySQL CPU usage and PHP start giving of max_execution time exceeded errors then you know you have got a bottle neck. (Your slow query log will be catching all queries too when that happens)

The best way I found to use MySQL was to download it directly from their website using their installer tell it to install for "Production".

The problem I have is the fact that with Joomla but it would not matter if it was Joomla or not because we all know that CMS's like drupal etc already have their queries optimized and all index keys assigned etc on tables where it gives the best performance. It is the fact that you can only take so much traffic before your MySQL server can not process any more on a single machine. So the solution is simple right lets utilize multiple machines CPU abilities with a form of load balancing for MySQL.

But then the snag I found is what is the best method for Load Balancing simple Joomla or Drupal InnoDB tables because there is allot of different methods to load balancing with MySQL you have MySQL's community clustering, You can do a Master-Master replication, Replication of master(s) and slave(s) there is allot of different solutions but none seem to be explained of what would be best suited for your specific database needs.

So I thought I would ask here to see what other peoples solutions or recommended solutions to enable utilizing of multiple servers resources for load balancing MySQL. This site is full of systems administrators dev's learners new comers and professionals as a system admin running any website you should always plan ahead and be prepared to solve problems like this and for all my research i have found that an Multi Master setup could be the key to solve the problem. But I really don't know.

Multi Master looks like this : And as MySQL percona explained "Multi-Master replication is the usual circular replication where you can write on any server and data gets replicated to all others."


So the way this could be used is as follows.

The LoadBalancer I use as a example is Nginx because it is my favourite proxy.

Load Balancing IP : 192.168.0.1 (This is where we will send all MySQL requests from any application and it will be redirected to a random MySQL server but all MySQL servers have the same data replicated across them so it should not matter in theory what one deals with it.)

[James Blond]

I tried master slave. But that wasn't a solution since the slave is read only. Then I tried master master with 2 servers. Was fine until I shut one down for maintaning it. The sync after that slowed both servers down ( each time I tried). And I don't forget the desaster of separation. In the end I took a dump from the night before and installed fresh...

The best solution ( except of horizontal scaling) was to use http://dev.mysql.com/downloads/cluster/

Sure SSD drives made a lot of speed.

Also the DB format can improve the speed. If you have mainly read connections MyISAM is a good choise. For more write than read innoDB is faster. However InnoDB isn't that crash save and you can't copy the the file to a nother servers as a backup but you have to tun a dump.

I never heard of using a "self made" proxy with a multimaster solution so far.

[C0nw0nk]

Thanks for the info will give it a test I was just seeing if Nginx had the ability to load balance for MySQL and that was what i found people have been doing is using Nginx stream to get the job done.

But i think if i am not gonna do multi master replication and i go with clustering i thought clustering gives me its own address to connect to what uses some built in load balancing method ?

[James Blond]

With the clustering you have a single IP / name to connect to. And yes it does load balacing inside and also does the replication for you.

[C0nw0nk]

Thanks James thats useful and nice so what concurrency and max selects read writes etc do you see with it do I also have to change my tables over to "ndbcluster" or is MyISAM fine for it. I read somewhere i think it was MySQL's website documentation all tables have to be on the "ndbcluster" engine. I know i can easly alter all my tables to MyISAM since Joomla is fully compatible with both InnoDB and MyISAM tables, and i changed all my tables from MyISAM to InnoDB in the first place, I have never tested any of my Joomla tables on the engines other than InnoDB and MyISAM so i know they can work with MyISAM but could be compatibility conflicts where il have to alter fields and table column / row types TEXT blob fields etc but that is only if it has to be "engine=ndbcluster".

//Edit :
I read that it can do 200 Million queries per second and more not sure if that is updates selects read writes or anything specific.
https://dev.mysql.com/tech-resources/articles/mysql-cluster-7.4.html