Number of Asynchronous Page Cleaners (num_iocleaners)

Configuration Type

Database

Parameter Type

Configurable

Default [Range]

1 [ 0 - 255 ]

Unit of Measure

Counter

Related Parameters

• Buffer Pool Size (buffpage)
• Changed Pages Threshold (chngpgs_thresh)

This parameter allows 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. This means that the agents will not wait for changed pages to be written out, before being able to read a page. As a result, your application's transactions should run faster.

If you set the parameter to zero (0), 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, since in this case there is a greater chance that one of the devices will be idle. If no page cleaners are configured, your applications may encounter periodic log full conditions.

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.

Recommendation: Consider the following factors when setting the value for this parameter:

• Application type
  • 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 work load results in many TEMP tables being created (you can determine this by using the exlain 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.
• Workload

  Environments with high update transaction rates may require more page cleaners to be configured.

• Buffer pool sizes (buffpage)

  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.

For more information about these monitor elements, see:

• Buffer Pool Data Writes
• Buffer Pool Asynchronous Data Writes
• Buffer Pool Index Writes
• Buffer Pool Asynchronous Index Writes.