Reprinted with Permission by Quest Software April  2004

Basic Performance Tuning
Roger Sanders, NetApp

Note:  This article was originally printed in DB2 Magazine

Your platform-specific guide to getting the very best performance from DB2 UDB.

DB2 Universal Database (UDB) version 8.1 for Linux, Unix, and Windows can exist in environments ranging from simple, stand-alone systems to complex combinations of servers and clients running on a variety of platforms. Regardless of the environment, users tend to care most about one issue: the performance of the database applications. Just what is performance, and how can you improve it?

In its simplest terms, performance is the way a computer system behaves while executing a given task. Performance is typically measured in terms of system response time, throughput, and availability. Each of these metrics can be affected by several factors, including hardware, system (and database) configuration, the type and number of users working concurrently, and the workload performed by each user's application.

If a system is performing poorly, you usually have several options to choose from to tune it. Because of the variety of options, you should always approach tuning in an organized, concise manner with a specific goal in mind. And that goal should be realistic, quantitative, and measurable; otherwise, performance tuning becomes a hit-or-miss exercise.

So, where should a DBA start? From a database perspective, performance problems typically arise from deficiencies in one or more of the following:

Focus your initial tuning efforts on each of these areas, gradually working through each one until you get the desired performance. In this column, I'll describe how to examine the system configuration for Linux, Unix, and Windows platforms. Future columns will cover the remaining areas. Before we get into specifics, though, I'll present some tuning guidelines relevant to all platforms.

GENERAL TUNING RULES

Your tuning project will be much easier and more likely to succeed if you review these guidelines before you begin.

  1. Check for known hardware and software problems. Some performance problems can be corrected simply applying service packs to your software or upgrading your hardware. Why spend the time and effort monitoring and tuning other parts of your system when a simple service pack may resolve the problem? That said, make sure you understand the problem before you decide to upgrade hardware. Adding another network interface card before you discover that the system actually needs more memory is a costly mistake that does nothing to improve performance.

  2. Consider the whole system. Usually, you can't tune one aspect of a system without affecting at least one other part of that system. For example, if you reserve a large chunk of memory for the DB2 Database Manager's background processes, you may not have enough free memory available to execute a stored procedure your application depends on. So, before you make changes, consider how those changes will affect the system as a whole.

  3. Measure and reconfigure by levels. Never tune more than one level of your system at a time. Even if you're sure all the changes you plan to make will be beneficial, you'll have no way to evaluate how much each change contributed to the resulting performance improvements. If you're wrong, and performance goes down instead of up, you'll have no way of knowing which change had the negative impact. In a database server environment, these are the levels to consider individually: hardware, operating system, communications software, database, SQL statements, and applications.

  4. Change one thing at a time. For the same reasons that you should tune only one system level at a time, you should change only one element (registry variable, instance configuration variable, database configuration variable, and so on) at a time as you tune each system level.

  5. Put tracking and fallback procedures in place before you start. Performance tuning isn't an exact science. Some changes you make will hurt rather than help performance. If such a situation occurs, you'll spend less time trying to get the system back to an earlier state if you have a way to back out every change made. I like to make changes using shell scripts or batch files. That way, I can store a command (that will return, say, a configuration value to its original state) as a comment line directly above the command that assigns the configuration parameter a new value. Then, if I need to back out changes, I can do so by uncommenting the original command, commenting out the command that made the change, and rerunning the script or batch file. If you're forced to back out of several changes, be prepared to reapply every change made if necessary.

  6. Don't tune for the sake of tuning. Tuning should be performed to resolve an identified problem. If you tune resources that aren't directly related to the primary cause of the problem you're trying to solve, you'll see little or no effect until the primary problem itself has been resolved. In some cases, such actions can actually make subsequent tuning work much more difficult.

  7. Remember the law of diminishing returns. Keep in mind that the greatest performance tuning gains usually come from your initial efforts. Subsequent tuning usually results in progressively smaller gains and requires progressively greater amounts of effort.

TUNING DB2 UDB SYSTEM CONFIGURATION

DB2 UDB uses a set of registry variables to configure the system where DB2 UDB has been installed. Some of these registry variables significantly affect performance; others have little or no impact whatsoever. I'll explain which DB2 UDB registry variables have the greatest impact on performance for each platform.

Remember, changes to these variables affect the entire system, so be especially careful when changing registry variables.

