The Definition of Database Performance

If you are a DBA or a performance analyst, chances are that you deal with database performance management every day of the week. But have you ever stopped for a moment and tried to define what you mean when you say “database performance?” That is the idea behind today’s post.

Don’t we need a firm definition of database performance before we can attempt to improve efficiency? Think, for a moment, of database performance using the familiar concepts of supply and demand. Users demand information from the DBMS. The DBMS supplies information to those requesting it. The rate at which the DBMS supplies the demand for information can be loosely thought of as “database performance.”

But let’s dive a little deeper. There are five factors that influence database performance: workload, throughput, resources, optimization, and contention.

The workload that is requested of the DBMS defines the demand. It is a combination of online transactions, batch jobs, ad hoc queries, data warehousing analysis, utilities, and system commands directed through the DBMS at any given time. Workload can fluctuate drastically from day to day, hour to hour, minute to minute, and yes, even second to second. Sometimes workload can be predicted (such as heavy month-end processing of payroll, or very light access after 6:00 p.m., when most users have left for the day), but at other times it is unpredictable. The overall workload can have a major impact on database  performance.

Throughput defines the overall capability of the computer to process data. It is a composite of I/O speed, CPU speed, parallel capabilities of the machine, and the efficiency of the operating system and system software. And don’t forget about those specialty processors (zIIPs and zAAPs) if you are a mainframe DBA.

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 bufferpools or address spaces), disk, cache controllers, and microcode.

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. However, there are many other factors that need to be optimized (SQL formulation, database parameters, system parameters, etc.) to enable the relational optimizer to create the most efficient access paths. And there are optimization aspects that are outside the scope and control of the relational optimizer, too, such as efficient script coding, proper application design, 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). As contention increases, throughput decreases.

So putting this all together, 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.

In addition, database applications regularly communicate with other system software, which must also be factored into performance planning. Many factors influence not only the performance of the DBMS and applications accessing its databases, but also the performance of the other system components (e.g. transactions processor, network software, application servers, etc.)

Advertisements

About craig@craigsmullins.com

I'm a strategist, researcher, and consultant with nearly three decades of experience in all facets of database systems development.
This entry was posted in DBA, performance. Bookmark the permalink.

3 Responses to The Definition of Database Performance

  1. Pingback: What is Database Performance?

  2. .r.a.s.g.a.d.d says:

    May I just say what a comfort to discover someone that really understands what they’re talking about over the internet. You certainly realize how to bring a problem to light and make it important. A lot more people need to read this and understand this side of the story. I was surprised that you are not more popular since you most certainly possess the gift.

  3. This unique post, “The Definition of Database Performance | Data
    and Technology Today” was in fact superb. I’m producing out a replica to
    present to my buddys. Thanks,Stephen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s