Performance refers to the way that a computer
system behaves in response to a particular workload. Performance is
measured in terms of system response time, throughput, and resource
utilization.
Performance is also affected by:
- The resources that are available on the system
- How well those resources are used and shared
In general, you will want to tune your system to improve its cost-benefit
ratio. Specific goals could include:
- Processing larger, or more demanding, workloads without increasing
processing costs
- Obtaining faster system response times, or higher throughput,
without increasing processing costs
- Reducing processing costs without degrading service to users
Some benefits of performance tuning, such as a more efficient use
of resources and the ability to add more users to the system, are
tangible. Other benefits, such as greater user satisfaction because
of quicker response times, are intangible.
Performance tuning guidelines
Keep the following
guidelines in mind when developing an overall approach to performance
tuning.
- Remember the law of diminishing returns: The greatest performance
benefits usually come from your initial efforts.
- Do not tune just for the sake of tuning: Tune to relieve
identified constraints. Tuning resources that are not the primary
cause of performance problems can actually make subsequent tuning
work more difficult.
- Consider the whole system: You cannot tune one parameter
or resource in isolation. Before you make an adjustment, consider
how the change will affect the system as a whole. Performance tuning
requires trade-offs among various system resources. For example, you
might increase buffer pool sizes to achieve improved I/O performance,
but larger buffer pools require more memory, and that might degrade
other aspects of performance.
- Change one parameter at a time: Do not change more than
one factor at a time. Even if you are sure that all the changes will
be beneficial, you will have no way of assessing the contribution
of each change.
- Measure and configure by levels: Tune one level of your
system at a time. System levels include:
- Hardware
- Operating system
- Application server and requester
- Database manager
- SQL and XQuery statements
- Application programs
- Check for hardware as well as software problems: Some performance
problems can be corrected by applying service to your hardware, your
software, or both. Do not spend excessive time monitoring and tuning
your system before applying service to the hardware or software.
- Understand the problem before you upgrade your hardware:
Even if it seems that additional storage or processor power could
immediately improve performance, take the time to understand where
your bottlenecks are. You might spend money on additional disk storage,
only to find that you do not have the processing power or the channels
to exploit it.
- Put fallback procedures in place before you start tuning:
If tuning efforts result in unexpected performance degradation, the
changes made should be reversed before you attempt an alternative
approach. Save your original settings so that you can easily undo
changes that you do not want to keep.
Developing a performance improvement process
The
performance improvement process is an iterative approach to monitoring
and tuning aspects of performance. Depending on the results of this
performance monitoring, you will adjust the configuration of the database
server and make changes to the applications that use the database
server.
Base your performance monitoring and tuning decisions
on your knowledge of the kinds of applications that use the data and
on your understanding of patterns of data access. Different kinds
of applications have different performance requirements.
Any
performance improvement process includes the following fundamental
steps:
- Define the performance objectives.
- Establish performance indicators for the major constraints in
the system.
- Develop and execute a performance monitoring plan.
- Continually analyze monitoring results to determine which resources
require tuning.
- Make one adjustment at a time.
If, at some point, you can no longer improve performance
by tuning the database server or applications, it might be time to
upgrade the hardware.
Performance information that users can provide
The
first sign that your system requires tuning might be complaints from
users. If you do not have enough time to set performance objectives
and to monitor and tune in a comprehensive manner, you can address
performance issues by listening to your users. Start by asking a few
simple questions, such as the following:
- What do you mean by "slow response"? Is it 10% slower than
you expect it to be, or tens of times slower?
- When did you notice the problem? Is it recent, or has it always
been there?
- Do other users have the same problem? Are these users one or two
individuals or a whole group?
- If a group of users is experiencing the same problem, are these
users connected to the same local area network?
- Does the problem seem to be related to a specific type of transaction
or application program?
- Do you notice any pattern of occurrence? For example, does the
problem occur at a specific time of day, or is it continuous?
Performance tuning limits
The benefits of
performance tuning are limited. When considering how much time and
money should be spent on improving system performance, be sure to
assess the degree to which the investment of additional time and money
will help the users of the system.
Tuning can often improve
performance if the system is encountering response time or throughput
problems. However, there is a point beyond which additional tuning
cannot help. At this point, consider revising your goals and expectations.
For more significant performance improvements, you might need to add
more disk storage, faster CPUs, additional CPUs, more main memory,
faster communication links, or a combination of these.
DB2 10.5 for Linux, UNIX, and Windows
