DB2 UDB Practical Performance Tuning
Practical tools and techniques for tuning the performance of DB2 applications running on Linux, UNIX, and Windows platforms.
May, 2004
by John Maenpaa

About the Author


[Hide]

Introduction


This will be an introduction to the tools available to analyze the performance of DB2 applications running on Linux, UNIX, and Windows platforms. We'll focus on practical methods and tools that are readily available for these platforms. Platform-specific tools will be covered first as these are usually the first tools used to determining if there is a performance problem.

Windows Platforms
There are several tools that can be used to find out what is going on with the system, applications and the database. We'll take a look at the Task Manager, Performance Monitor and Event Viewer tools, which are built into Windows. Then we'll look at some tools that aren't built in.

Most of the built-in tools use a GUI interface, so you may need to use a remote desktop tool to manage DB2 running on server systems. Windows XP Professional includes the remote desktop service.

Task Manager
The Task Manager is the quickest way to see what is running on a Windows system. Just right-click on the task bar and select Task Manager from the menu. The Windows XP version of this application includes information on Applications, Processes, Performance, Networking and Users. Older Windows releases have only Applications, Processes and Performance tabs. If I have direct access to a Windows system, this is the first place I look to see what is happening.

Figure 1

Applications
The Applications tab shows running applications. This list is usually restricted to applications that have a window. You don't really get much more information than that unless the program is not responding to Windows messages.

Processes
The Processes tab is the most useful part of the Task Manager. It lists all processes that are running on the system, along with CPU, memory, and I/O utilization of each process. The columns can be selected by using the View - Select Columns menu. Click on a column header to sort by that column.

You should see several DB2 processes in the Task Manager window, including db2sec.exe, db2rcmd.exe, db2licd.exe, db2jds.exe, and db2syscs.exe. Most of these processes correspond to services available in the Services management console. The db2syscs.exe process is the primary database engine and db2bp.exe processes are the DB2 agent processes. If agents are the primary resource users here, then the problem may be in the application SQL statements or in the database configuration. High resource usage in the other DB2 processes would likely indicate a problem in the database configuration.

Performance
The Performance tab displays CPU and memory usage in graphical and summary text views. This is very useful for determining whether the system is overloaded. If committed memory exceeds physical memory, then the system is very likely experiencing paging which would, of course, be detrimental to database execution. Consistently high CPU usage here could indicate that the server is overloaded.

Networking
The Networking tab gives a quick overview of network activity and network utilization for each enabled network adapter. Long spikes here could be indicators of network overload conditions.

Users
The Users tab shows which userids are logged into the system.

Performance Monitor
The Windows Performance Monitor tool has been available in the Windows NT line since the beginning. Many aren't even aware of its existence. Windows has many measurements points that allow for the capture of performance metrics. The Performance Monitor can be run as needed to capture the performance information in log files. These log files can be analyzed later or even on another machine. So if you want to capture system-wide information throughout the execution of an application, this is the tool for Windows.

DB2 makes its performance information available to the Performance Monitor tool. From the tool you can collect information on the DB2 Database Manager, DB2 Databases and DB2 Applications.

Event Viewer
The Windows Event Viewer logs information on errors that have occurred in the system. Some DB2 errors may be logged under the Application area, so if something really strange is happening this might be a good place to look.

Cygwin
There are a lot of tools available for UNIX and Linux platforms that really aren't included in Windows; the solution is to install Cygwin on the Windows machine. This allows the use of many of the same commands you would use on a UNIX-like system. After installing Cygwin and DB2 on my Windows system, I set up my primary command prompt window to use the DB2 Command Window to start the Cygwin command prompt. This gives me a bash shell under Windows.

OpenSSH can be installed with Cygwin. Then the ssh server daemon can run on Windows as a service allowing you to remotely connect to the Windows system using a secure terminal session.

Why would you want Unix tools? Well, sed and awk allow you to process and parse command output. You can take an event monitor trace and reduce and sort the output to something much more usable. There is also a free X Windows implementation available.

