Performance Tuning for Content Manager

240 Performance Tuning for Content Manager

The async write percentage gives you an indication of how the cleaners and

changed pages threshold are performing. A percentage over 90% is desirable.

This is the formula to calculate the percentage:

((Async pool data writes + Async pool index writes) / (Buffer pool data

writes + Buffer pool index writes)) * 100

For databases with a heavy update transaction workload, you can generally

ensure that there are enough clean pages in the buffer pool by setting the

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.

Starting with DB2 version 8.1.4, there is an alternative method of configuring

page cleaning. This alternative method differs from the default behavior in that

page cleaners behave more proactively in choosing which dirty pages get written

out at any given time. This new method differs from the default page cleaning

method in two major ways:

 Page cleaners do not consider the CHNGPGS_THRESH configuration

parameter.

 Page cleaners no longer respond to LSN gap triggers issued by the logger.

To use the new method of page cleaning, set the

DB2_USE_ALTERNATIVE_PAGE_CLEANING registry variable to ON.

8.6.31 Number of asynchronous page cleaners (NUM_IOCLEANERS)

Impact

DB - High

Description

This parameter enables you to specify the number of asynchronous page

cleaners for a database. These page cleaners write changed pages from the

buffer pool to disk before the space in the buffer pool is required by a database

agent. As a result, database agents should not have to wait for changed pages to

be written out so that they might use the space in the buffer pool. This improves

overall performance of the database applications.

If you set the parameter to zero, no page cleaners are started, and as a result,

the database agents will perform all of the page writes from the buffer pool to

disk. This parameter can have a significant performance impact on a database

stored across many physical storage devices, because in this case there is a

greater chance that one of the devices will be idle. If no page cleaners are

configured, your applications could encounter periodic log-full conditions.

Chapter 8. Tuning DB2 for Content Manager 241

If the applications for a database primarily consist of transactions that update

data, an increase in the number of cleaners will speed up performance.

Increasing the page cleaners will also decrease recovery time from soft failures,

such as power outages, because the contents of the database on disk will be

more up-to-date at any given time.

Default values (range)

1 (0 - 255)

To update

Run the following DB2 command:

db2 update db cfg for <database name> using NUM_IOCLEANERS <parameter

value>

Our recommendation

Use the default initially. As discussed in 8.6.30, “Changed pages threshold

(CHNGPGS_THRESH)” on page 238, increase the number of IOCLEANERS if

the AWP is less than 90%, unless you are using proactive page cleaning. Monitor

your system during normal workload to verify the necessity of changes.

Consider the following factors when changing the value for this parameter:

 Application type

– If it is a query-only database that will not have updates, set this parameter

to zero. The exception is 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 between

one and the number of physical storage devices used for the database.

 Workload

Environments with high update transaction rates might require more page

cleaners to be configured.

 Buffer pool size

Environments with large buffer pools also might 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.