DB2 Container Rebalancing: Choosing the right filesystems

Our ERP software's database spans over multiple filesystems for better load balancing, but mixing high-load and low-load database tables on the same filesystem. Also, filesystem are grouped in volume groups that reside on different physical disks.

To better understand this, here's how our production SAN is devised:

Array 2 is one of two database data arrays, and is created over 6 hard disks in RAID level 5.
Array 2 contains two Logical Drives: SAPappShared and SAPdataDrv2. These will be mapped to the operating system (OS) as physical disks.

 

Here you can see that the 3rd array spans over different disks. This is to optimize data load times when requesting data from the database.
The two red/white disks are hot-spares that will substitute any failed disk immediately.
You can also see that this array reside on a different controller (B), while the previous is on (A). The controller handles I/O requests from the OS.

 

Database logs are written to a RAID level 1 array spanned over 2 disks. Logs are written always so it's better to keep them away from data to not hamper the performance.






From what you saw above, you can count 6 Logical Drives that relate to the database. The database uses only 4 of them: Log, Data1-1, Data1-2 and Data2. The Heartbeat drives are used by the cluster services of the OS.



Now, moving to the OS: IBM's AIX. We run version 5.3L.

On AIX, you cannot create a filesystem on a physical disk directly. The physical disk has to belong to a Volume Group. A volume group can house multiple physical disks, whether internal disks or attached through storage or network. A volume group can contain multiple filesystems. This all falls under the topic of the Logical Voume Manager, which I will not talk about here.

Back to our setup above, from AIX's point of view, the Logical Drives are shown as physical disks:

# lspv
hdisk0          00c3b3f0feb38826                    rootvg          active
hdisk1          00c3b3f0d832abbc                    rootvg          active
hdisk2          00c3b3e0fbad39e6                    hrtvg1
hdisk3          00c3b3e0fbb076c8                    saplogsvg       active
hdisk4          00c3b3e0fbef90aa                    sapdatavg1      active
hdisk5          00c3b3e0fbf2e9bf                    sapdatavg2      active
hdisk6          00c3b3f0168bedbb                    sapdatavg3      active

You can see from the list above that each Logical Drive is a physical disk and moreover, each has been put to a separate volume group.

These are filesystems spanned over the volume group on hdisk4:

# lspv -l hdisk4
hdisk4:
LV NAME               LPs   PPs   DISTRIBUTION          MOUNT POINT
db2lv                 2     2     00..02..00..00..00    /db2/EHP/db2ehp
db2dumplv             24    24    00..01..00..00..23    /db2/EHP/db2dump
sapdata6lv            280   280   123..00..00..135..22  /db2/EHP/sapdata6
sapdata3lv            340   340   13..132..135..00..60  /db2/EHP/sapdata3
loglv2                1     1     00..01..00..00..00    N/A

Before continuing, let's talk about database usage, to be able to justify the next moves.