ALL PLATFORMS

The following registry variable recommendations apply to Linux, Unix, and Windows platforms.

DB2_APM_PERFORMANCE. OFF is the default value for this registry variable, which specifies whether or not performance-related changes in the access plan manager (APM) that will affect the behavior of the SQL cache (package cache) are to be made. It also specifies whether the global SQL cache will operate without the use of package locks, which are internal system locks that prevent cached package entries from being inadvertently removed.

This registry variable should only be set to ON in a nonproduction environment. When ON, you may see Out of package cache errors, and memory usage may increase. And PRECOMPILE, BIND, and REBIND operations can't be performed, nor can operations that invalidate packages or make them inoperable.

DB2_AVOID_PREFETCH. This registry variable specifies whether or not prefetching should be performed during crash recovery. The default is OFF; if set to ON, prefetching isn't performed.

DB2BPVARS. Supported parameters for DB2BPVARS, which specifies the location of a file that contains parameter values to be used when tuning buffer pools, include:

For each of the _SCATTER parameters, the default value is 0 (or OFF) and the possible values are: 0 (OFF) and 1 (ON). For NUMPREFETCHQUEUES, the default value is 1; the range of values is 1 to NUM_IOSERVERS. And for PREFETCHQUEUESIZE, the default value is whichever is the largest: 100 or 2 * NUM_IOSERVERS. The range is 1 to 32,767.

Each _SCATTER parameter is used to turn scatter read on or off for the respective type of tablespace containers used (or to turn scatter read off for all containers). The remaining parameters can be used to improve buffer pool data prefetching.

Note: The A _SCATTER parameter can only be set to ON if DB2NTNOCACHE is set to ON and the Windows operating system is being used.

DB2CHKPTR. This registry variable specifies whether or not pointer checking for input will be performed; the default value is OFF.

DB2_ENABLE_BUFPD. The default value is OFF for this registry variable, which specifies whether or not DB2 is to use intermediate buffering to improve query performance.

DB2_EXTENDED_OPTIMIZATION. This registry variable specifies whether or not the query optimizer will use optimization extensions to improve query performance; the default is OFF.

DB2MAXFSCRSEARCH. This variable can be set to -1, or a value from 1 to 33554 in order to specify the number of free-space control records to search when adding a record to a table. It allows you to balance insert speed with space reuse (small values optimize for insert speed, large values optimize for space reuse). If the registry variable is set to -1, the DB2 Database Manager will search all free-space control records. The default value is 5.

DB2MEMMAXFREE. This variable specifies the amount of free memory that each DB2 agent will retain; values range from 0 to 2.0e+32 bytes. The default is 8,388,608 bytes.

DB2_OVERRIDE_BPF. This registry variable, which can be set to a positive number of 4k pages, specifies the size of the buffer pool (in pages) that will be created at database activation or the first time a connection is established. DB2_OVERRIDE_BPF is useful when failures resulting from memory constraints occur during database activation or the first time a connection is established. Such a memory constraint could arise either because of a real memory shortage (which is rare) or because of an attempt by the DB2 Database Manager to allocate large, inaccurately configured buffer pools. The default value is null.

DB2PRIORITIES. The values for this registry variable are platform-dependent. DB2PRIORITIES controls the priorities of DB2 processes and threads.

DB2_SORT_AFTER_TQ. DB2_SORT_AFTER_TQ specifies how the DB2 optimizer works with directed table queues in a partitioned database environment when the receiving end requires the data to be sorted and the number of receiving nodes is equal to the number of sending nodes. When set to NO (which is the default), the DB2 optimizer tends to sort at the sending end and merge the rows at the receiving end. When set to YES, the optimizer transmits the unsorted rows and sorts them at the receiving end after receiving all the rows.

DB2_STPROC_LOOKUP_FIRST. This registry variable specifies whether or not the DB2 UDB server will perform a catalog lookup for all DARIs and stored procedures before looking in the function subdirectory of the sqllib subdirectory and in the unfenced subdirectory of the function subdirectory of the sqllib subdirectory. The default is OFF.

DB2_HASH_JOIN. A YES or NO value specifies whether or not a hash join can be used when compiling a data access plan. The default is NO.

DB2_PARALLEL_IO. Possible values include * and Null (the default) for this registry variable, which specifies whether or not DB2 can use parallel I/O when reading or writing data to and from tablespace containers (even in situations where the tablespace contains only one container).

