DB2 Express C v9.7 Performance Tuning on Ubuntu 9.10 64bit

I've been struggling on tuning my application. I google on how to tune my back end and make my front end work smoothly with high scalability. My objective for this project was to make my back end run faster and accept multiple connection without risking other connection, high scalability and of course secured. I used Apache Wicket as my MVC, Hibernate as my persistence layer and Spring as my Security Layer. My topic is how to tune my DB2 running on a Linux Ubuntu v 9.1 64 bit.

This is what I did on DB2 Express C v9.7 running on Linux v 9.10 64 bit

I updated the DB2 environment variables

db2set DB2_MMAP_WRITE = NO

db2set DB2_MMAP_READ = NO

db2set DB2_FMP_COMM_HEAPSZ =

db2set DB2_EVALUNCOMMITTED = YES

db2set DB2_SKIPINSERTED = YES

db2set DB2_SKIPDELETED = YES

db2set DB2_HASH_JOIN = NO

I turn off some of the setting for the monitoring to reduce CPU usage. If the DB2 is for testing mode, the monitoring tool can be turn on.

db2 update dbm cfg using NUMDB 4

db2 update dbm cfg using DFT_MON_LOCK OFF

db2 update dbm cfg using DFT_MON_SORT OFF

db2 update dbm cfg using DFT_MON_STMT OFF

db2 update dbm cfg using DFT_MON_BUFPOOL OFF

db2 update dbm cfg using DFT_MON_TABLE OFF

db2 update dbm cfg using MON_HEAP_SZ 10000

db2 update dbm cfg using DFT_MON_UOW OFF

db2 update dbm cfg using DFT_MON_TIMESTAMP OFF

db2 update dbm cfg using SHEAPTHRES 10000

db2 update dbm cfg using UDF_MEM_SZ 256

db2 update dbm cfg using JAVA_HEAP_SZ 512

db2 update dbm cfg using DIR_CACHE YES

db2 update dbm cfg using ASLHEAPSZ 15

db2 update dbm cfg using DRDA_HEAP_SZ 128

db2 update dbm cfg using RQRIOBLK 65535

db2 update dbm cfg using QUERY_HEAP_SZ 16384

I initialized the connection pooling even there is already a connection pooling established on the application server. The connection pooling on the application should be lesser or equal to the max connections on DB2 to avoid runtime error.

db2 update dbm cfg using FENCED_POOL 100

db2 update dbm cfg using NUM_INITAGENTS 50

db2 update dbm cfg using MAXAGENTS 100

db2 update dbm cfg using MAX_COORDAGENTS 100

db2 update dbm cfg using NUM_INITFENCED 50

db2 update dbm cfg using NUM_POOLAGENTS 100

db2 update dbm cfg using MAX_CONNECTIONS 100

I updated the database memory and heap management to provide high scalability.

db2 update db cfg for DB2T using DFT_QUERYOPT 2

db2 update db cfg for DB2T using DBHEAP 1200

db2 update db cfg for DB2T using CATALOGCACHE_SZ 64

db2 update db cfg for DB2T using LOGBUFSZ 128

db2 update db cfg for DB2T using UTIL_HEAP_SZ 5000

db2 update db cfg for DB2T using LOCKLIST 1000

db2 update db cfg for DB2T using APP_CTL_HEAP_SZ 1000

db2 update db cfg for DB2T using APPGROUP_MEM_SZ 60000

db2 update db cfg for DB2T using SORTHEAP 256

db2 update db cfg for DB2T using STMTHEAP 4096

db2 update db cfg for DB2T using APPLHEAPSZ 4096

db2 update db cfg for DB2T using PCKCACHESZ 5000

db2 update db cfg for DB2T using STAT_HEAP_SZ 4384

db2 update db cfg for DB2T using MAXLOCKS 25

db2 update db cfg for DB2T using LOCKTIMEOUT 60

db2 update db cfg for DB2T using CHNGPGS_THRESH 60

db2 update db cfg for DB2T using NUM_IOCLEANERS 4

db2 update db cfg for DB2T using NUM_IOSERVERS 6

