Raghu On TechPro tips on managing data at scale
DB2 Server Build Best Practices!
The reason I am posting this article is I have worked in a number of places as a DBA and I must admit that I worked in places that were run meticulously and also worked in places where the Db2 and application were running on a single mount point “/” (root) on the same server 🙂
If you build Db2 servers on a regular basis, you must have a standard template to build your servers. DBA’s nightmare is nothing but different Db2 servers looking and acting differently. To avoid operational and support issues its worth creating a template and stick to it, although, that template may change from version to version and could be a living document.
Here are some of the things that I check for my stand alone DB2 Linux servers i.e. non-pureScale and non-DPF environments. For the sake of simplicity I am only focusing on Linux but principles stay the same for AIX and my Windows experience is limited and it was a decade ago…
User accounts and groups:
Before you create an instance, you need to create Db2 accounts for instance owner and fenced user. As much fun as it would be to run Db2 under “root” or “raghu”, I would advise against it :-). I would have no problems using below two users and groups for Db2 instance and fenced purposes. However lately security folks are questioning using default names for Db2/Informix instance and Fenced users. Atleast for Db2 you have the option to choose whatever the instance name you would like such as db2adm etc. but from what I heard from my Informix DBA’s they have no option to change instance name from Informix.
Whatever names you choose for your instance and fenced account, I recommend sticking to them for all of your Db2 servers and it will make your life easy when you are scrpting and automating. Although some places I worked at used Db2 instance owner as the fenced account, I would recommend against it. As it would allow user developed code such as Stored procedures and UDF’s to share the same address space as db2sysc.
Although, IBM’s documentation states that Db2 should automatically raise the ulimits, I still perform these settings on my new servers. If you install Db2 using Non-Root installation method, you should definitely look into these settings.
I have seen at least a couple of instances where Db2 servers are running into performance issues due to non-existent ulimits especially in the older versions of Db2(V9.7). Depending on where you work, as a systems DBA you may get “root” access and use it very judiciously. If you have root access you should be able to modify the “/etc/security/limits.conf” by adding below parameters or ask your System Admin to make the below changes. If you are using a different instance account than db2inst1, you need to update the first column with you Db2 instance name.
db2inst1 hard nofile 65536
db2inst1 – core unlimited
db2inst1 – stack -1
db2inst1 – data -1
db2inst1 – fsize -1
db2inst1 – rss -1
db2inst1 – cpu -1
db2inst1 – nproc 5000
In the past System Admins and/or DBA’s had to explicitly set kernel parameters for various shared memory areas of the operating system. However this has changed in the recent versions of Db2 as the database manager automatically adjust these kernel parameters (like SHMMAX, SHMALL, SHMMNI etc.) and eliminate the need for manual intervention.
Apart from these shared memory kernel parameters, Db2 also recommends you to set below two kernel parameters.
vm.swappiness=5 (IBM documentation until V10.5 says “0” but IBM updated their recommendation starting in V11.1. This really depends on the version of your Linux kernel rather than Db2 version. If you are running RHEL/CentOS kernels > 2.6.32-303 then you should set this to 5 or close to 5 as per IBM.)
Read below blog and comments, they were insightful.
Once the above parameters were set, you need to refresh the kernel parameters by running below command.
/sbin/sysctl -p /etc/sysctl.conf
In the era of SAN and Virtual environments, storage is getting more and more abstract to the DBA’s and it is one of the most important/slowest pieces of the performance triangle. Once in the past, I had to convince an application owner managing Db2 and Application servers that using root file system for Db2 is a very bad idea. He argued with me saying that in the era of SAN, there is no point in creating different filesystems for different parts of the applications/databases as SAN would manage which physcial disks a specific LUN will be written to etc. He has a point but there a number of reasons why you would still need to create separate file systems for different parts of Db2 and why you should not run application on the database server.
- Operating systems perform recovery at file system level, heard about fsck ?? If you install Db2 and create databases on “/” file system, what will happen if an OS patch corrupt a file system ?
- You don’t want some other application or rogue process filling up your file system and crashing Db2.
- Isolating the different work loads to different file systems for e.g. sequential(transaction logs) with random/sequential(tablespace IO) will make it easier to fine tune file systems by type of IO.
- Monitoring will be a lot easier if you have different components of your database sitting on different filesystems.
- Db2 may also take advantage of multiple filesystems although I do not know how in the era of SAN, but given Db2’s evolution over time its highly possible.
I digress but I create below file systems on all of my Db2 servers at a minimum. I worked at places where other structures were followed but this is pretty good for my needs.
/db2data/db2inst1 –> File system for your tablespaces.
/db2logs/db2inst1 –> File system for active transaction logs.
/db2archlogs/db2inst1 –> File system for archival logs, in case your TSM/Netbackup fails.
/db2backup/db2inst1 –> Emergency or regular backup file system depending on what you use for your backups.
/db2tmp/db2inst1 –> This is where my System temporary tablespaces go.
/home/db2inst1 –> Separate mount point for instance owner home directory. DO NOT share with other users.
/db2scripts –> Place to store your scripts and outputs.
/opt/ibm –> Place to install my Db2 binaries and store my tar balls.
If I run a multi instance environment which is a minority, I usually share these file systems among all the instances (no instance name on mount points) but sticklers may disagree with me. You may use below reference from IBM on supported file system types for your operating system. Make sure to check the supported file systems for your version of Db2.
Version And Fixpack:
I always try to stay one Fixpack behind the latest Fixpack unless my current Fixpack is having major issues and I need to upgrade to the latest Fixpack. I would never upgrade my Db2 servers using GA (General Availability), I was bitten in the past by being too aggressive with my upgrades and learned my lessons a hardway.
Open LDAP Configurations:
If you are using LDAP to authenticate your users by setting DB2AUTH registry variable to OSAUTHDB, you should seriously consider making below changes to your LDAP configuration file. I will be writing another blog post on why you should. Long story short this will cut down your Db2 LDAP authentication times by more than 10x.
Add below tid-bit to your /etc/sssd/sssd.conf file and recycle your ssh daemon.
ignore_group_members = True
Before I write a blog post on this topic, you may refer to original IBM’s tech note on this.
Exclude db2sysc From OOM Killer:
Linux has this OOM (out of memory manager) that kills the process using the most memory on the server when the server is running low on memory. Any guesses on what will be the process consuming most memory on a Db2 server ? You guessed it right its going to be “db2sysc”. If you are running RedHat or related family of Linux, you should consider telling OOM not to prioritize killing Db2 first. This way OOM will go after other big consumers first. Run below command as part of your db2start up script and you should be spared by OOM.
echo -1000 > /proc/<db2sysc PID>/oom_score_adj
Where <db2sysc PID> is the process ID of db2sysc.
Third Party Installations:
Along with Db2, you may need to install any third party products such as NetBackup, TSM, Avamar etc. for your backup needs. You need to ensure the pre-requisite libraries are in place and my system admins does a great job for me in this aspect.
I will be covering installation of Db2 and configuration in another blog post.