DB2 provides various memory configuration parameters with varying granularity in order to achieve the most efficient use of available memory. Such detailed configuration options requires that DBAs understand each component of DB2 memory.

To overcome the complexity involved in tuning DB2 memory configuration, IBM introduced the self tuning memory manager (STMM) in DB2 9. STMM saves DBAs considerable time by tuning DB2 memory dynamically depending on the workload. This kind of workload-specific tuning wasn't previously feasible.

Understanding DB2 memory usage is even more critical in a 32-bit environment, in which processes are restricted to 4GB of virtual address space (VAS). To encourage the use of 64-bit systems, which are growing increasingly popular, DB2 9 for Unix (on AIX, SUNOS, and HP-UX) is supported on 64-bit only. I'll explain how STMM works and how to monitor DB2 memory usage. Using a drill-down approach to monitor DB2 memory usage facilitates in troubleshooting memory-related issues.

DB2 Problem Diagnostic Tool

DB2 provides several ways to monitor its memory usage; I prefer to use the DB2 Problem Diagnostic tool (db2pd). This powerful tool for monitoring and troubleshooting was introduced in version 8.2 and has been enhanced in subsequent releases. The db2pd tool runs quickly (it doesn't acquire locks or latches) outside the DB2 engine. Despite its usefulness, the tool remains unexplored by many DBAs.

Although db2pd includes many options to allow monitoring and troubleshooting of different aspects of DB2, I will discuss only those options available for memory usage monitoring and tuning. Most of the examples I'll use are from DB2 9 on Linux; however, db2pd has similar command options on Unix and Windows platforms.

Before digging into DB2 memory monitoring, let's go over some concepts important to understanding memory usage.

Virtual Address Space

Most of us are aware of virtual memory management, but many still overlook it when analyzing memory. The main memory of a computer system is organized as an array of contiguous byte-sized cells. Each byte has a unique physical address (PA). The first byte has an address of 0, the next byte an address of 1, the next an address of 2, and so on. Given this simple organization, the most natural way for a CPU to access memory would be to use physical addresses. However, there are several disadvantages or limitations with physical addressing mechanisms.

In physical addressing, the program behaves differently (as different addresses are used) every time the program is executed on a machine with a different memory organization. Also, it's likely to behave differently every time it's executed on the same machine with the same memory organization, because the program is likely to be loaded at a different location every time. In addition, the physical addressing mechanism isn't very flexible. The program can't be fragmented or partially loaded. The entire program file must be read into main memory to execute. This requirement can create problems for systems with too little memory to hold all the running programs. Moreover, the user process can modify even the read-only portion of the memory (for example, kernel code and data structure, private memory of another process, and so on.)

To overcome the problems of physical addressing, the responsibility of managing the physical memory migrated from individual applications to the operating system itself. The individual applications have a virtual view of computer system memory, and the operating system translates these virtual addresses to physical addresses. Virtual addressing also introduced the concept of "swapping,” in which programs and data not currently required can be moved to disk (swap space) and brought back to memory when required again. The virtual addressing mechanism in simplified form is depicted in Figure 1.

Figure 1. Virtual address space and page table.
Click image thumbnail for larger image.

In a system with virtual memory, every process has a virtual address space. In a 32-bit system, the virtual address space size is 2³²(4GB); in a 64-bit system, the virtual address space size is 2 ⁶⁴ bytes (assuming a byte- addressable system). The 4GB of virtual address space in 32-bit environment was more than enough a couple decades ago. That’s not the case anymore. More and more 64-bit systems are being used for that reason. Note that the virtual address space is available to each process and has nothing to do with total physical memory available. Processes can use this address space to refer to the physical memory, swap space on disk, or memory mapped files to improve the I/O performance. Data and code are moved between physical memory and swap space in a unit of memory called a page. The page size is normally 4KB, but it can be a multiple of 4KB on some systems. Processes keep track of their virtual address space with the help of a page table.

In the example in Figure 1, the page table consists of eight entries. I'm using eight entries to simplify the discussion; otherwise, in a 32-bit system with page size 4KB (2 12), the page table consists of 1024x1024 (2 20) entries, and in a 64-bit system with page size 4KB, the page table consists of 2 52 entries. Out of eight entries, PTE (Page Table Entry) 0 and 4 are blank. That means the process hasn't exhausted its virtual address space and it can address more virtual memory (either physical memory or disk). Four entries (PTE 1, 2, 5, and 7) are pointing to physical memory. That means these four pages can be accessed by the process without swap-in. The other two pages (PTE 3 and 6) are swapped out to disk. If the process needs these two pages, the virtual memory manager will have to swap them in from disk to physical memory. Before swapping in these pages, it will have to make room for the new pages in physical memory either by finding empty pages or overwriting or swapping the victim pages to disk. The process of swap-in and swap-out is also called page-in and page-out, because the unit of memory used in this process is page.

Where a page will go when it's “swapped-out” depends on how it's being used.

OS memory uses include:

• Kernel , the operating system’s own (semi-) private memory space, which is always in main memory (RAM)
• Cache, the main memory that holds elements of the file system and other I/O operations (not to be confused with the CPU cache or disk drive cache, which aren't part of main memory).

Process memory uses include:

• Data , memory allocated and used by the program (usually via malloc, new, or similar runtime calls)
• Stack , the program’s execution stack (managed by the OS)
• Mapped , file contents addressable within the process memory space (primarily used for loading program executable code and shared libraries).

In general, pages are swapped out as follows:

• Kernel : Never swapped out
• Cache : Page discarded.
• Data: Moved to swap space
• Stack: Moved to swap space
• Mapped : Moved to originating file if changed and shared, or moved to swap space if changed and private.

If more than one process is sharing a piece of memory, this shared memory might have different virtual addresses in the virtual address space of each process. Figure 2 shows this phenomenon.

Figure 2. Virtual address space and shared memory.
Click image thumbnail for larger image.

A few important points regarding virtual address space:

1. In a 64-bit system, a process can address 16 x 1024 x 1024 TB of memory, which is so huge that the memory addressing issue is completely out-of-context in 64-bit environment.
2. As I mentioned, every process has its own virtual address space. Windows OS, in general, provides VirtualAlloc/VirtualFree APIs to allocate or free VAS regions. Any new allocation using VirtualAlloc will always be rounded up to 64 KB. That means the minimum size of the VAS region is 64 KB, and the VAS region is always a multiple of 64 KB on Windows. So, if you allocate a new VAS region bound to physical memory, the OS will consume physical memory rounded up to page size and will consume VAS of the process rounded up to 64 KB. For example if you call VirtualAlloc for 1024 bytes with COMMIT flag, the OS will allocate 64 KB VAS region in the specified process and will allocate a page (4KBor 8KB depending on page size) of physical memory. You can read more about this on Slava Oks’s Weblog at http://blogs.msdn.com/slavao/archive/2005/01/27/361678.aspx.
3. On a Unix based system, the OS will use any available RAM for disk caching. Since cache memory is a low priority and can be freed up at any time, it doesn't impede process performance, but is usually counted as “used.” As a result, you would mostly see less or no free memory on such systems; but that doesn't indicate that the system is running out of memory. A better indication of a memory problem is heavy paging activity, which shows that the system is compensating for not having adequate main memory by swapping pages in and out from swap space (and slowing down system performance).
4. As mentioned earlier, a process's VAS can access up to 4GB of memory on 32-bit system. However the process needs to use this 4GB of address space to access even the kernel memory, mapped files for shared libraries, stacks, and so on. As a result, a process on a 32-bit system usually has much less than 4GB of addressable memory for data (the actual size is different on each operating system). For a detailed understanding of DB2 process virtual address space on each individual OS (AIX, SunOS, HP-UX, Linux, and Windows), read “ The DB2 UDB Memory Model,” by Sylvia Qi and Michael Dang.

DB2 Memory Model

Here's the DB2 Memory model in a nutshell.

DB2 breaks and manages memory in four types of memory sets:

• Instance shared memory
• Database shared memory
• Application group shared memory
• Agent private memory.