12.1. The Database Is SlowLet'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:
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.
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. |