DB2_STRIPED_CONTAINERS. This variable is set to ON or Null (the default) to specify whether or not the tablespace container ID tag will take up a partial or full RAID disk stripe. When using RAID devices, the tablespace should be created with an extent size that is equal to, or a multiple of, the RAID stripe size. However, because of the one-page container tag, the extents will not line up with the RAID stripes. It may be necessary to access more physical disks than would be optimal during an I/O request unless this registry variable is set to ON.

UNIX PLATFORMS

The following variables apply to Unix platforms (either AIX, HP-UX, or both as noted).

DB2MEMDISCLAIM. This registry variable specifies whether or not the AIX operating system should stop paging memory so that it no longer occupies any real storage. Setting DB2MEMDISCLAIM to YES (the default) tells DB2 UDB to disclaim some or all memory once freed, depending on the value specified with the DB2MEMMAXFREE registry variable. If DB2MEMMAXFREE is null, then all the memory is disclaimed once freed. If DB2MEMMAXFREE is given a value, then only some of the memory is disclaimed once freed (up to the value given in DB2MEMMAXFREE). Disclaiming ensures that the memory is made readily available for other processes as soon as it's freed.

DB2_MMAP_READ. Used in conjunction with DB2_MMAP_WRITE, the default value of YES allows DB2 for AIX to use mmap as an alternate method of I/O. In most environments, mmap should be used to avoid operating system locks when multiple processes are writing to different sections of the same file.

DB2_MMAP_WRITE. Used in conjunction with DB2_MMAP_READ, the default value of YES allows DB2 for AIX to use mmap as an alternate method of I/O.

DB2_PINNED_BP. This AIX and HP-UX variable specifies whether or not the database global memory used (including buffer pool memory) will be kept in system main memory for more consistent database performance.

WINDOWS PLATFORM

The following registry variables apply to the Windows NT operating environment.

DB2NTMEMSIZE. Windows NT requires that all shared memory segments be reserved at dynamic link library (DLL) initialization time in order to guarantee matching addresses across processes. DB2NTMEMSIZE lets you override the DB2 defaults on Windows NT if necessary; however, in most situations, the default values should be sufficient. Default values are as follows:

DB2NTNOCACHE. Specifies whether or not file system caching is performed. This registry variable applies to all data except LONG or LOB data. Eliminating system caching allows more memory to be available to the database so that the buffer pool or sortheap can be increased. The default is OFF.

DB2NTPRICLASS. Used in conjunction with individual thread priorities (set using DB2PRIORITIES), this registry variable sets the priority class for the DB2 instance program (DB2SYSCS.EXE) and determines the absolute priority of DB2 threads relative to other threads in the system. Three priority classes are available:

Values can be R, H, or any other value; the default is NULL.

DB2NTWORKSET. This registry variable specifies the minimum and maximum working set size available to DB2. By default, when Windows NT isn't in a paging situation, a process's working set can grow as large as needed. However, when paging occurs, the maximum working set is approximately 1MB. This registry variable lets you override this default behavior. Possible values include PositiveNumber, PositiveNumber0. (The default is 1,1.)

CHANGING DB2 REGISTRY VARIABLES

How do you determine whether or not these registry variables have already been set and what they've been set to? DB2 UDB v.8.1 gives you two ways to view and change registry variables: by using the DB2 registry management tool (accessible from the Configuration Assistant) or by executing the system command db2set. Listing 1 shows the syntax for that command. Note: Parameters shown in angle brackets (< >) are optional; parameters or options shown in straight brackets ( ) are required.

Listing 1: The syntax for viewing and changing registry variables.

Table 1 describes the other available options shown with this command.

Table 1: The db2set command options.

If the db2set command is executed without options, a list containing every registry variable that has been set, along with its current value, will be returned.

STAY TUNED

DB2 UDB performance tuning can be a lengthy process because so many factors can affect performance. This article covered general performance tuning and ways to improve a DB2 UDB system configuration. In the next article, I'll explain ways to tune instance and database configurations for optimum performance.

Roger E. Sanders is a database performance engineer with Network Appliance Inc. and is author of the book All-In-One DB2 Administration Exam Guide (McGraw-Hill/Osborne Media, 2002). You can reach him at roger.sanders@netapp.com.