db2 update db cfg for DB2T using MAXAPPLS 1200

db2 update db cfg for DB2T using AVG_APPLS 1

db2 update db cfg for DB2T using MAXFILOP 64

db2 update db cfg for DB2T using LOGFILSIZ 1000

db2 update db cfg for DB2T using LOGPRIMARY 10

db2 update db cfg for DB2T using LOGSECOND 20

Note: DB2T is the name of Database.

Before I did all the configuration / settings shown above, I execute RUNSTAT and REORGCHK after I created the database. This will help the query performance when using RI and indexes. I am not sure if the syntax or command shown above is also supported on DB2 z/OS. One more thing, the DB2 Linux and the Application Server runs on the same machine which I there is a minimal network traffic because I used localhost instead of the IP address.

My reference on DB2 performance tuning can be found on this link: Reference on DB2 Tuning can be found on this link: http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.doc/doc/t0024229.htm

How to prevent OOME? Memory Leak analysis...

What is Memory Leak and how it affects everything in your server? First, Memory Leak is not good. Memory leaks are hard to detect especially when using frameworks or third party library that you didn’t created. Seconds, it affects everything when you deploy it in production environment and don’t care about memory leak until your server is down. If you want to keep the server run 24 x 7, check for memory leak.

In Java, Garbage Collector (GC) is your friend, it collects and handles most of the memory leak issue by default but it still requires help from your code. The GC is just a temporary workaround. When creating an application that use memory as storage, make sure that you clean your own mess. Don’t depend on GC because GC sometimes it ignores your object to be collected because GC doesn’t have a way to identify if your application is really using the object. Memory leak usually cause when an object in JVM are not accessible by a running thread and also caused by a Rogue Thread.

Rogue Thread is an unmanageable thread. No application can control it and it is caused when there is an application running and in the middle, the application stop. Another cause of memory leak is a Circular Reference, on my previous job, I always tell my colleagues to make sure that they’re not doing a circular reference because it’s like doing an infinite loop.

Lesson learned about Memory Leak and what to do:

1.    Stress testing is necessary if you’re application is for multithreaded.

2.    Utilize the tools that Java JDK provided.

3.    Take care of your code before sending it to production.

4.    NEVER EVER BLAME THE SERVER. Server is just another folder in your windows OS.

5.    To be continued…

Basic tips for performance:

1.    Try to minimize the use of STATIC object.

2.    Convert “+” concatenation into StringBuffer or StringBuilder.

3.    Understand GC.

How I search for memory leak?

Simple, use the tools that you already have. The question is how to use it? Anyway, I listed the approach below. Note: You must use JDK 6 or higer

HotSpotDiagnosticMXBean

ManagementFactory.getDiagnosticMXBean().dumpHeap("\temp\heapdump_apps.bin", true);

After getting the heapdump, use the jVisualVM to find the leak.

-XX:HeapDumpOnCtrlBreak

The -XX:HeapDumpOnCtrlBreak option adds the hprofdump diagnostic command to the list of commands that run automatically when the Ctrl-break keys are pressed (similar to the print_threads diagnostic command). The HPROF dump file is written to the location defined by the -XX:HeapDumpPath option. Add this option when running JVM. It will create heap dump every time ctrl+break (kill -3) signal is sent to JVM.

-XX:+HeapDumpOnOutOfMemoryError

If you application throws an OutOfMemoryError, it will create a heap dump (.hprof file) into a binary format. You need to add –XX:HeapDumpPath option to define the location of your heap dump file.

jMap –dump:file=.bin

jMap is the tools that I’ve been using to generate heap dump. It comes with JDK install. is just file name and is your application process_id. In unix, you can get this by “ps eaf”, in windows use the “jps” to get the process ID.

Use jMap in the code

All you need to do is create a simple java class and add the code to your main method.

String appName= ManagementFactory.getRuntimeMXBean().getName()

String processId  = appName.substring(0, appName.indexOf("@"));

String [] myCmd = {"jmap", "-dump:file=/tmp/appHeapDump.bin", processId};

Process proc = Runtime.getRuntime().exec(cmd);