large scale dbs on multiple servers

mikethecoder

i am curious about doing something. i plan to possibly have a database with several tables that will start small but will expand massively if all goes well. now with an extremely large scale db (mysql preferably), what is the best way to maximize performance? should i just get a single server for the DB and have amazing specs on it?

or is there a way to have a single database running over multiple servers? like keep some tables on server 1 and others on server 2 etc? what is the best way to do a method similar to this and how is it performed?

any help is MUCH appreciated!

Roger_Ramjet

1) The limit of the size of any table or database is basically the limit of the physical filesize for your operating system and server hardware. However, with massive tables you need to pay a lot of attention to your indexing and query optimisation.

2) Yes, you can spread your data tables across multiple databases and servers, nothing wrong with that at all. You just have to benchmark the loads to get the best distribution of tables. Of course it means multiple connections so you get added overhead, and network latency becomes a factor, especially with queries that need tables on multiple servers.

3) The simplest way to distibute the load on a busy db is to use Master/Slave Replication . You have one master that handles all writes, and multiple slaves that handle all reads. You can just keep on adding slaves as the load grows (within limits of course). You also get some failure protection this way as each slave is a fully operational db server in it's own right.

mikethecoder

so using a setup of master slave replication where writes are done from the master and reads are done from the slaves...is this more like a RAID configuration? like..how exactly is doing this set up? is this a function of mysql? becuase if everything is replicated how would you specify in a query to use a particular database to read from or write to since they both have the same name (im assuming)? and if you had 2 slaves for reading how would they be used properly... would some queries on the site specify slave 1 and others just specify slave 2?

Roger_Ramjet

Well, good old RTFM is the short (both meanings) answer to that question. But it's saturday arvo I'm in a better mood, so I'll explain some of it here.

You as the author of the system know which queries update the db so you direct them to the master. By the same token you also direct all reads to the slaves.
Now distributing reads to several slaves has several possible solutions and which you need/choose really does depend on what these dbs are for.

One point I will make is that, in this forum, it is assumes that we are talking about dynamic data-driven websites. So I would expect that the optimum solution is going to be multiple integrated (on the same box) web/db servers; otherwise you are into network latency for connections between web-server and db-server.

So the problem is redefined as: how to load-balance multiple web-servers?

There is all sorts of cluster-server technology out there, and some of it is very expensive.
On the other hand, open-source wins out once again cos round-robin dns resolution is built into BIND on *NIX. Have a read of this and even if it is not your solution, it will introduce you to DNS round-robin load balancing far better than I can (I'm not a great technical writer).

Finally, do a google for IP or DNS load balancing for all the info you can handle.

Oh, and finally, good luck with this. I hope it takes off as you expect and the problem becomes real. Even if it does not, knowing this stuff will place you on a different level from most of the website nerds out there and make you highly employable.