Level: Intermediate Fraser McArthur (fgmcarth@ca.ibm.com), DB2 Enablement Consultant, IBM Canada Ltd.
01 Apr 2004 Here's the help you need for getting optimal performance out of your DB2® UDB database and its applications. The author takes a practical approach, discussing techniqes you can implement at various stages of development or to a production system, including techniques for database design and layout, database configuration, SQL statement design, maintenance, and monitoring.
Introduction
Performance is a vital key to the success of your on demand applications. When those applications are using IBM® DB2 Universal Database™ as a data store, it's essential that you begin with a fundamental knowledge of how to achieve the best possible performance with DB2 UDB. In this article I'll give in-depth recommendations for tuning a DB2 UDB V8 system.
We'll talk about performance issues from the beginning to the end of the process. You can follow the flow from creating a new database to running with your application. You will see how to use the DB2 auto-configuration utilities to initially configure your database manager and database environment. Then I'll discuss best practices for creating buffer pools, table spaces, tables, and indexes. There are some important configuration parameters you may want to adjust from their initial settings to better support your application, so we'll take a look at those configuration parameters as well.
We'll cover tuning based on monitor output in detail. I'll show you how to use snapshot monitoring to help tune your SQL, buffer pools, and various database manager and database configuration parameters. Then, we'll take a closer look at the SQL your application issues to DB2. We'll cover statement event monitoring, showing you how to capture the SQL your application is issuing. Using Explain, you can generate the access plan that the SQL is taking and look for opportunities to better optimize. We'll examine the Design Advisor, a tool which can recommend new indexes or evaluate existing indexes, based on SQL workloads that you supply to it. Finally, I'll discuss some DB2 SQL options.
In addition, on-going maintenance is very important to maintain optimal performance. So we'll cover the important utilities to help you do this. For those who are using the DB2 ESE Database Partitioning Feature (DPF), I've included a section covering the issues you should be concerned with to keep the database performing well. Sometimes there is an external bottleneck (from DB2) which can prevent you from achieving your performance goals. Common bottlenecks and the utilities you can use to monitor them are listed. Finally, the paper ends with a listing of valuable IBM resources to help you find valuable DB2 information.
The article is intended for those with an intermediate skill in DB2 database administration.
Before you start
Before you begin the performance tuning process, make sure you have applied the latest DB2 fix pack. There are often performance enhancements in fix packs. DB2 FixPak 4 was used as a basis for this article. If you are using a pre-FP4 release, not all options discussed may be available in your environment.
When you are tuning, it is ideal to have a reproducible scenario of database use (that is, the workload your application runs against DB2) that you can use for tailoring your tuning efforts. For example, if the workload has variations of 10% is elapsed time from run to run, it is very difficult to know what effect tuning has really had. Additionally, in cases where the workload varies from run to run, it is hard to measure the changes to the database manager and database configuration parameters.
Always keep track of all changes. This can be useful for the development of tuning scripts or recommendations, as a history for other DBAs, and for backing out of any bad changes.
At the end of most sections, there are pointers to relevant sections of the DB2 v8 HTML Documentation. The online documentation can be found at
http://publib.boulder.ibm.com/infocenter/db2help/index.jsp.
The "Top 10" performance boosters
Here are the top 10 things you can do to get the most performance out of your database. Usually, you will find that about 90% of maximum performance is achieved using about 10% of possible configuration changes. I'll discuss each item in detail in the appropriate section below (identified in parenthesis):
- Ensure that you have enough disks (6-10 per CPU is a good start). Each table space's
containers should span all available disks. Some table spaces, such as SYSCATSPACE and
those with a small number of tables do not need to be spread across all disks, while
those with large user or temporary tables should (Table Spaces).
- Buffer pools should make use of about 75% (OLTP) or 50% (OLAP) of available memory
(Buffer Pools).
- Runstats should be performed on all tables, including the system catalog tables (Runstats).
- Use the Design Advisor to recommend and review indexes for SQL workloads (Design
Advisor).
- Use the Configuration Advisor to configure the database manager and database for your
application environment (Configuration Advisor).
- Logging should occur on a separate high-speed disk, identified by the NEWLOGPATH
database configuration parameter (Experimenting).
- Concurrency can be increased by committing often (SQL statement tuning).
- SORTHEAP should be increased to avoid sort overflows (DBM and DB configuration).
- Table space type should be SMS for the system catalog table space, and temporary table
spaces and DMS raw (device) or file for the rest. Run db2empfa to enable multi-page
file allocation for the SMS table spaces; this will allow SMS table spaces to grow an
extent at a time (instead of a page), which can speed up heavy insert operations and
sorts which spill to disk (Table spaces).
- Use parameter markers for repeated statements (SQL statement tuning).
 |
