Return to Home Page

Dissecting Database Performance

Assuring optimal performance is one of the biggest problems faced by DBAs on an ongoing basis. The loudest complaints emanate from those users who have to wait longer than they are used to waiting for their applications to respond. This is to be expected because no one likes to wait. Especially when they never had to wait in the past.

But what causes those formerly fast applications to stall and deliver sub-par performance? If there were an easy answer to that question many DBAs would be out of work. Instead of attempting to answer that question in-depth, let’s examine the basics of performance management and optimization that apply to all database applications.

Every database application, at its core, requires three components in order to operate: the system, the database, and the application. To deliver performance, the DBA must be able to monitor and tune each of these components. This is easier said than done.

The system consists of the system software and hardware required for the application to provide service. This includes the computer itself, its disk subsystems, network connections, and all peripherals. From a software perspective the system includes the operating system, the file system, the DBMS itself, networking protocols, and any related middleware such as transaction processors or message queues.

To deliver system performance the DBA must have the resources to monitor, manage and optimize the performance of these disparate pieces of hardware and software. Some of the tasks required for system tuning include the proper allocation and management of memory structures (e.g. buffer pools, program cache area, etc.), storage management, integration of the DBMS with other system software, proper usage of database logs, and coordination of the operating system resources used by the DBMS. Additionally, the DBA must control the installation, configuration, and migration of the DBMS software. If the system is not performing properly, everything that uses the system will perform poorly. In other words, a poorly performing system impacts every database application.

The second component is the database. The database stores the data that is used by the application. When the application needs to access data, it does so through the DBMS to the database of choice. If the database is not optimally organized or stored, the data it contains will be difficult or slow to access. The performance of every application that requires this data will be negatively impacted.

Over time, as data is modified and updated, the DBMS may have to move the data around within the database. Such activity causes the data to become fragmented and inefficiently ordered. The longer the database remains online and the more changes made to the data, the more inefficient database access can become. To overcome disorganized and fragmented databases the DBA can run a reorganization utility to refresh the data and make the database efficient once again. But the key to successful reorganization is to reorganize only when the database requires it; instead, some companies over-reorganize by scheduling regular database reorganization jobs to be run whether the database is fragmented, or not. This wastes valuable CPU cycles.

But reorganization is only one of many database performance tasks performed by the DBA. Others include data set placement, partitioning for parallel access, managing free space, and assuring optimal compression.

The third, and final, component of database performance is the application itself. Indeed, as much as 80% of all database performance problems are caused by inefficient application code. The application code consists of two parts: the SQL code and the host language code in which the SQL is embedded.

SQL is simple to learn and easy to start using. But SQL tuning and optimization is an art that takes years to master. Every DBMS provides a method of inspecting the actual access paths that will be used to satisfy SQL requests. The DBA must be an expert at understanding the different types of access paths, as well as which ones are best in which situation. Furthermore, the DBA must be able to interpret the output of the access path explanation produced by the DBMS, since it is often encoded and cryptic.

Host language code refers to the application programs written in C, COBOL, Java, Visual Basic or the programming language du jour. It is quite possible to have finely tuned SQL embedded inside of inefficient host language code. And, of course, that would cause a performance problem.

The Bottom Line

DBAs must understand all three aspects of database performance management. Furthermore, the DBA must be able to identify problems when they occur. Once identified, the problem must be analyzed to determine its cause. And only then can a proper tuning strategy be deployed to rectify the problem.

From Database Trends and Applications, October 2001.
 

© 2001 Craig S. Mullins,  All rights reserved.
Home.