Deriving the Ideal Index for a SELECT - Relational Database Index Design and the Optimizers - page 59

estimate assumes that the new index rows go to the same leaf page in one
index (an ever-increasing key) and to 20 different leaf pages in the 9 other
indexes. From the response time point of view, a large table with many inserts
(or deletes) per transaction may not tolerate 10 indexes. Furthermore, from the
disk drive load point of view, a large table with more than 10 inserted rows
per second may not tolerate 10 indexes. Let us take a closer look at the sec-
ond issue.

Drive Load

Sooner or later, the modified leaf pages must be written to disk drives. Because
database writes are asynchronous, they do not affect the response time of the
transaction. However, they do increase the drive load. RAID 5 increases the
impact as each random update of a page results in access to two drives. Each
access may take 12 ms because a whole track must be read and written back: one
seek (4 ms) and two rotations (2 4 ms). Thus, the total increase in drive busy

caused by writing a modified page to the disk drives is 24 ms. The corresponding
value for RAID 10 (striping and mirroring) is 2

×

12 ms.
If the INSERT rate to a table is high, drive load may be the primary problem,
which sets a limit to the number of indexes on a table. DELETEs cause the
same disk load as INSERTs, so massive delete jobs are another serious concern.
UPDATEs affect only the indexes whose columns have been changed.
Let us assume a RAID 5 disk server has 128 drives: 112 active, 16 spares.
The database (tables and indexes or their partitions) is striped over the active
drives. Read cache is 64 GB, write cache 2 GB.
In table TRANS, shown in Figure 4.5, new rows go the end of the table and
its clustering index; they do not cause a large number of disk reads and writes
because many rows are inserted to a page before the page is written to disk. The

× 6ms =

C

0.3 GB 0.3 GB 0.3 GB 0.3 GB

0.3 GB

TRANS

20 rows inserted
per second

5,000,000 rows

112

×

36GB

Read 80 leaf pages per second from disk
Write 80 leaf pages per second to disk

Figure 4.5 Stressful for the disk server.