Memory View (DB2 LUW Performance Analyst)

From DBArtisan
Jump to: navigation, search

Go Up to IBM DB2 (W/U/L) Performance Analyst statistics

The Memory category of DB2 Performance Analyst includes the following tabbed pages:

Contents

Home

The Home page of the Memory performance category view has statistics in the following categories:

Key Ratio Analysis Pane

Database performance analysts typically use one of two methods for examining the performance levels of a database - ratio-based or wait/bottleneck-based. Ratio-based analysis involves examining a number of key database ratios that can be used to indicate how well a database is running. Performance ratios serve as very good roll-up mechanisms for busy DBAs to use for at-a-glance performance analysis. Many DBAs have large database farms to contend with and cannot spend time checking detailed wait-based analysis outputs for each and every database they oversee. Succinctly presented performance ratios can assist in such situations by giving DBAs a few solid indicators that can be quickly scanned to see if any database needs immediate attention.

While there are certainly many opinions as to what rules to follow, there are some standards you should always adhere to. To start with, many of the formulas that make up ratio-based analysis must be derived from delta measurements instead of cumulative statistics. Most of the global ratios that a DBA will examine come from the DB2 Snapshot Monitor. The Snapshot Monitor returns various metrics for the instance and database since the start of monitoring. Some of the metrics are cumulative (i.e., counters) and others are instantaneous (i.e., gauges).

In the case of cumulative metrics, the values can grow quite large over the monitoring period and the ratios calculated on these metrics can hide the skews that may occur during the monitoring. However, if delta statistics are used (taking, for a specified sampling period, the before and after counts of each statistic that make up a ratio), then an accurate and current portrayal of the database activity over the sampling period can be had.

A final thing to remember about using ratio-based analysis is that, while there are a number of rules of thumb that can be used as starting points to begin the evaluation of database performance, DBAs must determine each DB2 databases' individual personality with respect to the key performance ratios. Some hard and fast rules simply do not apply to every database. The danger in using blanket ratio standards is that they can lead the DBA to haphazardly take action, which can at times contribute nothing to the situation, and sometimes even degrade performance.

The following memory ratios are used on the Performance Analyst Key Ratio Analysis section to succinctly communicate the general overall memory performance levels of the monitored database:

The following topics, available on this pane, duplicate statistics available on the Home View (DB2 LUW Performance Analyst) page:

Data Page Hit Ratio

This metric is the percentage of all data reads satisfied because the page was already available in a bufferpool.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Home > Key Ratio Analysis Pane

Metrics

The index page hits are used in conjunction with data page hits to calculate the overall buffer pool hit ratio. Use this element, in conjunction with the index page hit ratio when tuning the overall buffer pool hit ratio.

Private Workspace Hit Ratio

Each application executing on the database has access to the private workspace of the agents working for it.

The private workspace hit ratio is a percentage indicating how well the private SQL workspace is helping to avoid initialization of sections for SQL statements that are about to be executed. A high ratio indicates it is successful in avoiding this action.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Home > Key Ratio Analysis Pane IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Caches Tab

Correction

Shared workspace memory is allocated from the application heap (app_ heap_sz database configuration parameter) and increasing this may improve the private workspace hit ratio.

Index Page Hit Ratio

The index page hit ratio is the percentage of all index reads that were satisfied because the page was already available in a bufferpool.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Home > Key Ratio Analysis Pane

Metrics

The index page hits are used in conjunction with data page hits to calculate the overall buffer pool hit ratio. Use this element, in conjunction with the data page hit ratio when tuning the overall buffer pool hit ratio.

Bottleneck Analysis Pane

The Bottleneck Analysis section identifies the number of times when overflows or over-utilization may have occurred in areas of memory. An overflow occurs when the usage of a memory area goes beyond the allocated amount.

Overflows are serious bottlenecks and may cause unnecessary lock escalations, out of memory errors or performance degradations.

The following statistics are used on the Performance Analyst for DB2 Memory home page to succinctly communicate the general overall performance levels of the memory structures:

The following statistics, available on this pane, dupicte statistics availabe on the Bottleneck Analysis Pane of the Home View (DB2 LUW Performance Analyst) page.

Catalog Cache Overflows

The catalog cache overflow statistic is the number of times the catalog cache overflowed the bounds of its allocated memory.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Catalog cache overflows can cause unnecessary lock escalations. This can result in loss of concurrency, or ‘out of memory’ errors from other heaps allocated to the database’s shared memory. Overflows of the catalog cache can also cause performance degradation. DB2 reclaims the catalog cache space by evicting table descriptor information for tables, views, or aliases and/or authorization information that is not currently being used by any transaction.

Correction

Use this element with the catalog cache high watermark (Memory> Caches Drilldown) to determine whether the size of the catalog cache needs to be increased to avoid overflowing. If the number of overflows is large, the catalog cache may be too small for the workload. Enlarging the catalog cache can improve its performance. If the workload includes transactions that compile a large number of SQL statements referencing many tables, views, aliases, user-defined functions, or stored procedures in a single unit of work, then compiling fewer SQL statements in a single transaction can improve the performance of the catalog cache. Or, if the workload includes binding packages that contain many SQL statements referencing many tables, views, aliases, user-defined functions, or stored procedures, you can try splitting packages so that they include fewer SQL statements to improve performance.

Hash Join Small Overflows

The hash join small overflows statistic is the number of times that hash join data exceeded the available sort heap space by less than 10%.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

If the value of hash join small overflows is greater than 10% of hash join overflows, then you should consider increasing the sort heap size. Values at the application level can be used to evaluate hash join performance for individual applications.

Correction

Increase the sortheap database configuration parameter to reduce the number of overflows.

Package Cache Overflows

The package cache overflows metric is the number of times that the package cache overflowed the bounds of its allocated memory.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Package cache overflows can cause unnecessary lock escalations. This can result in loss of concurrency, or ‘out of memory’ errors from other heaps allocated to the database’s shared memory. Overflows of the package cache can also cause performance degradation.

Correction

Use this element with the package cache high watermark (Memory > Caches Drilldown) to determine whether the size of the package cache needs to be increased to avoid overflowing.

Private Workspace Overflows

