|
Performance Tuning:
Virtually
all systems and applications are sophisticated
by design and are highly tunable. Therefore small
adjustments can affect your system, database
and application performance. By tuning your system,
P4 can tailor the relationships between your
applications and databases to achieve the highest
level of performance to best meet your business
needs.
Database performance tuning is the iterative
process of analyzing the ramifications of hardware
and/or software configuration changes with the
intent of increasing application performance
while minimizing costs.
P4 has successfully assisted
in many database performance tuning engagements.
Armed with a
proven testing methodology and test automation
best practices, P4 has provided an integral
solution for resolving many issues associated with
the
relational database management system (RDBMS)
and operating system kernel parameters. These
include:
- providing an inventory of slow or inefficient
database queries
- determining the proper size
of connection pools to support the arrival rate
of SQL requests
- discovering the inability of a RDBMS
to scale on a multiprocessor database server (RS/6000
SMP)
- establishing the best configuration
sizes for data and procedure caches
- ascertaining
the best hardware platform to implement
- discerning
the most efficient auditing scheme that would prevent
deadlocks, while
maintaining history of the business processes
- validating the correct indexes
to employ, such as clustered indexes versus non-clustered
indexes
The database performance tuning realm also
extends to Web clients, such
as application servers,
and fat clients (i.e. Powerbuilder,
Visual Basic, C++). P4 has pinpointed issues
relating to the
manner in which data is requested
and client/server communication is enabled.
How does P4 solve the
problem?
Regardless of the RDBMS that is implemented,
P4 has a solution for tuning
database servers and their clients. Although
the configurable
parameter terminology differs
by
platform, the same performance tuning
concepts apply to all
database server vendors.
- Determine
the level of tuning - Component-level tuning
or
system-level tuning? Do you
want to tune the database
server as
an isolated
component
or as part of a larger application?
- Understand
the end-user community - Gather metrics
regarding the
manner in
which the database
will be accessed. What
SQL queries will be executed?
What business
transactions will be
executed?
How often are transactions
executed?
- Gather performance
requirements - Determining the exit
criteria for tuning
needs to be
established in order
to know when sufficient testing
has
occurred.
- Automate test
scripts - Create automated test
scripts
that issue
the necessary
SQL queries, updates
and deletes. Generate
automated
test
scripts that emulate
the business scenarios.
- Execute & analyze
tests - Run the planned
tests and
collect metrics, such
as response times,
transaction volumes,
operating system statistics, database
server statistics.
- Application Profilers - Implement
ancillary tools to
profile transaction
characteristics.
Determine the network
characteristics of
a transaction, such
as bandwidth utilization
and conversational
chattiness. Ascertain
the CPU utilization
on
the database server
and client, memory
utilization,
query compilation
and
execution times.
The
Solution
As a result, database
server capacity and
scalability is increased
by
addressing:
- the use
of a small packet size between the client and
the server
- chatty conversation over high latency network
links
- large amounts
of unused data returned to the client
- redundant
database queries
- additional tuning methods
Deliverables
At the conclusion of the project, P4 provides
an executive
summary report illustrating performance
of your
application and/or database
server as quantified
by response times, throughput, application
and communication
errors, system resources and capacity,
as related to
the particular
database
server
tuning parameters.
The engagement
will
also provide a suite
of automated
test
scripts that
can be
used
for future
testing and tuning
endeavors along with a
set of
best practices for approaching
database
server
performance tuning. |
Process