Ensuring optimal performance is one of the most important goals of IT professionals. And for DBAs, tuning and monitoring are often daily tasks. But there are many factors involved in properly optimizing, monitoring, and tuning databases and the applications that access them—too many to do the topic justice in a single column. Nevertheless, we can address the basics!
Even in today’s modern IT environment, performance management is often conducted reactively instead of proactively. You know the drill. A client calls with a response time problem. A table space maxes out on extents. A program is running without taking commits causing all kinds of locking problems. Somebody changed an application package without checking on the new access paths and transactions are slower than before. And then somebody submitted that “query from hell” again that just won’t stop running. Sound familiar? All too often DBAs are being asked to respond reactively to problems that could have been prevented, if only we implemented more proactive performance management steps. So, we must routinely and reactively put out the performance fires.
Yet management and consultants continue to promote the benefits of proactive management. And, of course, they are correct. Being proactive can reduce, or even eliminate many of the problems that DBAs deal with on a daily basis.
Many of the supposedly proactive steps taken against completed applications in production are mostly reactive. DBAs are often too busy taking care of day-to-day tactical tasks to be able to proactively monitor and tune their systems like they wish they could. Setting up a proactive performance infrastructure takes time and effort, and time is not something that DBAs have in abundance as they tackle their required daily tasks.
Of course, tools and monitors are available that can make performance management easier by automatically taking pre-defined actions when alerts are triggered. Some of these tools can analyze problems and suggest solutions. But before you tackle performance problems you’d be wise to set up service-level agreements (SLAs) that define agreed-upon performance criteria. To be effective, a service-level agreement must specify what is being serviced, the response time or availability required, who the service is being delivered to, who is responsible for ensuring the service level, and the budget being allocated to enable the service. All too often, SLAs are either incomplete or worse, not created at all.
Defining Database Performance
All this talk about database performance and we have yet to define what that means, so let’s do so! Think about the familiar concepts of supply and demand. End users demand information from databases, and the DBMS supplies information to those requesting it. At a high level there are 5 factors that influence database supply and demand: workload, throughput, resources, optimization, and contention.
Workload is a combination of online transactions, web requests, batch jobs, ad hoc queries, data warehousing analysis, utility jobs, and other commands directed through the system at any given time. It can fluctuate from day to day, hour to hour, and even minute to minute. Sometimes workload is predictable (such as heavy month-end processing, or very light access after 6:00 p.m. and before the nightly batch cycle). At other times it can be unpredictable. The workload to be processed has a major impact on database performance.
Throughput defines the overall capability of the computer to process data. It is a composite of the hardware being used, CPU speed, I/O speed, any additional co-processors and specialty processors involved, parallel capabilities of the hardware and software, and the efficiency of the operating system and system software.
The hardware and software tools at the disposal of the system are known as the resources of the system. Examples include memory (such as that allocated to buffer pools, sorting or address spaces), disk subsystems, cache controllers, microcode, and so on.
The fourth defining element of database performance is optimization. All types of systems can be optimized, but relational database systems are unique in that query optimization is primarily accomplished internal to the DBMS. Nevertheless, there are many other factors that need to be optimized including program code, database object definitions, system parameters, and so on.
When the demand (workload) for a particular resource is high, contention can result. Contention is the condition in which two or more components of the workload are attempting to use a single resource in a conflicting way (for example, dual updates to the same piece of data). Contention can cause portions of the workload to be paused until competing workload has completed. Simply stated, as contention increases, throughput decreases.
So, with all of that in mind, we can put the pieces together and define what we mean by the term “database performance.” Database performance can be defined as the optimization of resource use to increase throughput and minimize contention, enabling the largest possible workload to be processed.
Now that we have defined database performance, you can use it as a starting point to create a strategy for ensuring optimal performance at your organization.