The private workspace overflows statistic is the number of times that private workspaces overflowed the bounds of their allocated memory.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Private workspace overflows can cause performance degradation as well as ‘out of memory’ errors from other heaps allocated to the agent’s private memory.

Correction

Use this element with the private workspace high watermark (Memory -> Caches Drilldown) to determine whether the size of the private workspace needs to be increased to avoid overflowing.

Shared Workspace Overflows

The shared workspace overflows metric is the number of times that shared workspaces overflowed the bounds of their allocated memory.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Overflows of shared workspaces can cause performance degradation. Overflows can also ‘out of memory’ errors from the other heaps allocated out of application’s shared memory.

Correction

Use this element with the shared workspace high watermark (Memory > Caches Drilldown) to determine whether the size of the shared workspaces need to be increased to avoid overflowing.

Memory Analysis Pane

This section helps you to track memory usage for database processes through Memory Analysis Heaps.

Note: The memory management for DB2 (and hence the metrics) varies between platforms. You only see the memory metrics at the database level for UNIX system. If you are tracking the memory metrics for a Windows system, please refer to Instance -> Memory Pool Drilldown.

Memory Analysis Heaps

Under normal operating conditions you can track the memory usage for the following elements:

Application Heap

Database Heap

Application Control

Lock Manager Heap

Backup/Restore/Utility Heap

Statistics Heap

Package Cache Heap

Catalog Cache Heap

DFM Heap

Query Heap

Database Monitor Heap

Statement Heap

FCMBP Heap

Import Pool

Other Memory

Buffer Pool Heap


Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Home > Memory Analysis Pane

Metrics

The various memory heap usages are useful when tuning the corresponding database configuration parameters.

Correction

To see greater details on the current utilizations, maximum available, and high watermarks, go to the Memory Pools tab of the Memory detail view. Based on these you can make decisions about whether you need to increase or decrease the maximum allocations for the heaps (mostly through database configuration parameters)

SQL Analysis Pane

A lot of a database's overall performance can be attributed to SQL statement execution. Poorly optimized SQL statements can drag an otherwise well-configured server down in terms of user response times.

Before you can identify problem SQL in your database, you need to understand the level of SQL activity that is occurring in the database. A high level overview of the type of SQL activity helps determine not only they types of statements that are being executed but also the type of internal activity they may be causing.

The following indicators are used on the Performance Analyst Memory page to succinctly communicate the general overall performance level of the monitored database. They duplicate statistics available on the SQL Analysis Pane of the Home View (DB2 LUW Performance Analyst) page:

Workload Analysis Pane

When your phone starts ringing with complaints of performance slowdowns, one of the first things you should get a handle on is:

  • Who is currently connected to the database?
  • What resources are they using?
  • What are they currently executing?

The Workload Analysis section of the Performance Analyst Memory page provides insight into the leading applications that caused memory overflows to disk or lock escalations. Drill down's are available so you can easily get detailed information into what each leading application is currently involved with.

Application Activity Analysis Pane

Top memory issues are displayed in the following categories:

  • Top Locks
  • Top Lock Escalations
  • Top Cache Overflows
  • Top Sort Overflows
  • Top Workspace Overflows

Metrics

The list of top memory hogs can help you quickly identify the applications that are causing memory usage problems. You can drill down into the Memory tab views to see how these applications are using memory compared to their peers and the overall memory being used by the database. This is a good place to start, if you think that the memory problems you are noticing on the database may be as a result of poorly written applications rather than database configuration or overall system memory availability.

Caches Tab

The Caches tab of the Memory Detail page offers detailed views of cache usage metrics and includes the following statistics:

The following statistics, available on this page, dupication statistics available on the Key Ratio Analysis Pane of the Home View (DB2 LUW Performance Analyst) page:

The following statistic, available on this page, dupication statistics available on the Caches Tab of the Memory View (DBArtisan - DB2 LUW Performance Analyst) page:


Application Details

The Application Details section shows the list of applications connected to the database and the various cache usage metrics for each application. It also shows the rolled up metrics at the database level in the same list. You can select the type of cache monitoring you intend to perform for the applications from the following:

  • Catalog Cache: Shows the details of all catalog cache activity for connected applications
  • Package Cache:Displays the details of all package cache activity for connected applications
  • Shared Workspace:Gives the details of all shared workspace activity for connected applications

Private Workspace:This shows the details all of private workspace activity for connected applications

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Caches Tab

Correction

Depending on the category of cache details you select, you can view cache activity associated with the applications. This section is very useful in isolating the applications and the cache areas you need to tune by comparing the cache performance for all the applications and also the overall cache performance at the database level.

Catalog Cache Usage Details

This section shows detailed catalog cache usage metrics for the selected item. The metrics presented here include:

  • Catalog Cache Lookups: The number of times that the catalog cache was referenced to obtain table descriptor information or authorization information
  • Catalog Cache Inserts: The number of times that the system tried to insert table descriptor or authorization information into the catalog cache.
  • Catalog Cache Overflows: The number of times that the catalog cache overflowed the bounds of its allocated memory
  • Catalog Cache High Watermark: The largest size reached by the catalog cache

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Caches Tab

Metrics

These metrics are used for calculating the catalog cache hit ratio. They also give you an estimate of catalog access activity occurring in the database. You can use the catalog cache overflow metric with the catalog cache high watermark to determine if the catalog cache size is too small. If the catalog cache overflows frequently, you consider increasing the catalog cache size.

Correction

If catalog cache overflows are large, the catalog cache may be too small for the workload. Enlarging the catalog cache may improve its performance. If the workload includes transactions that compile a large number of SQL statements referencing many tables, views, aliases, user-defined functions, or stored procedures in a single unit of work, then compiling fewer SQL statements in a single transaction may improve the performance of the catalog cache. Or if the workload includes binding packages that contain many SQL statements referencing many tables, views, aliases, user-defined functions, or stored procedures, you can try splitting packages. If they include fewer SQL statements, performance can improve. Monitor the catalog cache metrics for your normal workload. If you see frequent overflows, you can increase the value of catalogcache_sz database configuration parameter to the catalog cache high watermark/4096 (rounded up to whole integer).

Package Cache Usage Details