Since the database tables have been split over multiple filesystems, some of the filesystems have high growth rate. Our database (IBM's DB2) can span the contents of a table over multiple filesystems; a load balancing technique. It does this through its concept of rebalancing containers. (In DB2, tables reside in Tablespaces and a tablespace consists of one or more containers).

So, if we create a second container of a high growth table, it will take half of it and dump it over the new container and in the future, it will write to both in a load balancing form.

Now, to be able to achieve a true sense of load balancing, in filesystem utilization and database performance, ideally these two filesystems (that house the high growth table) should be on different controllers, so that data requested residing on both filesystems can be served in parallel by the controllers.

If you're doing the rebalancing after going live with the system (which is the case mostly), then you'd need to know which physical disk on the OS goes to which controller.

Here, you see that each Logical Drive has a unique identifier called Logical Unit Number (LUN).

The LUN is represented as a hex value on AIX.





From the shell, type "lspv" to list the physical volumes, then "lsattr -El " to list the attributes of one of the disks. The value "lun_id" is what you want. See the output below.

# lspv
hdisk0          00c3b3f0feb38826                    rootvg          active
hdisk1          00c3b3f0d832abbc                    rootvg          active
hdisk2          00c3b3e0fbad39e6                    hrtvg1
hdisk3          00c3b3e0fbb076c8                    saplogsvg       active
hdisk4          00c3b3e0fbef90aa                    sapdatavg1      active
hdisk5          00c3b3e0fbf2e9bf                    sapdatavg2      active
hdisk6          00c3b3f0168bedbb                    sapdatavg3      active

# lsattr -El hdisk4
PR_key_value   none                             Persistant Reserve Key Value           True
cache_method   fast_write                       Write Caching method                   False
ieee_volname   600A0B80003260360000A92E472997CA IEEE Unique volume name                False
lun_id         0x0005000000000000               Logical Unit Number                    False
max_transfer   0x100000                         Maximum TRANSFER Size                  True
prefetch_mult  1                                Multiple of blocks to prefetch on read False
pvid           00c3b3e0fbef90aa0000000000000000 Physical volume identifier             False
q_type         simple                           Queuing Type                           False
queue_depth    10                               Queue Depth                            True
raid_level     5                                RAID Level                             False
reassign_to    120                              Reassign Timeout value                 True
reserve_policy single_path                      Reserve Policy                         True
rw_timeout     30                               Read/Write Timeout value               True
scsi_id        0x10400                          SCSI ID                                False
size           173670                           Size in Mbytes                         False
write_cache    yes                              Write Caching enabled                  False

# lsattr -El hdisk6
PR_key_value   none                             Persistant Reserve Key Value           True
cache_method   fast_write                       Write Caching method                   False
ieee_volname   600A0B80003260360000A9AA472EA49C IEEE Unique volume name                False
lun_id         0x000c000000000000               Logical Unit Number                    False
max_transfer   0x100000                         Maximum TRANSFER Size                  True
prefetch_mult  1                                Multiple of blocks to prefetch on read False
pvid           00c3b3f0168bedbb0000000000000000 Physical volume identifier             False
q_type         simple                           Queuing Type                           False
queue_depth    10                               Queue Depth                            True
raid_level     5                                RAID Level                             False
reassign_to    120                              Reassign Timeout value                 True
reserve_policy single_path                      Reserve Policy                         True
rw_timeout     30                               Read/Write Timeout value               True
scsi_id        0x10500                          SCSI ID                                False
size           294225                           Size in Mbytes                         False
write_cache    yes                              Write Caching enabled                  False

Using the above commands, finding which disk belongs to which controller on a SAN on AIX becomes like baby steps ;)

We did a rebalance on one of the containers today. The container's size was about 50GB and it took about an hour on an IBM DS4800 SAN hooked with fiber to the database node residing on an IBM pSeries (570) machine: 4 POWER5 processors allocated and 30GB of RAM.

I tried looking up time estimation for rebalancing containers on DB2 on Google, but didn't find anything useful. Luckily, things went on smoothly.
The error file "db2diag.log" had the following with regards to rebalancing:

2009-05-18-01.05.59.552039+180 E106271503A342     LEVEL: Warning
PID     : 799110               TID  : 1           PROC : db2rebal 0
INSTANCE: db2ehp               NODE : 000
FUNCTION: DB2 UDB, buffer pool services, sqlb_rebalance, probe:2876
MESSAGE : ADM6062I  Rebalance for table space "EHP#BTABD" (ID "17") has been
 completed.

2009-05-18-01.05.59.552281+180 I106271846A295     LEVEL: Warning
PID     : 799110               TID  : 1           PROC : db2rebal 0
INSTANCE: db2ehp               NODE : 000
FUNCTION: DB2 UDB, buffer pool services, sqlb_rebalance, probe:2876
MESSAGE : PoolID 17: Last extent moved was #1866949

During the rebalancing period, the container being worked on was not showing in the list on the ERP software (SAP) and threw some errors. It was working fine on the database level however. After the process completed, SAP reported the new containers properly.

TPSA: When Everything Goes Right

I was going to write about a networking story, but what happened yesterday registered as a day to never be forgotten, hence this write-up.

TPSA: The Perils of System Administration -- A series of true stories about system administration. The first story is here.

Yesterday, Thursday April 16^th, we had a scheduled maintenance job on our production servers. The cluster wasn't functioning as it should and yesterday's tasks were aimed at rectifying the problems once & for all, and update the software installed.

This was our time-plan for each task on the list:

