• 1 Planning phase - Before Server Build
  • 1.1 Server Memory Configuration
    • 1.1.1 CMM configuration
    • 1.1.2 SWAP Configuration
    • 1.1.3 DB2 Instance Memory Configuration
  • 1.2 DASD or SAN Storage Configuration
  • 1.3 CPU Configuration
• 2 Server Configuration - After Server Build
  • 2.1 Linux Shared Memory
  • 2.2 Linux Virtual Memory Management
  • 2.3 Storage Configuration - SAN
  • 2.4 Storage Configuration - ECKD
  • 2.5 Tuning Storage Devices 
  • 2.6 DB2 Instance User configuration

Planning phase - Before Server Build

These are configurations that should be done at server build . They should not be done after the application is already running DB2. Some information needs to be obtained from Application Architect or the Project Manager .

Server Memory Configuration

Depending of the DB2 server, CMM application could become a problem . DB2 server application has its own memory management system and the way that it manages memory can conflict with CMM .

Basic information that is necessary to define the server configuration includes the following.

1. How many instances and database will run in that server ;
2. And what is the total workload of this database .

CMM configuration

The first configuration is about CMM . That is made at lxnames level . There is a lxnames tag avaliabled called cmm. This tag will provide us the following desired results.

• CMM target will be 50% of the total server memory.
• The server will always have left 1G of memory left that is not retained by CMM algorithm .
• The CMM will not leave more than 10G of free memory at the specific server .

_{:cmm. tag form lxnames}

