Home / server / Questions / 296113
In Process
Will
Asked: 2011-08-01 15:42:43 +0800 CST

Multiple servers using a single database?

  • 772

I have never done something like this. I currently am running a forum and service which rely on the same database on the same server but as they grow it has become apparent that 1 box is not enough. Plus this will be good experience for when I add on to it even more.

So I have a forum (vbulletin) and a wcf service which uses the user table to get user information when they log into the service.

What should I do for splitting them apart? I imagine having the service connect remotely would be slow. I have read about replication but I am not really sure how to set that up. Also does replication support only doing a single table? As I don't need it replicating the entire database.

I was thinking about getting a stable 3rd server for hosting the mysql database that way if the forum server goes down the service server will still work. But how would that work? Can replication go both ways? Like changes to the replicated database on the forum server will be sent to the database server.

mysql

2 Answers

  1. The web application scaling path is:

    1. Everything on the one box
    2. Get bigger box
    3. Split database onto it's own box
    4. Get bigger database box
    5. Optimise database (could be done earlier, but people don't understand the value a good database optimisation can provide)
    6. Shard database, or sell your soul to the replication Gods

    Somewhere in there you'll also probably have "Get load balancer and more webapp boxes", but that's not the important bit here. Note, also, that this is the scaling path, not the high availability path, so replication and redundancy don't come into it.

    You're currently at either step 1 or 2. You're a long way from step 6, and you want to keep it that way -- sharding sucks, and replication will suck away your will to live.

    If, as you say in your comments to BenGC, you can't get two boxes in the same rack and physical network segment, get a decent hosting provider. Billy Bob's Servers & All-you-can-eat Crawfish Hut can do that (although Rackspace, notably, has problems with it -- hence the bulging vein on my temple), so you shouldn't have to make accomodations to suit your hosting provider's lack of competence.

    • 5

  2. The cost of shipping your queries over a LAN to another server will be negligible (think a couple milliseconds). I would say for you the best thing to do is go with that third server and have your apps query it directly. This way it stays up if the webserver crashes. Would replication work in your case? Sure. But is it really needed? Probably not.

    Your separate DB server will want lots of RAM, fast disks (think 10k RPM), and you'll want to dedicate it to MySQL.

    • 2