1400 to 1410	Shutdown SAP on DB and APP Server
1410 to 1440	Take a database backup Shutdown database
1440 to 1500	Take a Filesystem backup (All Filesystems on both nodes) Changing mointpoints for High Availability Cluster switchover testing
1500 to 1700	Shutdown SAP and database Perform Kernel Upgrade on SAP CI Start the database and SAP on the CI Instance ONLY (Not the APPS) Perform technical testing on CI Check the mountpoints exe Start SAP on APPS Perform technical testing on APPS
1700 to 1730	Import the ST-A/PI Patch 01L_ECC600 Change SAP Parameters based on the document Review the parameters on CI and APPS (Memory and Work Processes) Restart the SAP CI and APPS Perform technical testing on CI and APPS

All tasks were easy and planned out with all members of this properly set:
- ERP software consultant (joined by his colleague later)
- ERP offshore support consultant
- AIX Unix consultant from IBM Kuwait
- Myself

We grabbed lunch around 1230 and some snacks and headed to the Head Office (H.O.).

@1401: ERP Applications were stopped

@1406: We started a full offline backup of the production database

@1437: The backup reached its final stage, then threw an error, stopped, and gladly deleted the backup

*NO!! THIS IS NOT THE TIME!! PLEASE!*

After being stunned and depressed for 2 minutes, I thought of stopping the database and starting again, using the user db2ehp I did that and when I tried to start the database again, it threw an error!

*JAWS DROPPED -- LAAAAAAAAA!!*

We faced the same error a few days back due to some user profile changes, so we had to run the command "/usr/opt/db2_08_01/instance/db2iupdt db2ehp" -- I ran that command and it puked an error ...

*NOOOO!! WHY WHY!! IT WAS FINE WHY NOW!! I HATE YOU!!*

I navigate around and go to the home directory of the user db2ehp to check the environment variables and profiles, only to find out that these files have been corrupted and turned to binary garbage

*SHOCKED*
*EYE TWITCHES*

I ftp to the secondary database node, looked for similar files, then decided to copy the whole directory and renamed the files to match the host name of the primary node.

It didn't work for some reason, even though all the environment variable scripts had proper values...

I called the company responsible for the support on our backup software (TSM) and in about half an hour their consultant provided me with a command line to recover a certain directory to a specific location.

Then we restored from our backup software that whole directory: "/usr/tivoli/tsm/client/ba/bin/dsmc restore /home/db2ehp/ -subdir=yes /bkfs2/restoreyaman/"

@1620: All files were recovered, and now were able to switch to the user db2ehp properly.
I run the comman "/usr/opt/db2_08_01/instance/db2iupdt db2ehp" and then start the database and it worked!

*YESSS!!*

Database backup started again and we waited till it finished.

DB backup finished and we wanted to take a backup of the filesystems through TSM. Going back & forth, we eventually invoked it manually through TSM, but it timed out & didn't work. Error in communication through TCP/IP, it said!

*THE IP IS WORKING! I CAN PING AND LOGIN WHY CANT YOU!!???? BLOODY $##^%^@*

@1743: The IBM engineer arrived and suggested that since the directory to be changed is very small, then just copy the directory somewhere else. We copied the directory we were going to change with "cp -pR " to preserve permissions, to a remote filesystem.

Now that everything is ready to be changed, the offshore support was contacted and their consultant logged in to our server through VPN and did the required changes.
The changes were simple: One of the filesystems was part of the cluster resources and its mount point was incorrect. We simply had to change the mount point from "/db2/db2EHP to /db2/EHP/db2ehp"

After that step was done, the IBM dude synchronized the changes between the cluster nodes on the IBM AIX Unix cluster. That was half the work -- now we just needed to make sure that we can failover back & forth, then we will proceed with patching the ERP software to the latest version.

@1805: We crashed the primary node, to simulate a failover from the primary DB node to the secondary, tested our ERP software, and it was working.
Upon crashing the primary node, it was booted up again from the management console and left to come up. Meanwhile, we were checking that the DB was working properly on the 2nd node and that the ERP software was able to communicate with the DB and everything was fine.

@1815: The primary DB node didn't come up yet. Fishy. Upon checking on it, it seemed to be stuck, so we restarted it again.

@1825: The machine didn't come up after 10 minutes, which was very suspicious... after checking the management console, it was stuck at code: "0557" -- Some Googling away and to our luck, the message meant problems with the filesystem....

The system couldn't boot because from what it seems, the root filesystem (/) is corrupted, hence the operating system can't load.

*EYE TWITCHES*
*WHY ???? WHYYYYY??? WHY NOW? WHY ME? *

