By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,847 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,847 IT Pros & Developers. It's quick & easy.

How to reduce dirty page steals?

P: n/a
Helpful folks,

I am having a hard time figuring out how to reduce my percentage of
dirty page steal activity. Below are statistics for three fairly
normal days, with the bufferpool hit ratios and page clean
percentages, as well as an average of transaction rate for the entire
days work.

The ORDERST_BP was created to service our most active table, and I've
been assuming that the low hit ratio for this BP is what is
contributing to the high percentage of dirty page steals. Does this
seem to be a valid assumption? Would simply increasing th BP size
reduce the dirty page steals? What other aspects of database
configuration may impact the dirty page steal cleans?

From posting on this newsgroup and elsewhere, I've gained the
perception that an ideal situation would be to have the majority
(%90>) of page cleaning activity to be handled by LSN and the page
threshold cleaners. I would like to get my dirty page steal percentage
down into single digits. Is this reasonable? Also, I would have
expected a CHNGPGS_THRESH of twenty to be rather agressive and help
boost my percentage of these cleaners to a much higher number than I
am seeing. Could anyone provide some recommendations for producing a
more balanced ratio of page cleaning activity?

Any response or feedback would be greatly appreciated,
Sean

Win2000 - Dual processor zeon, 4g ram
DB2 UDB 7.2 fp5
TOT DATA IDX ASYNC %Dirty Page %LSN %Thresh
Name HR HR HR READ% Steal Clners PgClnrs PgClnrs
TX/Sec

(DB) CICPROD 87 78 99 11 41 52 5
54.79
(BP) TEMP 99 99 0 0
(BP) INDEX 99 96 99 0
(BP) TABLE 93 93 92 4
(BP) TABLE8K 6 6 0 85
(BP) ORDERST_BP 37 37 0 59
(DB) CICPROD 88 79 99 10 40 47 11
54.85
(BP) TEMP 94 94 0 4
(BP) INDEX 99 74 99 0
(BP) TABLE 95 95 92 2
(BP) TABLE8K 26 26 0 64
(BP) ORDERST_BP 37 37 0 59
(DB) CICPROD 88 79 99 10 38 57 3
54.52
(BP) TEMP 99 99 50 0
(BP) INDEX 99 64 99 0
(BP) TABLE 95 95 93 3
(BP) TABLE8K 32 32 0 66
(BP) ORDERST_BP 36 36 0 60
Database Configuration for Database cicprod
Log retain for recovery status = RECOVERY
User exit for logging status = NO
Database heap (4KB) (DBHEAP) = 5600
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 128
Log buffer size (4KB) (LOGBUFSZ) = 512

Changed pages threshold (CHNGPGS_THRESH) = 20
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 3
Number of I/O servers (NUM_IOSERVERS) = 10
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES

Log file size (4KB) (LOGFILSIZ) = 16384
Number of primary log files (LOGPRIMARY) = 2
Number of secondary log files (LOGSECOND) = 124

Group commit count (MINCOMMIT) = 25
Percent log file reclaimed before soft chckpt (SOFTMAX) = 10
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = OFF
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"Sean C." <db*****@yahoo.com> wrote in message
news:2f**************************@posting.google.c om...
Helpful folks,

I am having a hard time figuring out how to reduce my percentage of
dirty page steal activity. Below are statistics for three fairly
normal days, with the bufferpool hit ratios and page clean
percentages, as well as an average of transaction rate for the entire
days work.

The ORDERST_BP was created to service our most active table, and I've
been assuming that the low hit ratio for this BP is what is
contributing to the high percentage of dirty page steals. Does this
seem to be a valid assumption? Would simply increasing th BP size
reduce the dirty page steals? What other aspects of database
configuration may impact the dirty page steal cleans?

From posting on this newsgroup and elsewhere, I've gained the
perception that an ideal situation would be to have the majority
(%90>) of page cleaning activity to be handled by LSN and the page
threshold cleaners. I would like to get my dirty page steal percentage
down into single digits. Is this reasonable? Also, I would have
expected a CHNGPGS_THRESH of twenty to be rather agressive and help
boost my percentage of these cleaners to a much higher number than I
am seeing. Could anyone provide some recommendations for producing a
more balanced ratio of page cleaning activity?

Any response or feedback would be greatly appreciated,
Sean

I would be careful about creating too many bufferpools. At most, I would
have 3 bufferpools:

- small frequently used tables, small to medium size indexes
- medium size tables, and all other indexes
- very large tables with a lot of tablespaces scans