Cygwin can be downloaded from http://www.cygwin.com for free. It includes a fairly sophisticated setup tool that allows you to select the desired tools and automatically tracks dependencies.

Unix and Linux Platforms

Looking at Processes and Memory
Most UNIX-like platforms provide the ps command to see which processes are running. The variations I use most are 'ps aux' and 'ps -ef'. You'll often need to pipe this output through grep to get it down to a useful list. DB2 processes mostly include 'db2' as part of the process name, so you can whittle it down to just the several hundred DB2 processes. Here is a sample section of output from the ps command:


UID        PID  PPID  C STIME TTY          TIME CMD
root      1720     1  0 Dec02 ?        00:00:00 /opt/IBM/db2/V8.1/bin/db2fmcd
root      4051     1  0 08:34 pts/1    00:00:00 db2wdog
db2inst1  4056  4051  0 08:35 pts/1    00:00:00 db2sysc
root      4057  4056  0 08:35 pts/1    00:00:00 db2ckpwd
root      4058  4056  0 08:35 pts/1    00:00:00 db2ckpwd
root      4059  4056  0 08:35 pts/1    00:00:00 db2ckpwd
root      4060  4056  0 08:35 pts/1    00:00:00 db2gds
db2inst1  4061  4056  0 08:35 pts/1    00:00:00 db2syslog
db2inst1  4062  4056  0 08:35 pts/1    00:00:00 db2ipccm
db2inst1  4063  4056  0 08:35 pts/1    00:00:00 db2tcpcm
db2inst1  4064  4056  0 08:35 pts/1    00:00:00 db2resync
db2inst1  4065  4060  0 08:35 pts/1    00:00:00 db2srvlst
jmaenpa   4156     1  2 08:36 pts/2    00:00:04 /home/db2inst1/sqllib/bin/db2bp 4089C1001 5
db2inst1  4157  4062  0 08:36 pts/1    00:00:00 db2agent (GDMS0401)
db2inst1  4160  4060  0 08:36 pts/1    00:00:00 db2loggr (GDMS0401)
db2inst1  4161  4060  0 08:36 pts/1    00:00:00 db2loggw (GDMS0401)
db2inst1  4162  4060  0 08:36 pts/1    00:00:00 db2dlock (GDMS0401)
db2inst1  4163  4060  0 08:36 pts/1    00:00:00 db2pfchr
db2inst1  4164  4060  0 08:36 pts/1    00:00:00 db2pfchr
db2inst1  4165  4060  0 08:36 pts/1    00:00:00 db2pfchr
db2inst1  4166  4060  0 08:36 pts/1    00:00:00 db2pclnr
db2inst1  4167  4060  0 08:36 pts/1    00:00:00 db2event (DB2DETAILDEADLOCK)
jmaenpa   4202  4089  1 08:37 pts/2    00:00:01 db2

As with windows, you can identify the individual components of DB2 that are using resources. If the DB2 prefetch engines (db2pfchr) or page cleaners (db2pclnr) are high resource users, then there may be a problem in the storage configuration of the database.

On Linux, there is the top command which gives you a quick overview of system resources (CPU and memory) and a list of the processes using the most CPU. The display will also automatically update itself. As you can see from the following, the top command gives you some useful information:


