2 - Tuning the guts

Abstract

This chapter discusses the tuning considerations having to do with the underlying components common to most database management systems. Each component carries its own tuning considerations. Some of the components are (1) concurrency control, (2) recovery and logging, (3) operating system, and (4) hardware. Database applications divide their work into transactions. When a transaction executes, it accesses the database and performs some local computation. The strongest assumption that an application programmer can make is that each transaction will appear to execute in isolation—without concurrent activity. Lock tuning should proceed along several fronts: (1) use special system facilities for long reads, (2) eliminate locking when it is unnecessary, (3) take advantage of transactional context to chop transactions into small pieces, and (4) select the appropriate granularity of locking. Locking is unnecessary in two cases: (1) when only one transaction runs at a time, for example, when loading the database, and (2) when all transactions are read-only, for example, when doing decision support queries on archival databases. In such cases, users should take advantage of options to reduce overhead by suppressing the acquisition of locks.