Generally there were two ways to approach the database capacity. One way is to scale up by increasing the capacity of your existing servers. The other way is to scale out by adding additional servers. Pro's and Con's exists both ways. Let's discuss that in detail.
Scaling Up
Scaling up to add capacity to your database server is little bit easier when compared with scale out both in hardware and software perspective. But you will come to a situation where you can't scale up anymore; at that point you should go for scale out methodology.
Deciding which way to go first is the key thing you should concentrate. For that you look at how busy your CPU is. If they are close to 100 percent most of the time, then you are CPU bound and adding more I/O capacity won't help you. In that case you should add more CPU sockets, or switch to CPU's with a larger cache or a higher clock rate.
First thing you need to do is, adding more memory up to the system maximum capacity or what your budget allows.
Next add more disk or controllers to increase your system I/O throughput. I/O bound servers sometimes benefit from a large number of drives. Few things that should be kept in mind are as follows
- Adding more I/O capacity in the form of a new server (scale out) is more expensive than adding it to an existing one (scale up).
- You can increase your database performance by first making sure that the log file is kept in a dedicated drive. It's very much less expensive than adding new servers.
- Adding I/O capacity won't help if your system is CPU bound.
Scaling Out
When you reach a situation that you can move on to scaling out strategy, you can start partitioning your data in several ways.
- Horizontally – Place your data in different server based on certain key values. For example, names starts from A-M data will be Server 1. Names starts from N-Z will be in Server 2. In that case, when you search for a name which starts with the letter between A-M, will hit only the server 1 and the same applicable to Server 1. Here numbers of rows are split in each server, which makes the query to fetch the data in faster manner. We are searching in very less number of rows, since the data is divided and placed in to two different servers, which makes the query to execute in faster manner.
- Vertically – Group the tables into two or three different groups. Tables that are going to be joined frequently can be place in the same group. For example, if we have two different groups of tables, place one group of tables in one server and the other one in another server. When the query gets executed, only one server will be hit at the time, which makes the execution faster and increases the performance.
- Read Only Servers – Place the read only data in separate servers. Easiest way to do this having the related table information in one server and get it sync with the write only copy by making replica of that server or using the load balancing concepts. That could give a good performance on the query execution.
- Write Mostly Servers – heavy write operation or log inserts can be in separate servers. Increasing the write performance is easier than adding the new servers.