DB2 Version 9.7 for Linux, UNIX, and Windows

Diagnosing an over-allocation of memory problem

An over-allocation of memory occurs by mistakenly configuring software to use more memory than is physically available. Over-allocation can result in the lack of free memory, and a significant increase in hard disk paging and system CPU time consumption. A slow down in SQL query performance can be the result.

About this task

Diagnosis

In a DB2® system, strive to use system memory by leaving as little of it unused as possible. Unfortunately, if you over-allocate memory, mistakenly configure the DB2 software or other software to use more than the amount of physical memory on the system, the result is system processor overhead (and possibly disk overhead) caused by paging.

Servers with large amounts of physical memory-100 or more GB-can be subject to extra processor overhead if the system is not configured to use large memory pages. The OS manages memory at a page-level granularity. OS memory pages are different from DB2 pages. A typical page size is 4 KB, meaning that the OS must look after 250 million page table entries in a machine with 100 GB of RAM. Most operating systems support larger page sizes, which helps to reduce the overhead of virtual memory management. The AIX® operating system, for example, supports large pages up to 16 GB in size, although it would be rarely used in practice.

Indicative signs

This situation is identified on UNIX systems by low free memory and high page-in or page-out activity reported in vmstat (the free, pi, and po columns, respectively).
On AIX systems, vmstat -P ALL shows what page sizes are available and in use on the system. If 64 KB pages are enabled on the system and the DB2 database is running, you might see large allocations of 64 KB pages in vmstat -P ALL. It is where the DB2 database manager allocates memory. If not, and the system has a large amount of RAM, it might be the cause of higher-than-normal system processor consumption. You can also view the allocation of memory in the paging space by issuing the svmon -G and svmon -U <instance user id> command on AIX. On Linux, view the /proc/meminfo file to examine the memory usage from the operating system perspective. Examine the DB2 system memory usage by viewing the output from the db2pd -dbptnmem command and the db2pd -inst -mempools -memsets -alldbs command.

What to monitor

Use the operating system tools to check for memory allocation information and the page size used.

Check these monitoring elements through an event monitor or by way of a table function:
- total_cpu_time
- cputime_threshold_violated

After you observe one or more of the indicative signs that are listed here, then you are likely experiencing a problem with memory over-allocation. Follow the link in the "What to do next" section to resolve this issue.

Before you begin

To be able to objectively assess that your system is demonstrating abnormal behavior, you must have information that describes the typical behavior (baseline) of your system. A comparison can then be made between your observations of suspected abnormal behavior and the baseline. Collecting baseline data, by scheduling periodic operational monitoring tasks, is a key component of the troubleshooting process. For more detailed information about establishing the baseline operation of your system, see: "Operational monitoring of system performance".

What to do next

After you diagnose that an over-allocation of memory is likely causing the problem that you are experiencing, follow the link to obtain information about the steps that can be taken to resolve the issue: Resolving over-allocation of memory problems