Capacity Planning

A Case for Capacity Planning

A Case for Capacity Planning Embarcadero Capacity Analyst By Rob Young Director of Administration Tools, Embarcadero Technologies, Inc. July 2003

Embarcadero Technologies 425 Market St., Suite 425 San Francisco, CA 94105 www.embarcadero.com

Embarcadero Technologies

-1-

A Case for Capacity Planning

Table of Contents A Case for Capacity Planning ........................................................................................ 1 Embarcadero Capacity Analyst.................................................................................. 1 A Case for Capacity Planning ........................................................................................ 3 Embarcadero Capacity Analyst.................................................................................. 3 Introduction........................................................................................................................ 3 Example 1 – Large Database Growth – A Lesson in Smart Asset Allocation ........ 4 Example 2 – Practical Application – Keeping Track of Departmental Charge Backs.................................................................................................................................. 6 Example 3 – The Case of the New Index – Will it do the .......................................... 8 Conclusion......................................................................................................................... 9 About the Embarcadero Analyst Product Line ............................................................ 9 About Embarcadero Technologies ................................................................................ 9

Embarcadero Technologies

-2-

A Case for Capacity Planning

A Case for Capacity Planning Embarcadero Capacity Analyst By Rob Young, Director of Administration Tools, Embarcadero Technologies, Inc. July 2003

Introduction When it comes to designing and building a new enterprise database, smart database professionals understand the importance of planning. In fact, most of the early work centers around the specifications for platform, sizing, anticipated growth, and needed infrastructure. This is time well spent as it helps to ensure: • • • • •

The database is sized correctly for the data it will house The data relationships and dependencies are designed to service application users as efficiently as possible Applications that consume the database experience the highest level of response and reliability possible All funds needed for infrastructure are planned and accounted for in the project budget The project and resulting database provide the expected ROI at delivery and into the future as the organization matures

For a new implementation the pieces of the capacity question are easy to plug into the equation used to determine the overall database solution. A simple rule of thumb for building the capacity needed for a new database (and for building anything that requires a best guess estimate for that matter) is to calculate the storage numbers, add 50%, and then start building. Building on an existing database framework is similar but introduces several new opportunities to fine-tune and hone in on a database’s true growth and performance chart. To build successfully, the following questions have to be asked: • Can you accurately predicate where a database is headed based on where it has been? • Can information available about the database today help reduce the overall cost of ownership and help maximize the owner’s return on investment? • Can you test and track the success of your database adjustments to ensure your changes have hit the intended mark? The answers to these questions may seem trivial, but they are rooted in the ability to proactively plan and manage database capacity andcapacity. And, with the right tools and knowledge, database professionals can intelligently trim outages from the database and expenses from the bottom line. Embarcadero Capacity Analyst, one of the components in Embarcadero Technologies’ Analyst product line, offers intelligent insight into the past, present and future inner workings of a database. By collecting historical usage and performance metrics at user-defined intervals,

Embarcadero Technologies

-3-

A Case for Capacity Planning

Capacity Analyst provides historical trend analysis along with smart forecasting abilities that allow a database professional to spot problems before they happen and to plan for the future needs of their mission-critical databases. This balance of this paper will focus on three case examples of capacity-related projects that exemplify the need for proactive and accurate database capacity planning.

Example 1 – Large Database Growth – A Lesson in Smart Asset Allocation True story: In 1998, two large public utilities located in the Midwest (names withheld to protect the guilty) forged a merger strategy that would combine the two organizations and consolidate the affected customer base into a single service block. When it was decided to consolidate customer bill formats and to extend web payment options to all customers, a project was started to combine billing data and print images on a common Oracle database. Each of the original customer groups remained on independent mainframe-base customer support databases, all of the bills produced by the separate billing systems would be printed as normal, but then the images would be stored into the common database where they could be accessed by Customer Service via a CRM application and by the paying customer via a public website. Upon implementation of the common billing database and supporting applications, the overall system required little support; response times were good, batch windows were in the acceptable range and the plan was to review the overall resource usage on a quarterly basis and make adjustments when needed. On the outside the project was a raging success. Customers had access of up to 3 years of historical billing records, customer service could easily reprint bills for mailing and the organizations had cleared a major integration hurdle. But this success story is somewhat tarnished in that while the project was a success in the implementation phase and the end result did accomplish the goals of the project, the overall outlay exceeded the budgeted funds by over $500,000. How is this possible? First, there was no analysis done on the content, redundancy and storage requirements associated with storing images of all printed bills for all customers. Lack of a plan in this area guaranteed that all bills would be stored in the database any time they were printed or reprinted, regardless of if they had changed or not. Second, assuming all bills for each billing cycle would need to be stored, there was a rush to buy a very large storage area network (SAN) to house the bill images, with supporting infrastructure, andinfrastructure; and, there was no proactive investigation on how to utilize the assets currently owned by the organization. The first problem is a pure business logic problem with implications to how quickly database storage will be consumed. The second speaks loud and clear on the importance of due diligence when it comes to properly planning for capacity needs. While the storage requirements for the billing images was very large, the overall capacity to store the data over time only had to account for a rolling 36 months of data. Embarcadero Capacity Analyst is tailor made to help avoid such project cost overruns due to uninformed estimations and assumptions. While it cannot fix poor business logic, it can help you with capacity related purchasing decisions allowing you to collect data for all of your storage objects over time. The process to set up of collection jobs is simple and the framework for

Embarcadero Technologies

-4-

A Case for Capacity Planning

capturing valuable information regarding the rate by which database resources are currently consumed. Embarcadero Capacity Analyst makes it easy to set up collections for storage and database objects via a wizard driven process. To track growth patterns for storage, there are options to track Storage Structures and Database Objects, and then at a lower level, all of these objects or selected subsets of each:

Embarcadero Capacity Analyst then allows selection by schema or object, so in the above example, if you know all billing data will be confined to a set of tables, you can track the growth of only those tables. If the billing data will be exploded across the database, you can expand the capture to all tables and indexes under a specified schema. At this point the collection can be scheduled to run using Windows Task Manager or Embarcadero Job Scheduler so the space growth can be recorded at user-defined intervals. Collected data can then be used for immediate analysis or extrapolated out for forecasting the growth pattern over time. In the above example, Embarcadero Capacity Analyst collections could have been set up early in the project analysis phase to run against production mock ups of both billing databases to determine growth patterns for a set period of time. At the point that the DBAs were ready to begin the analysis to “right size” the common database to meet the immediate, future space, and performance requirements, Capacity Analyst could be used to visually represent where the space requirements would lead for a specified time period. Based on purge and archive rules built into their current administration schedules, the DBAs involved could use the historical growth of the two billing databases to determine that the combined database would be very large at implementation, but growth would flatten and only increase with new customer additions and changes in the size of the printed bill.

Embarcadero Technologies

-5-

A Case for Capacity Planning

Example 2 – Practical Application – Keeping Track of Departmental Charge Backs Many organizations have adopted the concept of the internal profit center. Basically, each business unit is held accountable for ensuring its expenses do not exceed the value it adds to the overall balance sheet. When it comes to tangible overhead items (office supplies, travel, etc) it is fairly straightforward to chart and assign expenses on a monthly basis. For true charge back implementations, businesses also have to determine an equitable method for recovering and billing out expenses related to “E-expenses”, or any activity that requires an IT or database backing. The models available for tracking who is using what becomes somewhat muddied on this front, but the metric most readily and most commonly used is physical storage. Embarcadero Capacity Analyst allows you to build metric collections for all of your storage structures. Whether you are collecting statistics for all of your tablespaces, only non-temp tablespaces or a set of specific tablespaces, Embarcadero Capacity Analyst allows you to analyze historical data for total, used, free, percent used and percent free space. Because all Embarcadero Capacity Analyst collection data can be analyzed and reported at the year, month or day level, all usage can be set up to correspond with other general ledger based billing cycles. An example of how this might work: XYZ corporation currently charges back all database usage to the departmental owner(s) of the data. Traditional methods for collecting usage data for monthly consumption might involve a time-consuming process of running scripts and interpreting data and then forwarding the percent increase in consumption on to the general ledger accountants for departmental billing. Embarcadero Capacity Analyst greatly simplifies this process by allowing DBAs to set up usage metric collections for specific tablespaces:

Embarcadero Technologies

-6-

A Case for Capacity Planning

Once set this collection can be scheduled to run at regular intervals to ensure accurate growth and consumption is captured. At the end of the billing cycle, the DBA can visually review how each tablespace has increased as follows.

For easy distribution this information can quickly be funneled to a Growth Trend report, shown below. Note that the report includes Growth Rate Percentage details on each of the selected tablespaces for the time specified:

Embarcadero Technologies

-7-

A Case for Capacity Planning

Example 3 – The Case of the New Index – Will it do the OK, so now you have added that new index to the hottest table in your database. You know that your work will silence your phone and your pager (at least until the next problem comes up), but can you be sure you indexed the right columns on the right table? Embarcadero Capacity Analyst allows you to monitor your changes and analyze performance related trends to determine if your work has proven effective. Consider the above examples;examples: you have established collections to record metrics on storage and object growth, so why not also build a simple collection to stockpile information on your performance related statistics? Embarcadero Capacity Analyst allows you to collect historical statistics on a full range of performance statistics for your database (physical reads, disk I/O, table scans, etc). As with storage and object related collection analysis, you can graphically track what is currently happening with performance and further to see how your environment will improve or degrade over time:

In the above example, the Capacity Analyst collection is tracking physical reads and full table scans and the impact on performance. The upward trend is based on the statistics gathered by a collection that runs every 2 hours Monday – Saturday. As a DBA, you may recognize that this trend is ultimately detrimental to user response time so you identify several tables that could benefit from additional indexes. After altering the tables and allowing the performance collections to complete a 24-hour cycle, you can analyze the collection data to determine if your changes will have an impact on the forecasted performance of your database:

Embarcadero Technologies

-8-

A Case for Capacity Planning

Conclusion While the examples in this paper have been rather simplistic they should help to drive home the important role Capacity Planning plays in preserving and optimizing your enterprise data assets. Embarcadero’s Capacity Analyst is designed to help ensure you get the most out of your current database resources, while allowing you to recognize and plan for when those assets need to be extended. This functionality is available in a user-friendly product that allows you a “set it and forget it” approach to proactively managing and planning for your current and future data asset needs.

About the Embarcadero Analyst Product Line Embarcadero Technologies’ Analyst products provide advanced management and performance diagnostic capabilities for today’s database professional to use in managing complex database environments. Powerful enough for advanced experts, yet easy to use for database novices, all Analyst products from Embarcadero contain built-in intelligence that ease the process of troubleshooting and resolving problems that threaten the availability and performance of missioncritical databases.

About Embarcadero Technologies Embarcadero Technologies’ software products enable companies to build, optimize and manage their critical business applications and underlying databases. Embarcadero leads the market in providing high quality, easy-to-use database and application development tools that deliver costeffective solutions.

Embarcadero, the Embarcadero Technologies logos and all other Embarcadero Technologies product or service names are trademarks of Embarcadero Technologies, Inc. All other trademarks are property of their respective owners.

Embarcadero Technologies

-9-