Unless you have a lot of tables, you probably can get by with 1 or 2
bufferpools. DB2 does a good job of managing a fewer number of larger
bufferpools. One table per bufferpool is usually a bad idea.

Bufferpools should be as large as possible so long as there is enough real
memory for all the bufferpools, other DB2 resources, other applications on
the server, and the OS.

You can go above CHNGPGS_THRESH of 20 if you want to. DB2 version 8 has a
more aggressive page cleaning algorithm. I believe that there is also a
registry value for more aggressive page cleaners (check out the
Administration: Performance Guide).
Nov 12 '05 #2

P: n/a
Mark,

Thanks for the reply. I have roughly 200 tables which were all
formerly defined within the TABLE and INDEX BP's. My TABLE BP hit
ratio was around 45%. When I split our largest and most heavily
beat-against table into it's own BP, the HR for the TABLE BP rose into
the 90's, and overall system performance improved noticably.
When you say "You can go above CHNGPGS_THRESH of 20 if you want to", I
assume you mean setting the value to a lower (more agressive) number?
I've been hunting for a registry variable pertaining to page cleaning
in the Admin performance guide, but have been unseccessful so far. Is
this only relevant to V8?

"Mark A" <no****@nowhere.com> wrote in message news:<vY****************@news.uswest.net>...
"Sean C." <db*****@yahoo.com> wrote in message
news:2f**************************@posting.google.c om...
Helpful folks,

I am having a hard time figuring out how to reduce my percentage of
dirty page steal activity. Below are statistics for three fairly
normal days, with the bufferpool hit ratios and page clean
percentages, as well as an average of transaction rate for the entire
days work.

The ORDERST_BP was created to service our most active table, and I've
been assuming that the low hit ratio for this BP is what is
contributing to the high percentage of dirty page steals. Does this
seem to be a valid assumption? Would simply increasing th BP size
reduce the dirty page steals? What other aspects of database
configuration may impact the dirty page steal cleans?

From posting on this newsgroup and elsewhere, I've gained the
perception that an ideal situation would be to have the majority
(%90>) of page cleaning activity to be handled by LSN and the page
threshold cleaners. I would like to get my dirty page steal percentage
down into single digits. Is this reasonable? Also, I would have
expected a CHNGPGS_THRESH of twenty to be rather agressive and help
boost my percentage of these cleaners to a much higher number than I
am seeing. Could anyone provide some recommendations for producing a
more balanced ratio of page cleaning activity?

Any response or feedback would be greatly appreciated,
Sean

I would be careful about creating too many bufferpools. At most, I would
have 3 bufferpools:

- small frequently used tables, small to medium size indexes
- medium size tables, and all other indexes
- very large tables with a lot of tablespaces scans

Unless you have a lot of tables, you probably can get by with 1 or 2
bufferpools. DB2 does a good job of managing a fewer number of larger
bufferpools. One table per bufferpool is usually a bad idea.

Bufferpools should be as large as possible so long as there is enough real
memory for all the bufferpools, other DB2 resources, other applications on
the server, and the OS.

You can go above CHNGPGS_THRESH of 20 if you want to. DB2 version 8 has a
more aggressive page cleaning algorithm. I believe that there is also a
registry value for more aggressive page cleaners (check out the
Administration: Performance Guide).

Nov 12 '05 #3

P: n/a
"Sean C." <db*****@yahoo.com> wrote in message
news:2f**************************@posting.google.c om...
Mark,

Thanks for the reply. I have roughly 200 tables which were all
formerly defined within the TABLE and INDEX BP's. My TABLE BP hit
ratio was around 45%. When I split our largest and most heavily
beat-against table into it's own BP, the HR for the TABLE BP rose into
the 90's, and overall system performance improved noticably.
When you say "You can go above CHNGPGS_THRESH of 20 if you want to", I
assume you mean setting the value to a lower (more agressive) number?
I've been hunting for a registry variable pertaining to page cleaning
in the Admin performance guide, but have been unseccessful so far. Is
this only relevant to V8?

Sorry, I did mean lower to be more aggressive. I believe that the registry
value for more aggressive page cleaning is new for Version 8.
Nov 12 '05 #4

P: n/a
One thing I notice is that you have a dual processor machine with 2+
buffer pools and only 3 IO_CLEANERS.
The chngpgs_thresh at 20% is called if any of the bp's are at 20% and
DB2 will dispatch a cleaner to each and all bp's to get cleaned even if
the others are not at 20%. If one of the bp's is relatively small and
gets a lot of change transaction, it would be the culprit for calling
thresh cleaners for all.

