DB2 UDB V8 and WebSphere V5. Performance Tuning and Operations Guide2004
Authors: Chen W. J.
Published year: 2004
|< Day Day Up >|
Although the performance considerations have been put in place as much as we can during the application development, once the whole system is up and running we need to monitor and adjust the system or application to ensure the optimal performance is reached.
In "DB2 architecture overview" on page 49 you can find the architecture to understand how DB2 UDB is designed. In this section we show you how to tune a running DB2 UDB system. We go through the key performance areas and describe what to look for and how to set parameters to fit the requirements for your system.
When creating a buffer pool (DB2 CREATE BUFFERPOOL) you have to specify the size of a buffer pool. The size specifies the amount of pages to use. When using -1, the DB parameter BUFFPAGE is used to specify the size of the buffer pool. To change the size use DB2 ALTER BUFFERPOOL, when the size of the buffer pool is -1 then change the BUFFPAGE.
The DB parameter BUFFPAGE controls the size of any buffer pool with the size set to -1. If you want different sizes you have to set the size using the command CREATE BUFFERPOOL or ALTER BUFFERPOOL.
In prior DB2 UDB versions it was necessary to increase the DBHEAP parameter when using more space for the buffer pool. With version 8 nearly all buffer pool memory, including page descriptors, buffer pool descriptors, and the hash tables, comes out of the database shared-memory set and is sized automatically.
To determine how well your buffer pool is designed, have a look using the snapshot monitor. The monitor switch for BUFFERPOOLS must be set to ON . Check the status with command the DB2 GET MONITOR SWITCHES. If the switch for BUFFERPPOOL is OFF, use the following command to turn it on:
DB2 UPDATE MONITOR SWITCHES USING BUFFERPOOL ON
Or use the following to set the DBM parameter DFT_MON_BUFPOOL to ON .
DB2 UPDATE DBM CFG USING DFT_MON_BUFPOOL ON
When the system is running for a while with the monitor switch for buffer pools set to on, then use DB2 GET SNAPSHOT FOR ALL BUFFERPOOLS to see the current status of the buffer pools. In Example 7-8 we show output of a snapshot for buffer pools. As you can see, the systems have the standard buffer pool with the standard size (for UNIX systems) of 1000 4-K pages.
Example 7-8: Sample output for a snapshot of a buffer pool
$ db2 get snapshot for all bufferpools Bufferpool Snapshot Bufferpool name = IBMDEFAULTBP Database name = TRADE3DB Database path = /home/db2inst1/db2inst1/NODE0000/SQL00002/ Input database alias = Snapshot timestamp = 11-17-2003 16:30:46.883397 Buffer pool data logical reads = 12823 Buffer pool data physical reads = 279 Buffer pool data writes = 0 Buffer pool index logical reads = 222 Buffer pool index physical reads = 68 Total buffer pool read time (ms) = 775 Total buffer pool write time (ms) = 0 Asynchronous pool data page reads = 88 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 = 22 Total elapsed asynchronous write time = 0 Asynchronous data read requests = 5 Asynchronous index read requests = 0 Direct reads = 17210 Direct writes = 0 Direct read requests = 7545 Direct write requests = 0 Direct reads elapsed time (ms) = 3564 Direct write elapsed time (ms) = 0 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 = 5 Pages from vectored IOs = 88 Block IOs = 0 Pages from block IOs = 0 Physical page maps = 0 Node number = 0 Tablespaces using bufferpool = 5 Alter bufferpool information: Pages left to remove = 0 Current size = 1000 Post-alter size = 1000
From the output of DB2 GET SNAPSHOT FOR ALL BUFFERPOOLS you can determine how good the hit ratio of your buffer pools is. The more logical reads and the less physical reads the buffer pool has, the better the ratio will be. To determine the ratio for the buffer pool and the index pool use the following formulas:
In our example the overall buffer pool ratio is 97.82, and this means that the buffer pool is big enough. The hit ratio for indexes is below 70. The small value of the indexes hit ratio show that the indexes are in less use. This could mean that the system is in the point of diminishing returns for a over sized buffer pool. With this ration we could try to reduce the buffer pool size to set memory free to improve the system performance.
In general, a good value for the hit ratio is a value more than 90 percent for both buffer pool hit ratio and index pool hit ratio. When the value is smaller, then increase the buffer pool size and continue monitoring. But be sure not to allocate so much memory that the operating systems starts paging. In AIX use lsps -s or topas to monitor the memory usage and the paging activities.
To monitor the memory of the database use the DB2 Memory Visualizer.
The optimizer uses the table statistics to calculate the best access plan. The statistics will become out-of-date invalid because of insert, update, and delete operations performed on tables. It is necessary to update the statistics from time to time using the runstats command. After runstats is performed on a table, it is necessary to rebind the applications that access the table(s). When an application with static SQL is bound to the database the access plan gets calculated using the current statistics. This access plan is updated as the application is rebound due to the statistics change, or an index is created.
If your SQL codes slows down and this performance problem does not improved after running runstats and rebinding the applications, the SQL code performance might be helped by reorganizing the tables. When inserting new data into tables, it is often not possible to place them in a physical sequence that is the same as the logical sequence defined by the index (unless you use clustered indexes). When accessing the data, many more operations are necessary than with having the data on sequential pages.
With DB2 UDB Version 8, reorg becomes an online feature. The reorg can be paused and restarted. The in-place reorg operation reorganizes only a small portion of data and therefore only a small amount of additional space is necessary and only a few pages are locked for updates at a time.
For more details on how to determine if an REORG is necessary, search DB2 Information Center or DB2 Online Help for the REORGCHK command at:
After table is reorganized, you should run the update the statistic by running the the runstats , then re-bind the static applications. To reduce the need for reorganizing a table, perform these tasks after you create the table:
Alter table to add PCTFREE.
Create clustering index with PCTFREE on index.
Sort the data.
Load the data.
After you have performed these tasks, the table with its clustering index and the setting of PCTFREE on the table helps preserve the original sorted order. If enough space is allowed in table pages, new data will be inserted into the pages to maintain the clustering characteristics of the index. As more data are inserted and the pages of the table become full, records are appended to the end of the table so that the table gradually becomes un-clustered.
The most important thing for performance in the database design is index. When designing the index on tables, you need to understand how the tables will be accessed by the application. First determine what queries will be used on the database and then analyze the statements by yourself or use the Design Advisor provided by DB2 UDB.
Once the data are loaded into the tables, run the runstats utility to update the statistics on the tables so the optimizer can utilize the indexes. For frequently updated tables, you should run the runstats regularly to update the table statistics to ensure that the best access plan will be used in your queries.
If you notice that there are long-running queries in an existing system, you should use the explain tool to analyze the access path the optimizer created. If the table scan is used, it could be an indication that an index is needed. But in some situations it is better to use a table scan than an index if the table has only a few rows or the query needs most of the rows as a result set. Therefore, we recommend using the Design Advisor because you can virtually test if a new index will help to achieve a better performance.
Be careful that if you see that the access plan uses a table scan and you have already had an index that fits, then it may be that the statistics of the table are not up to date. Use the runstats utility to update the statistics and let DB2 UDB create a new access plan. If you are testing with static SQL, do not forget to rebind your application to generate a new access plan.
Each index entry contains a search-key value and a pointer to the row containing that value. If you specify the ALLOW REVERSE SCANS parameter in the CREATE INDEX statement, the values can be searched in both ascending and descending order. It is therefore possible to bracket the search, given the right predicate. An index can also be used to obtain rows in an ordered sequence, eliminating the need for the database manager to sort the rows after they are read from the table.
Consider the following suggestions for using and managing indexes:
Specify parallelism at index creation.
When you create indexes on large tables hosted by an SMP machine, consider setting the DBM configuration parameter INTRA_PARALLEL to YES (1) or SYSTEM (-1) to take advantage of parallel processing for performance improvements. Multiple processors can be used to scan and sort data.
Specify separate table spaces for indexes.
Indexes can be stored in a different table space from the table data. This can allow for more efficient use of disk storage by reducing the movement of read/write heads during index access. You can also create index table spaces on faster physical devices. In addition, you can assign the index table space to a different buffer pool, which might keep the index pages in the buffer longer because they do not compete with table data pages.
Ensure the degree of clustering.
If your SQL statement requires ordering, such as ORDER BY, GROUP BY, and DISTINCT, even though an index might satisfy the ordering, the optimizer might not choose the index if clustering is poor or the table is so small that it is cheaper to scan the table and sort the answer set in memory.
After you create a clustering index, perform a REORG TABLE in classic mode, which creates a perfectly organized index. In general, a table can only be clustered on one index.
Use volatile tables for tables that vary widely in size.
A volatile table is a table that might vary in size at run time from empty to very large. For this kind of table, in which the cardinality varies greatly, the optimizer might generate an access plan that favors a table scan instead of an index scan.
Declaring a table "volatile" using the ALTER TABLE...VOLATILE statement allows the optimizer to use an index scan on the volatile table.
In DB2 UDB V8.1 and later, all new indexes are created as type-2 indexes. The one exception is when you add an index on a table that already has type-1 indexes. In this case only, the new index will also be a type-1 index. To find out what type of index exists for a table, execute the INSPECT command. To convert type-1 indexes to type-2 indexes, execute the REORG INDEXES command. To see the advantages of the new index, look at Chapter 8, "Operational performance," in DB2 UDB Administration Guide: Performance - Document Number , SC09-4821.
There are some database manager configuration parameters that influence the number of agents created and the way they are managed.
The number of agents that can be working at any one time. This value applies to the total number of agents that are working on all applications, including coordinator agents, subagents, inactive agents, and idle agents.
The total number of agents, including active agents and agents in the agent pool, that are kept available in the system. The default value for this parameter is half the number specified for maxagents.
When the database manager is started, a pool of worker agents is created based on this value. This speeds up performance for initial queries. The worker agents all begin as idle agents.
Specifies the maximum number of connections allowed to the database manager system on each partition.
For partitioned database environments and environments with intra-partition parallelism enabled when the connection coordinator is enabled. This value limits the number of coordinating agents.
This value controls the number of tokens permitted by the database manager. For each database transaction (unit of work) that occurs when a client is connected to a database, a coordinating agent must obtain permission to process the transaction from the database manager. This permission is called a processing token. The database manager permits only agents that have a processing token to execute a unit of work against a database. If a token is not available, the agent must wait until one is available to process the transaction.
This parameter can be useful in an environment in which peak usage requirements exceed system resources for memory, CPU, and disk. For example, in such an environment, paging might cause performance degradation for peak load periods. You can use this parameter to control the load and avoid performance degradation, although it can affect either concurrency or wait time, or both.
The snapshot monitor provides some information about the activity of the agents. When taking a snapshot of the database manager and grep for agent you will see information like that shown in Example 7-9.
Example 7-9: Agent information from the snapshot monitor
db2 get snapshot for dbm grep -i agent High water mark for agents registered = 16 High water mark for agents waiting for a token = 0 Agents registered = 16 Agents waiting for a token = 0 Idle agents = 10 Agents assigned from pool = 92 Agents created from empty pool = 18 Agents stolen from another application = 0 High water mark for coordinating agents = 16 Max agents overflow = 0 Gateway connection pool agents stolen = 0
How the prefetcher works is described in "Key performance- related areas" on page 58. Prefetching can be configured using the NUM_IOSERVERS database parameter. Use as many prefetchers as agents allow DB2 to perform the prefetching requests in parallel.
Use the snapshot monitor for buffer pools and calculate the difference of Buffer pool data physical reads and Asynchronous pool data page reads to see how well the prefetchers are working. It is better to define too many than too little prefetchers. If you specify extra I/O servers, these servers are not used, and performance does not suffer. Each I/O server process is numbered. The database manager always uses the lowest numbered process, so some of the upper numbered processes might never be used.
Configuring enough I/O servers with the NUM_IOSERVERS configuration parameter can greatly enhance the performance of queries for which prefetching of data can be used. To maximize the opportunity for parallel I/O, set NUM_IOSERVERS to at least the number of physical disks in the database.
To improve performance in update- intensive workloads, configure more page-cleaner agents. Performance improves if more page-cleaner agents are available to write dirty pages to disk. This is also true when there are many data-page or index-page writes in relation to the number of asynchronous data-page or index-page writes.
Consider the following factors when setting the value for DB parameter NUM_IOCLEANERS :
If it is a query-only database that will not have updates, set this parameter to be zero (0). The exception would be if the query workload results in many TEMP tables being created (you can determine this by using the explain utility).
If transactions are run against the database, set this parameter to be between one and the number of physical storage devices used for the database.
Environments with high update transaction rates may require more page cleaners to be configured.
Buffer pool sizes
Environments with large buffer pools may also require more page cleaners to be configured.
You may use the database system monitor to help you tune this configuration parameter using information from the event monitor about write activity from a buffer pool:
The parameter can be reduced if both of the following conditions are true:
pool_data_writes is approximately equal to pool_async_data_writes
pool_index_writes is approximately equal to pool_async_index_writes.
The parameter should be increased if either of the following conditions are true:
pool_data_writes is much greater than pool_async_data_writes
pool_index_writes is much greater than pool_async_index_writes.
The sort heap is a piece of memory where DB2 UDB stores data during a sort. When the sort heap is big enough the sort can be done in one operation called piped sort. If the sort heap is not big enough to store the whole data for the sort then a temporary table is created in the buffer pool and sort will be divided in several pieces, which is more time consuming. So try to avoid sorts that do not fit into the sort heap. The application developer has to verify if the sort is really necessary or if he can perform the search query without a order by clause. The DBA should monitor if the sort heap is big enough and if not he needs to increase the heap size when a better performance is necessary and more memory is available.
Be careful when increasing the sort heap. Because the heap is part of the agent memory, for each connection the space is allocated and that multiples the memory required by the number of agents.
To find out if you have a sort performance problem, look at the total CPU time spent on sorting compared to the time spent for the whole application using, for example, the snapshot monitor for database and database manager.
If total sort time is a large portion of CPU time for the application, then look at the following values, which are also shown by default:
Percentage of overflowed sorts
This variable (on the performance details view of the Snapshot Monitor) shows the percentage of sorts that overflowed. If the percentage of overflowed sorts is high, increase the SORTHEAP . To find out if there were any post threshold sorts, use the Snapshot Monitor.
Post threshold sorts
If post threshold sorts are high, it indicates that more sort memory is requested than defined with the SHEAPTHRES parameter. So any sort following after this value that is reached receives less sort memory than defined by the SORTHEAP parameter. To avoid this situation increase SHEAPTHRES and/or decrease SORTHEAP .
In general, overall sort memory available across the instance ( SHEAPTHRES ) should be as large as possible without causing excessive paging. Although a sort can be performed entirely in sort memory, this might cause excessive page swapping. In this case, you lose the advantage of a large sort heap. For this reason, you should use an operating system monitor to track changes in system paging whenever you adjust the sorting configuration parameters. Also note that in a piped sort, the sort heap is not freed until the application closes the cursor associated with that sort. A piped sort can continue to use up memory until the cursor is closed.
An indicator for problems is lock time-outs or long respond times reported from the users. The database parameter LOCKTIMEOUT specifies how long an application should wait for a resource that is locked by another application. The value -1 defines an infinite wait time. Any positive values specifies the wait time in seconds. The LOCKTIMEOUT has nothing to do with deadlocks. DB2 recognizes deadlocks using separate process. Setting LOCKTIMEOUT to infinite waiting will cause the application to wait forever, but will not result in a deadlock situation.
Use the snapshot monitor described in "Snapshot monitor" on page 238 to look for lock time-out and deadlock situations. The locks currently held can be monitored with the command:
DB2 GET SNAPSHOT FOR LOCKS ON database name
In Example 7-10 is the first part of the snapshot output of our sample database TRADE3DB. This part shows the current lock situation on the database. You can see that currently three locks are held and that no other application is waiting for any of the locked objects.
Example 7-10: Snapshot for locks (database part)
Database Lock Snapshot Database name = TRADE3DB Database path = /home/db2inst1/db2inst1/NODE0000/SQL00002/ Input database alias = TRADE3DB Locks held = 3 Applications currently connected = 3 Agents currently waiting on locks = 0 Snapshot timestamp = 11-24-2003 15:08:46.134255
From the same output, you can see which application is currently locking one or more objects in the database. Example 7-11 is the output shown the application that held the three locks. The status of the application is UOW Waiting. DB2 UDB is waiting for the user to do something. The output of the snapshot shows all connected applications, so you have a chance to see which application is waiting for a lock.
Example 7-11: Snapshot for locks (application part)
Application handle = 18 Application ID = G9012775.MA0D.017404230648 Sequence number = 0018 Application name = javaw.exe CONNECT Authorization ID = DB2INST1 Application status = UOW Waiting Status change time = 11-24-2003 15:08:43.201674 Application code page = 1208 Locks held = 3 Total wait time (ms) = 0
The list of currently held locks follows the list of connected applications as shown in Example 7-12. From the output you can see what object is locked and in what mode.
Example 7-12: Snapshot for locks (list of locks)
List Of Locks Lock Name = 0x00000001000000010001940056 Lock Attributes = 0x00000000 Release Flags = 0x40000000 Lock Count = 1 Hold Count = 0 Lock Object Name = 0 Object Type = Internal V Lock Mode = S Lock Name = 0xA6B2A69FA4A17C7D9175505041 Lock Attributes = 0x00000000 Release Flags = 0x40000000 Lock Count = 1 Hold Count = 0 Lock Object Name = 0 Object Type = Internal P Lock Mode = S Lock Name = 0x00030008000000000000000054 Lock Attributes = 0x00000000 Release Flags = 0x00000001 Lock Count = 1 Hold Count = 1 Lock Object Name = 8 Object Type = Table Tablespace Name = TRADEDAT Table Schema = DB2INST1 Table Name = ACCOUNTEJB Mode = IN
If there are a lot of locks held or if DB2 UDB finds a lot of deadlock situations, it indicates that something with the application is wrong. From the database side, you can check if there is a long running transaction that causes the problem. Try to tune the system to make this transaction faster. On the application side check if the isolation level is needed and try to have short transaction or to commit as often as possible to reduce locking. When changing the isolation level, both DBA and application developer should work together to experiment with different isolation levels.
Some error situations occur because of lock escalation used by DB2 UDB to reduce locks. There are two parameters in the database configuration responsible for lock escalation. With LOCKLIST you define the amount of memory used for the locks. MAXLOCKS parameter indicates the percentage an application can hold of the available locks. If an application requests more locks than specified by this parameter then lock escalation occurs.
The database manager determines which locks to escalate by looking through the lock list for the application and finding the table with the most row locks. If after replacing these with a single table lock, the MAXLOCKS value is no longer exceeded, lock escalation will stop. If not, it will continue until the percentage of the lock list held is below the value of MAXLOCKS. The MAXLOCKS parameter multiplied by the MAXAPPLS parameter cannot be less than 100.
As a recommendation set MAXLOCKS to 2 * 100 / MAXAPPLS.
Lock escalation can result in deadlock and time-out situations. Look into the DB2DIAG.LOG file and search for lock escalation, especially when deadlocks or time-outs are reported.
The database parameter LOGBUFSZ allows you to specify the amount of the database heap (defined by the DBHEAP database parameter) to use as a buffer for log records before writing these records to disk. The log records are written to disk when one of the following occurs:
A transaction commits or a group of transactions commit, as defined by the MINCOMMIT configuration parameter.
The log buffer is full.
As a result of some other internal database manager event.
This parameter must also be less than or equal to the DBHEAP database parameter. Buffering the log records will result in a more efficient logging file I/O because the log records will be written to disk less frequently and more log records will be written at each time.
Increase the size of this buffer area if there is considerable read activity on a dedicated log disk, or there is high disk utilization. When increasing the value of this parameter, you should also consider the DBHEAP parameter since the log buffer area uses space controlled by the DBHEAP parameter.
You may use the snapshot monitor for the application to determine how much of the log buffer space is used for a particular transaction (or unit of work). Refer to the unit of work log space used monitor element.
When using DMS tablespace it is necessary to check if there is enough space available in the tablespaces. Use the snapshot monitor for tablespaces and look for the available and used pages in the containers. With the command ALTER TABLESPACE name_of_tabblespace ADD CONTAINER... you can add containers to a tablespace.
With DB2 UDB V8 it is now possible to drop containers (if there is enough space in the other containers to store the data of the container you want to drop), reduce the size of containers, and add containers without rebalancing the data.
|< Day Day Up >|
DB2 UDB V8 and WebSphere V5. Performance Tuning and Operations Guide2004
Authors: Chen W. J.
Published year: 2004