Paul McNally’s Blog

Scaling out databases is generally more difficult than web servers. The easiest way of scaling a database is by scaling up. Databases are by their very nature processor and memory intensive applications.¹ Scaling up is a better option today for most databases and most users should scale out only if they can not reach their requirements with a single system.² Keeping the whole database on a single server also have its benefits. Lower maintenance costs, storage requirements, cost of purchasing separate servers etc

Scaling up the database

Once the database server processor is working at 80% capacity it cannot process new requests. There is a rapid decline in throughput for the server. Adding new processors increases processing power and throughput, but statistics provided by the Duwamish Online Sample Site show that the addition of multiple processors does not provide a linear increase in server performance as scaling out with additional servers would (Capacity Planning – Duwamish Online Sample E-Commerce Site, by Paul Johns et al)

This is because there are other factors that contribute to the database server’s performance. Increasing RAM would allow for the caching or common database queries increasing response times, and increasing the number of processors would allow for more queries to be executed concurrently.

Scaling up the storage memory of the database server would allow more data to be stored, but could also slow down retrieval of this data as its quantity increases.

Scaling up database servers may be the most cost effective and sensible method for growth until such time as scaling out is absolutely necessary, but it also has its drawbacks. Since scaling up a single server is just one machine, the database server itself may become the single point of failure of the whole application.

If you were using licensed software on the database server (operating system/RDBMS) there may be some benefits of only needing to purchase one licence for the software as it resides on only one machine(although some licences may be on a per-processor basis). This would be more likely to affect proprietary software users such as users of MS SQL Server and less likely for users of open source solutions such as MySQL.

If scaling up is not enough, there are methods of scaling out larger databases.

Scaling Out

Scaling databases poses some unique problems since database interaction may include both reading and writing information to the database. If we were to scale out in a similar way to load balancing, a separate copy of the entire database on each server, any updates to each server would have to be replicated on all the servers to keep the database up to date. This can be achieved and there are whole books out there on how to implement these kinds of clusters with software such as MySQL. This could end up using much of the servers’ resources transferring data between one another. However, it’s a different matter if the database is read only.

Clustering the database service

One method would be to produce a cluster of database servers with access to shared database content on a shared disk. This would provide the benefit of both doubling the capacity of the initial database server by adding another machine and the ability to add more nodes to the cluster as demand increases, with accurate projected costs and performance increases while providing failover reliability.

However the shared disk would still provide a potential point of failure and would require a RAID configuration. A combination of both web server network load balancing and database server clustering with access to shared disk data can be found in database services such as Oracle 9i (referred to as Real Application Clusters) and IBM’s DB2 for OS/390³.

Not all database software support shared disk clustering (e.g Microsoft SQL 2000 and MySQL), but do support parallel-server, fault tolerant clustering architectures⁴ which are shared-nothing clusters. As Google has demonstrated, a pure SN system can scale almost indefinitely simply by adding nodes in the form of inexpensive computers, since there’s no single bottleneck to slow the system down.⁵

MySQL Clusters use synchronous replication to write data to multiple nodes providing availability of the data and reliability of the cluster.

Partitioning the Database

Large databases can be split up onto separate servers by means of partitioning. How the data is partitioned is mainly dependant on how the database is used:

Horizontal Partitioning

Horizontal partitioning of a database is splitting the database across multiple servers based on groupings such as alphabetical grouping of records A-C D-F etc or date Svr1:1980-1989, Svr2:1990-1999 for example on separate servers.

Vertical Partitioning and work type partitioning

Vertical partitioning is where a large database is split up into several smaller databases, each sharing a common primary key of a conceptual joined master database similar to Normalization. This may work as long as the queries do not require columns located on more than one server.

Another method is vertical partitioning of the database by work type. For example the application could be separated by read/write usage. For example, catalogue pages of an application are going to be primarily read queries, while the transaction of orders are likely to be both read and write. Each could be on a separate server.

1 Comment »

Best practice dictates that the web server CPU capacity should never exceed 70%, known as the linear region of operation, usage above this capacity causes an exponential increase in response time as more CPU time is being spent context switching. The web or database service should not exceed 50% capacity, and should have a normal operating capacity of no greater than 20%.

Considering Scalability

The scaling of a web service first requires the separation of the web server from the database service as each entity has distinct scalability options. To ensure that the web service does not reach the non-linear region of processing capacity there are techniques that can be implemented to reduce the workload for individual machines by means of Load Sharing.

Scaling the web server

Load sharing techniques can be implemented for web servers and include load balancing and clustering. Load balancing techniques incorporate using more than one server of comparable performance, each with a copy of the websites files. Incoming TCP/IP packets would then be directed, by the use of a network switch, to the servers, sharing the load between them. It is worth noting that the servers should be of comparable capabilities, since the workload will be shared among them equally, one machine more powerful than another would be pointless. There are however, pro’s and cons to load balancing.

If a website utilises sessions for the storage of user data on the server, this type of load sharing could cause problems, as a user’s request may be sent to another server.

Since you would be running several identical web servers, several instances of Application Server technology may or may not me necessary depending on your chosen technology. ColdFusion Enterprise-level J2EE application servers for example can provide load balancing and failover from the web server to the application server, including replicating session data between servers in-memory.¹

There are however other ways, using special software to keep a user on a specific server, or by attaching the user’s session data to a query string when changing pages, or saving the users session data in a browser cookie.

Load balancing also has its benefits in regards to reliability. Since there would be several identical servers running, this creates a state of high availability. If one server were to fail, the network switch can send incoming requests to the remaining active servers preventing website failure. The network switch itself could prove to be the single point of failure though for the entire application if it were to fail.

Another load sharing technique is clustering. A cluster is a set of two or more independent computers that share resources. These computers are managed as a single system to provide high availability and scalability.² An example of this would be to set up several interconnected web servers that have access to a shared disk and thus shared data. The cluster as an entity would act as a single unit, and if one node in the cluster were to fail, other nodes could take over its tasks. Because of this, the cluster architecture is highly reliable. However if shared disks are used, these can provide a single point of failure for the whole cluster. So for increased reliability one may implement a degree of redundancy when it comes to disk sharing such as RAID (Redundant Array of Independent Disks) configurations, including disk mirroring.

Clustering can also follow the ‘shared-nothing model’³ where each server controls a set of resources and only one server has access to the resources at a time. So, if one server in the cluster were to fail, the next would take over the workload providing increased reliability.

Clustering is designed to provide an extremely stable environment which is highly scalable. If the workload of the cluster were to increase, the cluster could be easily scaled out by adding more servers to the cluster without disrupting it, at a predictable cost and performance increase.

Another option is scaling up machines, which may prove to be a cheaper alternative for expanding web services capabilities but more useful when it comes to database services.

Scaling up involves adding more to a machine to increase performance, such as adding more powerful processors, more processors, more RAM etc. More processors will obviously allow the server to process more concurrent requests, as increasing a web/database servers’ RAM will allow it to cache more web/script pages/database queries increasing response time.

No comments »