I also notice that most of your async writes are being done by LSN gap
cleaners meaning they're called whenever you hit SOFTMAX. It might be
the size of your logfile size and/or the low value of softmax that
causes this.

Given that cleaners are called for LSN gap cleaners and thresh page
cleaners and multiple buffer pools, you might not have enough of them to
dispatch and therefore DB2 has to do page steal cleans because the
cleaners are busy servicing gap and thresh requests.

I'd also look at the recommended value for mincomit, I don't know
enought from your environment to appreciate why at 25.

Have you tried running the Configuration advisor. Take it thru its last
page and request to save it in your task center. Do not execute it.
Then edit it from your command center and look at the recommended value
for the cleaners. I wouldn't be surprised if it recommended toraise it
to 12+.
Yopu can then junk the task but issue your own update db cfg commands to
set the cleaners.
HTH, Pierre.

Sean C. wrote:
Helpful folks,

I am having a hard time figuring out how to reduce my percentage of
dirty page steal activity. Below are statistics for three fairly
normal days, with the bufferpool hit ratios and page clean
percentages, as well as an average of transaction rate for the entire
days work.

The ORDERST_BP was created to service our most active table, and I've
been assuming that the low hit ratio for this BP is what is
contributing to the high percentage of dirty page steals. Does this
seem to be a valid assumption? Would simply increasing th BP size
reduce the dirty page steals? What other aspects of database
configuration may impact the dirty page steal cleans?

From posting on this newsgroup and elsewhere, I've gained the
perception that an ideal situation would be to have the majority
(%90>) of page cleaning activity to be handled by LSN and the page
threshold cleaners. I would like to get my dirty page steal percentage
down into single digits. Is this reasonable? Also, I would have
expected a CHNGPGS_THRESH of twenty to be rather agressive and help
boost my percentage of these cleaners to a much higher number than I
am seeing. Could anyone provide some recommendations for producing a
more balanced ratio of page cleaning activity?

Any response or feedback would be greatly appreciated,
Sean

Win2000 - Dual processor zeon, 4g ram
DB2 UDB 7.2 fp5
TOT DATA IDX ASYNC %Dirty Page %LSN %Thresh
Name HR HR HR READ% Steal Clners PgClnrs PgClnrs
TX/Sec

(DB) CICPROD 87 78 99 11 41 52 5
54.79
(BP) TEMP 99 99 0 0
(BP) INDEX 99 96 99 0
(BP) TABLE 93 93 92 4
(BP) TABLE8K 6 6 0 85
(BP) ORDERST_BP 37 37 0 59
(DB) CICPROD 88 79 99 10 40 47 11
54.85
(BP) TEMP 94 94 0 4
(BP) INDEX 99 74 99 0
(BP) TABLE 95 95 92 2
(BP) TABLE8K 26 26 0 64
(BP) ORDERST_BP 37 37 0 59
(DB) CICPROD 88 79 99 10 38 57 3
54.52
(BP) TEMP 99 99 50 0
(BP) INDEX 99 64 99 0
(BP) TABLE 95 95 93 3
(BP) TABLE8K 32 32 0 66
(BP) ORDERST_BP 36 36 0 60
Database Configuration for Database cicprod
Log retain for recovery status = RECOVERY
User exit for logging status = NO
Database heap (4KB) (DBHEAP) = 5600
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 128
Log buffer size (4KB) (LOGBUFSZ) = 512

Changed pages threshold (CHNGPGS_THRESH) = 20
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 3
Number of I/O servers (NUM_IOSERVERS) = 10
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES

Log file size (4KB) (LOGFILSIZ) = 16384
Number of primary log files (LOGPRIMARY) = 2
Number of secondary log files (LOGSECOND) = 124

Group commit count (MINCOMMIT) = 25
Percent log file reclaimed before soft chckpt (SOFTMAX) = 10
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = OFF


--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

Nov 12 '05 #5

P: n/a
Mark A <no****@nowhere.com> wrote:
Sorry, I did mean lower to be more aggressive. I believe that the registry
value for more aggressive page cleaning is new for Version 8.


Specifically, Fixpack 4.

jsoh

Nov 12 '05 #6

P: n/a
Pierre,

