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
