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

2. Application tables
3. Work files for sorting and temporary tables
4. System tables and their indexes
5. Dedicated pool for in-memory tables and indexes
There are two reasons for using subpools: (1) monitoring and (2) prioritization.
Monitoring
If one transaction causes 500 random disk reads, it would be useful to know how
many of them apply to application tables and how many to application indexes.
The overhead of monitoring by subpool is much lower than the overhead of
monitoring by object.
Prioritization
It is now technically and economically feasible to hold selected tables and indexes
in memory with a dedicated database buffer subpool; each table or index page
is read into it only once from the disk server after the DBMS has been started.
A realistic order of magnitude for such a subpool is a few gigabytes. In the near
future, a third dimension may be required regarding billing for outsourced hard-
ware, in addition to CPU time and allocated disk space— dataheldinmemory.
Currently, the cost might be up to U.S.$1000 per month per gigabyte, but this
figure is still decreasing rapidly as time goes by.
Current memory (16 to 64 GB) is largely occupied by system and application
programs. What is left for the operational database buffer pools is often only a
few gigabytes. As time passes, with 64-bit addressing and even cheaper memory,
database buffer pools of a 100 GB will become feasible.
When the DBMS is started, the subpools are empty. It may take half an
hour before the largest subpool is filled with pages read from the disk server.
From then on, new pages from disk must overlay older pages that, if updated, will
have already been sent to the disk server by the DBMS. The main algorithm used
for choosing which pages are to be overlaid is the least recently used algorithm
(LRU) . Sequentially read pages stay in the pool for a shorter period of time than
randomly read pages. Many DBMSs allow a systems administrator to influence
the overlay algorithms at a subpool level.
Determining the size of the database buffer pools is one of the key tuning
activities at the DBMS level. The basic objective is to increase the size of the
pools until paging becomes imminent, when the unused memory is within a few
percent of the total memory available. Allocating pool space to different instances
of the DBMS, for example, operational, data warehouse, application test, system
test purposes, is a complex trade-off. Pool size is a powerful priority mechanism.
Some platforms, for instance, IBM iSeries, previously AS/400, do not support
pools; all pages are considered equal by the operating system. The main algorithm
is the LRU. This eliminates a system tuning task, but also a means to set priorities.