:cmm target 50 minfree 1024 maxfree 10240
The nedit command :
nedit <servername> :cmm target 50 minfree 1024 maxfree 15360 (Newt

SWAP Configuration

Another reference is SWAP filesystem . The swap size should be 20% of the real memory size , but if the server have more than 10G of real memory it is not recommended that you have more than 2Gb of swap memory .

At server build process the bigger swap size should be 2Gb .And following the recommendation about do not use more than 1G within V-DISK as swap . All additional swap space need over the 1GB line use the DASD devices to setup it .

DB2 Instance Memory Configuration

The setup of db2 memory instance ( INSTANCE_MEMORY ) should not be setup as automatic . However that is not a Linux on Z Admin responsability we should advise the server owner or the Project leader of the server that parameters must be configured within a fixed value . The size must be less than total memory of the server and the sum of all memory instance value could nerver be higher than the total of the shared memory setup to the server ( see more about shared memory configuration later in this document ) .

That recommendation is part of the DB2 Manual and the reason to do that is because we already have two Operating System managing memory ( zVM and Linux on Z )  , so a thirty player to manage memory isn't really desirable .

DASD or SAN Storage Configuration

In a DB2 server the  I/O throughtput his high in a very used database . It is difficult to determine what is the instance workload to discover what is the best choice for server configuration .

There is some basics that should be observed :

• For each instance on Volume Group : Based on experience the performance of one instance could interfer in another instance because of the I/O procedures . It is a fact that a bad filesystem design can compromise all server , but the probability is higher when two instance are sharing the same MDISK .
• One Logical Volume for each database functionality : DB2 Data files and DB2 log files should not share the same filesystem . Each instance should have at least 3 filesystem/volume group . General files , data files , log files .
• SAN devices are less complexity than ECKD : After some tests the performance x complexity give SAN disk advantages among ECKD devices . If you want performance and less complexity use SAN disk instead ECKD . 
• When you are using ECKD LVM striped is the best choice : At ECKD devices try to use small MDISK ( model 9 or model 3 ) and if possible that DISK aren't part of the same volume .

CPU Configuration

Because we are working with high IO server at most of the time the number of CPU should be greater than 2 ( two ) . That will provide the system enough power to handle the IO without interfer at the application requests .

Server Configuration - After Server Build

After server was rebooted some points must be covered before server deliver .

Linux Shared Memory

The basics of a database server performance is to have a good memory area for buffers pools . This memory is allocated at shared memory and there is some limits that could be configured at linux server . The initial configuration should be 60% of the total memory and the paging system should be monitored . The highest value is 90% of total of the memory .

More information about how to configure memory could be accessed at Memory Allocation to DB2 Servers document .

Some point should be covered :

• The configuration must be done at/etc/sysctl.conf  per standard bases .
  
• The script used to automation configuration /usr/local/support/db2_kernel_settings.sh reserves 90% of the server memory as shared . Because of some omm-kill at db2sysc process the best configuration is set this level to 60% depending of the server memory size . ( After the scripts changes this line can be deleted ) .

Linux Virtual Memory Management

Linux system has its unique way to control the server memory , that is called Linux Virtual Memory Management and more info could be found at Linux Memory Management & CMM .

The configuration objectives are performance and avoid out off memory problems .

_{Configurations for /etc/sysctl.conf file}

vm.page-cluster = 1
# Number of pages that will be write at swap in a single attempt.
vm.swappiness = 0
# That's decrease the server swapping process
vm.overcommit_ratio = XX
# Percent of real memory that can be committed + swap size
vm.min_free_kbytes = XXXXXXX
# The number of free kbytes should be 5% of total of memory
vm.dirty_ratio =20
# The size in percents of memory when researched the system will start to write page to the disk
vm.dirty_background_ratio = 5
#The size in percent of memory when reached system release memory .

The overcommit_ratio of 90% indent to do not have server stopped because of OMM-Kill process . The default value of the overcommit memory is 50% of total server memory .

To use the overcommit_memory as a mechanism of memory protection can cause problems at application level and must be set and test at development or test server first , the reason is if the DB2 server doesn't setup to limit the maximum of memory that it could turn a issue .

The dirty_background_ratio and dirty_ratio interfere at the file cache memory size the first parameters defines when the pages will be write into the disk . At the servers with 10G per example with the default value of 10% only when the cache memory reaches the 1G the pages will be flushed what can cause problem in the Database server where we need memory available to the Database system . The second value forces the kernel start to write out the dirty data when the cache reaches the percent of set memory . After it reaches that value all IO transaction start become synchronous . That procedure will decrease the size of the memory cached but it can in other hand increase the IO time on CPU. The default values is 40 .

For the most of the servers the 20 of dirty_ratio is enough but if the server have more than 20GB of memory try to reduce the value to 10 .

The min_free_kbytes should be set to 5%  total of memory and it  is related to memory reclaim . When this value is researched the kswapd start the process to clean some pages to get free memory . That would prevent the application turn into sleep state waiting for kernel to release memory . High values could affect system performance.

Storage Configuration - SAN

As previous describe the simplest and best choice for DB2 servers are the SAN devices with multiples paths . That is the best choice because it is possibe to actived DIRECT I/O parameter at Db2 configuration , but it is necessary to be activated . It is simple because linear logical volumes has been used .

Storage Configuration - ECKD

In case to use ECKD devices please try to set up the striped logical volumes , it is a little more complicated but the performance results are very good and it is better then SAN performance when it is configured with one unique device per stripe.

The logical to create a lvm striped is simple : If you are using 3 MDisk you set 3 stripes if you are 4 Mdisk you set 4 stripes , so the number of MDISK is equal to the number of stripes and number of cilynders of the MDISK must be the same for all MDISKs.

Tuning Storage Devices 

For database system with large number of devices attached there is some configuration that could help at performance . The basics here is disable the readahead linux function that is responsable to populates the page cache with data . Disabling this functionality Linux would not spend more system time controling the disk page cache , on other words we are giving the hardware the function that it was design for : I/O control and cache .

1. Disable LVM read ahead funciton at all logical volumes of the database VGs

lvchange -r 0 /dev/<volume group>/<logical volume>

2. Set the Linux block device layer to 0 bytes

blockdev --setra 0 <device>

3. Change the elevator type from default cfq to deadline .

# vi /etc/zipl.conf

At the botton of the "paramater=" line include the information elevator=deadline

# zipl.conf
# shutdown -h now

The output of cat /proc/cmdline command must include the string elevator=deadline .

4. For ECKD devices only, change the cache mode to record using tunedasd tool  .

tunedasd -c record <device>

These procedures 2 and 4 must be issued after every server IPL .

The script bellow can be used to setup the ECKD devices . Do not try to use that script with SAN devices I new script must be implemented .

#!/bin/bash
if [ ! -s /etc/sysconfig/readahead_vg_list ] ; then
exit 1
fi


for VG in $(cat /etc/sysconfig/readahead_vg_list)
do
for LV in $(lvs $VG -o name| grep -v LV)
do
echo "Setting the $LV LV"
lvchange -r 0 /dev/$VG/$LV
done
for DEVICE in $(lvs $VG -o devices| grep -v Devices |awk '{print $1}'| cut -d "(" -f 1)
do
echo "Setting the $DEVICE"
blockdev --setra 0 $DEVICE
tunedasd -c record $DEVICE
done
done

DB2 Instance User configuration

After the installation of the server the DataBase administrator or server owner must provide the users ID that will handle the DB2 instance . That's why the number of open files handle by the user must be 65536 or higher according within [Http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.qb.server.doc/doc/r0052441.html DB2 Manual ] . The maximum file size allowed to the instance user must be unlimited and also the memory allocated must be unlimited too.

Per default the memory allocated per user (data)  is ulimited so no change are need for this parameter . The same applies to file size (blocks) .

The change that must be issued it the number of the files opened . To setup these parameter modify the/etc/security/limits.conffiles including the following lines . At the follow example conseider the db2inst1 the user ID that handles the instance .

db2inst1     soft nofile 65536
db2inst1     hard nofile 65536

That configuration must be issued for every user ID that handles a instance at the server . Altought you can use a wildcard to minimize the work as the example bellow .

db2inst*     soft nofile 65536
db2inst*     hard nofile 65536