DB2 memory and file cache performance tuning on Linux
DB2 performance optimization on the Linux platform
Database systems running on the Linux platform have become a foundation for critical business applications. DB2 Universal Database (DB2 UDB) utilizes the Linux platform well by leveraging a designed feature set unique to this platform.
This article summarizes issues related to memory utilization and file caching on Linux. Learn about the advantages of specific DB2 features to best manage these resources from a DB2 database perspective.
Memory utilization on Linux
Let's start out by discussing a few terms and concepts that are important to understand when discussing memory utilization. First is the concept of a 32-bit address space, as opposed to a 64-bit address space. Every byte in memory has to have an "address" for a process to be able to locate it. The list of these addresses is known as the "address space." A single bit has two possible values and could refer to two possible addresses -- 2 bytes; 32 bits have 232 combinations and could address 4,294,967,296 bytes (4 GB), and 64 bits have 264 combinations and could address 16 exabytes.
Most modern non-64-bit systems have the ability to create an address space of 36 bits, yielding 64 GB of addressable memory. However, for details not available here, this is not available to every individual process but to the Linux OS, which creates a mapping of these addresses to addresses assigned to a process. The process addresses are still limited to 32 bits, yielding address spaces for individual processes in differing areas of memory. This allows systems with greater than 4 GB of memory to be utilized by many processes. However, if two or more processes want to communicate using shared memory, they need to have the same address space, thus limiting the addresses they can utilize. Systems utilizing 64 bits for the address space have virtually no limits.
The address space is not entirely devoted to a single process. Certain areas of the address space are limited to specific processes, such as the kernel. This yields approximately 2 GB of address space for DB2 and is the focus of this article.
Linux, under normal circumstances, uses a system file cache to buffer, read, and write requests from disk. Linux performs this task fairly aggressively, and you will quickly see a running system have very little unused/free memory. Memory allocated to the file cache can be reduced and given to requesting processes, reducing the amount of data being buffered; however, read and write requests still pass through the smaller file cache. The entire amount of memory allocated to all processes, kernel, and file cache is your total working set. If your entire working set exceeds the amount of physical RAM in the system, then Linux can use disk space as a virtual extension to RAM, known as the swap space. Naturally, reads and writes to this area are far slower than between areas of memory.
Memory utilization examples
Let's look at a couple of examples of memory utilization using the Linux
free command. The command displays the total amount of free, used physical, and swap memory in the system, as well as the buffers used by the kernel. We will be using the options
-m, which displays the values in megabytes, and
-o, which suppresses buffer information, allowing a more readable output. Figure 1 shows an example of the free command. The information displayed is as follows:
- a – total physical memory
- b – total used memory
- c – amount of free physical memory
- d – size of the file cache
- e – total size of the swap space
- f – swap space used
- g – swap space free
Figure 1. Free command example
I rebooted the system "wrightxe" shortly before taking the screen capture, yielding a system with a large percentage of the overall memory free (930 MB) and very little cached (28 MB). Figure 2 illustrates the aggressive nature of Linux file caching.
Figure 2. File cache example
I pumped the contents of a large file, DB2ESEV8.2.tar, to /dev/null (a virtual device that acts like a black hole, essentially discarding all writes). The size of the cache grew to 501 MB, roughly the size of the file I used in my test, 473 MB. In addition, used memory and free memory were modified by roughly the same amount.
When DB2 pulls data from disks, it places the data in a DB2 shared memory area. One purpose of the shared memory area is to act as a buffer, minimizing the necessity for further reads from disk. However, with Linux caching, you are essentially double buffering the data. One goal of DB2 memory tuning is to maximize the amount of memory allocated to DB2 buffers and minimize the amount cached by Linux, if in doing so increases memory available for DB2.
Figure 3 illustrates a system that is under-powered for the memory demands placed on it by running applications.
Figure 3. Under-powered system
The key is to look at the relationship of values
swap used. The primary thing to note is that the working set was large enough to require 138 MB of swap space. This is a significant amount when compared to total memory. If the use of swap space is common or represents a significant portion of memory, you have either over-allocated memory to DB2 or other processes, or your machine is under-powered for the workload you are attempting to run.
One tool to determine how much memory DB2 is using is
db2mtrk, the DB2 memory tracker command. While this command has several options, we will only be looking at the
-d option, which shows database level memory, illustrated in Figure 4.
Figure 4. DB2 memory tracker
The output shows the memory usage of the SAMPLE database. If I had other databases, they would have been displayed as well. The largest values are usually assigned to
bph, the DB2 bufferpools. The output shows values for five bufferpools. In reality, the first bph represents the IBMDEFAULTBP, the only user defined bufferpool on the system. The other four bufferpools are "hidden" bufferpools, one for each DB2 page size (4 K, 8 K, 16 K, and 32 K) to ensure DB2 can run if the allocation of the user bufferpool fails. In this example, 201.3 MB have been allocated to the IBMDEFAULTBP, an over-allocation for a system with only 357 MB of memory. The DB2 memory tracker can display information on memory used by the instance, as well as DB2 agents.
Configuring Linux for DB2 UDB
One common question I receive is, "What Linux kernel or system tweaks do I need to make DB2 run well on Linux?" When I respond that this is not necessary and the stock kernel for supported distributions is what should be used, readers are surprised. The truth is, DB2 makes some kernel memory adjustments when it is started. DB2 probes the system to determine the current configuration. The following is the output of the probe on my system as written to the diagnostic log file.
Listing 1. Probe output
System: Linux wrightxe 6 2 i686 CPU: total:1 online:1 Physical Memory(MB): total:1013 free:326 Virtual Memory(MB): total:3006 free:2319 Swap Memory(MB): total:1993 free:1993 Kernel Params: msgMaxMessageSize:8192 msgMsgMap:16384 msgMaxQueueIDs:16 msgNumberOfHeaders:16384 msgMaxQueueSize:16384 msgMaxSegmentSize:16 shmMax:33554432 shmMin:1 shmIDs: 4096 shmSegments:4096 semMap:32000 semIDs:128 semNum:32000 semUndo:32000 semNumPerID:250 semOps:32 semUndoSize:20 semMaxVal:32767 semAdjustOnExit:32767
In addition to information on the overall memory usage of the system, DB2 determines the values of several kernel parameters. DB2 then adjusts some of these parameters, also noted in the DB2 log.
Listing 2. Parameter adjustments
2005-07-20-09.41.35.723122-300 E5444G363 LEVEL: Warning PID : 9905 TID : 332645760 PROC : db2sysc INSTANCE: db2inst1 NODE : 000 FUNCTION: DB2 UDB, base sys utilities, DB2main, probe:9 MESSAGE : ADM0506I DB2 has automatically updated the "semmni" kernel parameter from "128" to the recommended value "1024". 2005-07-20-09.41.35.732485-300 E5808G362 LEVEL: Warning PID : 9905 TID : 332645760 PROC : db2sysc INSTANCE: db2inst1 NODE : 000 FUNCTION: DB2 UDB, base sys utilities, DB2main, probe:9 MESSAGE : ADM0506I DB2 has automatically updated the "msgmni" kernel parameter from "16" to the recommended value "1024". 2005-07-20-09.41.35.732688-300 E6171G373 LEVEL: Warning PID : 9905 TID : 332645760 PROC : db2sysc INSTANCE: db2inst1 NODE : 000 FUNCTION: DB2 UDB, base sys utilities, DB2main, probe:9 MESSAGE : ADM0506I DB2 has automatically updated the "shmmax" kernel parameter from "33554432" to the recommended value "268435456".
If you have already adjusted the parameters to suit your needs, perhaps for an application already running on the system, DB2 may not adjust these values. This will also be noted in the DB2 diagnostic log. Refer to the online DB2 UDB Information center section on modifying kernel parameters (Linux) for information on how to check these parameters and set them if you do not want DB2 to make these adjustments automatically. (See Related topics.)
DB2 automatically makes further system adjustments to maximize the use of the system memory. DB2 needs a contiguous address space for the shared memory area. The kernel, however, maps the attachment address for shared libraries to an address somewhere in the middle of the address space, limiting the contiguous area. DB2 will automatically move this map, allowing a larger contiguous area. This will allow an additional 500 MB of memory available for database shared memory, pushing total utilization slightly above 2 GB to about 2.2 GB. This is accomplished by modifying the mapped_base address, which specifies the attachment address of shared libraries. This will only affect the DB2 process address space and not the system as a whole. The following entry in the DB2 diagnostic log describes this event on my system:
Listing 3. Further system adjustments
2005-07-20-09.41.35.732853-300 E6545G433 LEVEL: Warning PID : 9905 TID : 332645760 PROC : db2sysc INSTANCE: db2inst1 NODE : 000 FUNCTION: DB2 UDB, base sys utilities, DB2main, probe:9 MESSAGE : ADM0506I DB2 has automatically updated the "mapped_base" kernel parameter from "0x40000000(hex) 1073741824(dec)" to the recommended value "0x10000000(hex) 268435456(dec)".
DB2 configuration tuning
The following DB2 features are all supported as of the latest release of DB2 V8.2 through fix pack 9 (FP9), DB2 V8.2.2. While many of these features have been implemented in earlier V8.1 releases, I will assume for discussion purposes that the system is at this release level of DB2.
DB2 Asynchronous I/O (AIO) for Linux is only partially related to memory utilization. AIO allows processes to perform other work while waiting for an I/O request to return. DB2 page cleaners can make use of AIO to more effectively use system resources. To use AIO, library libaio-0.3.96 or later must be installed and your kernel must support AIO. Kernels 2.6 and above support AIO; you will need to check with your distribution vendor for support if running a 2.4 kernel. DB2 page cleaners essentially have the responsibility of removing dirty or unneeded data from the bufferpools to disk. This ensures there is room in the bufferpool for new pages. To enable AIO on Linux, you must set the DB2 registry variable DB2LINUXAIO (using the command shown below) and restart DB2.
AIO is most effective in OLTP environments. If your system does not support AIO, then DB2 will not implement this feature. Refer to the proper section of the release notes for further details. (See Related topics.)
DB2 bufferpools and the Linux file cache perform largely the same function; that is, cache a copy of data read from disk. As noted earlier, the flow of reads under normal circumstances is to read into the file cache and then copy into the bufferpool. The result is that system cycles are used to make the copy, as well as maintain two areas of memory largely performing the same function. Prior to the Linux 2.6 kernel, file caching on a Linux system could go awry, using a huge number of cycles trying to manage the file cache.
Direct I/O solves this issue by directing reads to be made directly from disk into DB2 memory areas, bypassing the file cache entirely. This feature is available on a tablespace-by-tablespace basis, effectively allowing some data in DB2 to use file caching and other data to bypass it. The tablespace option
NO FILE SYSTEM CACHING can be specified during the creation of the tablespace or altered to include this option. The following example shows how to create or alter a tablespace to use this option.
Listing 4. Create or alter tablespace to use NO FILE SYSTEM CACHING option
CONNECT TO SAMPLE; CREATE REGULAR TABLESPACE TESTBSP MANAGED BY SYSTEM USING (‘/data/’) NO FILE SYSTEM CACHING; ALTER TABLESPACE TESTTBSP2 NO FILE SYSTEM CACHING;
This option is available for all DB2 tablespaces, including system and temporary tablespaces and database managed tablespaces. The only exception is Linux raw device tablespace containers, which inherently bypass the file cache. To set up raw devices on Linux refer to "Linux raw devices" in the DB2 Information Center. (See Related topics.) LOB and LONG VARCHAR data fields will not typically benefit from direct I/O and will continue to use the file cache. It is recommended for database managed tablespaces to split regular data from LOB data, placing LOB data in a large tablespace.
Read requests typically require "x" bytes to be placed in a single I/O buffer. This I/O buffer is a contiguous area of memory and, if very large, may have to be broken down into smaller chunks and copied into other areas of memory to be used by an application, such as DB2. For example, DB2 may choose to pre-fetch multiple extents of data into the bufferpool in response to a query. An extent by default is 32 (4 KB) pages. From here, the extent is broken up into 4 KB chunks (the individual pages) and copied to the bufferpool.
Vector I/O allows the allocation of smaller I/O buffers; in this case, of size 4 KB. A single vector read fills these buffers, which directly relate to pages in the bufferpool, eliminating the need to perform the copies. To enable Vector I/O on Linux, you must set the DB2 registry variable
DB2_SCATTERED_IO (shown in the command below) and restart DB2.
For more information on DB2 Vector I/O refer to "DB2 Vector I/O" in the DB2 Information Center.
DB2 is tightly integrated with Linux and leverages the very latest Linux memory technologies. Tuning DB2 memory usage on Linux should not be a daunting task. DB2 provides features unique to Linux to make the best use of this system resource. The points discussed in this article provide a good starting point for producing a well performing DB2/Linux system.
- "The DB2 UDB memory model" (developerWorks, June 2004): Learn more about how DB2 uses memory.
- "High-performance Linux clustering" (developerWorks, September 2005): Learn the basics of Linux clustering, then move on to high-performance clusters and find out why Linux is everyone's favorite clustering platform.
- Memory address (Wikipedia): Gain a greater understanding of memory addressing concepts.
- Physical Address Extension (Wikipedia): Gain a greater understanding of how physical address extension allows for more memory to be used in 32-bit systems.
- DB2 UDB for Linux page: Learn more about DB2 UDB on the Linux platform.
- The DB2 Information Center: This online resource is your source for all kinds of information about DB2 UDB.
- IBM DB2 Universal Database Enterprise Server Edition V8.2 for Linux: Download a trial version from developerWorks.
- IBM trial products for download: Build your next development project on Linux with IBM trial software, available for download directly from developerWorks.