1

The documentation of num_iocleaners says:

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.

What is approximately? What is "much greater"? What ratio? What percentage of difference?

Does anyone have an example of values that are "approximately equal" and where the Page Cleaners value should be increased? The same info for the "much geater" values would also be appreciated.

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.config.doc/doc/r0000332.html?cp=SSEPGG_10.5.0%2F2-4-4-8-108&lang=en

Improve this question
3
  • I have a database with: Asynchronous pool data page reads = 1061292948 and Buffer pool data writes = 21701013. I think that is "much greater than". Am I right?
    – AngocA
    Sep 17, 2014 at 12:47
  • What's the value for async pool data page writes ?
    – Ian Bjorhovde
    Sep 17, 2014 at 15:28
  • Problem when selecting from the snapshot. Here the values: Buffer pool data writes = 21708727 Asynchronous pool data page writes = 20343761
    – AngocA
    Sep 17, 2014 at 15:36

1 Answer 1

Reset to default
2

The idea is that you want as many of your writes as possible to be asynchronous. On the other hand, pool_data_writes will always be greater than pool_async_data_writes (ditto for index writes).

The tolerance depends entirely on your system. If each minute you have 3 pool_data_writes and only 1 pool_async_data_writes, you obviously have twice as many synchronous writes as you have asynchronous, but it's hardly a reason for concern.

If, on the other hand, each minute you have 300K pool_data_writes and 290K pool_async_data_writes, the difference appears to be minor, but you still have 10000/60 ~ 166 waits per second, which is pretty bad.

Please keep in mind that num_iocleaners is not the only configuration parameter that affects the write efficiency: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0011381.html?lang=en

PS. In your comment you compare asynchronous reads with writes, which doesn't make much sense.

Improve this answer
2
  • Ok, I understand what you said. However, how can I create a formula to verify the values? I have many databases with different configuration, and I would like to see if there is a problem with all of them. I cannot check one by one. I would like to create a Nagios script that checks that but I need a formula or method to raise an error. Actually, the num_iocleaners is static in most of the databases, and I need to identify where this fixed value is impacting the performance.
    – AngocA
    Sep 17, 2014 at 15:43
  • 2
    The formula you probably want to look at is the number of synchronous buffer pool writes per second. You will want to monitor for when that goes over a certain threshold and then tune based on that (whether its IO Cleaners, buffer pool size, etc.).
    – Ian Bjorhovde
    Sep 17, 2014 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.