Thanks for the detailed reply. My number of iocleaners was chosen
based on a number of presentations given at IDUG that recommended
num_iocleaners = (# processesors + 1). I didn't think to verify this
in the manuals, but sure enough, both the V7 and V8 admin performance
guides recommend at least 1 iocleaner per physical disk. I will
increase this parameter appropriately and see what happens. I will
also tweak softmax and mincommit and see how that affects the LSN gap
cleaners.

Thanks again for all the help.

Pierre Saint-Jacques <se*****@attglobal.net> wrote in message news:<40**************@attglobal.net>...
One thing I notice is that you have a dual processor machine with 2+
buffer pools and only 3 IO_CLEANERS.
The chngpgs_thresh at 20% is called if any of the bp's are at 20% and
DB2 will dispatch a cleaner to each and all bp's to get cleaned even if
the others are not at 20%. If one of the bp's is relatively small and
gets a lot of change transaction, it would be the culprit for calling
thresh cleaners for all.

I also notice that most of your async writes are being done by LSN gap
cleaners meaning they're called whenever you hit SOFTMAX. It might be
the size of your logfile size and/or the low value of softmax that
causes this.

Given that cleaners are called for LSN gap cleaners and thresh page
cleaners and multiple buffer pools, you might not have enough of them to
dispatch and therefore DB2 has to do page steal cleans because the
cleaners are busy servicing gap and thresh requests.

I'd also look at the recommended value for mincomit, I don't know
enought from your environment to appreciate why at 25.

Have you tried running the Configuration advisor. Take it thru its last
page and request to save it in your task center. Do not execute it.
Then edit it from your command center and look at the recommended value
for the cleaners. I wouldn't be surprised if it recommended toraise it
to 12+.
Yopu can then junk the task but issue your own update db cfg commands to
set the cleaners.
HTH, Pierre.

Sean C. wrote:
Helpful folks,

I am having a hard time figuring out how to reduce my percentage of
dirty page steal activity. Below are statistics for three fairly
normal days, with the bufferpool hit ratios and page clean
percentages, as well as an average of transaction rate for the entire
days work.

The ORDERST_BP was created to service our most active table, and I've
been assuming that the low hit ratio for this BP is what is
contributing to the high percentage of dirty page steals. Does this
seem to be a valid assumption? Would simply increasing th BP size
reduce the dirty page steals? What other aspects of database
configuration may impact the dirty page steal cleans?

From posting on this newsgroup and elsewhere, I've gained the
perception that an ideal situation would be to have the majority
(%90>) of page cleaning activity to be handled by LSN and the page
threshold cleaners. I would like to get my dirty page steal percentage
down into single digits. Is this reasonable? Also, I would have
expected a CHNGPGS_THRESH of twenty to be rather agressive and help
boost my percentage of these cleaners to a much higher number than I
am seeing. Could anyone provide some recommendations for producing a
more balanced ratio of page cleaning activity?

Any response or feedback would be greatly appreciated,
Sean

Win2000 - Dual processor zeon, 4g ram
DB2 UDB 7.2 fp5
TOT DATA IDX ASYNC %Dirty Page %LSN %Thresh
Name HR HR HR READ% Steal Clners PgClnrs PgClnrs
TX/Sec

(DB) CICPROD 87 78 99 11 41 52 5
54.79
(BP) TEMP 99 99 0 0
(BP) INDEX 99 96 99 0
(BP) TABLE 93 93 92 4
(BP) TABLE8K 6 6 0 85
(BP) ORDERST_BP 37 37 0 59
(DB) CICPROD 88 79 99 10 40 47 11
54.85
(BP) TEMP 94 94 0 4
(BP) INDEX 99 74 99 0
(BP) TABLE 95 95 92 2
(BP) TABLE8K 26 26 0 64
(BP) ORDERST_BP 37 37 0 59
(DB) CICPROD 88 79 99 10 38 57 3
54.52
(BP) TEMP 99 99 50 0
(BP) INDEX 99 64 99 0
(BP) TABLE 95 95 93 3
(BP) TABLE8K 32 32 0 66
(BP) ORDERST_BP 36 36 0 60
Database Configuration for Database cicprod
Log retain for recovery status = RECOVERY
User exit for logging status = NO
Database heap (4KB) (DBHEAP) = 5600
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 128
Log buffer size (4KB) (LOGBUFSZ) = 512

Changed pages threshold (CHNGPGS_THRESH) = 20
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 3
Number of I/O servers (NUM_IOSERVERS) = 10
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES

Log file size (4KB) (LOGFILSIZ) = 16384
Number of primary log files (LOGPRIMARY) = 2
Number of secondary log files (LOGSECOND) = 124

Group commit count (MINCOMMIT) = 25
Percent log file reclaimed before soft chckpt (SOFTMAX) = 10
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = OFF

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.