Table and Index Organization - Relational Database Index Design and the Optimizers - page 20

Synchronous read
Random read
Synchronous write
Log write

Sync I/O C

C

Asynchronous read
Sequential prefetch
Assisted random read

C C C C C

Sync I/O

I/O I/O I/O

Asynchronous write
Database writes

I/O

Figure 2.9 Synchronous and asynchronous I/O.

before the pages are actually required for processing. Each group of pages being
prefetched and processed in this way is shown in Figure 2.9; note that a syn-
chronous read kick-starts the whole prefetch activity before the first group of
pages is prefetched to minimize the first wait.
When the DBMS requests a page, the disk system may read the next few
pages as well into a disk cache (anticipating that these may soon be requested);
this could be the rest of the stripe, the rest of the track, or even several stripes
(striping is described shortly). We call this Disk Read Ahead.
Most database writes are performed asynchronously such that they should
have little effect on performance. The main impact they do have is to increase
the load on the disk environment, which in turn may affect the performance of
the read I/Os.

HARDWARE SPECIFICS

At the time of writing, the disk drives used in database servers do not vary much
with regard to their performance characteristics. They run at 10,000 or 15,000
rotations per minute and the average seek time is 3 or 4 ms. Our suggested
estimate for an average random read from a disk drive (10 ms)—including drive
queuing and the transfer time from the server cache to the pool—is applicable
for all current disk systems.
The time for a sequential read, on the other hand, varies according to the con-
figuration. It depends not only on the bandwidth of the connection (and eventual
contention), but also on the degree of parallelism that takes place. RAID striping
provides potential for parallel read ahead for a single thread. It is strongly rec-
ommended that the sequential read speed in an environment is measured before
using our suggested figure of 0.1 ms per 4K page (refer to Chapter 6).