The Package Cache Usage Details section shows the detailed package cache usage metrics for the selected item. The metrics presented here include:

  • Package Cache Lookups: The number of times that an application looked for a section or package in the package cache. At a database level, it indicates the overall number of references since the database was started, or monitor data was reset.
  • Package Cache Inserts: The total number of times that a requested section was not available for use and had to be loaded into the package cache. This count includes any implicit prepares performed by the system.
  • Package Cache Overflows: The number of times the package cache overflowed the bounds of its allocated memory
  • Package Cache High Watermark: The largest size reached by the package cache.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Caches Tab

Metrics

These metrics are used to calculate the package cache hit ratio. They also give you an estimate of package cache access activity occurring on the database. You can use the package cache overflow metric with the package cache high watermark to determine if the package cache size is too small. If the package cache overflows frequently, you may need to increase the package cache size.

Correction

Monitor the package cache metrics for your normal workload. If you see frequent overflows, you can increase the value of the pckcachesz database configuration parameter to the package cache high watermark/4096 (rounded up to whole integer).

Private Workspace Usage Details

This section shows the detailed private workspace usage metrics for the selected item. The metrics presented here include:

  • Private Workspace Lookups: Lookups of SQL sections by an application in its agents' private workspace.
  • Private Workspace Inserts: Inserts of SQL sections by an application into the private workspace.
  • Private Workspace Overflows: The number of times that the private workspaces overflowed the bounds of its allocated memory.
  • Private Workspace High Watermark: The largest size reached by the Private Workspace.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Caches Tab

Metrics

These metrics are used to calculate the private workspace hit ratio. They also give you an estimate of private workspace access activity occurring on the database. When the private workspace overflows, memory is temporarily borrowed from other entities in agent private memory. This can result in memory shortage errors from these entities or possibly performance degradation.

Correction

You can reduce the chance of overflow by increasing APPLHEAPSZ.

Shared Workspace Usage Details

This section shows the detailed shared workspace usage metrics for the selected item. The metrics presented here include:

  • Shared Workspace Lookups: Lookups of SQL sections by applications in shared workspaces.
  • Shared Workspace Inserts: Number of inserts of SQL sections by applications into shared workspaces.
  • Shared Workspace Overflows: The number of times that shared workspaces overflowed the bounds of their allocated memory.
  • Shared Workspace High Watermark: The largest size reached by shared workspaces.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Caches Tab

Metrics

These metrics are used to calculate the shared workspace hit ratio. They also give you an estimate of shared workspace access activity occurring on the database. When the shared workspace overflows, memory is temporarily borrowed from other entities in application shared memory. This can result in memory shortage errors from these entities or possibly performance degradation.

Correction

You can reduce the chance of overflow by increasing APPL_CTL_HEAP_SZ.

Memory Pools Tab

The Memory Pools tab offers the following detailed views:

Application Details

This section shows the list of applications connected to the database. You can select an application to show the breakdown of the memory pool usage by the application.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Memory Pools Tab

Memory Pool Details

This section shows the memory pool usage details for the selected application (or database level) in the application details section. The metrics shown here include:

  • Pool ID: This is the kind of memory pool. Each pool type is only shown once
  • Pool Current Size: The current size of a memory pool.
  • Pool Max Size: The internally configured size of a memory pool in DB2.

Pool High Watermark: The largest size of a memory pool since its creation.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Memory Pools Tab IBM DB2 (W/U/L) Performance Analyst statistics > Instance View (DB2 LUW Performance Analyst) > Memory Pools tab

Metrics

The nature of memory_pool data elements varies between platforms. On Windows systems, no memory usage is reported at the database level, while on UNIX systems, memory is reported at the database level. Instead of reporting this memory at the database level, the system monitor for Windows systems reports it in instance -level snapshots. This divergence in reporting is due to differences in the underlying memory architecture between Windows systems and UNIX systems.

Memory Pool Usage

This section shows the memory pool usage details of the selected memory pool over time including the current size of the memory pool, its high watermark, and the maximum size of the memory pool.

Location

Memory Pool Utilization

This section shows the percentage utilization of the selected memory pool.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Memory Pools Tab IBM DB2 (W/U/L) Performance Analyst statistics > Instance View (DB2 LUW Performance Analyst) > Memory Pools tab

Metrics

You can use this metric to see if a memory pool is nearly full. You can diagnose the problems with specific memory pools by monitoring their utilization over time.

Correction

If the value of the pool utilization is consistently close to or exceeds 100%, you may need to increase the configuration parameter associated with that pool.

Lock Memory Tab

The Lock Memory tab of the Memory Detail view shows the following statistics:

Lock Escalations

This section shows the metrics for lock escalations in the database. The metrics shown here are:

  • Lock Escalations: The number of times that locks have been escalated from several row locks to a table lock.
  • Exclusive Lock Escalations: The number of times that locks have been escalated from several row locks to one exclusive table lock, or the number of times an exclusive lock on a row caused the table lock to become an exclusive lock.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Memory View (DBArtisan - DB2 LUW Performance Analyst) > Lock Memory Tab

Metrics

A lock is escalated when the total number of locks held by an application reaches the maximum amount of lock list space available to the application, or the lock list space consumed by all applications is approaching the total lock list space. The amount of lock list space available is determined by the maxlocks and locklist configuration parameters. When an application reaches the maximum number of locks allowed and there are no more locks to escalate, it will then use space in the lock list allocated for other applications. When the entire lock list is full, an error occurs. This data item includes a count of all lock escalations, including exclusive lock escalations. There are several possible causes for excessive lock escalations: The lock list size (locklist) may be too small for the number of concurrent applications The percent of the lock list usable by each application (maxlocks) may be too small One or more applications may be using an excessive number of locks.

Correction

To resolve these problems, you may be able to: Increase the locklist configuration parameter value. See the Administration Guide for a description of this configuration parameter. Increase the maxlocks configuration parameter value. See the Administration Guide for a description of this configuration parameter. Identify the applications with large numbers of locks (see locks_held_top), or those that are holding too much of the lock list, using the following formula: (((locks held * 36) / (locklist * 4096)) * 100) and comparing the value to maxlocks. These applications can also cause lock escalations in other applications by using too large a portion of the lock list. These applications may need to resort to using table locks instead of row locks, although table locks may cause an increase in lock_waits and lock_wait_time.

