Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 16, 2010

A Perspective on Database Performance Tuning

By Julian Stuhler

Fundamentally, database performance tuning is done for two basic reasons, to reduce response time and to reduce resource usage, both of which can apply for any given situation. Julian Stuhler looks at database performance tuning, and why it remains one of the most important topics for any DBA, developer or systems administrator.

Last month, I covered IBM DB2 10 for z/OS: Justifying the Upgrade , and spoke briefly about the performance enhancements in that release that can help in justifying the upgrade to hard-nosed business execs looking for real return on investment for infrastructure projects.

In this article, I want to take a more general look at database performance tuning, and the reasons why it remains one of the most important topics for any DBA, developer or systems administrator. I’ll also cover some golden rules for performance tuning, which tend to apply regardless of the specific platform or application you’re responsible for.

Why Bother with Database Performance?

So, what’s the big deal with database performance? Fundamentally, database performance tuning is done for two basic reasons, and either or both can apply for any given situation:

  • Reduce response time. With today’s internet-enabled, customer facing applications, providing sub-second response times for OLTP applications has never been more critical. A few years ago, a skilled telephone customer support operative could hide longer response times for account queries by chatting with the caller, but now that the customer has direct access to those same systems, any delays become much more obvious. Many studies have demonstrated the link between response time and customer satisfaction for online B2C applications, and when your nearest competitor is only a click away, keeping customers happy is paramount. Although database performance is just one component of the overall end-to-end response time experienced by the customer, it is often the most critical and variable one.
  • Reduce resource usage. Running any sort of query against a database generates a load on the database server, and the less efficiently that query runs the more CPU and I/O it will require. These are finite resources for a given server configuration, so the transaction throughput can be limited at peak times (potentially leading to a response time issue as described above). Tuning a system to reduce the CPU and/or I/O resources required can have a significant impact on the transaction throughput for a given server, potentially allowing expensive upgrades to be avoided or deferred. Reducing resource usage can be even more important in a mainframe environment, where most customers are on some form of Monthly Licence Charge (MLC) model. With MLC, the amount a System z customer pays depends on the amount of processor resource consumed during the previous period, measured in MSUs (Million Service Units). So, the monthly cost paid by most customers for their IBM software is directly related to the amount of work their CPs process: the higher the workload, the higher the monthly cost. Tuning the application to reduce CPU costs can generate immediate savings in software licence fee charges.

Some Golden Rules of Database Performance

OK, so you’re convinced of the benefits of tuning, but where do you start? Many people (including me) have built their careers around the dark art of database tuning, and there is an enormous amount of product and platform specific knowledge needed to eke the very best performance out of a given application. However, there are also some generic rules, which apply to all tuning situations, and I’m always amazed at how often some or all of these are ignored.

1.  Define your objectives. Tuning is a journey: how will you know you’ve arrived if you don’t know your destination? Start by clearly defining a realistic objective for the exercise, such as “reduce average response time for 95% of my Order transactions to less than 1 second” or “increase average throughput from 100 to 150 transactions per second during peak hours).

2.  Know when to quit. Performance tuning can be both fun and addictive. You can often take a 10-second query and make it run in 2 seconds with a little basic tuning, and that feels good. A little more effort might bring that down to 1.5 seconds. Spending still more time might squeeze it down to 1 second, which would give you real bragging rights at the next DBA team meeting. But wait a moment: remember Rule #1? When was the last time you set out on a journey, arrived safely and in good time, but decided to continue driving until you were late for your appointment? Tune until your objective is met, then leave it alone: there will be plenty of other queries that need your attention!

3.  Prevention is better than cure. However valuable monitoring and tuning your production system is, you should consider it to be a last resort. The later in the application life cycle a performance problem is detected, the more expensive it is to fix. It is far better to define your performance objectives up front (see Rule #1 again), design the application and database with those objectives in mind, and test as fully as possible during development to maximise the chances that those objectives will be met. Of course, no testing can hope to fully simulate real production conditions and some tuning will probably be necessary when the application actually goes live, but catch the big issues early and you can be out celebrating that first night rather than at your desk fixing major performance problems.

4.  Identify the culprit. In today’s complex client/server, SOA-enabled, multi-platform landscape, a bewildering number of components could be involved in any given business transaction. The user is experiencing a response time problem, but which of the individual components is to blame? Don’t automatically assume it’s the database and dive into a tuning exercise. Treat this like any good murder mystery, using logic and perseverance to eliminate potential culprits until you have isolated the component responsible. Sometimes that will indeed be the database, but often it will be some other element (such as the network, the application server, or the operating system).

5.  Change only one thing at a time. You’ve analysed the situation and you have several potential solutions: perhaps you could change the order of some columns in that index, or maybe move the whole index to a different buffer pool. Decide on what’s likely to give you the biggest benefit, make that single change and re-measure. Implementing multiple tuning actions at once makes it impossible to see the benefit of each one individually: what if some are helping and others are making things worse?

6.  One accurate measurement is worth a thousand expert opinions. Also known as “measure twice, cut once”, this is all about making sure you understand the issue before trying to resolve it. Measurement, via the many diagnostic and trace facilities provided by DB2 and other databases, is key to understanding the problem and being able to formulate a suitable tuning response.

7.  Remember Heisenberg’s Uncertainty Principle. This can be stated as Ñ c Ñ p ³ h / 4p ... or more commonly “The observer affects the observed”. As per Rule #6, it’s not unusual to have to switch on non-standard performance traces in order to pin down the cause of a given performance issue, but always be aware that the very fact that you’re switching on those traces is going to make performance even worse. Trace for the minimum period necessary, against as small a subset of the workload as possible, using the least expensive trace required to allow your analysis to proceed.

I hope these will be useful to you in your tuning endeavours. Next month I’ll be returning to the subject of DB2 for z/OS Version 10 and covering some of the exciting new features and functions in that release in more detail. See you then!

Additional Resources

Tuning DB2: Where Your Data Is and Why it Matters
Decrease IT Costs with IBM DB2 9.7
developerWorks: DB2 Memory and File Cache Performance Tuning on Linux
Redbooks: Best Practices for DB2 on AIX 6.1 for POWER Systems
Redbooks: DB2 9 for z/OS Performance Topics
Redbooks: Using Integrated Data Management To Meet Service Level Objectives

» See All Articles by Columnist Julian Stuhler

DB2 Archives