Additional Estimation Considerations - Relational Database Index Design and the Optimizers - page 270

The read cache of a disk server behaves much like memory without pools.
The disk server decides which page is overlaid by a new page. The main algorithm
used, unsurprisingly, is also the LRU, the main exception being a shorter cache
holding time for sequentially read pages. Typically, the algorithms cannot be
influenced by a systems administrator, but it may be possible to force a short
cache hold time for the pages of selected objects.
This equal treatment in the use of the read cache may be an issue when the
disk server contains pages from many systems. The number of 4K pages from an
operational database may vary between 1 and 10 million, depending on the other
load. A fairly large database buffer pool as in Figure 15.2 reduces the variation
in the response times of the operational transactions.

Long Rows

For sequential read, the I/O time per row depends on the length of the row. If
sequential read time is 40 MB/s, the I/O time per 4K page is 100 µ s. Therefore,
if there are 10 rows per page, the I/O time per row will be 10 s. This is the

µ

default value we have used for the QUBE.
With rows longer than 400 bytes, the actual I/O time

I/O time per page / number of rows per page

can be significantly more than 10 µ s, especially if there is a lot of distributed
free space.

Slow Sequential Read

The coefficient for TS should be calibrated according to the measured peak hour
read speed. If the measured speed is only 10 MB/s (a value quite possible with
older disk drives) instead of the assumed 40 MB/s for the QUBE, the coefficient
will be 0.4 ms instead of 0.1 ms.

WHEN THE ACTUAL RESPONSE TIME CAN BE MUCH
SHORTER THAN THE QUBE

The purpose of the QUBE is to find potentially slow access paths with minimal
effort . Because of the simplicity of the formula (only two essential variables are
used—the number of random touches and the number of sequential touches), it
is feasible to check every SQL call at an early stage.
When designing indexes using the QUBE, we need to keep a sense of pro-
portion with regard to the changes being suggested for the indexes; for example,
when choosing between a semifat and a fat index (perhaps because the additional
disk space would be more than 10 GB) or between a fat index and an ideal index
(perhaps because more than 10,000 rows are added to the table during peak