I/O

In addition to a Home page, the I/O category of DB2 Performance Analyst includes the following tabbed pages are available on the I/O Detail view:

Home

The Home Page of the I/O performance category page displays the following vital DB2 I/O statistic categories:

Key System Analysis Pane

The root cause of many database problems can be attributed to I/O-related operations. I/O monitoring is essentially a review of your physical database design. All physical storage characteristics and placements, table and index designs, and the speed with which all processes works are on display when I/O is monitored. Because a database's main index of performance is measured by how fast I/O needs are satisfied, it is your responsibility to quickly determine if a reported database slowdown is I/O related.

The following statistics are used on the Performance Analyst for DB2 I/O Home Page to succinctly communicate the general overall performance levels of I/O:

Async Read Ratio

Async read ratio is the percentage of all index and data reads that performed asynchronously by the database manager prefetchers.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > Key System Analysis Pane

Metrics

Prefetching refers to the technique whereby the Database Manager can read several pages on the physical reads device simultaneously into a bufferpool in anticipation of access to these pages. Since each physical read operation is costly, a 50% async read ratio is desirable. It shows that prefetchers are working well and read waits are minimal.

Correction

The num_ioserver database configuration parameter defines the number of prefetchers that are available for the database. To get an optimal async read ratio, you should set the num_ioserver database configuration parameter to 1-2 more than the number of physical storage devices used by the database to ensure that asynchronous I/O is occurring. This parameter should be changed judiciously. Having too many prefetchers invariably results in high system I/O because prefetchers can read many more pages than required into the bufferpools.

Async Write Ratio

Async write ratio is the percentage of all index and data writes that were performed asynchronously by the database manager bufferpool page cleaners.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > Key System Analysis Pane

Metrics

Page cleaners write changed pages from bufferpool to disk before the space in the bufferpool is needed by a database agent. If the page cleaners are not working well, the problem may manifest itself in two ways: The database agents need to synchronously free space in bufferpools resulting in poorer response time. If the system crashes, recovery time is greater because there will be a large number of pages that would not have been committed to disk at the time of the crash and they require processing for a large number of log records. An overall async write ratio of 50% is desirable for most transactional database systems. If your database is 'query only' (i.e., updates are rarely or never performed), it is fine to have an async write ratio of 0 to less than 50%. Even in 'query only' databases if the queries create temp tables on the database for intermediate results, a 30-40% async write ratio is desirable.

Correction

The async write ratio can be tuned using the num_iocleaners database configuration parameter, which specifies the number of asynchronous page cleaners for a database. Increasing the number of page cleaners generally results in a higher async write ratio. The following rules of thumb should be followed when tuning this parameter: For most transactional systems set this parameter to between one and the number of physical storage devices used by the database. For workloads that consists mainly of simple reads from the database, set this parameter to zero. When you define very large bufferpools on your database you may need to increase the num_iocleaners Monitor the victim page cleaners% metric when tuning the num_iocleaners.

Victim Page Cleaners %

The victim page cleaners % metric is the percentage of times the page cleaners were invoked because synchronous write was needed during the victim buffer replacement in the database.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > Key System Analysis Pane

Metrics

Usually this metric should be maintained around the 50-60% level. If this ratio much lower than 50%, it can indicate that you have defined too many page cleaners. If the chngpgs_thresh database configuration parameter is set too low, the page cleaners may be writing out pages that will be dirtied later. Aggressive cleaning defeats one purpose of the bufferpool, that is to defer writing to the last possible moment. If this ratio is high, you may have too few page cleaners defined. This can increase recovery time after failures.

Correction

You may increase or decrease the chngpgs_thresh database configuration parameter to bring the victim page cleaner % to optimal range. Usually, for databases with heavy transaction workloads, chngpgs_thresh should be set to around 60%. For a database with only a few very large tables you can try and increase this parameter to a higher value to see if you get performance improvements.

Log Space Cleaners Triggered

The log space cleaners triggered metric is the number of times a page cleaner was triggered because log space usage reached a predefined threshold for the database.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > Key System Analysis Pane

Metrics

The log space cleaners are triggered every time the space used by the DB2 log reaches the limit set in the softmax database configuration parameter. This parameter specifies the percentage of primary log size at which the cleaners are triggered. By default this parameter is set to 100. A low rate of log space cleaners getting triggered may indicate that logs are not being written to disk frequently enough and that you may need to process a large number of log records and redundant log records in the event of a crash recovery. High rates of log space cleaners getting triggered can indicate that your primary log is too small or that you have set the softmax too high. In either case, a very high rate of log cleaners being triggered may adversely impact database performance. It is important to keep in mind, however, that more page cleaner triggers and more frequent soft checkpoints increase the overhead associated with database logging.This can have an impact on the performance of the Database Manager. Also, more frequent soft checkpoints may not reduce the time required to restart a database, if you have: Very long transactions with few commit points. A very large bufferpool and the pages containing the committed transactions are not written back to disk very frequently. The use of asynchronous page cleaners can help avoid this situation. In both of these cases, the log control information kept in memory does not change frequently and there is no advantage in writing the log control information to disk, unless it has changed

Correction

Examine this metric together with other page cleaner metrics and the async write ratio to determine if excessive numbers of log page cleaners are being triggered. If this is true, you need to either increase your primary log size or adjust the softmax parameter to a higher value.

Note: Decreasing the log page cleaner triggering rate can impact the overall crash recovery time so it needs to be done judiciously.

Victim Page Cleaners Triggered

The victim page cleaners triggered statistic is the number of times the page cleaners were invoked because synchronous write was necessary during the victim buffer replacement in the database.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > Key System Analysis Pane

Metrics

If the chngpgs_thresh database configuration parameter is set too low, the page cleaners may be writing out pages that will be dirtied later. Aggressive cleaning defeats one purpose of the bufferpool, which is to defer writing to the last possible moment. If this ratio is high, it may indicate that you have too few page cleaners defined. Too few page cleaners increases recovery time after failures.

Correction

