Databases Reference
In-Depth Information
The first problem with many of these papers is that their approaches are not practi-
cal enough to be implemented in industrial database products. For example, in the
research focused on buffer pool tuning, many of the approaches require the user to set
response-time goals on sets of queries. While this is reasonable in theory, in practice, the
task of setting response-time goals may be just as difficult as manually tuning the data-
base's memory.
The previous buffer pool research is also problematic because it relies on heuristic
hit rate estimation. In cases where the hit rate estimation is incorrect, suboptimal tuning
will occur. Compounding the problem is the fact that even if hit rate estimation is accu-
rate, hit rates alone fail to account for the potentially uneven cost of page misses.
Depending on the disk from which the page must be read, certain page reads may be
dramatically more expensive than others since page reads from hotly contested disks will
take longer than page reads from idle disks.
The second problem with the academic approaches is that, to our knowledge, they
all deal with only one aspect of the memory tuning problem. For instance, a great deal
of work has been done on approaches to buffer pool tuning. Similarly, there is a consid-
erable amount of research into optimizing the sort and hash join memory usage of a
database system. The trouble with these approaches is that there is no clear method of
integrating the separate components into a comprehensive database memory tuning sys-
tem that can optimize all (or even most) of the database's memory.
Oracle, SQL Server, and DB2 all have introduced self-tuning memory manage-
ment in recent releases to help address these issues. Tuning of database memory can be
divided into two design problems:
•
How much memory should be allocated to the database.
•
How should this memory be divided among the various needs of the database
for caching, sorting, locking, etc.
It's very important to bear in mind that the tuning of database memory is a zero-sum
game:
6
there are many uses for the memory, but the total resource is finite, and usually
not sufficient to optimize the performance requirements of all the memory usages.
SQL Server and DB2 both tune total database memory by exploring the availability
of memory on the system. Special allowances are made for the file systems file cache so
6
Zero-sum games involve situations where one player's gains are the result of another player's
losses. Benefit is neither created nor destroyed. Memory tuning can be viewed as a zero-sum game
if you consider memory allocation to each heap as “gains.” Total cumulative memory is fixed, or
“zero sum.” John von Neumann and Oskar Morgenstern showed in 1944 that a zero-sum game
involving multiple players is actually a generalized form of a zero-sum game for two players.
Interestingly, they also showed that a non-zero-sum game for
n
players is reducible to a zero-sum
game for
n
+ 1 players where the
n
+1th player represents the overall non-zero gain.