I'm quite disappointed, since I have never expected to face such an issue with AIX and on a p5 series machine. Even the IBM dude was shocked.

The IBM engineer said he can proceed with the procedure we found here:
http://www.docstoc.com/docs/2801670/AIX-BOOTING-PROBLEM -- page 5

But he said that since his job isn't support, it would be better for us to call IBM's support line and log the call, and whatever instructions they give, he'll execute them.

I call IBM's branch in Kuwait and dial the extension which usually takes me to their support in UAE. No one answered... I called 2 more times, without avail.

The IBM dude called a colleague and he gave him another extension for off-hours support. We called that extension and someone picked up! (OH JOY)

I told the support dude my company's name & that we're from Kuwait and he asked me what was the problem & to log an issue by sending an email. I sent the email.

@1953: I received an email from him asking for my company's name, again.

I reply to it and wait for another 10 minutes. Then I call the extension again and ask him what is going on, he said that our support contract had expired in 2005.

*HUH?!*

I tell him that we bought the hardware in November 2007!! And that the support contract for that didn't expire yet!! He insisted that there was no data on their end to support my claim and we argued for a good 10 minutes.
The IBM dude with us interfered and said that my claims are correct and that he was present during the purchase and commissioning of these boxes, but the dude at UAE said if their database doesn't show such a thing, there's nothing he can do.

@2024: He emails me with instructions to contact IBM Europe, and that if they are to help us, they will charge $360 an hour for a minimum of two hours!

According to all the links we found on Google, the error code points at a corrupt filesystem, so we know what's the problem exactly, and there was no point in contacting or paying for IBM Europe.

@2035: we proceed with loading the first DVD of AIX and boot from it to recovery mode on he primary node, and follow instructions in the document above.

All filesystems we corrupted. ALL of them.

*I looked at the IBM dude and said: I'm this close to sit in a corner and cry*

The AIX guru started fixing them one by one, and all got repaired (including root filesystem), except one: /home, which contains the startup and environment scripts for the database...

*CRAP! but at least the root filesystem is sane!*

We reboot the machine and enter the recovery mode again and run fsck (ilesystem check) again to make sure all the filesystems are fine now .. all were, except /home -- it's a goner. Can't be recovered anymore. The LVM partition was corrupt beyond recognition.

The IBM engineer proceeds to make sure that the root filesystem is bootable, on both disks, by issuing the respective commands to write the Master Boot Records to them, and other information.

We exit the recovery mode, for the machine to boot in normal mode and we look anxiously at the error code display... as soon as it passed and the system started to come up, we jumped in joy and hugged.

@2050: Now that we have most of the filesystems working, we decide to back them all up on a DVD (sysback).

@2105: The backup to the DVD failed. Apparently the unix box only likes DVD-RAM media. Luckily, there was an option to take a backup over the LAN to TSM.

@2115: We then proceeded to destroy the corrupt filesystem and its evil logical volume, then create a fresh one and import the /home directory contents to it from TSM: "/usr/tivoli/tsm/client/ba/bin/dsmc restore /home/ -subdir=yes /home/"

@2135: After restoration was done, we took another full backup of the root volume group (which includes the new /home filesystem).

Then we rebooted the primary node, to make sure that the filesystems persisted. It didn't come up after 5 minutes... When checking, the IBM dude had forgotten to abort booting from CD, at which the screen was stuck at. Exiting from that menu, the machine booted normally.

@2145: We rebooted one more time, just to be sure, and everything went fine. At this point, we no longer needed the offshore support nor the IBM engineer since their job was done. The offshore support logged off & the IBM unix guru left, with many warm thanks from me.

Then we proceeded to patch the ERP software; basically, it's just a compressed file with the new binaries and an installer script. We already had the stuff uncompressed on a remote filesystem (NFS), so we just mounted that, renamed the old directory (exe) to "exe_old", then copied the files to the proper location.

It should be noted that the filesystem which we applied the patch to, is exported as a network filesystem (NFS) to other nodes.

As the ERP software came up on the primary node, we started it on the 2nd node, but it crashed...

The ERP dudes tinkered around and found out that all nodes except the primary ERP one are using the old files (pre-patch) !!!

*EYE TWITCHES*
*WHAT THE!!*

