IBM Support

Recommended AIX Virtual Memory Manager settings for DB2 database product

Question & Answer


Question

Are there recommended AIX® Virtual Memory Manager settings for the DB2® database product?

Answer

While it is difficult to prescribe a standard recommendation for all virtual memory manager tunables on AIX systems running the DB2 product, there are some important specific settings where this is possible. This document focuses primarily on parameters which have non-default settings and are considered "Best Practice". These settings apply to all versions of DB2 product running on AIX 5.3 and higher, and applies to systems using both cached and non-cached (Direct I/O) file system I/O.

Please note that the lru_file_repage setting referenced below is not applicable to AIX 7.1 and higher.

Recommendations:

The "Best Practice" recommendation for DB2 product running on AIX systems is :



maxperm%=90
maxclient%=90
minperm%=3
lru_file_repage=0 (does not apply to AIX 7.1 and higher)

You can implement these settings with the following command, which is dynamic and takes effect immediately (omit lru_file_repage for AIX 7.1 and higher) :


vmo -p -o maxperm%=90 -o maxclient%=90 -o minperm%=3 -o lru_file_repage=0


Optional:
page_steal_method=1

Note that maxperm%, maxclient%, lru_file_repage, and page_steal_method are restricted tunables as of AIX 6.1, and you should not tune these restricted tunables without first consulting AIX support.

You can view the current settings by executing the command "vmo -a". On AIX 6.1 and higher you can view the current settings for restricted tunables by executing the command "vmo -a -F". You can view a subset of settings such as minperm%, maxperm%, and maxclient%, by executing the command "vmstat -v" and you do not require root authority to issue this command.

Definitions:

Computational memory - Application memory (such as DB2), kernel memory

Permanent memory - File cache, such as JFS, JFS2, NFS, and GPFS.

Client memory - JFS2, NFS, and GPFS, which means all permanent memory except JFS.

maxperm% - the threshold above which permanent memory pages will be evicted in favor of computational memory pages. The default value of this setting for AIX 5L is 80%, and for AIX 6.1 and higher is 90% but it is now a restricted setting for AIX 6.1 and higher

maxclient% - the threshold above which client memory pages will be evicted in favor of computational memory pages. The default value of this setting for AIX 5L is 80% and for AIX 6.1 and higher is 90%, but it is now a restricted setting for AIX 6.1 and higher.

minperm% - the threshold below which computational memory pages will be evicted in favor of permanent memory pages. The default value of this setting for AIX 5L is 20% and for AIX 6.1 and higher is 3%.

lru_file_repage (not applicable to AIX 7.1 and higher) - when the number of permanent memory pages (numperm) falls between minperm and maxperm, or the number of client memory pages falls between minperm and maxclient, this setting indicates whether repaging rates are considered when deciding to evict permanent memory pages or computational memory pages. Setting this to 0 tells AIX to ignore repaging rates and favor evicting permament memory pages, and thus keeping more computational memory in RAM. The default value for AIX 5L is 1 or true, which means by default it considers the repaging rate. The default value for AIX 6.1 is 0 or false and is a restricted setting.

page_steal_method - selects the AIX LRU scanning method. "0" is the default value for AIX 5.3 and tells AIX to use the legacy page frame table method. "1" is the default value for AIX 6.1 and higher and tells AIX to use a list-based scanning method. This is a restricted tunable on AIX 6.1 and higher.

Explanation:

Since the lru_file_repage tunable and related AIX behavior changes were introduced, the recommended settings of key virtual memory manager parameters have changed. The older strategy for DB2 product running on AIX systems was to reduce file cache usage with a combination of moderately low maxperm or maxclient settings, such as 15-30%, and a much lower minperm setting, such as 5-10%. This strategy was required to prevent the paging out of computational memory on systems with a very active file cache.

The new tuning strategy takes advantage of lru_file_repage, and it includes settings which apply to all DB2 products running on AIX systems. These settings allow for a large filesystem cache where desirable, but not at the expense of paging out computational memory. Following these guidelines allows DB2 to take maximum advantage of available system memory without risking the effects of unnecessary paging due to file cache activity. It should be noted that the "lru_file_repage=0" setting is especially important for the optimal performance of DB2's Self-Tuning Memory Manager (STMM). STMM assumes that file pages above minperm can be used for other purposes, such as bufferpools, without paging out computational memory.

The new virtual memory manager settings are a general AIX recommendation and are not restricted to only systems running DB2 product. These settings are also the default settings on AIX 6.1 and higher. They have been adopted by the IBM Smart Analytics System and InfoSphere Balanced Warehouse and are now the recommended best practice for all DB2 products running on AIX systems.

An optional setting, "page_steal_method=1", has been included as an acceptable setting for DB2 product running on AIX systems. It can improve AIX performance by switching to list-based LRU scanning, and this is also the default on AIX 6.1 and higher. However, the benefits for DB2 product running on AIX systems are not as widespread as the other tunables, and it is not strictly a "Best Practice".

Other Resources:

For details on the AIX concepts discussed in this technote, please see the following links :


http://www.ibm.com/developerworks/aix/library/au-vmm/
http://publib.boulder.ibm.com/infocenter/systems/index.jsp
http://www.redbooks.ibm.com/redbooks/pdfs/sg246039.pdf



For further discussion on this topic, visit this developerWorks forum thread:
https://www.ibm.com/developerworks/community/forums/html/topic?id=0c95ecad-729b-4c18-973d-fb04ac410ece

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Performance - General\/Tuning","Platform":[{"code":"PF002","label":"AIX"}],"Version":"9.8;9.7;9.5;9.1;8.2;10.1;10.5;11.1","Edition":"Enterprise Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21328602