Cost Effective Capacity Modelling - A Real-life Case Study
Capacitas® is a technology consulting, training services and managed services company focussed on the performance, capacity and cost of Information and Communication Technology. This article describes how Capacitas developed a capacity model for a client using Microsoft® Excel.
Background
The client needed to determine the server capacity requirements of a large real-time billing system over the next two years. A detailed capacity model was required to predict the memory and CPU requirements and model ‘what-if’ scenarios; including the purchase of a new hardware platform to support future growth in demand.
The model was to be used and maintained by the organisation without requiring ongoing support from Capacitas. As well as the model, a two-day training course on how to use and maintain the model was delivered. Both deliverables were completed by Capacitas within ten weeks elapsed time.
System Architecture
The system ran on two (fully populated) HP 9000 Model V2500 servers with 32 CPUs and 32GB of RAM. The main components of the system were:
- Machine 1 – Proprietary Memory Resident Database (MRDB)
- Machine 2 – Two Oracle databases
- Machine 3 – Hot standby
HP Service Guard was used to failover packages between servers.
Model Design
Models produced using Excel are often difficult to use, as they may involve tens of worksheets (the model in this case study had over seventy). Simple VBA code was used to give a professional look and allow easy manipulation of the model. Clicking on the appropriate button on the ‘Overview’ worksheet navigates the user to the required worksheet, whilst a button in the upper left of each worksheet or chart returns the user to the ‘Overview’ worksheet (see Chart 1).
In order to provide ease of model development, two modes of model operation were implemented. In ‘User Mode’ calculation worksheets are inaccessible; gridlines, row and column headings and sheet tabs are hidden; worksheets are protected. In ‘Administrator Mode’ (accessible using a password) all aspects of the model structure may be viewed and edited.
All of the associated assumptions were documented as part of the model itself. A ‘Display/Hide Notes’ button toggles the notes on the ‘Overview’ worksheet.
‘What-if’ Scenarios
The model contains configuration data that may be changed to model ‘what-if’ scenarios.
- A list of future releases, along with their planned implementation dates
- The existing and proposed hardware platforms for the system, allowing the different proposed hardware solutions to be modelled
- Modelling of scenarios such as failover through configuration of which server each of the HP Service Guard packages is running on Model Resilience
In order to ensure the model may be used by anyone, it had to be as resilient as possible. The ‘Errors and Warnings’ worksheet checks that the model is operating within acceptable parameters, and displays a message if it is not. Errors and warnings are defined as follows:
- An ‘error’ means that the model is not working correctly, and that the results may be invalid
- A ‘warning’ means that a threshold has been breached, and that the system is expected to have insufficient capacity to support the modelled scenario Customer Numbers and Forecasts
The key business driver for system capacity was found to be customer numbers. The model contains estimates of future business volumes, expressed as number of CAN (CANcelled accounts) and NAC (New ACcounts) transactions per day during each month. Collection of historical customer numbers in the model allowed tracking of forecasts against actual values over time.
Memory Modelling
One important goal of the model was to size the Memory Resident Database (MRDB). The database was written so that each customer had a fixed length database record that was pinned in memory at all times. This meant that the principle memory requirement of the system was the product of the number of customers and the fixed record size.
Separate worksheets allowed the expected future configured sizes of other major users of memory such as operating system Buffer Cache and Oracle Shared Global Areas for each release to be entered.
In order to conserve memory the client planned to re-write the proprietary database to allow variable length customer records, resulting in a reduced memory requirement. As such, the model contained a summary of all of the estimated sizes of the proprietary data structures broken down by release. Once variable record lengths were implemented the record sizes were based on a statistical analysis of the number of customer price plans contributing to the theoretical record size.
The chart below shows the forecast memory requirements over time for one of the machines, broken down by workload.
CPU Workload Characterisation
The % CPU time forecasts in the model were calculated from CPU service times based on measurements taken from the production system. A strong linear correlation between per process % CPU time measurements for three UNIX processes (X_server, Y_server and mrdb_agent) and the two key business transaction types was established.
The CPU cost per business transaction was used by the model to predict a CPU service time on the new hardware. Benchmarks from the System Performance Evaluation Corporation (http://www.spec.org/) were used to estimate the relative CPU performance of the existing and proposed hardware. The following chart shows how the model displays predicted % CPU time by half-hour for a 48-hour period for each machine:
Conclusions
A vendor-neutral approach to the problem meant no ongoing model licensing or opex costs. A parameterised modelling solution provided the flexibility to adjust parameters over time to reflect real-world events. Business volumetrics forecasts were relatively simple to incorporate into the model compared to using commercial tools. Further, because the model was bespoke it was easier to handover to the client as it did not include any unwanted functionality. The capacity model reduced the risk of service unavailability due to capacity constraints; ensuring that the system had sufficient capacity to support the peak demand for the next eighteen months, at a cost equivalent to approximately 1% of the daily revenue dependent on the system.