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

Queuing (Q) 3 ms
Seek 4 ms
Half a rotation 2 ms
Transfer 1 ms
Total I/O time 10 ms

Dep end s on drive busy

S = Service time

One random read keeps a drive busy for 6 ms

Q = (u / (1-u)) S

×

50 random reads a second

×

u = 50 read/s 0.006 s/read

Q = Average queuing time
u = Average drive busy
S = Average service time

= 0.3
Q = (0.3 /(1- 0.3))

×

6 ms

= 3 ms

Figure 2.3 Random I/O from disk drive—2.

Database
buffer pool

Move one page
(4K or 8K)

Read cache

Estimate: 1 ms

Figure 2.4 Read from disk
server cache.

Depends on many factors

Reads from the Disk Server Cache

Fortunately, disk servers in use today provide their own memory (or cache) in
order to reduce this huge cost in terms of elapsed time. Figure 2.4 shows the
read of a single table or index page (again equivalent to reading a number of
table or index rows) from the cache of the disk server. Just as with the buffer
pools, the disk server is trying to hold frequently used data in memory (cache)
rather than incurring the heavy disk read cost. If the page required by the DBMS
is not in the buffer pool, a read is issued to the disk server who will check to
see if it is in the server cache and only perform a read from a disk drive if it is
not found there. The figure of 10 ms may be considerably reduced to a figure as
low as 1 ms if the page is found in the disk server read cache.
In summary then, the ideal place for an index or table page to be when it
is requested is in the database buffer pool. If it is not there, the next best place
for it to be is in the disk server read cache. If it is in neither of these, a slow
read from disk will be necessary, perhaps involving a long wait for the device
to become available.