You may increase or decrease the chngpgs_thresh database configuration parameter to bring the victim page cleaner triggering rate to optimal range. For databases with heavy transaction workloads, chngpgs_thresh should be set to around 60%. For a database with only a few very large tables you can try and increase this parameter to a higher value to see performance improvements.

Threshold Cleaners Triggered

The threshold cleaners triggered metric is the number of times a page cleaner was invoked because a bufferpool had reached the dirty page threshold criterion for the database.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > Key System Analysis Pane

Metrics

The dirty page threshold is set by the chngpgs_thresh database configuration parameter. It is a percentage applied to the bufferpool size. When the number of dirty pages in the pool exceeds this value, the cleaners are triggered. If this value is set too low, pages might be written out too early, requiring them to be read back in. If set too high, then too many pages may accumulate, requiring users to write out pages synchronously.

Correction

For databases with frequent update transactions, you can generally ensure that there are enough clean pages in the bufferpool by setting the chngpgs_thresh parameter value to be equal-to or less-than the default value. A percentage larger than the default can help performance if your database has a small number of very large tables.

Bottleneck Analysis Pane

High prefetch waits and file closures are good indicators of I/O waits at the database. Often, heavy I/O activity may not be due to non-optimal settings of the database configuration parameters. Rather, the underlying cause can be poor physical layout of a few tables/tablespaces or a few poorly written/suboptimal database applications.

It is usually best to try and tune individual objects/applications before attempting to tune database configuration parameters. Database configuration changes can have a profound impact on every application and object that resides in the database and if there are only a couple of apps/objects dragging down the performance of the entire database, it may be best to first isolate and tune these.

I/O Bottleneck analysis provides information on applications and tablespaces that are performing the highest number of read and write operations on the database at a glance. It also shows the bufferpool I/O metrics that point to direct I/O waits.

The following statistics are used on the Performance Analyst for DB2 I/O home page to succinctly communicate the general overall performance levels of I/O:

Hottest Tablespaces

This section displays the list of top three tablespaces with the highest number of physical reads and writes.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > Bottleneck Analysis Pane

Metrics

Understanding the storage-level hot spots of a database is beneficial for a two reasons. First, you can get a feel for overworked physical disks by viewing I/O statistics at the tablespace level. If a particular tablespace is under too much strain, you can attempt to relocate tablespace containers to other less-used devices. You can also try to create new tablespaces on different disks and move hot objects to them (assuming, of course, you have extra disks available). Second, if you have used standard DBA practices and placed indexes in their own tablespace, you can review the I/O statistics for the hottest tablespaces and see if the indexes are actually being used.

Correction

Some areas to consider when reviewing the hottest tablespaces display are: A temp tablespace showing high volumes of physical I/O could indicate a problem with excessive sort overflows. Quickly review all physical I/O for each drive/file system at the OS level and get a feel for the overworked disks on your server. If you have underutilized disk drives (with their own controllers), then you should consider relocating some tablespaces/containers that exhibit high I/O characteristics to those drives To minimize contention at the physical disk level, and to improve performance overall, it is generally recommended that a database have its tablespaces and log files physically separated onto different drives/file systems.

Hottest Apps

This metric shows the top three applications connected to the database with the highest number of physical reads and writes.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > Bottleneck Analysis Pane

Metrics

A few applications with suboptimal queries/database accesses can drag down the performance of the entire database. The hottest apps section allows you to get a good feel for the applications that are causing the highest number of reads and writes on the database. It is also a good indicator of where you will reap the greatest benefit when tuning the application’s performance.

Correction

Drill down to the application I/O pages and see how the high I/O applications are behaving compared to other applications.

Check the SQL Snapshot Metrics for the high read/write apps to see what queries these applications are running on the database and try to optimize these queries.

Total Database Files Closed

Both SMS tablespaces and DMS tablespace file containers are treated as files in the Database Manager's interaction with the operating system, and file handles are required. There is a limit to the number of files that can be open concurrently by each database agent and a file is closed when the limit of open files for an agent is reached and the agent needs to open another file. This metric gives the number of times a database file was closed because the limit for concurrently open files was reached.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > Bottleneck Analysis Pane

Metrics

Both SMS table spaces and DMS tablespace file containers are treated as files in the Database Manager's interaction with the operating system. More files are generally used by SMS tablespaces compared to the number of containers used for a DMS file table space. Thus, if you use SMS tablespaces, the database needs to open a larger number of files compared to what would be required for DMS file tablespaces. Closing and opening files is a very expensive operation and a large number of closed files can adversely affect the performance of the database. It is recommended that the number of files closed be as small as possible (preferably 0).

Correction

If you notice that files are being closed during the monitoring, first check the rate at which the files are being closed at the database level, from the I/O drill-down. If you see files being closed frequently, increase the maxfilop database configuration parameter. Note that the operating system on which the database is running also has a limit on the number of files that can be opened concurrently. Make sure that you do not increase the maxfilop parameter beyond the operating system limit.

Total Time Spent Waiting for Prefetchers

This is the total time database applications spent waiting for prefetchers to finish loading.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > Bottleneck Analysis Pane

Correction

If you see a high number for this metric, it indicates there is a bottleneck in the prefetcher operations. Experiment with changing the number of I/O servers (num_ioserver) and the I/O server sizes.

SQL Analysis Pane

Most of database's I/O performance can be attributed to SQL statement execution. Poorly optimized SQL statements can drag down an otherwise well-configured server in terms of user response times. This section shows a snapshot of row level SQL operations that have been performed on the database since you started monitoring. This gives you an insight into the row level of read/write activity that is currently occurring within the database and comparing this to the prefetcher, and cleaner activity allows you to relate the two.

The following statistics are used on the Performance Analyst for DB2 I/O home page to succinctly communicate the general overall performance levels of I/O:

Rows Deleted

The rows deleted metric is the total number of row deletions attempted on the database. It includes the internal row deletions that were caused by constraints or triggers.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > SQL Analysis Pane IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > SQL Analysis Pane

Rows Inserted

The rows inserted metric is the total number of row insertions attempted on the database. This metric includes the internal row insertions that were caused by triggers.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > SQL Analysis Pane IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > SQL Analysis Pane

Rows Selected

