logo
Apache Lounge
Webmasters

 

About Forum Index Downloads Search Register Log in RSS X


Keep Server Online

If you find the Apache Lounge, the downloads and overall help useful, please express your satisfaction with a donation.

or

Bitcoin

A donation makes a contribution towards the costs, the time and effort that's going in this site and building.

Thank You! Steffen

Your donations will help to keep this site alive and well, and continuing building binaries. Apache Lounge is not sponsored.
Post new topic   Forum Index -> Other Software View previous topic :: View next topic
Reply to topic   Topic: MySQL Load Balancing Clusters or Replication Masters Slaves
Author
C0nw0nk



Joined: 07 Oct 2013
Posts: 241
Location: United Kingdom, London

PostPosted: Fri 24 Jun '16 22:25    Post subject: MySQL Load Balancing Clusters or Replication Masters Slaves Reply with quote

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.)

Code:
http {
#Http server settings etc here.
}
stream {
upstream db {
server 1.1.1.1:3306; #1st MySQL server
server 2.2.2.2:3306; #2nd MySQL server
server 3.3.3.3:3306; #3rd MySQL server
server 4.4.4.4:3306; #4th MySQL server
}

server {
    listen 3306; #192.168.0.1:3306 becomes the MySQL connection address for our application
    server_name 192.168.0.1; #The load balancing IP that will redirect to a random MySQL server

    location / {
        proxy_pass db;
    }
}
}



But I have not tested any of this in production so I would really love to hear from others to know what their solutions are if any or if they have even encountered the need for multi MySQL servers in different locations that can share load and replicate the same data across all of them.

Thanks in advanced to anyone who reads that and has any advice or recommendations.
Back to top
James Blond
Moderator


Joined: 19 Jan 2006
Posts: 7288
Location: Germany, Next to Hamburg

PostPosted: Sat 25 Jun '16 22:36    Post subject: Reply with quote

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.
Back to top
C0nw0nk



Joined: 07 Oct 2013
Posts: 241
Location: United Kingdom, London

PostPosted: Sat 25 Jun '16 23:50    Post subject: Reply with quote

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 ?
Back to top
James Blond
Moderator


Joined: 19 Jan 2006
Posts: 7288
Location: Germany, Next to Hamburg

PostPosted: Sun 26 Jun '16 18:30    Post subject: Reply with quote

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.
Back to top
C0nw0nk



Joined: 07 Oct 2013
Posts: 241
Location: United Kingdom, London

PostPosted: Sun 26 Jun '16 18:48    Post subject: Reply with quote

Thanks James Very Happy thats useful and nice Smile 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
Back to top


Reply to topic   Topic: MySQL Load Balancing Clusters or Replication Masters Slaves View previous topic :: View next topic
Post new topic   Forum Index -> Other Software