top - 08:37:51 up 11:15,  0 users,  load average: 0.61, 0.33, 0.20
Tasks: 108 total,   5 running, 103 sleeping,   0 stopped,   0 zombie
Cpu(s):  86.8% user,  13.2% system,   0.0% nice,   0.0% idle
Mem:    774608k total,   767504k used,     7104k free,    70372k buffers
Swap:  1028152k total,    24776k used,  1003376k free,   471492k cached
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  Command
 3979 jmaenpa   20   0 16768  13m  11m R 82.0  1.8   0:09.58 kdeinit
 4822 jmaenpa   11   0  250m 250m 250m R  7.9 33.1  42:16.16 vmware
 4156 jmaenpa   11   0  9492 9472 4576 S  4.9  1.2   0:00.58 db2bp
 4202 jmaenpa   11   0  7520 7512 3148 S  2.3  1.0   0:00.25 db2
 4082 jmaenpa   12   0  1032 1032  808 R  1.3  0.1   0:01.86 top
 2035 jmaenpa    8   0 10772 7620 7520 S  0.3  1.0   0:00.26 kdeinit
 2313 jmaenpa    9   0 18176  15m  12m R  0.3  2.0   0:12.83 kdeinit
 4157 db2inst1   9   0 34500  33m  32m S  0.3  4.4   0:00.16 db2sysc
    1 root       8   0   512  464  448 S  0.0  0.1   0:03.47 init
    2 root       9   0     0    0    0 S  0.0  0.0   0:00.42 keventd
    3 root      18  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd_CPU0

The vmstat command is an excellent command for watching system overall utilization. It is available on most UNIX-based systems. It shows CPU and memory, along with I/O and swap information. The command accepts an interval parameter that will give you a new detail line once every interval. On many systems, the I/O and swap information is inaccurate. On AIX, the free memory information is probably inaccurate, but there is a tool called nmon that may provide correct memory usage numbers. Here is an example of what you would see from vmstat:


procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 1  0  61260  86448  27388 436992    0    1    17    15  166   508  6  6 88  0
 4  0  61260  86412  27420 436996    0    0     0    10 1190  3211 89 11  0  0
 6  0  61260  86396  27436 436996    0    0     0     6 1188  3157 88 12  0  0
 4  0  61260  86328  27472 436996    0    0     0    14 1343  3323 92  8  0  0
 4  0  61260  86312  27488 436996    0    0     0     6 1375  3270 89 11  0  0

DB2 also includes tools to analyze its memory utilization. These will be covered shortly.

Looking at Disk Activity
The iostat command is the tool of choice for monitoring I/O information on UNIX platforms. This command provides information about I/O activity to devices, so it is possible to see if a particular device is a hot spot for DB2 activity. Like vmstat, the iostat command accepts an interval parameter and produces detail lines for each device at each interval. Finding out which DB2 objects are on that device is an awkward process and varies depending on the system where DB2 is running. We've found it useful to build scripts that can determine the DB2 objects on a device before we need it.

In tuning disk activity for DB2, the best results are often achieved by distributing tables, indexes, and logs across multiple disk devices. In databases with high-volume updates, isolating the log datasets from the table datasets can reduce contention at the device level and improve throughput.

Figure 2

DB2 Tools

DB2 Applications
Seeing which applications are running against a DB2 database can be critical to understanding what is happening on a system. The DB2 list applications command displays the applications using the DB2 instance and their database connections. Adding the show detail option displays additional information, including the process id (pid) and the status of the application. The status will let you quickly determine if the application is in DB2 wait or executing the application program.

Memory Monitoring
The db2mtrk command is the DB2 tool used to display information on the memory usage of agents and memory pools owned by a DB2 instance. This command is available on both Windows and UNIX-like platforms. Scripts that use this to collect information may provide a historical view of memory activity. UNIX-style scripts could even run on Windows if you use Cygwin. Here is a sample of the output from the db2mtrk command:


$ db2mtrk -i -p -w -d -v
Tracking Memory on: 2002/12/03 at 10:03:26

Memory for instance

   Database Monitor Heap has watermark of 180224 bytes
   Other Memory has watermark of 5226496 bytes
   Total: 5406720 bytes