We found out a few minutes later that for some reason, the NFS mount is still pointing at the old directory, which we renamed! So, it seemed like even if you rename the directory, NFS would keep track of that!! (Maybe we should've stopped NFS before doing the renaming?)

I proceed to re-export the NFS directories, in an attempt to refresh any links to the directories. It didn't work, and now other nodes are getting an error for this particular NFS mount:
"df: /sapmnt/EHP/exe: A file, file system or message queue is no longer available."

*EYE TWITCHES*
*NO MORE PROBLEMS, PLEASE!!! LET US FINISH AND GO HOME!!*

I stopped the NFS service & started it again. No use. Deleted the old exported directory settings from NFS, and add it again, then restart NFS. No use.

At the end, we decided to try moving out the files from the old directory to a temporary one, putting the new files in the renamed dirctory "exe_old" then renaming it back to "exe"

It worked!!!!! And I laughed hysterically, not believing what happened and the "solution"

During all this, we had to take down the cluster resources before modifying anything, since all nodes point at the shared NFS filesystem, even though it's not part of the cluster resources!!!

We had to shut & start the cluster services about 5 times, till we figured out the solution above.

We brought up all the systems and the ERP guys applied application-level patches & plugins (yes, more of them...)

@0057: I took the ERP dudes to their hotel and I went home.

The Perils of System Administration

Whenever you join a company, visit the IT department and you'll find a group of guys calling themselves Systems Administrators, or Systems Engineers (depending on their rank).
These are the guys that keep your IT services running, and even if you see them slacking, playing chess, hide-and-seek, making fun of users, or sleeping on the desk, you can almost always count on them when one of the servers go down; they will stay at work and use toothpicks to keep their eyes open, until your precious(ssss) services are up & running.

I have come across two kinds of admins: Those who have ethics and those who don't. In time of crisis, you can tell which is which, in case the unethical was a cunning fox during the casual days of duty.

Ethics dictate that you state clearly what you know & what you don't, take responsibility for your actions, be loyal to your employer, don't abuse your power, and do your job as you should.

I'll share a couple of stories here to further show the dedication, demand and abuse that IT administrators are subjected to.

Sleepless Nights: Data? What Data?

Three weeks ago we had a scheduled maintenance task starting at Thursday 1400 hours (2 PM) till 1800 (6 PM). The scope of this task was to fix our Database Server cluster where our Enterprise Resource Planning (ERP) software works. This means HR, Finance, Warehouses, and Sales are all dependent on it.

We stopped the system at 1405, took an offline full backup of the database before working, then we proceeded to verify the backup to make sure it's consistent.

At 1435, the backup was done, and we proceed with the offshore support of the ERP system to fix the cluster problems on the secondary/standby node.

One thing lead to another, and we ended up staying till 2200 (10 PM) and planned to continue working on Friday starting at 0800, hoping to finish before lunch time.
The offshore guys were still logged in through VPN from India and continue to dig around for a few more hours.

On Friday I was at the Head Office (HO) at 0750, contacted the offshore support and we picked up from where we left. Around 1100, we got both nodes to work, and we did 2 failover tests and the database worked fine, until we switched back to the primary node.

Everything just went down the hill from there...

The database entered an infinite loop and entered recovery mode. What is recovery mode, you say?
Well, it crashes, then comes up again trying to start, then crashes, and so on.
These continuous cycles caused the error dump filesystem to fill up, which caused another crash at a higher level, stopping the recovery cycle and ending with a non-working database server.

Around 1600, we were still trying to bring the database up after investigating many error logs of the database and the operating system.

A few more futile attempts were made to run the database, after increasing the size of the error dump filesystem.

Around 2100, we realized that our database has been corrupted. No more data. No more business.

Enter panic mode.

We knew we had a safe full backup after the business closed, so we won't be losing any changes. Now, it was all about recovering the database, make sure the ERP software is working, then we could sleep.

I forgot to mention that since we couldn't leave the place, I had a friend of mine bring us lunch to work, and that was the only meal we had that day. THANKS HISHAM!!!

Anyway, we raised a support ticket to the ERP software vendor (SAP) with the highest priority possible and they called me within 30 minutes from Germany. They verified that it is indeed a top priority problem and they assigned one of their elite support guys to help us.

Around 0200, Saturday, we decided to dump the existing now corrupted database, and import the backup.

At 0300 Saturday, we had wrapped up the unfinished work of the cluster failover task, and were just happy that the data and the systems are up & running for the business to use, since my company works on Saturdays.



More stories to come...