HomeSearchSite MapContact Us RemoteDBA Services for CODASYL DBMS and RdbPreserving Mission Critical IT Applications Through Virtualization

Remote Management of OpenVMS SystemsExtreme Performance Consulting


DATABASE
ADMINISTRATION

Justifying Database Tuning to Management
TECHNICAL ARTICLES -> PERFORMANCE ARTICLES
Next ]

Databases are complex products, which are expected to provide optimal performance in diverse environments. However, achieving the highest level (or even a reasonable level) of performance is not automatic – and how to do so is not always intuitive. Many of the default values used for database design and creation are indeed very poor choices. Database performance may be acceptable immediately after installing the application, and then may begin to deteriorate over time. Performance may degrade as additional data is added or as new functionality is utilized. Determining when it is appropriate to tune your database or review your application is often difficult. This article examines some of the tangible and intangible costs associated with poor system response time.

 

MEASURING THE COST OF REDUCED PERFORMANCE

 

People Costs

The cost of and to people is frequently the most significant costs associated with diminished database performance.

 

Idle Time – The amount of excess time spent waiting for the database to complete a task. This cost can be directly measured using the equation:

COST = TIME * RATE * FREQUENCY

 

Increased Stress – Stress is a natural result of being blocked from performing a job efficiently. Poor system response time feels like sitting in stop-and-go traffic on the expressway to any employee determined to remain productive. Their job is to get the product out the door – not waiting for the system to respond!

 

Reduced Moral – Confronted with ongoing poor response time, it is easy to lose hope for improvement. If your operations staff is pressured to produce more – and their support systems impede on their ability to be productive – low moral is a natural result. Unwillingness of management to invest in improving system response may ultimately lead users of your system to feel that their efforts to remain productive are going unnoticed.

 

While difficult to measure, the impact of increased stress and reduced moral on the operations staff can be very real, including:

 

  • Increased sick leave

  • Higher turnover rates (increased training costs)

  • Increased errors (reduced product quality)

 

Production Costs

Businesses invest enormous sums of money in the buildings, equipment and technology of their production facilities. To obtain a reasonable return-on-investment, these facilities must operate at full capacity. Database applications are frequently a critical component in the scheduling, operation and utilization of these production facilities. Poor application performance can cause under-utilization of these resources. Job's that do not complete on time can result in:

 

  • Reduced Production Levels

  • Overtime

  • Idle Equipment

  • Missed Deadlines

 

Hardware Costs

Buying new hardware may or may not be an appropriate solution. A Ferrari is no faster in stop-and-go traffic than a Chevy (or an AlphaServer is no faster than a VAX when waiting on a record-lock). Yet, investing in additional or faster hardware is a common knee-jerk reaction to poor system response time...and like the Ferrari in a traffic jam, it probably will not help you finish any sooner! Determining where resource bottlenecks are, and identifying their causes is often a more cost-effective solution than just "throwing more hardware" at the problem. The following outlines some common database problems for which additional hardware may not be the appropriate solution:

 

Excessive I/Os – A transaction that currently takes 3000 I/Os to complete, but should complete in 30 I/Os may slightly improve with faster disks – fixing the real problem would result in substantial improvements.

 

Locking Problems – If a process is blocked from accessing a record because another run-unit has the record locked, a faster computer will not improve response time! Locking is a frequent bottleneck in concurrent database applications.

 

MANAGEMENT HARDWARE FETISH

Management prefers to buy hardware (rather than solve the real problem) because:

 

  • Money spent on hardware can usually be depreciated over time (thus deferring its real costs). Tuning an existing database/application generally must be expensed.

  • Management believes it will eventually be able to use this ‘excess capacity’ anyway.

  • Hardware is tangible (you can touch and feel it – you know just what you got). Software design, analysis and tuning are intangible (it makes you feel better – but you are not sure why).

 

Choosing an Appropriate Solution

The appropriate solution for a performance problem depends on the cause of the bottleneck. Software Concepts International has successfully identified and implemented effective performance solutions at many Oracle Rdb and Oracle CODASYL DBMS sites.

 

Common Performance Problems

While performance problems have many different causes, Software Concepts International has observed the following to be common bottlenecks at client sites:

 

  • Excessive lock conflicts

  • Excessive storage area utilization

  • Poor database or application design

  • Ineffective use of available hardware

  • Inadequate hardware

  • Unrealistic expectations

 

Acceptable or optimal database performance does not happen by accident (or default.) Knowledge of the application, sound principals of database design and an understanding of the tools available for monitoring and using database resources are essential for achieving and maintaining optimal database performance.

 

Top


If you have a technical question about this article or about any other 
CODASYL DBMS or Rdb topic, then ask our experts.

How would you rate this article?

5 (Highest)
4
3
2

0 (Did not apply)
Comments about this article or topic suggestions for new articles

Please type the word above:

Copyright © 2016 Software Concepts International
All Rights Reserved