DB2 memory and file cache performance tuning on Linux

DB2 performance optimization on the Linux platform

Memory utilization and file caching are related elements that affect performance and are important to consider when tuning a database system. This article summarizes the IBM® DB2® Universal Database™ (DB2 UDB) features specific to Linux® for best utilizing these important system resources.

Share:

Monty Wright (montywri@us.ibm.com), DB2 Specialist, Product Introduction and Exploration, IBM, Software Group

Monty WrightMonty Wright is a certified IT specialist and has been supporting the DB2 brand for 8 years. He has published several articles on DB2 distributed topics and authored the DB2 performance tuning section of the IBM Redbook "Tuning IBM eServer xSeries Servers for Performance." As part of the IBM Advanced Technical Support team, he has participated in many customer engagements and benchmarks. His areas of specialty include performance tuning, database partitioning, and high availability.



22 September 2005

Introduction

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.

Note: There are programming techniques to gain access to the memory outside a process address space known as extended storage. This extended area can only be used as a cache, and the data has to be moved back into the address space to be manipulated. DB2 on Linux does not currently support these techniques.

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
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
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
Under-powered system

The key is to look at the relationship of values total, free, cached, and 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
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 Resources.)

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.

AIO

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.

db2set DB2LINUXAIO=true

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 Resources.)

Direct I/O

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 Resources.) 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.

Vector I/O

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.

db2set DB2_SCATTERED_IO=ON

For more information on DB2 Vector I/O refer to "DB2 Vector I/O" in the DB2 Information Center.


Conclusion

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.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Linux
ArticleID=94423
ArticleTitle=DB2 memory and file cache performance tuning on Linux
publish-date=09222005