Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Working on finding a solution to a particular problem we have at the office,we have a web host and at times our web server (Apache) crashes; which causes our web app to be unavailable to users. Usually as a result of the number of users, we noticed that there is a rise in the number of database connections at peak times. Option 1: So we were thinking of getting a server solely to server as the database server(mysql) and another as as our web server on the same host. Q:Is there a better way to solve this problem while using a single server? Q:is there anyway to manage the number of connections to the database server without causing any serious issues for the users? Option 2: We were thinking of getting another server (i.e. one with both apache and mysql running), in the case where the first becomes unavailable, all the traffic switches to the second. The issue here is that mysql only supports 1 way replication (i.e. master-slave). Instance: normally the slave mirrors the database in the master, in a situation where there is a fail over,and a user performs a write action on the slave, when the master comes back up, how can we also replicate all the writes in the slave to the master for the sake of consistency( without which a user might find out that some data he saved previously seems not to be available, apart from primary key conflicts). Q:How can this be implemented, to cater for this need?

Also, would really love any suggestions on solutions (This problem is a new one for us).

share|improve this question
2  
Why not try to find out why Apache crashes? Fix the root cause, not the symptoms... –  user18682 Mar 18 '11 at 9:47
add comment

migrated from stackoverflow.com Mar 18 '11 at 13:31

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

3 Answers

instead of master-slave setup, you could try setting up master-master replication ...

share|improve this answer
add comment

You could also look at using memcached to ease the hits to your database

share|improve this answer
add comment

Circular replication MASTER-MASTER can be a pain and cause more problems than it's supposed to solve. How would you handle a split-brain situation? Normally you increment the IDs in a specific intervall. Master A increments 10,20,30 while Master B increments in 11,21,31 etc. But I don't recommend doing it more complicated than it needs to be. It doesn't sound like your too experienced in DBA so KISS. :)

You really should look into why your server crashes instead of "solving" it with replication. Have you checked the logs?

Look into lighttpd which has better performance than apache in high-load scenarios.

Too "manage" connections (there's also a my.cnf setting for max connections) to the SQL server you need to either make the SQL server finish faster, or reduce the number of queries the webservers makes. Caching is the answer to both. Memcached for application level cache (highly effective), and more ram in general for SQL server for qcache.

Fix the issue (f.ex more ram is needed), and then add Master-Slave replication for redundancy later. This is also gives the benefit that you can spread out the READ IOPS over multiple hosts. If you REALLY need hot-standby redundancy think of adding multi-master replication when you're comfortable down the road.

Also look into optimizing slow queries. Slow_queries.log is helpful for this.

Good luck!

share|improve this answer
    
Thanks @LonelyLonelyNetworkN00 –  Okeke Emmanuel Oluchukwu Mar 24 '11 at 11:31
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.