Memory for database: GDMS0401

   Backup/Restore/Util Heap has watermark of 16384 bytes
   Package Cache has watermark of 327680 bytes
   Catalog Cache Heap has watermark of 81920 bytes
   Buffer Pool Heap has watermark of 4341760 bytes
   Buffer Pool Heap has watermark of 671744 bytes
   Buffer Pool Heap has watermark of 409600 bytes
   Buffer Pool Heap has watermark of 278528 bytes
   Buffer Pool Heap has watermark of 212992 bytes
   Lock Manager Heap has watermark of 458752 bytes
   Database Heap has watermark of 1540096 bytes
   Other Memory has watermark of 0 bytes
   Total: 8339456 bytes

Memory for agent 4157

   Application Heap has watermark of 212992 bytes
   Application Control Heap has watermark of 16384 bytes
   Total: 229376 bytes

For those that prefer a GUI interface, the DB2 Memory Visualizer provides a way to monitor DB2 memory usage over time.

Snapshots
The DB2 Snapshot facility provides point in time information about the status of the database or application agents. This tool is frequently used from the command line to collect information on current activity, but it can be very difficult to analyze this way. It is best to set up a script to collect the output then process the output later. If you are using Windows as your database platform, you may want to consider using the Windows Performance Monitor tool instead.

Snapshot of Dynamic SQL Statements
One of the more recent and exciting additions to the DB2 Snapshot facility is the ability to get a snapshot of all of the statements in the dynamic statement cache along with statistical information about each statement. This new ability is perhaps the most useful way to tune applications that use dynamic SQL statements. Here is a portion of the output from the get snapshot for dynamic sql on database command:


 Number of executions               = 1
 Number of compilations             = 1
 Worst preparation time (ms)        = 10
 Best preparation time (ms)         = 10
 Internal rows deleted              = 0
 Internal rows inserted             = 0
 Rows read                          = 3
 Internal rows updated              = 0
 Rows written                       = 0
 Statement sorts                    = 0
 Total execution time (sec.ms)      = 0.011386
 Total user cpu time (sec.ms)       = 0.000000
 Total system cpu time (sec.ms)     = 0.000000
 Statement text                     = UPDATE ONTRACK SET TWCS_580 = '0´0´0'
                                      WHERE ONTRACKID = '1007371_CHELSEAEVANS_1'

This example shows the statement executed by the application along with performance information. From this output, we can see that statement preparation time was well over 90% of the time required to execute the statement. The embedded literals in the statement prevent DB2 from using the dynamic statement cache for this statement as the literals change with every execution. The use of parameter markers in the application could, therefore, reduce the application execution time by more than 90%.

Once again, using scripts to run, capture and condense the information provided by this command could prove very useful for managing and tuning your DB2 applications.

Event Monitors
The DB2 Event Monitors allow you to see everything that happened rather than just point-in-time snapshots. This is similar to the DB2 for z/OS accounting trace. We like to collect this information then feed it into awk scripts. This allows us to use available tools to quickly find out which embedded SQL statements are used most and how much CPU they use. Unfortunately, too much use of event monitors may reduce the capacity of a database server system. To avoid excess overhead, we set up scripts that activate the event monitors and collect the data for a given interval before deactivating the event monitors.

Statistics
DB2 chooses the access path it will use to retrieve data based on statistical information that is stored in the DB2 catalog tables. This information is absolutely critical. The biggest problem we've seen with poorly performing applications has been the lack of statistics. Without statistics, DB2 cannot make accurate access path choices. Statistics are collected on each table and its indexes using the runstats command. This should be done for every table in a database after data has been loaded.

Access Paths
DB2 has several tools to let help you understand the access path chosen for the SQL statements in an application. The most useful of these is the Visual Explain tool that provides a GUI picture of the access path. Begin at the bottom to see where DB2 accesses data and index objects and move up to see where the result is returned to the application. Several levels of information are available on each step shown in the tool.

Figure 3

Conclusion
There are many methods available to identify bottlenecks which may be impacting DB2. Most of the useful tools are dependent on the system where DB2 runs and require that you monitor system resources, not just DB2 activity. Collect and process the output from scripts containing system and DB2 commands; this will make your job easier.

 



Last Updated