Database Performance Monitoring - Risks, Controls And Best Practices
Relational databases such as Oracle and IBM's DB2 are generally installed behind the scenes supporting key functions of data storage and retrieval
. The performance of these relational databases should be considered as a potential area of high risk and a key area to implement robust controls.
Specific risks related to database performance revolve around availability and business continuity. Poor database performance can affect an organization's reputation, delivery of services and compliance with service level agreements.
As IT auditors who have reviewed mission critical databases in some of the world's largest processing environments, we have seen a full range of database environments in terms of management and control.
It is preferable to use preventive controls over detective or corrective controls. The objective is to prevent problems before they materialize into adverse conditions.
One area of preventive controls is performance monitoring which enables database administrators (DBAs) to proactively take action to ensure acceptable performance before issues materialize.
The degradation of database performance usually occurs gradually and can be identified through proper monitoring and reporting. Corrective measures may include the increased use of indexes, the redesign or re-normalization of data, the partitioning of tables, the expansion of buffer pools, increased CPU processing power or changes to system configuration. Another corrective measure might be to collect different or more accurate statistics.
IBM's approach to database performance monitoring is very methodical and is considered 'best practice.' First, objectives for database performance are established. Next, there is planning and implementation of specific monitoring. Performance reports must be analyzed on a regular schedule. Where performance is found to be unsatisfactory, database administrators must identify the constraints and tune the systems to balance resources.
There is a similar approach found in the COBIT framework with an IT 'process' to 'monitor and evaluate IT performance.' The specific control objective in the COBIT framework referred to as 'performance assessment' is to periodically review performance against targets, analyze the cause of any deviations and initiate remedial action to address the underlying causes.'
The setting of performance objectives involves defining what good performance is. The objectives should be realistic, understandable and measurable. The objectives for database performance generally consist of values for:
*Response time: acceptable response time for end users, accounting elapsed time (first SQL statement to thread termination), total transit time
*Throughput which can be measured by average throughput (the total number of transactions or queries that complete within a given time)
*System availability: mean time to failure and the durations of down times.
These kinds of objectives are used to define requirements for resources such as processor speed, I/O and disk speed and capacity, amount of storage and additional hardware and software.
These objectives can be achieved through actual performance monitoring and analysis at four levels as follows:
*Continuous performance monitoring of statistics and deviations from prior experience e.g. peak periods.
*Periodic performance monitoring of 'snapshots' at peak loads and under normal conditions. Trends can be identified.
*Detailed performance monitoring to research specific issues that were previously identified.
*Exception performance monitoring to identify exceptional values or events e.g. high response times or deadlocks.
To sum up, database performance monitoring is very important in managing risks associated with performance degradation. IBM's framework can be used to develop a performance monitoring program. Performance objectives should include database response time, throughput and system availability. Degraded performance that has been identified can be corrected through targeted changes to database objects, system hardware and software configurations.
References:
IBM DB2 Universal Database for z/OS Administration Guide. www.ibm.com
ISACA. Control Objectives for IT (COBIT) Framework 4.1. www.isaca.org
by: Sarah Abelow
What To Consider While Comparing Nursing Programs Alesis M1 Active 520 Usb Studio Monitors Review Ways To Increase The Speed Of Your Computer Monitor Cable Hooking Up Your Monitor Cable Monitor Cable Types Of Cables Paul Mitchell The School Programs Watch Unlimited TV on Computer Lightweight Rolling Laptop Bags A Computer Printer Is An Important Purchase Geology Programs In Maine For Pre-college Students Folder Printing Products for Sales Improvement 1tb Hard Drive: The 3 Best Portable Harddisks For 2010 Watch TV on Laptop - Save Money & Avoid Crowds