The rows selected statistic is the total number of rows selected at the database. This metric does not include a count of rows accesses for operations such as COUNT(*) or joins.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > SQL Analysis Pane IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > SQL Analysis Pane

Rows Updated

The rows updated metric is the total number of row updates attempted on the database. This metric includes the internal row updates that were caused by triggers or constraints

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > SQL Analysis Pane IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > SQL Analysis Pane

Rows Read

The rows read statistic is the number of rows read from the table. This count is not the number of rows that were returned to the calling application. Rather, it is the number of rows that had to be read in order to return the result set.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > SQL Analysis Pane IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > SQL Analysis Pane

Non Buffered I/O Activity

Some database operations such as backups, loads, restores, LOB (large object I/O) bypass the bufferpools and read and write directly to storage. Sometimes, even if your database I/O configurations and all of the other activities are within the normal thresholds, the I/O performance may be poor because LOB reads/writes or load/backup/restore operations are causing or waiting on I/O contentions on your storage devices.

The non buffered I/O activity section allows you to quickly identify the level of direct I/O activity on the database. The level of direct I/O activity is succinctly presented using the following metrics:

Direct Reads from the Database

The direct reads from the database metric identifies the number of read operations that did not use a bufferpool. Direct reads are performed for the following database operations:

  • Reading LONG VARCHAR columns
  • Reading LOB (BLOB/CLOB/DBCLOB) columns
  • Performing a backup

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > SQL Analysis Pane

Direct Writes to the Database

The direct writes to the database metric identifies the number of write operations that did not use a bufferpool. Direct writes are performed for the following database operations:

  • Writing LONG VARCHAR columns
  • Writing LOB (BLOB/CLOB/DBCLOB) columns
  • Performing a load
  • Performing a restore

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > SQL Analysis Pane

Direct I/O Averages

The following values show how well the direct I/O operations are performing:

  • Avg. Sectors / Read: Average number of sectors read for each direct read operation
  • Avg Sectors / Write: Average number of sectors written for each direct write operation
  • Avg Read Time: Average read time per sector for direct reads

Avg Write Time: Average write time per sector for direct writes

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > SQL Analysis Pane

Metrics

Understanding the level of direct I/O averages is important. A high level of activity with a slowdown in database performance can indicate that you need to have dedicated and/or high performance storage devices for your LOB objects or backup and load images. These metrics, when viewed with the device metrics at the OS level can also indicate I/O resource conflicts with other processes.

Correction

You can monitor these metrics for the LOB storage devices at the OS level to see if they are inordinately high for the database compared to the OS level. If so, there may be an I/O conflict with another process.

Workload Analysis Pane

When experiencing performance slowdowns, some of the first questions you should answer are:

  • Who is currently connected to the database?
  • What resources are they using?
  • What are they currently executing?

The Workload Analysis section of the Performance Analyst I/O page provides insight into the leading resource hogs of a server, as well as a count of total active and inactive processes. Drill-down's are available so you can easily get detailed information into what each leading application is currently involved with.

Workload Analysis statistics includes:

Top I/O Apps

The Top I/O Apps section gives a list of the apps with performing that are performing the highest number of most aggressive I/O activities The list includes:

  • Application with most bufferpool misses
  • Application with highest direct reads and writes
  • Application with highest sort overflows
  • Application with highest direct read/write time

Application with highest bufferpool read write time

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Home > Workload Analysis Pane

Correction

Drill down to the application I/O level to see the details and compare the metrics with the application's peers, and the database. In a lot of cases simple SQL rewrites, logic changes, and defining indexes based on access patterns can make a world of difference.

Applications Tab

This section shows the list of applications connected to the database and the various I/O metrics for each application. It also shows the rolled up metrics at the database level in the same list. You can select the type of I/O monitoring you intend to perform for the applications from the following:

  • Direct I/O: This shows the details of all direct I/O activity for the applications.
  • Buffered I/O: This shows the details of all bufferpool I/O activity for the applications connected to the database.
  • Extended Storage: This shows the level of extended storage usage by the applications.
  • Sorts & Joins:This shows the details for the number of files closed for the various applications.
  • Block / Vectored I/O: This shows the detailed of block and vectored I/O performed by the applications.

Correction

Depending on the type of details you select, you can view the different types of I/O activity associated with the applications. This section is very useful in isolating the applications, and the I/O areas you need to tune by comparing the I/O performance for all the applications and also the overall I/O performance at the database level.

Direct I/O

The Direct I/O Application or Tablespace Details view lets you the following statistics:


Direct Reads and Writes

This section shows the rate at which direct read/write operations are occurring for the selected element. Direct I/O operations are those that do not use the bufferpools. Direct read and write operations are performed when:

  • Reading/Writing LONG VARCHAR columns
  • Reading/Writing LOB data
  • Performing a backup operation
  • Performing a restore operation
  • Performing a load operation

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Applications Tab

Metrics

When using the performance analyst to track I/O, these elements help you distinguish the database I/O from the non-database I/O on storage devices.

Direct Read and Write Requests

This section shows the rate at which direct read/write operations requests are occurring for the selected element. Direct I/O operations are those that do not use the bufferpools. Direct read and write operations are performed when:

  • Reading/Writing LONG VARCHAR columns
  • Reading/Writing LOB data
  • Performing a backup operation
  • Performing a restore operation
  • Performing a load operation

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Applications Tab

Metrics

Use these metrics together with the Direct R/W metrics and Direct R/W request metrics to determine if the rate at which the read/write occurring is similar to the rate at which they are being satisfied. If there is a large discrepancy between the Direct I/O request rates and Direct I/O rates, it may mean that there are I/O contentions on the device(s) where the direct I/O operations are being performed.

Correction

If you suspect there are I/O contentions, you can investigate whether the contentions are being caused by the DB2 processes or other processes by examining the I/O metrics at the operating system level. Consider using dedicated devices for the direct I/O operations to reduce the contention levels.

Direct Read and Writes Time

This section shows the elapsed time to perform the direct I/O operations. Direct I/O operations are those that do not use the bufferpools. Direct read and write operations are performed when:

  • Reading/Writing LONG VARCHAR columns
  • Reading/Writing LOB data
  • Performing a backup operation
  • Performing a restore operation

