A Perspective on Database Performance Tuning


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

Julian Stuhler
Julian Stuhler
Julian is a Principal Consultant with Triton Consulting, and has over 22 years relational database experience working in a number of clients within the insurance, telecommunications, banking, financial services and manufacturing sectors. In that time he has gained a significant amount of practical knowledge in many aspects of the IBM Information Management portfolio, including experience in application programming, Database Administration, technical architecture, performance tuning and systems programming. Julian is an IBM Redbook author and IDUG Best Speaker, and has lectured widely on DB2 subjects, UK, Europe and US. This includes presentations for the International DB2 Users Group (IDUG), Candle Performance Seminars, , BMC Seminars, and European GUIDE meetings. He is also a regular teacher for IBM throughout Europe. In 1999 Julian was invited to join the IBM Gold Consultants programme, used to recognize the contributions and influence of the world's 100 leading database consultants. In May 2008, Julian was recognized as one of IBM's inaugural Data Champions - a program to recognize individuals for outstanding contributions to the data management community. Julian joined the IDUG Board of directors in 2003 and is currently serving as the organization's Immediate Past President.

Latest Articles