|
Database Tuning |
|
|
With the advent of the e-commerce
insurrection, much focus has been directed
towards the web-based technology that
supports the Internet, such as Java, HTTP,
Web Services, XML, etc. The common thread
to
all of the new technologies is their
dependency upon relational databases to
provide data essential to their business
paradigm. Database platforms such as Oracle9i,
IBM DB2, Microsoft SQL Server and Sybase
Adaptive Server Enterprise
therefore
provide the core foundation upon which
business decisions are made and revenue is
produced.
Whether implementing an on-line transaction
processing system (OLTP) or decision support
system (DSS) within a standard client/server
application or distributed web-based
application, the requirements are the same:
1. retrieve the data as quick as possible,
2. support hundreds or thousands of
end-users, and
3. keep hardware and software maintenance
costs at a minimum.
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.
RTTS has successfully assisted in many
database performance tuning engagements.
Armed with a proven testing methodology and
test automation best practices, RTTS 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++). RTTS has pinpointed
issues relating to the manner in which data
is requested and client/server communication
is enabled.
How does RTTS solve the problem?
Regardless of the RDBMS that is implemented,
RTTS 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.
1. 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?
2. 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?
3. Gather performance requirements -
Determining the exit criteria for tuning
needs to be established in order to know
when sufficient testing has occurred.
4. 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.
5. Execute & analyze tests - Run the planned
tests and collect metrics, such as response
times, transaction volumes, operating system
statistics, database server statistics.
6. 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, RTTS
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.
|
|
|
|
|
Related WhitePapers
(free to download) |
|
"Ensuring Pharmaceutical Safety Data Integrity in a Data Warehouse" |
|
|
 |
|
To successfully deploy
a data warehouse, an
organization must be
confident that the
reports generated
display the proper
data. In the
Pharmaceutical
industry, where the
correctness of
Safety Data is
paramount for
reasons of both
public health and
compliance with
Federal regulation,
the issue of data
quality takes on
even greater
proportions. The
focus of this
whitepaper is to
explain how to
ensure that the data
in the data
warehouse is
properly loaded from
the source system(s). |
|
"Data Warehouse Testing: Pharma" |
|
|
|
|
This paper reveals the issues related
to testing data warehouses,
especially in the pharmaceutical
industry, and RTTS' strategies to
overcome the complexity of testing
the architecture. |
|
|
|
|
