Database Performance and some Christmas Cheer

Thursday Jan 2nd 2003 by James Koopmann

James Koopmann demystifies some of the definitions surrounding the topic of database performance in his latest article. Whether a seasoned veteran or a beginner, the arena of database performance should not be confusing. Join Koopmann as he puts a framework around what database performance truly is.

This article's intent is to demystify some of the definitions surrounding the topic of database performance, and to put a framework around what database performance truly is. Whether a seasoned veteran or a beginner, the arena of database performance should not be confusing.

Most of the database performance books, or seminars have the same typical overtones to them. They typically present individual tuning solutions to narrow problems. While solving problems is all good and well, many beginners and veterans alike easily get confused when trying to piece the full puzzle of database performance together. Many books and seminars usually leave the reader/attendee with the following question:

  • Why does this problem exist?
  • What area of the database is the problem affecting?
  • What other areas of the database is the problem affecting?
  • Is it possible to measure if the tuning has been successful?
  • Are there steps to take if the tuning attempt has not been successful?
  • Are there any other ways to solve the same problem?

To get a grasp on some of these questions, the following framework is proposed. The framework should be followed, to categorize the performance attempts. Once the areas of performance tuning are categorized, it is possible to achieve a richer understanding of what the database is doing, and what affects it.

What Is Performance

It is generally agreed that performance is important, but what is performance? Performance can be defined as the ability of a system to deliver the results based on the request of the users, while keeping them satisfied. The key point to remember is satisfaction. If current database key indicators reveal that the database is running optimally, but users are not satisfied with the response times, is there a need for tuning? The answer is yes, (within reason, of course). Typically, if one looks closely enough, there is usually something that can be done to make users happier, or at least more educated.

Since the Christmas Season has just passed, let's compare performance to the task of putting up Christmas lights. In this instance, performance is the ability to put the lights up in a reasonable amount of time, before it gets dark, and before we get too cold (satisfaction).

Key Performance Metrics

When doing any form of database performance tuning, or applying the instructions from an article, please try and determine which of the following categories may be impacted. Most of the time more than one area will be impacted. (If you feel this list is incomplete or can't seem to make it work, please drop me a line).

How much is the database being asked to do? Do you know? You should! Workload is the combination of requests made upon the database. It is a great indicator for determining if everything is normal. If workloads are the same as always, and users are experiencing problems, it is safe to assume that something down the line processing user requests is not functioning properly.

If the workload has increased and people are having problems, the assumption can be made that an increase in workload will produce more of a strain on the system.

Workload is the collection of DDL (Data Definition Language) statements--the select, insert, update, and delete queries--which users and applications submit. Don't forget, it is also the administrative tasks that DBAs request of the database, everything from the scheduled backups, to the table that needs reorganization, to the indexes that have been rebuilt.

Back to our Christmas lights comparison. We need to:

  • Get (select) the lights from the garage
  • Go to the store and buy (insert) new lights or lawn ornaments
  • Replace (update) the burnt out bulbs from the current string of lights
  • Throw away (delete) any old or unusable lights.

Fixing the gutter to hang the lights on, or doing yard maintenance to clear a spot for the nativity scene could be compared to DDL.

Response Time:
Once the database acknowledges the request for work, it begins the work, and in a perfect world will complete the request. The time interval between the request and completion is the response time.

When stringing lights, if it takes two hours to go to the store, pick out the nativity scene, get home, and put it up, that is the response time to the request: "Honey, I would really like a nativity scene put in the front lawn this year."

As the database receives and processes requests, there is an underlying statistic that signifies the amount of requests that a database can handle over a period of time. This rate at which the requests can be performed is known as throughput. Throughput is a great indicator of productivity.

A string of lights has 200 bulbs, and takes five minutes to test for burned out bulbs. Your throughput is 200bulbs/5minutes, or 40bulbs/minute.

As a side note, if you get discouraged over the time it takes to put up all those lights, try equating the time and money you are forgoing if you were actually doing database administration work in a consulting role against the time and money you could spend to have someone else put up the lights. If the return on investment for you doing the Christmas lights is greater than someone else doing it, then great! If not, well, bah humbug.

By definition, once work has been submitted to the database and before the throughput and response time can be measured, something must be done to process the request. This "something" in the database performance world is called resource usage. In an attempt to over simplify things, there are two types of resource usage--good and bad. The good resources are those that are used in a positive way to produce the results; the bad are those resource usages that get in the way of good resources being used. The good could be actual disk reads that need to be done. The bad could be locks on objects that are in the way of reading data.

The good use of resources can be further broken into two types of usage, normal use and over use. Normal use is the perfect, limited consumption of a resource to produce a result. Over use is the bad side of resource usage where a resource is used beyond the desired amount. Whether a lot of good resources or a lot of bad resources are used, when mapped against time, that usage results in utilization. Typically, the resource hog or bottleneck in the system can be found if one looks at the resource with the largest utilization.

It took from sunup to sundown to put the lights up. The time spent was just over 7 hours. If everything within that seven-hour interval were mapped, time spent eating, talking, and resting would probably be tracked. If more time were spent talking than working, an over usage of a resource would take place, and a bottleneck in the light hanging would occur.

It is common knowledge that databases tend to be temperamental at times, networks go down, disks crash, and even databases crash sometimes. When resources, for whatever reason, take a vacation and inhibit any work from getting done, it is called a resource availability issue. A resource can only be available or unavailable and is measured as the amount of time it is available to work on requests. When a resource is available, it is known as uptime. When a resource is unavailable, it is downtime.

If a lot of holiday cheer were consumed while putting up the lights, several trips to the bathroom would probably be made. These trips resulted in downtime, no pun intended.

Not only do database systems crash, from time to time they produce errors. This has a direct correlation to database reliability. The ability to detect and predict the probability of errors is paramount.

After the seven-hour ordeal of hanging the lights, it is noticed that a few bulbs are out, and a few half-strands are unlit. Do not be the type of database guru that says a few burnt out lights are not noticeable. Take action and engage additional resources to fix the problems, or the problems will persist.

Final Thoughts

Everyone in the database community slings these terms around; the question is, do they understand them completely? Look at a few scripts to determine where they fit within the framework. At the least, you will understand your system better.

» See All Articles by Columnist James Koopmann

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved