Hey there!

I am looking into using multiple servers for an upcoming project. How do big sites manage to do this? I want 2 SQL servers incase one goes down and to spread server loads. But whats the most efficient way to do this? The databases must synchronise, if one goes down its not useable untill it has been synchronised with the other. Is oracle the way to go? Or can I use MySQL? What about hosting PHP files on multiple servers? What do I have to look out for?

Cheers in advance!

Hi Sam,

Are you building the setup yourself or paying for hosting? A lot of web hosts will have clustered networks that take care of this for you.

Check out Cal Henderson's 'Building scalable web sites' O'Reilly Media | Building Scalable Web Sites

Will be sure to check that book out! Planning on placing on own servers.

Yo can do replication in MySQL 5 so no need to spend a fortune on Oracle. It might be a little easier to manage in Oracle though!

Dependant on the application needs, you can setup the slave to read only and have server pull some of the data it needs from it to spread the load. All updates should go thru the master though.

Depending on what other hardware you have you can setup a load balancer port to automatically redirect the traffic to the slave if the master goes down, or some people prefer the switch to be a manual process the operations team controls.

If you're going to have multiple webserver the one thing you have to look out for is how you handle sessions. Most sites I've worked with have what you'd call sticky sessions, when you first visit the website you get routed to one of the server in the farm, all subsequent request go back to that same server, so if you have a site that requires logins, you don't have to worry about sharing sessions between the webserver. The other way to handle this is to move the session information to MySQL or some other kind of shared location.

I haven't read Cal Henderson's book, but I've seen some of his presentations from conferences and he definitely knows what he's talking about :)

Mubs

Ok, awesome! I guess if its possible in MySQL 5, thats the way to go for me :). I will purchase that book, think it will help a lot!

Ok iisbum, you say all db updates should go to the master - how does the slave know when the master is down - just check if connection cannot be made? And how would I get the master to first of all get updated details from slave when back up without going online straight away receiving data? (to prevent 2 semi-different db's?)

When I last did this, we had a manual cut over to the slave, which usually only happened when something really bad happened. The master was taken out of service entirely until we could diagnose what was wrong with it. We'd then manually sync things up and restore the the master into service. Its probably possible to automate much of this now, but I haven't need to do use replication recently.

We did have a "read only" mode for the webapp, so that if the master went down the website would still work reading content from the slave but no updates to the content could be made until we did our manual cutover, or the temporary problem with the master was resolved.

Mubs