Database Reference
In-Depth Information
Disk Server
CPU
64 GB
6 TB
32 GB
Read cache
Memory
Tables 500 GB
Indexes 500 GB
Nonleaf 10 GB
6 GB
1 GB
Table
pool
Figure 15.2 How often
must a nonleaf page be read
from a disk drive?
Index
pool
terabyte, retrieving an index row will often take two random disk reads, a level 2
nonleaf page and a leaf page; 20 ms. This may double the elapsed time of some
update transactions that have to maintain many indexes.
Many operational databases are much smaller than those for this insurance
company. If the whole database, tables and indexes, takes only, say, 32 GB, it
may remain resident in the 64-GB read cache of a disk server—or at least the
hit ratio may be close to 100%. In that case, the elapsed time for a random touch
is about 1 ms instead of 10 ms. Unfortunately, though, this can be realized only
if the other files sharing the 64-GB read cache are friendly neighbors.
A file is a friendly neighbor if it never occupies a large part of the read cache.
If a file is always accessed sequentially, its pages do not stay long in the read
cache because disk servers try to give preferential treatment to randomly read
pages. If the accesses to a large file are totally random and the access frequency
is not very high, each page that comes into the read cache stays there until it
becomes the least recently used randomly read page. Such pages may occupy
the read cache for 10 min, for instance. If a page is accessed randomly and
frequently—more than once every 10 min, say—it stays in the read cache for a
long time.
A disk server with a 64-GB read cache is likely to have 64 or 128 disk
drives sharing the cache: several terabytes. The 32-GB operational database in
our example is likely to have so many bad neighbors that a significant portion of
its pages are at times overlaid by the workload sharing the read cache. Assuming a
value of 1 ms for TR may be very optimistic unless the whole server is dedicated
to the small database (unlikely because of cost reasons).
Buffer Subpools
Many installations divide the database pool into five subpools:
1. Application indexes
Search WWH ::




Custom Search