Section 12.1. The Database Is Slow

12.1. The Database Is Slow

Let's first try to define the major categories of performance issues that we are likely to encounter in productionsince our goal, as developers, is to anticipate and, if possible, avoid these situations. The very first manifestation of a performance issue on a production database is often a call to the database administrators' desk to say that "the database is slow" (a useful piece of information for database administrators who may have hundreds of database servers in their care...). In a well-organized shop, the DBA will be able to check whether a monitoring tool does indeed report something unusual, and if that is the case, will be able to answer confidently "I know. We are working on the case." In a poorly organized shop, the DBA may well give the same answer, lying diplomatically.

In all cases, the end of the call will mean the beginning of a frantic scramble for clues.

Such communications stating that "the database is slow" will usually have been motivated by one of the five following reasons:

It's not the database

The network is stuttering or the host is totally overloaded by something else.

Thanks for calling.

Sudden global sluggishness

All tasks slow down, suddenly, for all users. There are two cases to consider here:

• Either the performance degradation is really sudden, in which case it can often be traced to some system or DBMS change (software upgrade, parameter adjustment, or hardware configuration modification).

• Or it results from a sudden inflow of queries.

The first case is not a development issue, just one of those hazards that make the life of a systems engineer or DBA so exciting. The second case is a development or specifications issue. Remember the post office of Chapter 9: when customers arrive faster than they can be serviced, queues lengthen and performance tumbles down all of a sudden. Either the original specifications were tailored too tightly and the system is facing a load it wasn't designed for, or the application has been insufficiently stress-tested. In many cases, improving some key queries will massively decrease the average service time and may improve the situation for a negligible fraction of the cost of a hardware upgrade. Sudden global sluggishness is usually characterized by the first phone call being followed by many others.

Sudden localized slowness

If one particular task slows down all of a sudden, locking issues should be considered. Database administrators can monitor locks and confirm that several tasks are competing for the same resources. This situation is a development and task-scheduling issue that can be improved by trying to release locks faster.

A slow degradation of performance reaching a threshold

The threshold may first be felt by one hypersensitive user. If the load has been steadily increasing over time, the crossing of the threshold may be a warning sign of an impending catastrophe and may relate to the lengthening service queues of a sudden global sluggishness. The crossing of a threshold may also be linked to the size increase of badly indexed tables or to a degradation of physical storage after heavy delete/update operations (hanging high-water mark of a table that has inflated then deflated, a Swiss cheese-like effect resulting in much too many pages or blocks to store the data, or chaining to overflow areas). If the problem is with indexes or physical storage (or outdated statistics taking the optimizer down a wrong path), a DBA may be able to help, but the necessity for a rescue operation on a regular basis is usually the sign of poorly designed processes.

One particularly slow query

If the application was properly tested, then the case to watch for is a dynamically built query provided with a highly unusual set of criteria. This is most likely to be a pure development issue.

Many of these events can be foreseen and prevented. If you are able to identify what loads your server, and if you are able to relate database activity to business activity, you have all the required elements to identify the weakest spots in an application. You can then focus on those weak spots during performance testing and improve them.

To anticipate live application performance, you must monitor activity very closely during stress tests and user acceptance trials.