Performing a load operation

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Applications Tab

Metrics

Use these metrics together with the Direct R/W metrics to determine average time it is taking to perform the read write operations. If the level of Direct I/O rates is low and the Direct RW times, it may mean that there are I/O contentions on the device(s) where the direct I/O operations are being performed.

Correction

If you suspect there are I/O contentions, you can investigate whether the contentions are being caused by the DB2 processes or other processes by examining the I/O metrics at the operating system level. Consider using dedicated devices for the direct I/O operations to reduce the contention levels. If the contention levels are low, you may also benefit from using faster storage devices for direct I/O.

Buffered I/O

The Buffered I/O Application Details view lets you see the following statistics:

Hit Ratios

The DB2 database server reads and updates all data from a bufferpool because memory access is much faster than disk access. Data is copied from disk to a bufferpool as needed by the applications using the database. When the server needs to read/write data and the data is already in the bufferpool, no disk access is required. However, if the data is not in the bufferpool, it needs to be read from the disk, which is significantly slower process.

The buffer pool hit ratio indicates the percentage of time that the database server did not need to load a page to service the request for a page. The calculation takes into account all the index and data pages that were requested

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Applications Tab

Metrics

Avoiding disk I/O is the main issue when trying to improve performance tuning. A high buffer pool hit ratio is desirable because it indicates a lower frequency of synchronous disk I/O. A database where data is accessed uniformly from very large tables will have a poor hit ratio. There is little you can do to improve the performance in such cases.

Correction

The buffer pool hit ratio on the database Home page is the overall hit ratio of the database. First, drill down to the bufferpool level and check the individual buffer pool hit ratios to identify the elements that may require tuning. Increasing the bufferpool size generally improves the buffer pool hit ratio. You can use one of the following methods to increase the bufferpool size depending on the layout of the data: If the tablespaces using the bufferpool have tables and indexes on them, increase the bufferpool size incrementally until the index page hit ratio stops increasing for that bufferpool. You will not usually get any benefit increasing the bufferpool size after you reach that point. If the tablespaces associated with the bufferpool have only indexes on them, increase the bufferpool size until the index page hit ratio stops increasing. If the tablespaces associated with the bufferpool have only data on them, increase the bufferpool size until the data page hit ratio stops increasing. One general design guideline is to try and use different tablespaces for indexes and tables and associate different bufferpools with them. This generally improves performance and makes bufferpool tuning much easier.

Write/Read Ratios

This section shows the overall level of read / write activity being performed on the selected element.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Applications Tab

Extended Storage

The Extended Storage Application or Transaction Details view lets you see the following statistics:

Index I/O on Extended Storage

This section shows the Index Pages copied to/from extended storage for the selected element. On 32-bit platforms, virtual addressable memory is usually limited to between 2 and 4 GB. If your 32-bit machine has more real addressable memory than the maximum amount, you can configure any additional real addressable memory beyond virtual addressable memory as an extended storage cache. Any of the defined bufferpools can use an extended storage cache to improve performance. You define the extended storage cache as a number of memory segments.

The bufferpools perform first-level caching, and any extended storage cache is used by the bufferpools as secondary-level caching. Ideally, the bufferpools hold the data that is most frequently accessed, while the extended storage cache hold data that is accessed less frequently.

Pages are copied from the bufferpool to extended storage, when they are selected as victim pages. This copying is required to make space for new pages in the bufferpool.

Required index pages are copied from extended storage to the bufferpool, if they are not in the bufferpool, but are in extended storage. This copying may incur the cost of connecting to the shared memory segment, but saves the cost of a disk read.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Applications Tab

Metrics

Because an extended storage cache is an extension to a bufferpool, it must always be associated with one or more specific bufferpools. Therefore, only the bufferpools that are declared to take advantage of advantage of the extended storage use it. The CREATE and ALTER BUFFERPOOL statements have the attributes NOT EXTENDED STORAGE and EXTENDED STORAGE that control cache usage. By default neither IBMDEFAULTBP nor any newly created bufferpool will use extended storage. Use these metrics together with the Data I/O on Extended Storage metrics to determine the level of I/O being performed on extended storage. If there is significant activity on this section, you may improve performance by increasing the size of extended storage.

Correction

The amount of extended storage for a database is determined by the following two database configuration parameters: num_estore_segs defines the number of extended storage memory segments. The default for this configuration parameter is zero, which specifies that no extended storage cache exists. estore_seg_sz defines the size of each extended memory segment. This size is determined by the platform on which the extended storage cache is used.

Data I/O on Extended Storage

This section shows the DataPages copied to/from extended storage for the selected element.

On 32-bit platforms, virtual addressable memory is usually limited to between 2 and 4 GB. If your 32-bit machine has more real addressable memory than the maximum amount, you can configure any additional real addressable memory beyond virtual addressable memory as an extended storage cache. Any of the defined bufferpools can use an extended storage cache to improve performance. You define the extended storage cache as a number of memory segments.

The bufferpools perform the first-level caching, and any extended storage cache is used by the bufferpools as secondary-level caching. Ideally, the bufferpools hold the data that is most frequently accessed, while the extended storage cache hold data that is accessed less frequently.

Pages are copied from the bufferpool to extended storage, when they are selected as victim pages. This copying is required to make space for new pages in the bufferpool. Required pages are copied from extended storage to the buffer pool, if they are not in the bufferpool, but are in extended storage. This copying may incur the cost of connecting to the shared memory segment, but saves the cost of a disk read.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Applications Tab

Metrics

Because an extended storage cache is an extension to a bufferpool, it must always be associated with one or more specific bufferpools. Therefore, only the bufferpools that are declared to take advantage of advantage of the extended storage use it. The CREATE and ALTER BUFFERPOOL statements have the attributes NOT EXTENDED STORAGE and EXTENDED STORAGE that control cache usage. By default neither IBMDEFAULTBP nor any newly created bufferpool will use extended storage. Use these metrics together with the Index I/O on Extended Storage metrics to determine the level of I/O being performed on extended storage. If there is significant activity on this section, you can improve performance by increasing the size of extended storage.

Bufferpools Tab

