Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Basically I have 3 edge servers and 3 application servers. The edge servers are running nginx and load balance between the application servers. The application servers are running Virtualmin GPL with PHP applications deployed. All are running CentOS 5.7

At the moment MySQL is running on one of the edge servers, and is the single point of failure - I'm looking to cluster it between the application servers, but I'm just not sure what the best thing would be to do. I was thinking of running MySQL load balancers on 2 of the edge servers, then the MySQL instances on all 3 of the application servers and the MySQL cluster management server on one of the edge servers.

Does anyone have any other ideas? The problem with the aforementioned setup is the apps will be connecting to the edge servers for MySQL which could affect performance.

As I'm the developer of most of the apps I can rewrite the database abstraction in such a way it could switch load balancers (like round-robin DNS) if one was unavailable. I have some Wordpress stuff that isn't mission critical, but my site powercuts.info is the biggest and gets the most traffic, I want to make sure it performs well.

Thank you

share|improve this question

migrated from stackoverflow.com Oct 24 '11 at 20:32

This question came from our site for professional and enthusiast programmers.

1  
I think what you probably want is circular replication –  Gaius Oct 25 '11 at 10:39
    
Agree on that, plus only use ONE of the masters for actual writes and use a floating IP controlled by Pacemaker (or some such tool) to move the IP if one master fails. –  TechieGurl Aug 16 '12 at 15:44
    
Bonus points if you also add 'leaf' slaves for OLAP queries and as dedicated backup slaves but this is where money starts becoming the sticking point :) –  TechieGurl Aug 16 '12 at 15:45

1 Answer 1

It depends on how you use your database. If it is read-heavy but with few writes (like a blog or a news paper) you could have one mysql for writes and two for reads. You would set up so the write server is a replication master and the two for reads are slaves.

All application servers needs to know about both the write server and one of the read servers, that way when you balance the loads to application servers you automatically balance the reads between the mysql servers. It's also easy to add another mysql+application server once the need is bigger.

If you on the other hand have a write heavy site (I can't even find an example) you need to do some research on sharding. It's normally not recommended unless you really need it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.