TABLE OF CONTENTS There are several major memory uses within a relational database system. The challenge for the database designer is that these memory areas are often configurable (designable) but have very little to do with each other. Some are used to reduce system I/O, while others are used to reduce CPU consumption or improve network usage efficiency. This makes tuning these heaps very difficult. The relative need for memory by each of these areas is critical to understand because the database server will have a fixed amount of real memory (RAM) to be shared by all of them. The server may be used for multiple databases, and possibly other middleware and applications, depending on your environment. This makes it impossible to simply give each memory heap as much memory as it would need to optimize its own resource goals. As a result, memory tuning is really defined as an art of tradeoffs. Wise tradeoffs lead to the best performance; poor tradeoffs give memory where it is not especially needed and starve the high-pressure areas, resulting in poor performance. The performance impact of good versus poor memory design can lead to performance differences measuring an order of magnitude or more. First let s discuss what the major memory areas are in modern database systems, and then examine some of the manual and automated methods for their tuning.
The following are generic terms for the main memory areas used by most modern...