This section shows the list of bufferpools on the database and the various I/O metrics for each bufferpool. It also shows the rolled up metrics at the database level in the same list. You can select the type of monitoring you intend to perform for the bufferpools from the following:

  • Direct I/O: This shows the details of all direct I/O activity for the various bufferpools
  • Buffered I/O: This shows the details of all bufferpool I/O activity for the various bufferpools in the database
  • Extended Storage: This shows the level of extended storage usage by the various bufferpools that use extended storage
  • Total Database Files Closed: This shows the details for the number of files closed for the various bufferpools in the database
  • Block/Vectored I/O: This shows the detailed of block and vectored I/O performed by the various bufferpools.

Correction

Depending on the type of details you select, you can view the different types of I/O activity associated with the bufferpools. This section is very useful in isolating the bufferpools, and the I/O areas you need to tune by comparing the I/O performance for all the bufferpools and also the overall I/O performance at the database level.

Block/Vectored I/O

The Block/Vectored I/O section of the Bufferpool Details view displays the following statistics:

Vectored I/O

The Vectored I/O section gives the metrics for vectored I/O requests and reads.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Bufferpools Tab

Metrics

Use this element to determine how often vectored I/O is being done. The number of vectored I/O requests is monitored only during sequential prefetching.

Block I/O

The Block I/O section gives the metrics for block I/O requests and reads. If block-based bufferpool is enabled, this section reports how often block I/O is being done. The number of block I/O requests is monitored only during sequential prefetching when using block-based bufferpools.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Bufferpools Tab

Metrics

If block-based bufferpool is enabled and the block I/O activity is very low, or close to the values in the vectored I/O section, consider changing the block size. This state can be an indication of the following: The extent size of one or more tablespaces bound to the bufferpool is smaller than the block size specified for the bufferpool. Some pages requested in the prefetch request are already present in the page area of the bufferpool. The prefetcher allows some wasted pages in each bufferpool block, but if too many pages are wasted, then the prefetcher will decide to perform vectored I/O into the page area of the bufferpool. To take full advantage of the sequential prefetch performance improvements that block-based bufferpools provide, it is essential to choose an appropriate value for the block size. This can, however, be difficult because multiple tablespaces with different extent sizes can be bound to the same block-based bufferpool. For optimal performance, it is recommended that you bind tablespaces with the same extent size to a block-based bufferpool with a block size equal to the extent size. Good performance can be achieved when the extent size of the tablespaces are greater than the block size, but not when the extent size is smaller than the block size. For example, if extent size is 2 and block size is 8, vectored I/O would be used instead of block I/O (block I/O would have wasted 6 pages). A reduction of the block size to 2 would solve this problem.

Correction

You can alter the block size for a buffer pool using the ALTER BUFFERPOOL ddl statement.

Logging Tab

The Logging tab of the I/O Detail view displays Logging I/O statistics. This section shows the level of read/write activity being performed by the logger. The information presented here is:

  • Log Reads: The number of log pages read from the disk by the logger per second.
  • Log Writes: The number of log pages written to the disk by the logger.

Metrics

You can use this page with the operating system level I/O metrics, to quantify the amount of I/O on a device that is attributable to database activity.

Tablespaces Tab

The Tablespace tab of the I/O detail view shows the list of tablespaces in the database and the I/O metrics for each tablespace. It also shows the rolled-up metrics at the database level in the same list. You can select the type of monitoring you intend to perform for the tablespaces from the following:

  • Direct I/O: Shows the details of all direct I/O activity for the various tablespaces
  • Buffered I/O: Shows the details of all bufferpool I/O activity for the various tablespaces in the database
  • Extended Storage: Shows the level of extended storage usage by the tablespaces that use extended storage
  • Total Database Files Closed: Shows details for the number of files closed for various tablespaces in the database

Correction

Depending on the type of details you select, you can view the different types of I/O activity associated with the tablespaces. This section is very useful in isolating the tablespaces, and the I/O areas you need to tune by comparing the I/O performance for all the tablespaces and also the overall I/O performance at the database level.

Buffered I/O

The following statistics are available on the Buffered I/O section of the Tablespace Details of the I/O view:

Async I/O

Async read ratio is the percentage of all index and data reads that were performed asynchronously by the database manager prefetchers. Async write ratio is the percentage of all index and data writes that were performed asynchronously by the database manager bufferpool page cleaners.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > I/O > Tablespaces Tab

Metrics

Prefetching refers to the technique whereby the Database Manager can read several pages on the physical reads device simultaneously into a bufferpool in anticipation of an access to these pages. Since each physical read operation is costly, a 50% async read ratio is desirable since it shows that the prefetchers are working well and read waits are being minimized. Page cleaners write changed pages from bufferpool to disk before the space in the bufferpool is needed by a database agent. If the page cleaners are not working well, the problem may manifest itself in two ways: The database agents will need to synchronously free up space in bufferpools resulting in poorer response time. If the system crashes, the recovery time of the system will be greater because there will be a large number of pages that would not have been committed to disk at time of crash and will require processing of a large number of log records. An overall async write ratio of 50% is desirable for most transactional database systems. If your database is ‘query only’ (i.e., updates are rarely or never performed), it is okay to have an async write ratio of 0 to less than 50%. Even in ‘query only’ databases if the queries create temp tables on the database for intermediate results, a 30-40% async write ratio is desirable.

Correction

num_ioserver database configuration parameter defines the number of prefetchers that are available for the database. To get an optimal async read ratio, you should set the num_ioserver database configuration parameter to 1-2 more than the number of physical storage devices used by the database to ensure that asynchronous I/O is occurring. This parameter should be changed judiciously since having too many prefecthers invariably results in high system I/O because prefecthers can read many more pages than required into the bufferpools. Async write ratio can be tuned using the num_iocleaners database configuration parameter. This parameter specifies the number of asynchronous page cleaners for a database. Increasing the number of page cleaners generally results in a higher async write ratio. The following rules of thumb can be followed when tuning this parameter: For most transactional systems set this parameter to between 1 and the number of physical storage devices used by the database. For workloads that consists of mainly simple reads from the database, set this parameter to 0. When you define very large bufferpools on your database you may need to increase the num_iocleaners. Monitor the victim page cleaners% metric when tuning the num_iocleaners.