Practical tools and techniques for tuning the performance of DB2 applications running on Linux, UNIX, and Windows platforms.
by John Maenpaa
|About the Author|
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.
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.
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.
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.
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.
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.
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.
The Users tab shows which userids are logged into the system.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.