If you are a DBA or a performance analyst, chances are that you deal with database performance management every day of the week. But have you ever stopped for a moment and tried to define what you mean when you say “database performance?” That is the idea behind today’s post.
Don’t we need a firm definition of database performance before we can attempt to improve efficiency? Think, for a moment, of database performance using the familiar concepts of supply and demand. Users demand information from the DBMS. The DBMS supplies information to those requesting it. The rate at which the DBMS supplies the demand for information can be loosely thought of as “database performance.”
But let’s dive a little deeper. There are five factors that influence database performance: workload, throughput, resources, optimization, and contention.
The workload that is requested of the DBMS defines the demand. It is a combination of online transactions, batch jobs, ad hoc queries, data warehousing analysis, utilities, and system commands directed through the DBMS at any given time. Workload can fluctuate drastically from day to day, hour to hour, minute to minute, and yes, even second to second. Sometimes workload can be predicted (such as heavy month-end processing of payroll, or very light access after 6:00 p.m., when most users have left for the day), but at other times it is unpredictable. The overall workload can have a major impact on database performance.
Throughput defines the overall capability of the computer to process data. It is a composite of I/O speed, CPU speed, parallel capabilities of the machine, and the efficiency of the operating system and system software. And don’t forget about those specialty processors (zIIPs and zAAPs) if you are a mainframe DBA.
The hardware and software tools at the disposal of the system are known as the resources of the system. Examples include memory (such as that allocated to bufferpools or address spaces), disk, cache controllers, and microcode.
The fourth defining element of database performance is optimization. All types of systems can be optimized, but relational database systems are unique in that query optimization is primarily accomplished internal to the DBMS. However, there are many other factors that need to be optimized (SQL formulation, database parameters, system parameters, etc.) to enable the relational optimizer to create the most efficient access paths. And there are optimization aspects that are outside the scope and control of the relational optimizer, too, such as efficient script coding, proper application design, and so on.
When the demand (workload) for a particular resource is high, contention can result. Contention is the condition in which two or more components of the workload are attempting to use a single resource in a conflicting way (for example, dual updates to the same piece of data). As contention increases, throughput decreases.
So putting this all together, database performance can be defined as the optimization of resource use to increase throughput and minimize contention, enabling the largest possible workload to be processed.
In addition, database applications regularly communicate with other system software, which must also be factored into performance planning. Many factors influence not only the performance of the DBMS and applications accessing its databases, but also the performance of the other system components (e.g. transactions processor, network software, application servers, etc.)