Creating the database
Creating a database will by default create 3 System Managed Storage (SMS) table spaces (SYSCATSPACE, TEMPSPACE1, and USERSPACE) and a 4 MB buffer pool (IBMDEFAULTBP), which all use a 4 KB page size. It is usually a good idea to drop TEMPSPACE1 and USERSPACE and recreate them according to the recommendations below. In almost all cases, SYSCATSPACE does not need to be optimized further, but you may notice a slight performance improvement by spreading its containers over a few disks (discussed later).
When creating the database, you can take advantage of the autoconfigure options to initially automatically configure the database for the environment. This is handy if your application programmatically creates a DB2 database, as you can provide these options from your application to DB2. The other option you have to automatically configure your database is the more powerful Configuration Advisor GUI, which not only configures your database but your instance as well. However, to use the Configuration Advisor, the database must exist. We'll discuss the Configuration Advisor in a following section.
In Listing 1, we use the autoconfigure option of the CREATE DATABASE command to create a database in Windows that has its SYSCATSPACE spanned across two available disks.
Listing 1. Creating a database with autoconfigure options
create database prod1 catalog tablespace
managed by system using ('c:\proddb\cattbs\01','d:\proddb\cattbs\02')
extentsize 16 prefetchsize 32
autoconfigure using mem_percent 50 workload_type simple num_stmts 10
tpm 20 admin_priority performance num_local_apps 2 num_remote_apps
200 isolation CS bp_resizeable yes apply db and dbm |
Table 1 shows the valid autoconfigure input keywords and values:
Table 1. Autoconfigure options
|
Keyword
|
Valid Value
|
Default Value
|
Description
| | mem_percent | 1-100 | 25 | Percentage of physical memory to dedicate to the database. If other applications are running on this server (not including the OS), set this to less than 100. | | workload_type | simple, mixed, complex | mixed | Simple workloads tend to be I/O intensive and mostly transactions (OLTP), whereas complex workloads tend to be CPU intensive and mostly queries (OLAP/DSS). | | num_stmts | 1-1000000 | 25 | Number of statements per unit of work. | | tpm | 1-200000 | 60 | Transactions per minute. | | admin_priority | performance, recovery, both | both | Optimize for better performance (more transactions per minute) or better recovery time. | | num_local_apps | 0-5000 | 0 | Number of connected local applications. | | num_remote_apps | 0-5000 | 100 | Number of connected remote applications. | | isolation | RR, RS, CS, UR | RR | Isolation level of applications connecting to this database (Repeatable Read, Read Stability, Cursor Stability, Uncommitted Read). | | bp_resizeable | yes, no | yes | Whether or not the buffer pools can be resized online. |
The Configuration Advisor
If you have already used autoconfigure while creating the database, this step is not as important. The Configuration Advisor is a GUI tool which allows you to automatically configure a database and instance based on the answers you provide to a series of questions. You will often notice a pretty significant increase in performance by running this tool. It can be launched from the Control Center by right-clicking on the database and selecting "Configuration Advisor…". Once you have answered all the questions, you can generate and optionally apply the results. Figure 1 shows a screenshot of the results page:
Figure 1. Configuration Advisor Results screen
Creating buffer pools
Properly defining buffer pools is one of the major keys to having a well-performing system. With 32-bit operating systems, it is important to be aware of the limit on shared memory, which restricts a database’s buffers pools (that is, database global memory) from exceeding the following limits (64-bit systems do not have such a limit):
- AIX - 1.75 GB
- Linux - 1.75 GB
- Sun - 3.35 GB
- HP-UX - approximately 800 MB
- Windows - 2-3 GB (use ‘/3GB’ switch in boot.ini on NT/2000)
Use the following formula for calculating your approximate database global memory usage:
Listing 2. Calculating database global memory usage (shared memory)
buffer pools + dbheap + util_heap_sz + pkgcachesz + aslheapsz + locklist + approx 10% overhead
|
If INTRA_PARALLEL is enabled, then add the value of sheapthres_shr to the total.
Determining how many buffer pools
You will need at least one buffer pool for each page size used by a table space in your database. Usually, the default IBMDEFAULTBP buffer pool is left for the system catalogs. New buffer pools are created to handle different page sizes and behaviors of the table spaces.
For starters, you should begin with one buffer pool for each page size being used, especially for OLAP/DSS type workloads. DB2 is very good at self-tuning its buffer pools and keeping the most frequently accessed rows in memory, so one buffer pool may be all you need. (This option also avoids the complexity of managing multiple buffer pools.)
If you have the time and require improvements, you may want to use multiple buffer pools. The idea is to keep the most frequently accessed rows in a buffer pool. Sharing a buffer pool with tables that are accessed randomly or infrequently can cause "polluting" of your buffer pool, by consuming space and possibly pushing a frequently accessed row to disk, for a row which may never be accessed again. Keeping indexes in their own buffer pool can also significantly improve performance when indexes are heavily used (for example, index scans).
This ties in closely to our discussion of table spaces, as you will want to assign buffer pools based on table behavior within the table space. With the multiple buffer pool approach, a good starting point would be to use 4 buffer pools as follows:
- A medium-sized buffer pool for temporary table spaces
- A large-sized buffer pool for index table space
- A large-sized buffer pool for table spaces which contain frequently accessed tables
- A small-sized buffer pool for table spaces which contain infrequently accessed,
randomly accessed, or sequentially accessed tables
DMS Table spaces which contain only LOB data can be assigned to any buffer pool, as LOBs do not use buffer pool space.
Determining memory allocation for buffer pools
Never allocate more memory than you have available or you will incur costly OS memory paging. Generally speaking, it may be pretty hard to know how much memory to initially allocate to each buffer pool without monitoring.
For OLTP type workloads, 75% of available memory being allocated to the buffer pool(s) is a very good starting point.
For OLAP/DSS, the rule of thumb is to give 50% of your available memory to a single buffer pool (given that only one page size is being used) and the other 50% to the SORTHEAP.
Using block-based buffer pools
OLAP queries that rely heavily on prefetching may benefit from a block-based buffer pool. By default, all buffer pools are page-based, which means that prefetches will place contiguous pages on disk into non-contiguous memory. If a block-based buffer pool is used, DB2 will use block I/Os to read multiple pages into the buffer pool in a single I/O which significantly improves the performance of sequential prefectching.
A block-based buffer pool consists of both the standard page area and a block area. The NUMBLOCKPAGES parameter of the CREATE and ALTER BUFFERPOOL SQL statement is used to define the size of the block memory, while the BLOCKSIZE parameter specifies the size of the blocks, and hence the number of pages to read from a disk in a block I/O.
Table spaces which share the same extent size should be the only users of a specific block-based buffer pool. Set the BLOCKSIZE equal to the table space’s EXTENT SIZE that is using the buffer pool.
Deciding how much memory to dedicate to blocking within the buffer pool is more complicated. If there is a lot of sequential prefetching occurring, you will likely want more block-based buffer pool available. NUMBLOCKPAGES should be a multiple of BLOCKSIZE and cannot be more than 98% of the number of pages for the buffer pool. Set small at first (not more than 15% or so of the total buffer pool size). It can be adjusted later, based on snapshot monitoring.
DB2 v8 Documentation:
- Concepts ==> Administration ==> Database objects ==> Buffer Pool Management
- Reference ==> SQL ==> SQL Statements ==> CREATE BUFFERPOOL
- Reference ==> SQL ==> SQL Statements ==> ALTER BUFFERPOOL
- Concepts ==> Administration ==> Performance tuning ==> Operational performance ==> Memory-use organization
Creating table spaces
Since table spaces are assigned a buffer pool, the previous section on buffer pools is very relevant to performance issues involving table spaces. Using the DB2 Control Center is the easiest and recommended way to create and configure table spaces (right-click on your database’s Table Spaces folder and select Create…).
Determining the type of table space to create (DMS or SMS)
System Managed Storage (SMS) should be used for system temporary table spaces and the system catalog table space, as it allows for the table space to grow and shrink dynamically. DMS may be more efficient in cases where there are large temporary tables that are flushed to disk (either by not having enough sort space or by explicitly creating temporary tables). When using SMS, you should run the utility ‘db2empfa’ which will enable multi-page file allocation to grow the table space one extent at a time, as opposed to one page at a time.
Database Managed Storage (DMS) should be used for all other table spaces. DMS allows for a single table to span multiple table spaces (index, user data, and LOB) which decreases contention between index, user, and LOB data during prefetch and update operations, thereby improving data access times. Using DMS raw may even squeeze an additional 5-10% performance increase.
Determining page size
In order for you to create a table, there must be a table space with a page size large enough to contain a row. You have the option of using 4, 8, 16 or 32 KB page sizes. Sometimes you simply have to use a larger page size to get around some database manager limits. For example, the maximum size of the table space is proportional to the page size of the table space. A table space’s size (per partition) is limited to 64 GB using a 4K page size and to 512 GB using a 32K page size.
For OLTP applications that perform random update operations, a smaller page size is usually preferable as it consumes less space in the buffer pool.
For OLAP applications that access large numbers of consecutive rows at a time, a larger page size is usually better because it reduces the number of I/O requests that are required to read a specific number of rows. Larger page sizes also allow you to reduce the number of levels in the index by keeping more row pointers on a single page. There is however an exception to this. If your row size is smaller than the page size / 255, there will be wasted space on each page due to there being a maximum of 255 rows per page (does not apply to index data pages). In this situation, a smaller page size may be more appropriate.
For example, if you were using a 32K page size to store rows with an average length of 100 bytes, only 100 * 255 = 25500 bytes (24.9 KBs) could be stored on a 32 KB page. This means about 7 KBs per 32 KB page is being wasted. This could be a lot of space in a large database.
Determining how many table spaces
Like buffer pools, you should begin with one table space for each page size being used. For each page size being used, a system temporary table space with a matching page size will need to exist (to support sorts and reorgs). You would then assign all table spaces which share a matching page size to a buffer pool with the same page size.
If you are still concerned about performance and have time to invest, you would use DMS table spaces and group tables based on use. Additionally, follow the recommendations above for using multiple buffer pools.
For each page size, create a:
- System temporary table space
- Regular table space for Indexes
- Regular table space for frequently accessed tables
- Regular table space for infrequently accessed, randomly accessed, and sequentially
accessed tables
- Large table space for LOB data
Container layout
A good starting point is about 6-10 disks per CPU dedicated to table spaces. Each table space should span all disks, that is, have one container (and no more) on each available disk.
You should create the same number of Logical Volumes (UNIX) on each disk as you have table spaces. This way, each table space will have its own logical volume on each disk for container placement. If you are not using DMS raw, you will need to create a file system within each logical volume.
Disk array and storage subsystems
For large disk systems, you should use a single container. Additionally, you will need to set the DB2 Profile Registry variable DB2_PARALLEL_IO for that table space. This is discussed in the Profile registry section.
Extent size
The Extent Size specifies the number of PAGESIZE pages that will be written to a container before skipping to the next container and is defined at table space creation time (and cannot be easily modified after). Smaller tables are handled more efficiently with smaller extents.
Rule of thumb is based on the average size of a table in the table space:
- Less than 25 MB, use an Extent Size of 8
- Between 25 and 250 MB, use an Extent Size of 16
- Between 250 MB and 2 GB, use an Extent Size of 32
- Greater than 2 GB, use an Extent Size of 64
Use larger values for OLAP databases and tables that are mostly scanned (query only) or have a very high growth rate.
If the table space resides on a disk array, set the extent size to the stripe size (that is, data written to one disk of the array).
Prefetch size
Prefetch size can be changed easily using ALTER TABLESPACE. The optimal setting seems to be:
Prefetch Size = (# Containers of the table space on different physical disks) * Extent Size
If the table space resides on a disk array, set it as:
PREFETCH SIZE = EXTENT SIZE * (# of non-parity disks in array).
DB2 v8 Documentation:
- Concepts ==> Administration ==> Database design ==> Physical ==> Table Space Design
- Reference ==> SQL ==> SQL Statements ==> CREATE TABLESPACE
- Reference ==> SQL ==> SQL Statements ==> ALTER TABLESPACE
Creating tables
Multidimensional clustering (MDC)
MDC provides for flexible, continuous, and automatic clustering of data along multiple dimensions. It improves query performance and reduces the need for REORG and index maintenance during insert, update, and delete.
Multidimensional clustering will physically cluster the table’s data along multiple dimensions simultaneously, which is similar to having multiple, independent clustered indexes on the table. MDCs are typically used to help speed up the performance of complex queries on large tables. There is no need to use REORG to recluster the index since MDCs maintain clustering in each dimension automatically and dynamically.
The best candidates for an MDC are those queries which have range, equality, and join predicates that access many rows. Never use a unique column as a dimension since that can cause a table to be unnecessarily large. Avoid using too many dimensions if there are not a significant number of rows with each combination of dimension values (i.e., cell). For best performance, you will want at least enough rows to fill a full block of each cell, which is equal to the extent size of the table space that the table resides in.
DB2 v8 Documentation:
- Concepts ==> Administration ==> Database design ==> Logical ==> Multidimensional clustering (MDC)
- Concepts ==> Administration ==> Database objects ==> Tables ==> Multidimensional clustering (MDC) tables
Materialized query tables (MQTs)
MQTs can be used to improve queries which use the GROUP BY, GROUPING, RANK, or ROLLUP OLAP functions. Their use is transparent to the user and DB2 chooses when to use them for optimization purposes. The MQT is used by DB2 to internally maintain summarized results of the required grouping, which allows a user to access a maintained grouping instead of reading through what could be multiple GBs of data to find the answer. These can also be replicated across partitions to help performance of collocated joins by avoiding the broadcasting of this information between partitions.
CREATE TABLE options
Avoid using the VARCHAR data type for columns of 30 bytes or less as it typically wastes space; instead use CHAR. Wasting space can even affect query times if the volume is significant.
When using an IDENTITY or SEQUENCE, use at least the default cache size of 20 (unless there is a big concern about gaps in the numbering). This way you are not calling on the DBM to generate a number every time one is needed and you are avoiding the logging which occurs with number generation.
VALUE COMPRESSION and COMPRESS SYSTEM DEFAULT can save disk space when a table uses many null and system default values. System default values are the default values used for the data types when no specific values are specified. This can also help improve query time when volume is significant. Compressed columns will incur a small overhead if a value is inserted or updated.
ALTER TABLE options
Use APPEND ON for tables which have heavy inserts to avoid the search for free space during the insert process and instead simply append the row to the end of the table. If you rely on the table being in a special order and cannot afford to perform REORGs, avoid using APPEND ON.
Set LOCKSIZE to TABLE for read-only or exclusive access tables. This avoids the time to lock the rows and reduces the amount of LOCKLIST needed.
Use PCTFREE to maintain free space to help with future INSERTs, LOADs and REORGs. The default is 10; try 20-35 for tables with clustered indexes and a heavy insert volume. If using with APPEND ON, set PCTFREE to 0.
Use VOLATILE to encourage an index scan. Volatile indicates that the cardinality of the table can vary significantly from quite large to empty. This helps to encourage the optimizer to use an index as opposed to a table scan, regardless of the statistics on the table. However, this only works in the cases where the index contains all the columns referenced or when the index is able to apply a predicate in the index scan.
Use NOT LOGGED INITIALLY to turn logging off for the duration of a transaction (that is, until COMMIT).
VALUE COMPRESSION and COMPRESS SYSTEM DEFAULT also work in the ALTER TABLE command.
DB2 v8 Documentation:
- Reference ==> SQL ==> SQL Statements ==> CREATE TABLE
- Reference ==> SQL ==> SQL Statements ==> ALTER TABLE
Creating indexes
Thanks to the Design Advisor, the burden of designing indexes has been eliminated. The Design Advisor is used to recommend and evaluate indexes for specific SQL workloads (that is, a set of SQL statements) and will be discussed shortly.
Nonetheless, the following are some index related issues that you should be aware of:
- When queries are completing in a reasonable time, avoid adding indexes as they can slow down update operations and consume extra space. It is sometimes possible to have one larger index that will cover several queries.
- Columns with high cardinality are good candidates for indexing.
- Avoid using more than five columns in an index due to management overhead.
- For multi-column indexes, place the column which is referenced most in queries first in the definition.
- Avoid adding an index which is similar to a preexisting index. It creates more work for the optimizer and will slow down update operations. Instead, alter the preexisting index to contain additional columns. For example, there is an existing index i1 on (c1,c2) of a table. You notice that your query using "where c2=?", so you create an additional index i2 on (c2). This similar index adds nothing, as it is redundant to i1 and is now additional overhead.
- If your table is read-only and contains a large number of rows, try to define an index which contains all columns referenced in the query using the INCLUDE clause of CREATE INDEX (INCLUDEd columns are not part of the index but are stored as part of the index page to avoid additional data FETCHES).
Clustered indexes
Clustered indexes can be created to order the rows in the table in the same physical order as the desired result set. They are created using the CLUSTER option of the CREATE INDEX statement. Do not create clustered indexes on volatile tables as the index will not be used. For best performance, create the index over small data types (like integer and char(10)), unique columns, and columns most often used in range searches.
Clustered indexes allow for a more linear access pattern to data pages and more effective prefetching, and help avoid sorts. This means longer inserts, but quicker selects. Consider increasing the free space on data and index pages when using clustered indexes to about 15-35 (instead of the default 10 for PCTFREE) for high volumes of inserts. For tables which are heavily inserted into, consider using a single dimension MDC table (perhaps using a generated column like idcolumn/1000 or INT(date)/100). This will cause the data to be block indexed (on the dimension) rather than on the row. The resulting index is smaller and log contention is significantly reduced during insert.
CREATE INDEX options
Use PCTFREE 0 if index is on a read-only table and 10 for others to help speed up inserts by having space available. Additionally, this should be higher for tables with clustered indexes to ensure that the clustered indexes do not become too fragmented. In such a case where there is a high volume of inserts, a value of 15 – 35 may be more appropriate.
Use ALLOW REVERSE SCANS to allow for an index to be scanned bi-directionally, which means quicker retrieval of ascending and descending result sets. This has no negative performance impact since the index structure does not change internally to support this feature.
INCLUDE can be used to include additional non-indexed columns in the index page to promote index-only access and avoid data page fetches.
UNIQUE columns can be used to efficiently enforce the uniqueness of a column or set of columns.
TYPE-2 INDEXES drastically reduce next-key locking, allow for index columns greater than 255 bytes by default, allow for both online REORG and RUNSTATS, and support the new multidimensional clustering ability. All new indexes in v8 will be created as type-2 except when there was already a (pre-migration) type-1 index defined on the table. Use REORG INDEXES to convert type-1 to type-2 indexes.
DB2 v8 Documentation:
- Concepts ==> Administration ==> Performance tuning ==> Operational performance ==> Table and index management ==> Index planning
- Concepts ==> Administration ==> Performance tuning ==> Operational performance ==> Table and index management ==> Performance tips for indexes
- Reference ==> SQL ==> SQL Statements ==> CREATE INDEX
Profile registry configuration
DB2 profile registry variables typically affect how the optimizer and the DB2 engine itself behave. There are many profile registry variables, but most of them have a very specific purpose which will not be used in most DB2 environments. Below are some of the commonly used ones.
Table 2 lists the basic administration commands for the profile registry:
Table 2. Profile registry administration
|
Command
|
Description
| | db2set –all | Lists all currently set DB2 registry variables. | | db2set –g | -i variable=value
| Sets the specified DB2 registry variable at either the global (-g) or instance (-i) level. |
Note: Do not place spaces between the variable and the value, or the variable will be reset to its default value.
DB2_PARALLEL_IO
This will help promote parallel access to any table space(s) which resides on a disk array. If all of your table spaces are on disk arrays, set this equal to *. If only some of the table spaces are on disk arrays, use "db2 list tablespaces" to retrieve their ID’s and set this equal to their ID’s (use comma to separate ID’s). For best performance, make sure that your table space’s prefetch size is significantly larger than its extent size.
DB2_EVALUNCOMMITTED
OFF by default, enabling to ON will defer locking until after predicate evaluation has taken place. Enabling this can be very useful for reducing lock contention in applications which have been migrated from Oracle.
DB2_SKIPDELETED
OFF by default, enabling to ON will allow statements using CS or RS to skip deleted keys of indexes and deleted rows of tables. Again, this can be very useful for reducing lock contention in applications which have been migrated from Oracle.
DB2_HASH_JOIN
Enabled by default. OLTP may benefit from disabling DB2_HASH_JOIN (NO).
(AIX): DB2_FORCE_FCM_BP
NO by default; if you are using DB2’s Database Partitioning Feature (DPF) and have multiple logical partitions, set to YES to improve inter-partition communication at a cost of 1 less shared memory segment being available to buffer pools. Use NO when database partitioning is not being used.
(AIX 4.3) DB2_MMAP_READ and DB2_MMAP_WRITE
Both are enabled by default. If you are using AIX 4.3, 32-bit DB2, and memory is limiting you from increasing the size of your buffer pools, set this to OFF to free up one more memory segment. This should free up approximately 256 MBs of shared memory (which you should be able to use some of for your buffer pools). Conduct tests to make sure that the change improves performance as sometimes using memory mapped reading and writing of disk buys better performance than increasing the buffer pool size, though this is not as common.
DB2 v8 Documentation:
- Reference ==> Registry and environment variables
Configuring to avoid runtime errors
Initial application runs typically uncover some problems with the values of some configuration parameters. If you don’t receive any errors or warnings during your application run, then you are safe. If you do, see the discussion of database manager and database configuration parameter administration later in the article. The following configuration parameters will complain if there is not enough memory to process your SQL:
MON_HEAP_SZ (DBM)
This is the amount of memory allocated for database system monitor data. Memory is allocated from the monitor heap when you perform database monitoring activities such as snapshot monitoring or activating an event monitor. If DB2 returns an error if there is not enough memory available, try a value of 256. If again you encounter an error, increase by increments of 256 until the error goes away.
QUERY_HEAP_SZ (DBM)
This is the maximum amount of memory that can be allocated to store each query in the agent's private memory. The query heap is also used for the memory allocated for blocking cursors. The query heap size must be greater than or equal to ASLHEAPSZ. If you receive an error from DB2 that performance may be less than optimal, at a minimum, set to a value at least five times larger than ASLHEAPSZ which will allow for queries larger than ASLHEAPSZ and provide enough memory for three or four concurrent blocking cursors.
MAXAPPLS (DB)
This specifies the maximum number of concurrent applications that can be connected (both local and remote) to a database. At the absolute minimum, set this >= (# of user connections). For more details, see the discussion of MAXAGENTS later on.
STMTHEAP (DB)
The statement heap is used as a work space for the SQL compiler during compilation of an SQL statement. This area is allocated and released for every SQL statement handled. If you receive a warning or error, increase by 256 until the error goes away.
APPLHEAPSZ (DB)
The application heap is the private memory available to be used by the database manager on behalf of a specific agent. The heap is allocated when an agent or subagent is initialized for an application and the amount allocated will be the minimum amount needed to process the request, if more is needed it can be allocated up to the maximum specified by this parameter. Increase in increments of 256 until the error goes away.
Snapshot monitoring for improved performance
Use snapshot monitoring to identify the behavior of a database over a period of time, showing things such as how memory is being utilized and how locks are being acquired. Monitoring is the approach used to fine-tune configuration and identify problems, such as long statement execution time. If you have already used the Configuration Advisor, you may not be able to achieve big gains in performance at this time.
The easiest way to collect data to analyze is to use a script to perform sampled snapshot monitoring while running your application.
A script like either the one shown in Listing 3 or the one shown in Listing 4 will collect all the information you need to get on your way. Start by running the script for a duration of 60 seconds over several intervals; this way you should get a nice sample of application behavior and not have too much information to work through.
Listing 3. getsnap.ksh (UNIX)
#!/usr/bin/ksh
# take a snapshot after specified sleep period for a number of iterations
# parameters: (1) database name
# (2) directory for output
# (3) interval between iterations (seconds)
# (4) maximum number of iterations
#
# Note: You may receive an error about the monitor heap being too small. You may
# want to set mon_heap_sz to 2048 while monitoring.
if [ $# -ne 4 ]
then echo "4 parameters required: dbname output_dir sleep_interval iterations"; exit
fi
dbname=$1
runDir=$2
sleep_interval=$3
iterations=$4
stat_interval=3
stat_iterations=$(($sleep_interval/$stat_interval))
if [[ -d $runDir ]]; then
echo "dir: $runDir already exists, either remove it or use another directory name"
exit
fi
mkdir $runDir
cd $runDir
db2 update monitor switches using bufferpool on lock on sort on statement on \
table on uow on
# repeat the snapshot loop for the specified iterations
let i=1
while [ i -le $iterations ]
do
if [ $i -le 9 ]
then
i2="0$i"
else
i2="$i"
fi
echo "Iteration $i2 (of $iterations) starting at `date`"
vmstat $stat_interval $stat_iterations > vmstat_$i2
iostat $stat_interval $stat_iterations > iostat_$i2
db2 -v reset monitor all
sleep $sleep_interval
db2 -v get snapshot for dbm > snap_$i2
db2 -v get snapshot for all on $dbname >> snap_$i2
echo "Iteration $i2 (of $iterations) complete at `date`"
let i=$i+1
done
db2 update monitor switches using bufferpool off lock off sort off statement off \
table off uow off
db2 terminate
|
Listing 4. getsnap.bat (Windows)
@echo off
REM
REM take a snapshot after specified sleep period for a number of iterations
REM parameters: (1) database name
REM (2) file name id
REM (3) interval between iterations (seconds)
REM (4) maximum number of iterations
REM
REM Note: You may receive an error about the monitor heap being too small. You may
REM want to set mon_heap_sz to 2048 while monitoring.
:CHECKINPUT
IF ""=="%4" GOTO INPUTERROR
GOTO STARTPRG
:INPUTERROR
echo %0 requires 4 parameters: dbname filename_id sleep_interval iterations
echo e.g. "getsnap.bat sample 0302 60 3"
GOTO END
:STARTPRG
SET dbname=%1
SET fileid=%2
SET sleep_interval=%3
SET iterations=%4
db2 update monitor switches using bufferpool on lock on sort on statement on table on uow on
REM repeat the snapshot loop for the specified iterations
SET i=1
:SNAPLOOP
IF %i% LSS 10 SET i2=0%i%
IF %i% GTR 9 SET i2=%i%
echo Starting Iteration %i2% (of %iterations%)
db2 -v reset monitor all
sleep %sleep_interval%
db2 -v get snapshot for dbm > snap%i2%_%fileid%
db2 -v get snapshot for all on %dbname% >> snap%i2%_%fileid%
echo Completing Iteration %i2% (of %iterations%)
SET /a i+=1
IF %i% GTR %iterations% GOTO ENDLOOP
GOTO SNAPLOOP
:ENDLOOP
db2 update monitor switches using bufferpool off lock off sort off statement off table off uow off
db2 terminate
:END
|
Note that the two scripts differ slightly in behavior, but both will produce the desired snapshot output.
Snapshot monitoring, amongst other things, will be used as a means to find the optimal setting of many of the DBM and DB configuration parameters in the following sections.
DB2 v8 Documentation:
- Reference ==> System monitor ==> Snapshot monitor
Dynamic SQL statements
The scripts shown in Listing 3 and Listing 4 will issue a "get snapshot for all on dbname" which includes all the output for "get snapshot for dynamic SQL on dbname". If you find that you are not capturing many SQL statements, increase your monitoring duration. The "Dynamic SQL Snapshot Result" section of the output for one statement will look as shown in Listing 5:
Listing 5. Sample Dynamic SQL Snapshot
Dynamic SQL Snapshot Result
Database name = SAMPLE
Database path = C:\DB2\NODE0000\SQL00003\
Number of executions = 1
Number of compilations = 1
Worst preparation time (ms) = 1624
Best preparation time (ms) = 1624
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 41
Internal rows updated = 0
Rows written = 0
Statement sorts = 0
Total execution time (sec.ms) = 0.134186
Total user cpu time (sec.ms) = 0.000000
Total system cpu time (sec.ms) = 0.000000
Statement text = select * from sales
...
|
You can see that there are some useful strings to search for in the output.
"Number of executions" - This can help you identify important statements which should likely be well-tuned. It is also very useful for helping to calculate the average execution time of the statement.
For the statements that are executed a large number of times, a single execution may not place large demands on the system, but the cumulative result may degrade performance significantly. Try to understand how the application uses this SQL and perhaps there may be a slight redesign of the application logic that could lead to an improvement in performance.
It may also be useful to see if parameter markers could be used so that only one package is created for the statement. Parameter markers are used as place holders in a dynamically prepared statement (when the access plan is generated). At execution time, values are supplied for the parameter markers and the statement runs.
For example, to search for the most frequently executed statements:
UNIX:
grep -n " Number of executions" snap.out | grep -v "= 0" | sort -k 5,5rn | more
Windows:
findstr /C:" Number of executions" snap.out | findstr /V /C:"= 0"
"Rows read" - This will help identify the Dynamic SQL statements which read the most rows. A high number of rows read will typically indicate that table scans are occurring. A high value can also indicate index scans with little or no selectivity, which can be just about as bad as a table scan.
You can use Explain to see if this is indeed the case. If a table scan is occurring, the problem may be remedied by performing a RUNSTATS on the table or by feeding the SQL statement to the DB2 Design Advisor to have it recommend a better index. If it is an index scan with poor selectivity, perhaps a better index is needed. Try the Design Advisor.
grep -n " Rows read" snap.out | grep -v "= 0" | sort -k 5,5rn
findstr /C:" Rows read" snap.out | findstr /V /C:"= 0"
"Total execution time" - This is the total execution time for all executions of the statement. It is very handy to divide this number by the number of executions. If you are seeing a very long average execution time for the statement, it could be because of table scans and/or a lock-wait situation. Heavy I/O from index scans and page fetches is another cause. By using an index, you can typically avoid both table scans and lock-waits. Locks are released on commit, so perhaps committing more often might remedy a lock-wait problem.
grep -n " Total execution time" snap.out | grep -v "= 0.0" | sort -k 5,5rn | more
findstr /C:" Total execution time" snap.out | findstr /V /C:"= 0.0" |sort /R
"Statement text" – Displays the text of the statement. If you notice repeated statements that are identical except for differing in values for the predicate of the WHERE, parameter markers could be use to avoid re-compilation of the statement. This can help avoid costly repeated prepares by using the same package. This text can also be input into the Design Advisor to generate optimal indexes.
grep -n " Statement text" snap.out | more
findstr /C:"Statement text" snap.out
Buffer pool sizing
Using "get snapshot for all on dbname" will generate one snapshot for each buffer pool on the database. Listing 6 shows such a snapshot:
Listing 6. Sample bufferpool snapshot
Bufferpool Snapshot
Bufferpool name = IBMDEFAULTBP
Database name = SAMPLE
Database path = C:\DB2\NODE0000\SQL00002\
Input database alias = SAMPLE
Snapshot timestamp = 02-20-2004 06:24:45.991065
Buffer pool data logical reads = 370
Buffer pool data physical reads = 54
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool data writes = 3
Buffer pool index logical reads = 221
Buffer pool index physical reads = 94
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total buffer pool read time (ms) = 287
Total buffer pool write time (ms) = 1
Asynchronous pool data page reads = 9
Asynchronous pool data page writes = 0
Buffer pool index writes = 0
Asynchronous pool index page reads = 0
Asynchronous pool index page writes = 0
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 0
Asynchronous data read requests = 3
Asynchronous index read requests = 0
No victim buffers available = 0
Direct reads = 86
Direct writes = 4
Direct read requests = 14
Direct write requests = 2
Direct reads elapsed time (ms) = 247
Direct write elapsed time (ms) = 56
Database files closed = 0
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Unread prefetch pages = 0
Vectored IOs = 3
Pages from vectored IOs = 9
Block IOs = 0
Pages from block IOs = 0
Physical page maps = 0
Node number = 0
Tablespaces using bufferpool = 4
Alter bufferpool information:
Pages left to remove = 0
Current size = 250
Post-alter size = 250
|
To determine the efficiency of a buffer pool, you want to calculate its buffer pool hit ratio (BPHR). The important information that you need has been bolded above. An ideal BPHR, if possible, is somewhere over 90%. The formula is as follows:
BPHR (%) = (1 - (("Buffer pool data physical reads" + "Buffer pool index physical reads") /
("Buffer pool data logical reads" + "Buffer pool index logical reads"))) * 100
In the above snapshot for the IBMDEFAULTBP buffer pool, we can compute the BPHR as:
= (1-((54 + 94) / (370 + 221))) * 100
= (1-(148 / 591)) * 100
= (1- 0.2504) * 100
= 74.96
|
In this case, the BPHR is about 75%. Currently the buffer pool is only 250 * 4KB pages (1MB). It would be well worthwhile to increase the size of this buffer pool and see if the BPHR increases. If the BPHR remains low, you may need to redesign your logical layout as discussed in the Creating buffer pools and Creating table spaces sections.
Block-based buffer pool efficiency
If this is a block-based buffer pool and you are seeing a low number of "Block IOs", consider altering the buffer pool and increasing the size of NUMBLOCKPAGES. If you see more "Block IOs" occurring as a result, consider increasing again. If you see diminishing returns, decrease the size.
DBM and DB configuration
DB2 has dozens of configurable parameters. Many are automatically configured by DB2, while others have defaults which have proven to work well in most environments. In the following, we describe only those parameters which most commonly require additional consideration.
Some database manager (that is, instance) configuration parameters may be changed online (take effect immediately), while others require that the instance be recycled (that is, DB2STOP followed by DB2START). The same applies to the database configuration parameters. Some take effect instantly while others require the database to be deactivated and reactivated. The documentation on each configuration parameter specifies whether the parameter is configurable online or not.
The basic administration commands for the database manager and database configuration file are shown in Table 3:
Table 3. Database Manager and Database Configuration Administration
|
Command
|
Description
| | GET DBM CFG [SHOW DETAIL] | Lists the current values of the database manager configuration file. | | UPDATE DBM CFG USING config_param value
| Sets the specified database manager configuration parameter to the specified value. | | GET DB CFG FOR db_name [SHOW DETAIL] | Lists the current values of a specific database's configuration file.. | | UPDATE DB CFG FOR db_name USING config_param value
| Sets the specified database manager configuration parameter to the specified value. |
After you make a change to a configuration parameter, you can identify if the setting took effect immediately (online) with the following DB2 CLP commands:
GET DBM CFG SHOW DETAIL
GET DB CFG FOR dbname SHOW DETAIL
For example, in the following case, MAX_QUERYDEGREE and MAXTOTFILOP have been increased to 3 and 19000 respectively. If the parameter has been configured online, the Delayed Value will be the same as the Current Value. Otherwise, an instance restart or database reactivation is required.
Listing 7. Show Details example
Database Manager Configuration
Node type = Enterprise Server Edition with local and remote clients
Description Parameter Current Value Delayed Value
-------------------------------------------------------------------------------------------
Maximum query degree of parallelism (MAX_QUERYDEGREE) = 3 3
Maximum total of files open (MAXTOTFILOP) = 16000 19000
|
Some of the following configuration parameters are allocated from shared memory, so you should keep the OS limits in mind (discussed above). You must ensure that you are not over allocating memory. If you over allocate memory, it will cause the OS to page which can be disastrous to performance.
DB2 v8 Documentation:
- Reference ==> Configuration parameters ==> Database manager
- Reference ==> Configuration parameters ==> Database
Listing 8 and Listing 9 show samples of a database manager and database snapshot. Along the right (in parentheses), you will see the configuration parameter(s) that can be tuned based on the output.
Listing 8. Database manager snapshot
Database Manager Snapshot
Node name =
Node type = Enterprise Server Edition with
local and remote clients
Instance name = DB2
Number of database partitions in DB2 instance = 1
Database manager status = Active
Product name = DB2 v8.1.4.341
Service level = s031027 (WR21326)
Private Sort heap allocated = 0 (SHEAPTHRES
Private Sort heap high water mark = 1024
Post threshold sorts = 0 and
Piped sorts requested = 0
Piped sorts accepted = 0 SORTHEAP)
Start Database Manager timestamp = 02-17-2004 14:24:37.107003
Last reset timestamp =
Snapshot timestamp = 02-20-2004 06:19:53.272049
Remote connections to db manager = 0 (MAXAGENTS,MAXAPPLS,MAX_COORDAGENTS)
Remote connections executing in db manager = 0
Local connections = 1 (MAXAGENTS,MAXAPPLS,MAX_COORDAGENTS)
Local connections executing in db manager = 0
Active local databases = 1 (NUMDB)
High water mark for agents registered = 8 (MAXAGENTS)
High water mark for agents waiting for a token = 0
Agents registered = 8 (MAXAGENTS)
Agents waiting for a token = 0
Idle agents = 6 (NUM_POOLAGENTS and NUM_INITAGENTS)
Committed private Memory (Bytes) = 46645248
Switch list for db partition number 0
Buffer Pool Activity Information (BUFFERPOOL) = ON 02-20-2004 06:18:57.403336
Lock Information (LOCK) = ON 02-20-2004 06:18:57.403338
Sorting Information (SORT) = ON 02-20-2004 06:18:57.403339
SQL Statement Information (STATEMENT) = ON 02-20-2004 06:18:57.403333
Table Activity Information (TABLE) = ON 02-20-2004 06:18:57.403335
Take Timestamp Information (TIMESTAMP) = ON 02-17-2004 14:24:37.107003
Unit of Work Information (UOW) = ON 02-20-2004 06:18:57.403328
Agents assigned from pool = 26 (NUM_POOLAGENTS and NUM_INITAGENTS)
Agents created from empty pool = 10 (NUM_POOLAGENTS and NUM_INITAGENTS)
Agents stolen from another application = 0 (MAXAGENTS)
High water mark for coordinating agents = 8
Max agents overflow = 0 (MAXAGENTS)
Hash joins after heap threshold exceeded = 0
Total number of gateway connections = 0
Current number of gateway connections = 0
Gateway connections waiting for host reply = 0
Gateway connections waiting for client request = 0
Gateway connection pool agents stolen = 0
Node FCM information corresponds to = 2
Free FCM buffers = 4093
Free FCM buffers low water mark = 4087 (FCM_NUM_BUFFERS)
Free FCM message anchors = 1279
Free FCM message anchors low water mark = 1276
Free FCM connection entries = 1280
Free FCM connection entries low water mark = 1276
Free FCM request blocks = 2031
Free FCM request blocks low water mark = 2026
Number of FCM nodes = 4
Node Total Buffers Total Buffers Connection (FCM_NUM_BUFFERS)
Number Sent Received Status
----------- ------------------ ------------------ -----------------
0 282 275 Active
1 51 48 Active
2 0 0 Active
3 1 1 Active
Memory usage for database manager:
Memory Pool Type = Backup/Restore/Util Heap (UTIL_HEAP_SZ*)
Current size (bytes) = 16384
High water mark (bytes) = 16384
Maximum size allowed (bytes) = 20660224
Memory Pool Type = Package Cache Heap (PCKCACHESZ*)
Current size (bytes) = 327680
High water mark (bytes) = 327680
Maximum size allowed (bytes) = 1071644672
Memory Pool Type = Catalog Cache Heap (CATALOGCACHE_SZ*)
Current size (bytes) = 81920
High water mark (bytes) = 81920
Maximum size allowed (bytes) = 1071644672
Memory Pool Type = Buffer Pool Heap
Current size (bytes) = 1179648
High water mark (bytes) = 1179648
Maximum size allowed (bytes) = 1071644672
Memory Pool Type = Lock Manager Heap (LOCKLIST*)
Current size (bytes) = 278528
High water mark (bytes) = 278528
Maximum size allowed (bytes) = 425984
Memory Pool Type = Database Heap (DBHEAP*)
Current size (bytes) = 3342336
High water mark (bytes) = 3342336
Maximum size allowed (bytes) = 6275072
Memory Pool Type = Database Monitor Heap (MON_HEAP_SZ)
Current size (bytes) = 180224
High water mark (bytes) = 425984
Maximum size allowed (bytes) = 442368
Memory Pool Type = Other Memory
Current size (bytes) = 8060928
High water mark (bytes) = 8159232
Maximum size allowed (bytes) = 1071644672
|
*Snapshot always shows Current size (bytes) = High water mark (bytes) because memory is allocated at database activation.
Listing 9. Database snapshot
Database Snapshot
Database name = SAMPLE
Database path = C:\DB2\NODE0000\SQL00002\
Input database alias = SAMPLE
Database status = Active
Catalog database partition number = 0
Catalog network node name =
Operating system running at database server= NT
Location of the database = Local
First database connect timestamp = 02-20-2004 06:19:00.847979
Last reset timestamp =
Last backup timestamp =
Snapshot timestamp = 02-20-2004 06:23:17.252491
High water mark for connections = 1 (MAXAPPLS)
Application connects = 1
Secondary connects total = 0
Applications connected currently = 1 (AVG_APPLS)
Appls. executing in db manager currently = 0
Agents associated with applications = 1
Maximum agents associated with applications= 1
Maximum coordinating agents = 1
Locks held currently = 0
Lock waits = 0
Time database waited on locks (ms) = 0
Lock list memory in use (Bytes) = 1000 (LOCKLIST and MAXLOCKS)
Deadlocks detected = 0
Lock escalations = 0 (LOCKLIST and MAXLOCKS)
Exclusive lock escalations = 0 (LOCKLIST and MAXLOCKS)
Agents currently waiting on locks = 0
Lock Timeouts = 0 (LOCKTIMEOUT)
Number of indoubt transactions = 0
Total Private Sort heap allocated = 0 (SHEAPTHRES and SORTHEAP)
Total Shared Sort heap allocated = 0 (SHEAPTHRES_SHR and SORTHEAP)
Shared Sort heap high water mark = 0 (SHEAPTHRES_SHR and SORTHEAP)
Total sorts = 0
Total sort time (ms) = 0
Sort overflows = 0 (SORTHEAP)
Active sorts = 0
Buffer pool data logical reads = 370
Buffer pool data physical reads = 54
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Asynchronous pool data page reads = 9 (NUM_IOSERVERS)
Buffer pool data writes = 3 (CHNGPGS_THRESH and NUM_IOCLEANERS)
Asynchronous pool data page writes = 0 (CHNGPGS_THRESH and NUM_IOCLEANERS)
Buffer pool index logical reads = 221
Buffer pool index physical reads = 94
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Asynchronous pool index page reads = 0 (NUM_IOSERVERS)
Buffer pool index writes = 0 (CHNGPGS_THRESH and NUM_IOCLEANERS)
Asynchronous pool index page writes = 0 (CHNGPGS_THRESH and NUM_IOCLEANERS)
Total buffer pool read time (ms) = 287
Total buffer pool write time (ms) = 1
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 0
Asynchronous data read requests = 3
Asynchronous index read requests = 0
No victim buffers available = 0
LSN Gap cleaner triggers = 0
Dirty page steal cleaner triggers = 0 (CHNGPGS_THRESH)
Dirty page threshold cleaner triggers = 0 (CHNGPGS_THRESH)
Time waited for prefetch (ms) = 0 (NUM_IOSERVERS)
Unread prefetch pages = 0
Direct reads = 86
Direct writes = 4
Direct read requests = 14
Direct write requests = 2
Direct reads elapsed time (ms) = 247
Direct write elapsed time (ms) = 56
Database files closed = 0 (MAXFILOP)
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Host execution elapsed time = 0.000039
Commit statements attempted = 6
Rollback statements attempted = 1
Dynamic statements attempted = 281
Static statements attempted = 7
Failed statement operations = 1
Select SQL statements executed = 4
Update/Insert/Delete statements executed = 0
DDL statements executed = 2
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 1
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Rows deleted = 0
Rows inserted = 0
Rows updated = 0
Rows selected = 336
Rows read = 375
Binds/precompiles attempted = 0
Log space available to the database (Bytes)= 5095757 (LOGPRIMARY and LOGSECOND)
Log space used by the database (Bytes) = 4243
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 6498 (LOGPRIMARY and LOGSECOND)
Secondary logs allocated currently = 0 (LOGPRIMARY and LOGSECOND)
Log pages read = 0 (LOGBUFSZ)
Log pages written = 5 (LOGBUFSZ)
Appl id holding the oldest transaction = 38
Package cache lookups = 10 (PKGCACHESZ)
Package cache inserts = 8 (PKGCACHESZ)
Package cache overflows = 0 (PKGCACHESZ)
Package cache high water mark (Bytes) = 191140 (PKGCACHESZ)
Application section lookups = 281
Application section inserts = 6
Catalog cache lookups = 18 (CATALOGCACHE_SZ)
Catalog cache inserts = 9 (CATALOGCACHE_SZ)
Catalog cache overflows = 0 (CATALOGCACHE_SZ)
Catalog cache high water mark = 0 (CATALOGCACHE_SZ)
Workspace Information
Shared high water mark = 0
Corresponding shared overflows = 0
Total shared section inserts = 0
Total shared section lookups = 0
Private high water mark = 21102
Corresponding private overflows = 0
Total private section inserts = 6
Total private section lookups = 6
Number of hash joins = 0
Number of hash loops = 0
Number of hash join overflows = 0 (SORTHEAP)
Number of small hash join overflows = 0 (SORTHEAP)
|
At times, it can be very handy to use ‘grep’ (UNIX) and ‘findstr" (Windows) to perform a preliminary search of the snapshot output for any problem issues. If you find something, you can investigate further by opening the snapshot output and locating the problem.
For example, to identify if any deadlocks have occurred:
UNIX:
grep -n "Deadlocks detected" snap.out | grep -v "= 0" | more
Windows:
findstr /C:"Deadlocks detected" snap.out | findstr /V /C:"= 0"
SHEAPTHRES (DBM)
This is the total amount of memory that concurrent private sorts may consume for all databases in the instance. Additional incoming sorts will be given a smaller amount of memory to use. For OLTP, a good starting point is about 20000, while 40000-60000 tends to work better for OLAP.
When "Piped sorts accepted" is a low value compared to "Piped sorts requested", performance can typically be improved by increasing the size of SHEAPTHRES. If "Post threshold sorts" (sorts which requested heaps after SHEAPTHRES had been exceeded) is a high value (i.e., double-digit), try increasing the size of SHEAPTHRES. "Total Private Sort heap allocated" should be less than SHEAPTHRES. If it is not, increase SHEAPTHRES.
MAXAGENTS (DBM)
This is the maximum number of database manager agents available to accept application requests for all databases within the instance. This parameter can be useful in memory constrained environments to limit the total memory usage of the database manager, because each additional agent requires additional memory.
If your machine is not memory constrained, increase MAXAGENTS until "Agents stolen from another application" is 0. Additionally, "Local connections" + "Remote connections to db manager" will give you an indication of the number of concurrent connections to the instance. "High water mark for agents registered" will report the largest number of agents ever connected at one time to the database manager. "Max agents overflow" reports the number of times a request to create a new agent was received when MAXAGENTS had already been reached. Finally, "Agents Registered" show the number of agents currently registered in the database manager instance that is being monitored.
NUMDB (DBM)
This specifies the number of local databases that can be concurrently active. On a production system, it is recommended that there be one database per instance, so you should set to 1. Otherwise, set it to the maximum number of concurrently active databases. Use "Active local databases" if unsure.
NUM_INITAGENTS and NUM_POOLAGENTS (DBM)
NUM_INITAGENTS specifies the number of idle agents that are created in the pool at db2start and helps speed up connections at the beginning of database use. NUM_POOLAGENTS is related, but has more effect on performance once the database has been running for a while. When the Connections Concentrator is OFF (default; MAX_CONNECTIONS = MAX_COORDAGENTS), NUM_POOLAGENTS specifies the maximum size of the agent pool. When the Concentrator is ON (MAXCONNECTIONS > MAX_COORDAGENTS) it is used as a guideline for how large the agent pool will be when the system workload is low.
NUM_INITAGENTS and NUM_POOLAGENTS should be set to the average number of expected concurrent instance-level connections, which is usually low for OLAP and higher for OLTP. For performance benchmarks where there is a substantial ramp up of connections, set NUM_INITAGENTS to the number of expected connections (this will significantly reduce time required to ramp up connections by reducing resource contention). In 3-tier environments where connection pooling is used, NUM_INITAGENTS and NUM_POOLAGENTS have little affect on performance as connections are continuously maintained by the application server even when the application is idle.
"Idle agents" shows the number of idle agents in the agent pool, while "Agents assigned from pool" shows the number of times an agent has been assigned from the agent pool. "Agents created from empty pool" shows the number of agents that had to be created because of an empty pool, which can be misleading right after db2start. After db2start, it will simply show that NUM_INITAGENTS of agents were created. If the ratio of "Agents created from empty pool" / "Agents assigned from pool" is high (5:1 or more), it could indicate that NUM_POOLAGENTS should be increased; this could also indicate that the overall workload for system is too high. You can adjust the workload by lowering MAXCAGENTS. If the ratio is low, it suggests that NUM_POOLAGENTS is probably set too high and that some agents are wasting system resources.
FCM_NUM_BUFFERS (DBM)
Only used within a DPF environment with multiple logical partitions and it specifies the number of 4 KB buffers that are used for internal communication. If DPF is not being used, this will not even show in the snapshot output. Additionally, this information will be from the partition that the snapshot was run against. For example, in the snapshot above DBM snapshot above, "Node FCM information corresponds to" shows a value of 2, so it was taken from partition number 2. "Get snapshot for dbm global" can be used to get an aggregate of all partition values.
The FCM Node section of DBM snapshots can be used to see where the major inter-partition communication is taking place for investigation purposes. Heavy communication could indicate a need for more FCM buffer memory, a need for a different partitioning key, or perhaps different table to table space assignments. If "Free FCM buffers low water mark" is less than 15 percent of FCM_NUM_BUFFERS, increase FCM_NUM_BUFFERS until "Free FCM buffers low water mark" is greater than or equal to it, to ensure adequate FCM resources are always available.
AVG_APPLS (DB)
Only change if your application issues complex SQL (e.g., joins, functions, recursive, etc.), otherwise leave at 1. This helps to estimate how much buffer pool will be available at runtime for an access plan. It should be set to a low number that is the average number of "Applications connected currently" multiplied by the percentage issuing complex SQL.
LOCKLIST and MAXLOCKS (DB)
There is one lock list per database and it contains the locks held by all applications concurrently connected to the database. On 32-bit platforms, the first lock on an object requires 72 bytes while each additional requires 36 bytes. On 64-bit platforms, the first lock requires 112 bytes while additional require 56 bytes.
When the percentage of the LOCKLIST used by one application reaches MAXLOCKS, the database manager will perform a lock escalation, where row locks for a given table will be traded in for a single table lock. Also, if LOCKLIST is close to being exhausted, the database manager will identify a connection holding the most row locks on a table and trade those for a table lock to free up LOCKLIST memory. Locking entire tables can greatly reduce concurrency – the chance of deadlock is also increased.
If "Lock list memory in use (Bytes)" exceeds 50 percent of the defined LOCKLIST size, then increase the number of 4K pages in the LOCKLIST. If there are "Lock escalations" or “Exclusive lock escalations” occurring, increase either LOCKLIST or MAXLOCKS, or both.
The database snapshot section on locking contains a lot of valuable information. Look for high values in "Locks held currently", "Lock waits", "Time database waited on locks (ms)", "Agents currently waiting on locks", and "Deadlocks detected" which could be the symptoms of less than optimal access plans, long transactions, or application concurrency problems. If you are seeing deadlocks, you will want to create an event monitor for deadlocks with details to see what is actually occurring.
For more detailed information on locking issues, see the article Diagnosing and Resolving Lock Problems with DB2 Universal Database by Bill Wilkins.
Some things you can do to reduce locking include:
- Ensure that your application is using the lowest isolation level required.
- Perform frequent COMMITs.
- When performing many updates, explicitly lock the entire table (using LOCK TABLE statement) before updating.
- Use Cursor Stability isolation level (default) when possible to decrease the number of share locks held. (Uncommitted Read can further decrease locking, if your application can function with dirty reads.)
LOCKTIMEOUT (DB)
This specifies the number of seconds that an application will wait to obtain a lock. This can help to avoid global deadlock situations. With -1, the application will appear to freeze if lock-wait is encountered. The Bill Wilkins article on locking also covers this in greater detail.
About 60 (seconds) for OLAP and 10 for OLTP for production environments is a good starting point. -1 should be used for development environments to identify and resolve lock-wait situations. If there are a large number of concurrent users, you may want to increase the OLTP time to avoid rollbacks.
If "Lock Timeouts" is a high number, it could be caused by (1) too low a LOCKTIMEOUT value, (2) a transaction holding locks for an extended period, or (3) lock escalations.
SHEAPTHRES_SHR (DBM)
This is a hard-limit on the total amount of memory that concurrent shared sorts may consume within an instance. This is only applicable if: (1) INTRA_PARALLEL=YES, or (2) Concentrator is on (MAX_CONNECTIONS > MAX_COORDAGENTS). Sorts that use a cursor with the WITH HOLD option, will be allocated from shared memory.
"Shared Sort heap high water mark" shows the most shared sort memory allocated at one time. If this value is always much lower than SHEAPTHRES_SHR, you should decrease SHEAPTHRES_SHR to save memory for other database functions. If this value is very close to SHEAPTHRES_SHR, you may want to increase SHEAPTHRES_SHR. "Total Shared Sort heap allocated" is the total number of allocated pages of sort heap space for all sorts. If the element value is greater than or equal to SHEAPTHRES_SHR, it means that the sorts are not getting the full sort heap as defined by the SORTHEAP parameter. Increase the size of SHEAPTHRES_SHR to help avoid this.
When setting, try to keep it as a multiple of SORTHEAP.
SORTHEAP (DB)
This parameter specifies the maximum number of private memory pages to be used for private sorts or the maximum number of shared memory pages to be used for shared sorts. Each sort has a separate sort heap that is allocated as needed, by the database manager.
What is often well understood is that sort overflows occur when the amount of memory needed for a sort exceeds SORTHEAP. Less well understood is that if your statistics are out of date, or if data is skewed and you have not collected distribution statistics, an overflow can occur if DB2 requests too small a sort heap, and the actual sort operation exceeds the requested amount. Therefore, it is very important to keep statistics up to date. Additionally, make sure that the sort is not the result of a missing index.
A good starting point is 128 for OLTP and between 4096 - 8192 for OLAP. If there are a lot of "Sort overflows" (double-digit), you likely need to increase SORTHEAP. If the "Number of hash join overflows" is not 0, increase SORTHEAP by increments of 256 until it is. If "Number of small hash join overflows" is not 0, increase SORTHEAP by 10% increments until small hash join overflows is zero.
CHNGPGS_THRESH (DB)
Use this to specify the percentage of changed pages in the buffer pool at which time the asynchronous page cleaners will be started to write the changes to disk to make room for new data in the buffer pool. In a read-only environment page cleaners are not used. In OLTP, a value of 20-40 should improve performance (20 in the case of very heavy update activity), as lowering the value makes the I/O Cleaners more aggressive in their writing out of dirty buffer pool pages, but with less work each time. If there are not a lot of INSERTs or UPDATEs, the default of 60 should be fine for OLTP and OLAP.
If "Dirty page steal cleaner triggers" is a double-digit number, try lowering. If high "Buffer pool data writes" and low "Asynchronous pool data page writes", try lowering.
As of FixPak 4, there is an alternate page cleaning algorithm available which may boost your specific buffer pool performance. You need to set the Profile Registry variable DB2_USE_ALTERNATE_PAGE_CLEANING=YES, which will ignore CHNGPGS_THRESH. Ensure NUM_IOSERVERS is at least 3 otherwise it will become an inhibitor to the new algorithm.
NUM_IOCLEANERS (DB)
This specifies the number of asynchronous page cleaners for a database, which write changed pages from the buffer pool to disk. Start by setting it equal to the number of CPUs on the system. When I/O Cleaners are triggered they are all started at the same time, therefore you do not want to have so many that they actually impact performance and block other processing.
Decrease NUM_IOCLEANERS if Asynchronous Write Percentage (AWP) is 90% or higher, increase if less than 90%.
AWP = (("Asynchronous pool data page writes" + "Asynchronous pool index page writes") * 100) / ("Buffer pool data writes" + "Buffer pool index writes")
NUM_IOSERVERS (DB)
I/O servers are used to perform prefetching and this parameter specifies the maximum number of I/O servers for a database. Non-prefetch I/Os are scheduled directly from the database agents and as a result are not constrained by this parameter. Start by setting equal to the number of physical disks that the database resides on (even if many disks in a disk array or many disks in a logical volume) + 1 or 2, but not more than 4-6 times the # of CPUs. Having too large a value will not hurt performance, unless it exceeds the 4-6 times the # of CPUs.
If you see "Time waited for prefetch (ms)" in the seconds, you may want to add an IO Server to see if performance improves.
MAXFILOP (DB)
This parameter specifies the maximum number of files that can be open for each database agent. If opening a file causes this value to be exceeded, some files in use by this agent are closed. Excessive opening and closing will degrade performance. Both SMS table spaces and DMS table space file containers are treated as files. More files are generally used by SMS.
Increase until the number of "Database files closed" is 0.
LOGPRIMARY, LOGSECOND and LOGFILSZ (DB)
LOGPRIMARY specifies the number of primary log files to be pre-allocated, while LOGSECOND are allocated on an as-needed basis. LOGFILSIZ defines the size of each log file.
If there is a high number of "Secondary logs allocated currently", you will want to increase LOGFILSIZ or LOGPRIMARY (while making sure that LOGPRIMARY + LOGSECOND does not exceed 256). You can also use "Maximum total log space used (Bytes)" to help figure out your dependency on log file space (primary + secondary logs).
Log file size has an implication on disaster recovery configurations where log shipping is used. A large log file will have better performance, but potentially increases the degree of lost transactions. When the primary system goes down, the last log file and its transactions may never be sent to the secondary because the file was not closed before failure. The larger the log file, the greater the degree of lost transactions due to the lost log file.
LOGBUFSZ (DB)
This parameter allows you to specify the amount of the database heap (DBHEAP) to use as a buffer for log records before writing to disk. The log records are written to disk when a transaction commits or the log buffer is full. Buffering the log records will result in log records being written to disk less frequently with more log records being written at each time. A good starting point is at least 256 pages for most OLTP and 128 for OLAP. When you consistently see more than a couple of "Log pages read" , you may need to increase the size. Log pages may also be read if a rollback occurs.
If you get an error while attempting to increase LOGBUFSZ, increase DBHEAP by the same amount first and try again.
PKGCACHESZ (DB)
The package cache is used for caching sections for static and dynamic SQL statements. Caching packages allows the database manager to reduce its internal overhead by eliminating the need to access the system catalogs when reloading a package; or, in the case of dynamic SQL, eliminating the need for recompilation.
PKGCACHESZ should be larger than "Package cache high water mark (Bytes)". If "Package cache overflows" is not 0, you can attempt to drive this counter to zero by increasing PKGCACHESZ.
The Package Cache Hit Ratio (PCHR) should be as close to 100% as possible (without taking needed memory away from buffer pools). Calculate with the following formula:
PCHR = (1-("Package cache inserts" / "Package cache lookups"))*100
CATALOGCACHE_SZ (DB)
This is used to cache system catalog information, such as SYSTABLE, authorization, and SYSROUTINES information. Caching catalog information is very important, especially when DPF is used, where internal overhead is reduced by eliminating the need to access the system catalogs (catalog partition) to obtain information that has been previously retrieved.
Keep increasing until Catalog Cache Hit Ratio (CCHR) is 95% or better for OLTP:
CCHR = (1-("Catalog cache inserts" / "Catalog cache lookups"))*100
Also increase if the value of "Catalog cache overflows" is greater than 0. You can also use the "Catalog cache high water mark (Bytes)" to determine the most memory ever consumed by the catalog cache. If the High water mark equals the Maximum size allowed, you will want to increase the catalog cache heap size.
Experimenting: DBM and DB Configuration
The following parameters may buy you extra performance. However, their impact is not directly reported by any specific monitor in the snapshot. Instead, you may want to change one at a time and measure the overall performance of the application. The best measurement you can take is to examine the overall SQL execution times from several snapshots, pre-change and post-change.
INTRA_PARALLEL (DBM)
This parameter specifies whether the database manager can use intra-partition parallelism. The default of NO is best for high concurrent connections (mainly OLTP), while YES works best for few concurrent connections and more complex SQL (OLAP/DSS). Mixed workloads typically benefit from NO.
When enabled, it causes sort memory to be allocated from shared memory. Additionally, it can cause excessive system overhead if the level of concurrency increases significantly. If the system is non-OLTP, there is a 4:1 ratio of CPUs to partitions, and the CPU busy rate runs less than 50 percent on average, INTRA_PARALLEL would likely improve performance.
DFT_QUERYOPT (DB)
This is used to specify a default level of optimization to use when compiling SQL queries. Try the default of 5 or 3 for mixed OLTP/OLAP, lower for OLTP, higher for OLAP. For simple SELECTS or short runtime queries (which generally take less than one second to complete) 1 and 0 may be appropriate. Try a class of 1 or 2 if you have many tables with many join predicates on the same column. Try using a class of 7 for longer running queries which take more than 30 seconds to complete or if you are inserting to a UNION ALL VIEW (which were added in FixPak4). You should avoid using a class of 9 under most circumstances.
UTIL_HEAP_SZ (DB)
This parameter specifies the maximum amount of memory that can be used simultaneously by the BACKUP, RESTORE, and LOAD utilities. If you are using LOAD, set UTIL_HEAP_SZ to at least 10000 pages per CPU.
NEWLOGPATH (DB)
This specifies a string of up to 242 bytes to change the location where the log files are written and stored. This can point to either a fully qualified path name or raw device. Changing the log path to a separate local high-speed disk (which is not in use for anything other than logging) can significantly improve performance.
Further SQL analysis
The Design Advisor
If you have a specific problem query or set of queries, you can input that workload into the DB2 Design Advisor (db2advis) to have it recommend a set of efficient indexes. If you do not know the SQL, you can
- Capture dynamic SQL using snapshots
- Collect all the SQL issued over a period of time with a statement event monitor
- extract static SQL from the SYSCAT.STATEMENTS ca
| 
