Find a bunch of links to find the ins and outs of scaling out with Sql Server 2005 -
http://blogs.msdn.com/sqlcat/archive/2008/06/12/sql-server-scale-out.aspx
Showing posts with label capacity planning. Show all posts
Showing posts with label capacity planning. Show all posts
Thursday, August 21, 2008
Monday, May 19, 2008
Capacity Planning - Sizing Memory
Physical memory is one of the important hardware components for a database server. This is because of the fact that, adequate physical memory decreases the physical IO for the server by reusing as much as the data and query plans that exist on the cache.
SQL Server Memory Requirements
SQL Server does dynamic memory allocation. This means SQL Server will allocate whatever memory left on the server that is not being used for its functioning. Whenever any application or the operating system requires additional memory, SQL Server releases memory for their use.
Different components of SQL Server require physical memory for their functioning. The following sections describe the components within SQL Server which are the main users of physical memory.
Buffer Cache Requirements
SQL Server buffer cache is the area in the physical memory where the data pages and index pages are cached. The buffer cache is the largest consumer of physical memory in SQL Server. The dynamic allocation and de-allocation of memory by SQL Server is done mostly by adjusting the size of the buffer pool. The server should be provided with adequate memory to ensure a very high cache hit ratio and the amount of physical IO be reduced to a very minimum level. A decrease in the buffer cache size increases physical IO activities.
Procedure Cache Requirements
Procedure Cache is the area in physical memory where SQL Server caches the compiled execution plans for queries and stored procedures. This is the second largest consumer of physical memory in SQL Server. A healthy size of procedure cache is required to reduce the number of query compilations and optimizations. A decrease in the size of procedure cache increases recompilations which in turn increases processor overhead.
Connection Context Requirements
Each user connection to SQL Server requires a certain amount of memory for handling the user session. This comes around an average of 500KB per connection. Hence, larger the set of concurrent users accessing the database, larger will be the amount of memory required to support the connections.
Other Requirements
There are other components in SQL Server that require memory. These include the lock manager etc.
The details on SQL Server memory allocation can be obtained by the command “DBCC MEMORYSTATUS”
See the hardware comparison details for different SQL Server editions.
Sizing Memory through Load Testing
The best method to estimate the physical memory requirements for a database application infrastructure is through load testing.
We need to do the simulation for the application load with the maximum number of concurrent users and transactions possible, and have to take the memory consumption. Physical memory has to be allocated enough to avoid any memory bottleneck.
A decrease in memory affects the whole system. Memory deficiency increases cache misses and paging, paging increases physical IO and these all increase the processor utilization.
Sizing Memory through Workload Analysis
In all cases, its will not be possible to create the infrastructure prototype and do simulations. In such cases, memory sizing can be carried out by manually doing a workload analysis and calculating the memory requirements by the different components of the server infrastructure.
We can have an example of a sales database application that experiences an annual user accounts growth of 15% and sales data growth of 20%. The following table shows a rough estimation of the memory requirements for procedure cache, user connections etc, and how that will affect the buffer cache.
This is just a rough estimation, assuming that one user connection requires around 500KB and the SQL Server, OS program components together require around 1GB. Similarly, we can do accurate calculations and project the memory requirements.
With this, I am concluding the current Capacity Planning series. I will be demonstrating on a few tools, trends etc in some future posts.
SQL Server Memory Requirements
SQL Server does dynamic memory allocation. This means SQL Server will allocate whatever memory left on the server that is not being used for its functioning. Whenever any application or the operating system requires additional memory, SQL Server releases memory for their use.
Different components of SQL Server require physical memory for their functioning. The following sections describe the components within SQL Server which are the main users of physical memory.
Buffer Cache Requirements
SQL Server buffer cache is the area in the physical memory where the data pages and index pages are cached. The buffer cache is the largest consumer of physical memory in SQL Server. The dynamic allocation and de-allocation of memory by SQL Server is done mostly by adjusting the size of the buffer pool. The server should be provided with adequate memory to ensure a very high cache hit ratio and the amount of physical IO be reduced to a very minimum level. A decrease in the buffer cache size increases physical IO activities.
Procedure Cache Requirements
Procedure Cache is the area in physical memory where SQL Server caches the compiled execution plans for queries and stored procedures. This is the second largest consumer of physical memory in SQL Server. A healthy size of procedure cache is required to reduce the number of query compilations and optimizations. A decrease in the size of procedure cache increases recompilations which in turn increases processor overhead.
Connection Context Requirements
Each user connection to SQL Server requires a certain amount of memory for handling the user session. This comes around an average of 500KB per connection. Hence, larger the set of concurrent users accessing the database, larger will be the amount of memory required to support the connections.
Other Requirements
There are other components in SQL Server that require memory. These include the lock manager etc.
The details on SQL Server memory allocation can be obtained by the command “DBCC MEMORYSTATUS”
See the hardware comparison details for different SQL Server editions.
Sizing Memory through Load Testing
The best method to estimate the physical memory requirements for a database application infrastructure is through load testing.
We need to do the simulation for the application load with the maximum number of concurrent users and transactions possible, and have to take the memory consumption. Physical memory has to be allocated enough to avoid any memory bottleneck.
A decrease in memory affects the whole system. Memory deficiency increases cache misses and paging, paging increases physical IO and these all increase the processor utilization.
Sizing Memory through Workload Analysis
In all cases, its will not be possible to create the infrastructure prototype and do simulations. In such cases, memory sizing can be carried out by manually doing a workload analysis and calculating the memory requirements by the different components of the server infrastructure.
We can have an example of a sales database application that experiences an annual user accounts growth of 15% and sales data growth of 20%. The following table shows a rough estimation of the memory requirements for procedure cache, user connections etc, and how that will affect the buffer cache.
This is just a rough estimation, assuming that one user connection requires around 500KB and the SQL Server, OS program components together require around 1GB. Similarly, we can do accurate calculations and project the memory requirements.
With this, I am concluding the current Capacity Planning series. I will be demonstrating on a few tools, trends etc in some future posts.
Tuesday, May 6, 2008
Capacity Planning - Planning the Processor Subsystem
Planning the processor subsystem for a database infrastructure is a straight forward task. Normally, all the high end servers will be using the latest available best processors. If all the individual servers are already loaded with the best the maximum supported number of best available processors, the way to increase the processing power is to increase the number of servers. These two ways of increasing processing power is explained in the sections below.
Scale-up vs. Scale-out
Scale up is the process of increasing the processing power of an already existing server either by adding more processors or by increasing the processing power of the processors. Scale out is the process of increasing the processing power of a database infrastructure by increasing the number of servers so that the load can be distributed among the different servers. Both these approaches have got their benefits and downsides.
Scale up is normally considered as a quick fix to resolve processor bottlenecks for database applications. With the arrival of hot swappable CPUs and databases supporting them, this approach doesn’t require little downtime and lesser overhead of planning. The major advantage is that, since it does not increase the number of servers, there is no extra overhead in terms of maintenance, monitoring, licensing etc.
Though scale up can be done easily as a quick fix for issues, it has its downsides. The major disadvantage is that there is always a maximum gain we can get by scaling up. This is because there are limitations for the maximum number of processors supported for a particular operating system as well as the RDBMS. Hence, scaling up cannot be considered as a permanent solution especially when the application is an ongoing growing application.
Scale out is normally considered as a permanent solution for increasing processing power. The most important advantage of this approach is the expandability. We can add more servers and make the application to use the newly added servers.
Since scale out is a more permanent solution, it requires a lot of planning and overhead. The most important disadvantage is that the application should be redesigned to make use of the multiple servers. This may not be an easy task for ongoing applications, and will be a costly affair. There are other disadvantages as well. These include the increased overhead of extra servers for maintenance, monitoring licensing etc.
We can decide whether to go for scale up or scale out depending upon the current situation of the application, and what is the future of the application.
Factors for Considerations
While selecting the processors, there are a few factors to be considered and see how they affect the application environment.
Ø Processor Architecture
Nowadays, processors are available in 32bit and 64bit architectures. We can decide which architecture our infrastructure requires based upon the cost and scalability requirements of the application.
The other important factors that have to be considered when selecting the processors are the following. Since, they are known to most people, I am not explaining each of them.
o Processor Cores
o L2 Cache
o Hyper threading
Scale-up vs. Scale-out
Scale up is the process of increasing the processing power of an already existing server either by adding more processors or by increasing the processing power of the processors. Scale out is the process of increasing the processing power of a database infrastructure by increasing the number of servers so that the load can be distributed among the different servers. Both these approaches have got their benefits and downsides.
Scale up is normally considered as a quick fix to resolve processor bottlenecks for database applications. With the arrival of hot swappable CPUs and databases supporting them, this approach doesn’t require little downtime and lesser overhead of planning. The major advantage is that, since it does not increase the number of servers, there is no extra overhead in terms of maintenance, monitoring, licensing etc.
Though scale up can be done easily as a quick fix for issues, it has its downsides. The major disadvantage is that there is always a maximum gain we can get by scaling up. This is because there are limitations for the maximum number of processors supported for a particular operating system as well as the RDBMS. Hence, scaling up cannot be considered as a permanent solution especially when the application is an ongoing growing application.
Scale out is normally considered as a permanent solution for increasing processing power. The most important advantage of this approach is the expandability. We can add more servers and make the application to use the newly added servers.
Since scale out is a more permanent solution, it requires a lot of planning and overhead. The most important disadvantage is that the application should be redesigned to make use of the multiple servers. This may not be an easy task for ongoing applications, and will be a costly affair. There are other disadvantages as well. These include the increased overhead of extra servers for maintenance, monitoring licensing etc.
We can decide whether to go for scale up or scale out depending upon the current situation of the application, and what is the future of the application.
Factors for Considerations
While selecting the processors, there are a few factors to be considered and see how they affect the application environment.
Ø Processor Architecture
Nowadays, processors are available in 32bit and 64bit architectures. We can decide which architecture our infrastructure requires based upon the cost and scalability requirements of the application.
The other important factors that have to be considered when selecting the processors are the following. Since, they are known to most people, I am not explaining each of them.
o Processor Cores
o L2 Cache
o Hyper threading
Monday, April 28, 2008
Capacity Planning - Planning the Disk Subsystem
Planning the disk subsystem for a database infrastructure is the process of deciding the proper RAID strategy that satisfies the optimal throughput requirements and the appropriate storage requirements. Usually, the best practice guidelines insist five separate physical-drive sets with a logical drive each mapping for the requirements of Operating System, Transaction Logs of the production database, the data files of the production database, TempDb and lastly, the backup files storage. Before going through each of these, let us discuss about the various standard RAID configurations and their characteristics. The following table shows the different standard RAID configurations and their characteristics. (Click for a larger display). 
We can see that, although the fault tolerant RAID configurations increase the number of IOs per second, we can increase the number of physical disks so that the IOs get divided among the physical disks. This means that we can increase the number of physical disks to increase the throughput. However, there will be limitations for the maximum physical throughput for individual disks such as 300IOps, and also the throughput will be dependant on the throughput limitation of the IO bus.
Requirements for Different Components
Let us take the main components for a SQL Server instance and see how to design the disk requirements of each. As mentioned above, usually, the best practice guidelines insist five separate physical-drive sets with a logical drive each mapping for the requirements of Operating System, Transaction Logs of the production database, the data files of the production database, TempDb and lastly, the backup files storage. The operating system and the SQL Serve program components don’t have very high throughput requirements or storage requirements. The main thing to consider here should be the recoverability. Considering the requirements, we can assign a RAID1 mirror for the operating systems and SQL Server program components so that we can easily recover in case of a failure. For the production database data files, we know that there will be very large storage requirements and high throughput requirements. From the RAID specifications above, we can easily make out that RAID1 cannot be used because of lack of high storage capacity as well as RAID0 cannot be used because it does not offer fault tolerance. Hence, the choice comes between RAID5 and RAID10. RAID5 and RAID10 offer good read while RAID5 is a bad choice for write. Depending upon our application, whether it is an OLTP application or an OLAP application, we can have a choice between these two.
Meeting the Throughput Requirements
To explain the throughput calculations for a database application, let us assume that the client technical team insists that the total disk IO should not exceed 85%. Consider that we have 300IOps disks. This means that each of the individual disks should not exceed 85% of its throughput, i.e. the maximum throughput of an individual disk should not exceed 255IOps. Now, let us assume that, during the peak hours, our application has approximately 600 reads and 200 writes per second. Assuming that we are considering only fault tolerant RAID configurations, let us take each possible configuration to satisfy the requirements. Let us assume that we use RAID1; we know that for RAID1 the Throughput = (Reads + (2 * Writes))/2 = (600 + 400)/2 = 500IOps. As per the requirements, the maximum throughput we can afford for an individual physical disk is 255, while our application with RAID1 will have 500IOps. Hence, we can conclude that RAID1 is not possible for the data files of our application. Now let us assume that we use RAID5; we know that for RAID5 the Throughput = (Reads + (4 * Writes))/No. of Disks. That means, No. of Disks = (600 + 800)/ Throughput; that is No. of Disks = 1400/255 ~= 6. This means that, we require at least 6 disks to satisfy the throughput requirements, if we are adopting a RAID5 configuration for the data files. Now, let us assume that we use RAID10; we know that for RAID10 the Throughput = (Reads + (2 * Writes))/No. of Disks; So, No. of Disks = (Reads + (2 * Writes))/ Throughput. Replacing the variables, No. of Disks = (600+400)/255 ~=4. This means that, we require only 4 disks to satisfy the throughput requirements, if we are adopting a RAID10 configuration for the data files. We have to notice that, there cannot be an odd number of disks for a RAID10 configuration. Even though we calculate that the total disks required is 4.5, we need to have 6 disks to configure the RAID.
Meeting the Storage Requirements
Once we decide the throughput requirements, and the number of disks required to satisfy the application throughput, we need to consider the storage requirements for the application. During the throughput calculation, the main focus was the number of disks required to satisfy the application throughput requirements, while in storage requirements calculations, we consider the capacity of the each disk. Here also, let us assume that the client insists that only 85% of the total disk capacity should be utilized at any time. This means, at any point of time there should be at least 15% free space on the disk. Consider that we are expecting our data files to have a total size of 115GB, we require total space of 115/.85 ~= 136GB. Assuming that we are considering only fault tolerant RAID configurations as above, let us take each possible configuration to satisfy the total 136GB requirement. Let us assume that we use RAID1 even though we ruled out the possibility of using RAID1 because it doesn’t satisfy our throughput requirements. We know that in a RAID1 configuration, we can have only 2 disks. This means each disk should have an individual storage capacity of 136/2 = 68GB. Now let us assume that we use RAID5; we know that in a RAID5 configuration, one physical disk will be used for parity purposes. During the throughput calculations, we concluded that we require at least 6 disks to satisfy the throughput requirements, if we are going for a RAID5 configuration. Hence, the minimum storage capacity for individual disk = 136/5 ~=28GB. Now let us assume that we use RAID10. During the throughput calculations, we concluded that we require at least 4 disks to satisfy the throughput requirements, if we are going for a RAID10 configuration. Among these 4 disks, only 2 can be used for storage purposes because of the mirrors. This means that the individual minimum storage capacity for each disk should be 136/2 ~= 68GB.
We can see that, although the fault tolerant RAID configurations increase the number of IOs per second, we can increase the number of physical disks so that the IOs get divided among the physical disks. This means that we can increase the number of physical disks to increase the throughput. However, there will be limitations for the maximum physical throughput for individual disks such as 300IOps, and also the throughput will be dependant on the throughput limitation of the IO bus.
Requirements for Different Components
Let us take the main components for a SQL Server instance and see how to design the disk requirements of each. As mentioned above, usually, the best practice guidelines insist five separate physical-drive sets with a logical drive each mapping for the requirements of Operating System, Transaction Logs of the production database, the data files of the production database, TempDb and lastly, the backup files storage. The operating system and the SQL Serve program components don’t have very high throughput requirements or storage requirements. The main thing to consider here should be the recoverability. Considering the requirements, we can assign a RAID1 mirror for the operating systems and SQL Server program components so that we can easily recover in case of a failure. For the production database data files, we know that there will be very large storage requirements and high throughput requirements. From the RAID specifications above, we can easily make out that RAID1 cannot be used because of lack of high storage capacity as well as RAID0 cannot be used because it does not offer fault tolerance. Hence, the choice comes between RAID5 and RAID10. RAID5 and RAID10 offer good read while RAID5 is a bad choice for write. Depending upon our application, whether it is an OLTP application or an OLAP application, we can have a choice between these two.
Meeting the Throughput Requirements
To explain the throughput calculations for a database application, let us assume that the client technical team insists that the total disk IO should not exceed 85%. Consider that we have 300IOps disks. This means that each of the individual disks should not exceed 85% of its throughput, i.e. the maximum throughput of an individual disk should not exceed 255IOps. Now, let us assume that, during the peak hours, our application has approximately 600 reads and 200 writes per second. Assuming that we are considering only fault tolerant RAID configurations, let us take each possible configuration to satisfy the requirements. Let us assume that we use RAID1; we know that for RAID1 the Throughput = (Reads + (2 * Writes))/2 = (600 + 400)/2 = 500IOps. As per the requirements, the maximum throughput we can afford for an individual physical disk is 255, while our application with RAID1 will have 500IOps. Hence, we can conclude that RAID1 is not possible for the data files of our application. Now let us assume that we use RAID5; we know that for RAID5 the Throughput = (Reads + (4 * Writes))/No. of Disks. That means, No. of Disks = (600 + 800)/ Throughput; that is No. of Disks = 1400/255 ~= 6. This means that, we require at least 6 disks to satisfy the throughput requirements, if we are adopting a RAID5 configuration for the data files. Now, let us assume that we use RAID10; we know that for RAID10 the Throughput = (Reads + (2 * Writes))/No. of Disks; So, No. of Disks = (Reads + (2 * Writes))/ Throughput. Replacing the variables, No. of Disks = (600+400)/255 ~=4. This means that, we require only 4 disks to satisfy the throughput requirements, if we are adopting a RAID10 configuration for the data files. We have to notice that, there cannot be an odd number of disks for a RAID10 configuration. Even though we calculate that the total disks required is 4.5, we need to have 6 disks to configure the RAID.
Meeting the Storage Requirements
Once we decide the throughput requirements, and the number of disks required to satisfy the application throughput, we need to consider the storage requirements for the application. During the throughput calculation, the main focus was the number of disks required to satisfy the application throughput requirements, while in storage requirements calculations, we consider the capacity of the each disk. Here also, let us assume that the client insists that only 85% of the total disk capacity should be utilized at any time. This means, at any point of time there should be at least 15% free space on the disk. Consider that we are expecting our data files to have a total size of 115GB, we require total space of 115/.85 ~= 136GB. Assuming that we are considering only fault tolerant RAID configurations as above, let us take each possible configuration to satisfy the total 136GB requirement. Let us assume that we use RAID1 even though we ruled out the possibility of using RAID1 because it doesn’t satisfy our throughput requirements. We know that in a RAID1 configuration, we can have only 2 disks. This means each disk should have an individual storage capacity of 136/2 = 68GB. Now let us assume that we use RAID5; we know that in a RAID5 configuration, one physical disk will be used for parity purposes. During the throughput calculations, we concluded that we require at least 6 disks to satisfy the throughput requirements, if we are going for a RAID5 configuration. Hence, the minimum storage capacity for individual disk = 136/5 ~=28GB. Now let us assume that we use RAID10. During the throughput calculations, we concluded that we require at least 4 disks to satisfy the throughput requirements, if we are going for a RAID10 configuration. Among these 4 disks, only 2 can be used for storage purposes because of the mirrors. This means that the individual minimum storage capacity for each disk should be 136/2 ~= 68GB.
Wednesday, April 23, 2008
Capacity Planning - Sizing a Production Database Server
The process of designing a database server infrastructure to cater the needs of the application or business before it goes live in to a production environment is known as sizing.
It requires a lot of skilled requirement collection and analysis to project the requirements of the application well before it is deployed. Following are a few generic techniques that are being use to size a database server infrastructure.
§ Capacity Planning Interviews
One of the best techniques to understand an application well before it is deployed is through the interactions with the stakeholders. The stakeholders can be anyone who is going to be using the application; the business analysts who did the system requirements collection and analysis, the technical team who designed and developed the application, or the end users who will use the business. We need to interact with various teams to understand and collect as much as information regarding the application architecture and its usage. Following are the different aspects we need to understand with the interviews.
Ø Business Requirements
Business requirements can be collected by meeting and interacting with the business users and their leaders. We need to understand the nature of the application, who will be using the application, how frequently will the users be using the application, how critical is the application, how many users will be concurrently using the application, how complex would be the usage nature of the application like whether there will be simultaneous data input processes ad data analysis process, whether there will be additional employees joining who will be using the application etc.
The business users requirements is the most important factor that has to be understood while doing a capacity planning. However the application is built and maintained, ultimately it is the application usage pattern that determines the performance and availability of the application.
Ø Technical Requirements
While the business requirements of the application can be obtained from the pure business people, the technical requirements should be collected from the technical people that represent the application. The technical requirements that have to be collected include such as database response time, application uptime, the maintenance windows, the expected performance benchmarks, data growth patterns etc. These requirements have to be collected and documented, so that we can size the infrastructure that meets the requirements.
Ø Technical Environment
Collecting information regarding technical environment of the application comes into the picture in business models where we will not be hosting the infrastructure. In such cases, the environment is equally important as the business requirements collection. This is because we need to understand the technical environment where the database application will be going to run. This includes the network infrastructure, the security infrastructure, how efficient is the client’s system support teams are, do they have proper storage infrastructure etc.
We need to understand whether the company normally supports a staging or implementation server along with the production server. In such a case, any change can be applied and tested on the staging environments before being applied on the production server.
§ Collecting Performance Data and Baselines
This is perhaps the most important step in doing a capacity planning. Having the proper baselines in place is one of the main things people tend to forget or ignore when a new application is being deployed. Rather than collecting the baselines, the main focus those times will tend to have the application satisfy all the functional requirements and gets live in the production.
It is important to have the baselines in place because in the absence of a proper set of baselines and workload analysis data, once the application goes live, we will be ignorant on how the application is expected to behave under various workload and environments. Workload analysis can be defined as the process of collecting information on the expected behavior of the application under various workload and environments. If there is a performance issue notified on a production application, the first thing we can do is to take the baseline under that workload and compare the information with the current response. Thus we can easily detect the bottleneck and resolve accordingly.
Workload analysis requires three main components. They are the prototype server infrastructure, the load simulating tools, and the workload monitoring tools. Each of these three components is explained below.
Ø Prototype Server Infrastructure
A prototype infrastructure is required to be in place that exactly matches, with the production environment where the application is planning to be deployed. The details that are required to simulate the production infrastructure have to be obtained from the client during the requirements analysis phase. Accordingly, a proper prototype has to be established on which the workload analysis can be done. We can see that there are numerous occasions where a matching prototype could not be established due to budget constraints. It doesn’t make sense to compare the results obtained from an environment that doesn’t match with the production environment. Any analysis or tuning should be based on matching environments only.
A few factors that have to be considered while matching the environments are the following –
Hardware configuration
Processor power
Memory configuration
Network infrastructure
Storage configuration
Operating Systems
Operating systems version and patch level
Server build
Application implementation
Tier configurations
Database Instance configurations
For well maintained production environments, there will be proper documentation of infrastructure configurations. Any new infrastructure change, or a new device or asset introduction will be based on a predefined plan and documentation. The documentation include such details as what is the production standard for operating system and its edition and version etc, what is the procedure to build and harden any production server, what is the standard hardware that will be used for a typical production server, what will be the storage configuration for a database server etc. These documentations can be used to replicate the environment for the prototype infrastructure.
Ø Workload Simulation
Once the prototype infrastructure is in place, the next step we have to do is to simulate the workload on the application. For a typical multi user database application, we can avail the help of various tools available for workload simulation. A few examples are Loadrunner, Erwin etc.
Simulation tools can be used to simulate various conditions on the applications. These conditions can be applied on the hardware side as well as on the application usage side. Initially, we need to define a fixed number of environments to be simulated. Then we should define the hardware and software conditions that come under any environment. The response of the application and the infrastructure should be measured against each of these simulated environments. These environments and the response to each of these environments make our performance baseline data. Baselines can be defined as performance measurements gathered over a specific periods of time with specific sampling intervals. We need to ensure that we define a large number of possible environments and performance baseline data is collected against each of these varied environments.
Ø Response Monitoring & Analysis
The application response to the various predefined environments should be monitored and documented. The main purpose of having the monitor logs in place is to have an official documentation for current and future references. The logs display the undisputable track record of the application’s response over various environments. Any SLA or other agreements should use these track records as reference to conclude the response capability of the application and the ability to sustain various pressures and adverse environments.
An example of a load response results for an application can be as shown below.
ConcurrentUsers ProcessorUtilization
10,000 40%
20,000 50%
30,000 60%
40,000 70%
50,000 80%
From the figure, we can see easily make out the relation between the number of concurrent users with processor load. The document shows the “fact” that if the total number of users is less that 10,000, we should not expect a processor utilization of 80% unless and until there is something wrong somewhere. This gives the management the strength to commit the client on the response level of the application, as well as give the technical team a baseline reference to find out any processor related application bottlenecks.
There are various tools that can be used for this kind of monitoring purpose. One of the main tools that can be used for this purpose is the in-built Windows Performance Monitor (Perfmon). Perfmon can be used to track the application response to various software and hardware counters. The tracked response levels can be used to project the response and growth trend of the application with respect to time and load, and used as the main tool for post-configuration capacity planning process. The upcoming sessions explain the monitoring and analysis using such counters specific to physical memory, processor subsystem, disk subsystem etc.
Ø Projecting the Trends
Projection is one of the main processes involved in post-configuration capacity planning. This is explained here because it comes as a continuation and makes use of the monitor logs that we obtained from the load simulation environments.
Projection is the process of estimating the response level of the application with the help of already existing load response logs. In the above mentioned example of users vs. processor utilization, we have information on maximum of 50,000 concurrent users. Assume that the client wants and plans to have 10,000 more analysts to use the application. We can create the trend line from the already existing information as follows.
The graph shows us that the additional users will increase the processor subsystem utilization to 90% of the total processor capacity. This information can help the stakeholders to plan and take decisions that help them effectively use the application for their business.
Linear Growth and Geometric Growth
In the above example, we assumed that there will be a direct relationship between the number of concurrent users and the processor utilization. Such a relationship can be termed as a Linear Projection. It may not be always the case. We need to think that there may be increased amount of data loaded with the additional number of users. This means that there may be an exponential relationship between the users and the processor utilization. Consider the following example where we have the data growth for the application with respect to periods.
Period Total Data
200701 100
200702 110
200703 121
200704 133
200705 146
200706 161
200707 177
200708 195
200709 214
200710 236
200711 259
We can see that the data size is increasing with a pattern of d+ d*(10/100), where d is the previous period’s data size. When we need to project the disk requirements for period 200712, we can easily calculate as 259 + (259*.1), and that is approximately 285. Here, the growth is a geometric growth. The projection will be as follows –
§ Final Decision on the Production Server Infrastructure
The final decision on the database server infrastructure is based upon the performance data and baselines we collected in the performance testing phase. When we share the performance test results with the client, the client will share the required parameters that have to be satisfied by the application like number of concurrent users, uptime requirements etc. based upon their requirements, we can finalize whether the hardware we have for the prototype satisfies the requirements. In the above example, assuming that the technical team of the client agrees that the maximum processor at any time can be up to 50%, it is clear that the processor subsystem of the prototype infrastructure can be sufficient if the number of analysts accessing the application is less than 30,000. In case, either the client business team wants their concurrent users more than 30,000 or if the client technical team wants the maximum processor utilization less than 40% or so, we need to reengineer our processor subsystem to increase the strength. Otherwise, we can easily decide that the processor subsystem is sufficient for the time being.
The final decisions can be so easy always. There will be a lot of parameters to be considered. For example, the client may require that at any time the application should have the past two years’ data. In such cases, the load simulating environments should consider the processor overhead that would be caused by the additional data. In short, the simulated environments should be smart enough to satisfy the forecasted environments for the specific period (like two years, three years etc).
Ø Finalizing the SLAs
The SLA is mutual agreement with the client and the solution provided agreeing that the application will satisfy the predefined requirements. The client technical team will insist on such agreements such as 99.99% uptime, a round trip response time of less than 1 second etc. The final decision on the infrastructure should be taken so that the SLAs can be met. Though the SLAs will come as part of the technical requirement specifications, it is possible that business and budget reasons will force to have adjustments on them once the prototype testing is done. Until the SLAs are met on the prototype infrastructure, the application should be fine tuned.
It requires a lot of skilled requirement collection and analysis to project the requirements of the application well before it is deployed. Following are a few generic techniques that are being use to size a database server infrastructure.
§ Capacity Planning Interviews
One of the best techniques to understand an application well before it is deployed is through the interactions with the stakeholders. The stakeholders can be anyone who is going to be using the application; the business analysts who did the system requirements collection and analysis, the technical team who designed and developed the application, or the end users who will use the business. We need to interact with various teams to understand and collect as much as information regarding the application architecture and its usage. Following are the different aspects we need to understand with the interviews.
Ø Business Requirements
Business requirements can be collected by meeting and interacting with the business users and their leaders. We need to understand the nature of the application, who will be using the application, how frequently will the users be using the application, how critical is the application, how many users will be concurrently using the application, how complex would be the usage nature of the application like whether there will be simultaneous data input processes ad data analysis process, whether there will be additional employees joining who will be using the application etc.
The business users requirements is the most important factor that has to be understood while doing a capacity planning. However the application is built and maintained, ultimately it is the application usage pattern that determines the performance and availability of the application.
Ø Technical Requirements
While the business requirements of the application can be obtained from the pure business people, the technical requirements should be collected from the technical people that represent the application. The technical requirements that have to be collected include such as database response time, application uptime, the maintenance windows, the expected performance benchmarks, data growth patterns etc. These requirements have to be collected and documented, so that we can size the infrastructure that meets the requirements.
Ø Technical Environment
Collecting information regarding technical environment of the application comes into the picture in business models where we will not be hosting the infrastructure. In such cases, the environment is equally important as the business requirements collection. This is because we need to understand the technical environment where the database application will be going to run. This includes the network infrastructure, the security infrastructure, how efficient is the client’s system support teams are, do they have proper storage infrastructure etc.
We need to understand whether the company normally supports a staging or implementation server along with the production server. In such a case, any change can be applied and tested on the staging environments before being applied on the production server.
§ Collecting Performance Data and Baselines
This is perhaps the most important step in doing a capacity planning. Having the proper baselines in place is one of the main things people tend to forget or ignore when a new application is being deployed. Rather than collecting the baselines, the main focus those times will tend to have the application satisfy all the functional requirements and gets live in the production.
It is important to have the baselines in place because in the absence of a proper set of baselines and workload analysis data, once the application goes live, we will be ignorant on how the application is expected to behave under various workload and environments. Workload analysis can be defined as the process of collecting information on the expected behavior of the application under various workload and environments. If there is a performance issue notified on a production application, the first thing we can do is to take the baseline under that workload and compare the information with the current response. Thus we can easily detect the bottleneck and resolve accordingly.
Workload analysis requires three main components. They are the prototype server infrastructure, the load simulating tools, and the workload monitoring tools. Each of these three components is explained below.
Ø Prototype Server Infrastructure
A prototype infrastructure is required to be in place that exactly matches, with the production environment where the application is planning to be deployed. The details that are required to simulate the production infrastructure have to be obtained from the client during the requirements analysis phase. Accordingly, a proper prototype has to be established on which the workload analysis can be done. We can see that there are numerous occasions where a matching prototype could not be established due to budget constraints. It doesn’t make sense to compare the results obtained from an environment that doesn’t match with the production environment. Any analysis or tuning should be based on matching environments only.
A few factors that have to be considered while matching the environments are the following –
Hardware configuration
Processor power
Memory configuration
Network infrastructure
Storage configuration
Operating Systems
Operating systems version and patch level
Server build
Application implementation
Tier configurations
Database Instance configurations
For well maintained production environments, there will be proper documentation of infrastructure configurations. Any new infrastructure change, or a new device or asset introduction will be based on a predefined plan and documentation. The documentation include such details as what is the production standard for operating system and its edition and version etc, what is the procedure to build and harden any production server, what is the standard hardware that will be used for a typical production server, what will be the storage configuration for a database server etc. These documentations can be used to replicate the environment for the prototype infrastructure.
Ø Workload Simulation
Once the prototype infrastructure is in place, the next step we have to do is to simulate the workload on the application. For a typical multi user database application, we can avail the help of various tools available for workload simulation. A few examples are Loadrunner, Erwin etc.
Simulation tools can be used to simulate various conditions on the applications. These conditions can be applied on the hardware side as well as on the application usage side. Initially, we need to define a fixed number of environments to be simulated. Then we should define the hardware and software conditions that come under any environment. The response of the application and the infrastructure should be measured against each of these simulated environments. These environments and the response to each of these environments make our performance baseline data. Baselines can be defined as performance measurements gathered over a specific periods of time with specific sampling intervals. We need to ensure that we define a large number of possible environments and performance baseline data is collected against each of these varied environments.
Ø Response Monitoring & Analysis
The application response to the various predefined environments should be monitored and documented. The main purpose of having the monitor logs in place is to have an official documentation for current and future references. The logs display the undisputable track record of the application’s response over various environments. Any SLA or other agreements should use these track records as reference to conclude the response capability of the application and the ability to sustain various pressures and adverse environments.
An example of a load response results for an application can be as shown below.
ConcurrentUsers ProcessorUtilization
10,000 40%
20,000 50%
30,000 60%
40,000 70%
50,000 80%
From the figure, we can see easily make out the relation between the number of concurrent users with processor load. The document shows the “fact” that if the total number of users is less that 10,000, we should not expect a processor utilization of 80% unless and until there is something wrong somewhere. This gives the management the strength to commit the client on the response level of the application, as well as give the technical team a baseline reference to find out any processor related application bottlenecks.
There are various tools that can be used for this kind of monitoring purpose. One of the main tools that can be used for this purpose is the in-built Windows Performance Monitor (Perfmon). Perfmon can be used to track the application response to various software and hardware counters. The tracked response levels can be used to project the response and growth trend of the application with respect to time and load, and used as the main tool for post-configuration capacity planning process. The upcoming sessions explain the monitoring and analysis using such counters specific to physical memory, processor subsystem, disk subsystem etc.
Ø Projecting the Trends
Projection is one of the main processes involved in post-configuration capacity planning. This is explained here because it comes as a continuation and makes use of the monitor logs that we obtained from the load simulation environments.
Projection is the process of estimating the response level of the application with the help of already existing load response logs. In the above mentioned example of users vs. processor utilization, we have information on maximum of 50,000 concurrent users. Assume that the client wants and plans to have 10,000 more analysts to use the application. We can create the trend line from the already existing information as follows.
The graph shows us that the additional users will increase the processor subsystem utilization to 90% of the total processor capacity. This information can help the stakeholders to plan and take decisions that help them effectively use the application for their business.Linear Growth and Geometric Growth
In the above example, we assumed that there will be a direct relationship between the number of concurrent users and the processor utilization. Such a relationship can be termed as a Linear Projection. It may not be always the case. We need to think that there may be increased amount of data loaded with the additional number of users. This means that there may be an exponential relationship between the users and the processor utilization. Consider the following example where we have the data growth for the application with respect to periods.
Period Total Data
200701 100
200702 110
200703 121
200704 133
200705 146
200706 161
200707 177
200708 195
200709 214
200710 236
200711 259
We can see that the data size is increasing with a pattern of d+ d*(10/100), where d is the previous period’s data size. When we need to project the disk requirements for period 200712, we can easily calculate as 259 + (259*.1), and that is approximately 285. Here, the growth is a geometric growth. The projection will be as follows –
§ Final Decision on the Production Server Infrastructure
The final decision on the database server infrastructure is based upon the performance data and baselines we collected in the performance testing phase. When we share the performance test results with the client, the client will share the required parameters that have to be satisfied by the application like number of concurrent users, uptime requirements etc. based upon their requirements, we can finalize whether the hardware we have for the prototype satisfies the requirements. In the above example, assuming that the technical team of the client agrees that the maximum processor at any time can be up to 50%, it is clear that the processor subsystem of the prototype infrastructure can be sufficient if the number of analysts accessing the application is less than 30,000. In case, either the client business team wants their concurrent users more than 30,000 or if the client technical team wants the maximum processor utilization less than 40% or so, we need to reengineer our processor subsystem to increase the strength. Otherwise, we can easily decide that the processor subsystem is sufficient for the time being.
The final decisions can be so easy always. There will be a lot of parameters to be considered. For example, the client may require that at any time the application should have the past two years’ data. In such cases, the load simulating environments should consider the processor overhead that would be caused by the additional data. In short, the simulated environments should be smart enough to satisfy the forecasted environments for the specific period (like two years, three years etc).
Ø Finalizing the SLAs
The SLA is mutual agreement with the client and the solution provided agreeing that the application will satisfy the predefined requirements. The client technical team will insist on such agreements such as 99.99% uptime, a round trip response time of less than 1 second etc. The final decision on the infrastructure should be taken so that the SLAs can be met. Though the SLAs will come as part of the technical requirement specifications, it is possible that business and budget reasons will force to have adjustments on them once the prototype testing is done. Until the SLAs are met on the prototype infrastructure, the application should be fine tuned.
Capacity Planning - Introduction
Capacity planning for a database infrastructure can be defined as the process of assessing the infrastructure requirements for a database environment before deployment, and projecting and predicting the future requirements for the infrastructure even after the environment gets deployed. Capacity planning involves various tests to measure how an environment should and will behave under the various factors that influence the environment, and planning for the infrastructure with the necessary resources that will help to achieve the response in a pre defined manner.
§ Pre-configuration & Post-configuration Capacity Planning
As mentioned above, the entire Capacity Planning process can be broadly categorized to the planning and the subsequent changes that we do before the environment is deployed, and the planning and changes that we do after the environment is deployed and is in production. We can define these as “pre-configuration capacity planning” and “post-configuration capacity planning”. The pre-configuration capacity planning is often referred as “sizing” the database infrastructure. The post-configuration capacity planning is an ongoing cyclic process of monitoring and projecting, planning and modifying. In precise, Capacity Planning for a database infrastructure can be pictorially represented as below.
In each of the upcoming sections, we will be discussing these processes in detail, and how to carry out each process for the various hardware and other requirements.
§ Pre-configuration & Post-configuration Capacity Planning
As mentioned above, the entire Capacity Planning process can be broadly categorized to the planning and the subsequent changes that we do before the environment is deployed, and the planning and changes that we do after the environment is deployed and is in production. We can define these as “pre-configuration capacity planning” and “post-configuration capacity planning”. The pre-configuration capacity planning is often referred as “sizing” the database infrastructure. The post-configuration capacity planning is an ongoing cyclic process of monitoring and projecting, planning and modifying. In precise, Capacity Planning for a database infrastructure can be pictorially represented as below.
In each of the upcoming sections, we will be discussing these processes in detail, and how to carry out each process for the various hardware and other requirements.
Capacity Planning - Series Start
I was asked by my department to take a session on how to do the capacity planning process for a database infrastructure. Since, the community includes data services professionals across a lot of platforms and communities, I tried to make the session a very generic one. I will be writing a series of posts to cover what all I presented in the session. I know there will be a few instances where things would be explaned with the help of SQL Server (as that is by far my expertise); forgive me for that :-)
Subscribe to:
Posts (Atom)
