Database Capacity Planning

Most database implementations are anything but static. Once deployed, databases are queried, updated, loaded, unloaded, reorganized, and data is deleted and inserted from them on an ongoing basis. Data is also moved around and replicated all over the place. All of these activities resulting in database changes cause storage requirements to change as well.

The DBA must be ever vigilant to plan for future growth. To do so involves storage and capacity monitoring. The DBA must keep an eye on the amount of data and the number of users accessing the data. When either expands, database storage may have to be modified. And as usage increases, computing capacity may need to be adjusted, as well.

As such, DBAs must be skilled in the discipline of capacity planning. At its highest level, capacity planning is a process whereby the storage required for an entire system is measured and compared against requirements. The goal of this comparison is to adjust the resources available to the system as appropriate. To successfully embark on a capacity planning exercise requires an understanding of new corporate initiatives and how they are likely to impact existing infrastructure—that is, your hardware (CPU, storage, network, and memory) and your software (application and system).

By measuring current capacity, gauging the growth of capacity over time, and factoring in the anticipated capacity requirements of new corporate and IT initiatives, you can determine whether your existing infrastructure can sustain the anticipated workload. If the projected growth outpaces the ability of your computing environment to support it, you will need to evaluate the cost dynamics of modifying and possibly scaling up your computing infrastructure.

From a storage perspective this may involve simply adding more disk devices and assigning them to the DBMS. But it may involve additional tasks to support additional data and users including:

  • Redesigning applications
  • Redesigning databases
  • Modifying DBMS parameters
  • Reconfiguring hardware components
  • Deploying additional CPU capacity
  • Adjusting software interfaces

There are multiple perspectives from which to view storage consumption. Each is valid and provides a different view of storage utilization and growth. A system-wide perspective views the rate at which disk space is being consumed on an aggregate level. At a lower level, viewing capacity by server determines which computers are consuming disk space at the fastest rate. You also can monitor storage consumption at the file system level to determine which file systems are consuming disk space at the fastest rate.

As a DBA, though, you will want to view just those files that are associated with the DBMS. The bottom line is that you will need to be able to answer all of the following questions about storage when performing storage capacity planning:

  • When will more storage be required?
  • How much additional storage is needed?
  • Where is the additional storage needed?
  • What needs to be done to align the additional storage with the DBMS?
  • Are we reaching any capacity limits (file, database, server, etc.)?

Fortunately, there are tools that can be deployed to help DBAs answer these questions correctly. Multiple vendors offer storage management and reorganization products that chart the growth and organization of database files over time. By vigilantly monitoring database storage structures, a wise DBA can glean patterns from their databases and determine when more storage will be needed.

There are also capacity planning tools geared especially for database management. These tools go a step further by combining performance profiling and performance planning to allow DBAs to better understand current requirements and predict future expectations. Basically, a proactive capacity planning tool gathers instrumentation details and analyzes the data for trends. Upon recognizing a trend, the software makes recommendations to prepare for future capacity needs--or perhaps autonomically deploys changes on its own.

Simulations are also useful for determining future requirements. There are solutions that allow DBAs to play “what if” games with their environment to see what changes in usage, demand, and volume will do to their databases. Based on the results, the DBA can better prepare for multiple future scenarios.

Of course, technology alone is not sufficient. DBAs must be business savvy as well as technology savvy. This is especially true for capacity planning because only by staying on top of business changes will you have any chance of being able to successfully plan for the future of your systems. I mean, let’s face it, if you don’t know about that new product that will increase demand and thereby escalate data growth, the future storage needs of your databases will be beyond your ability to understand. And your databases will not be ready to accommodate the new business.

Although I have focused primarily on storage; CPU capacity is just as an important  consideration. It can be easier to scale up or out in a cloud implementation as cloud providers typically provide tools and APIs to achieve this. Doing so on-premises can be more challenging as it requires knowledge of your enterprise and its IT infrastructure; and it requires your organization to make all of the infrastructure changes including equipment, configuration, software, and so on.

The bottom line is that capacity planning is an important part of the DBA’s job, and it requires both technical knowledge and business acumen in order to succeed.