Some observations regarding the honoring of the VPSEQT threshold

Larry Kirkpatrick

Some observations regarding the honoring of the VPSEQT threshold
DB2 community:

I have been doing some additional testing of how sequential detection
works with the VPSEQT threshold and thought that I would share my testing
results with you. Any collaboration that you can provide to either confirm
or deny these findings would be appreciated. My hope is that by doing
this, more people will know how DB2 actually works, and also through this
DB2 will become an even better product! Almost all of these observations
are being verified with IBM. I am not yet 100% sure that these are indeed
facts, but am fairly confident that this information is factual.

Most people assume that the VPSEQT threshold is honored by all
sequential access. In other words, whenever the portion of a bufferpool
that is occupied by sequentially read pages meets or exceeds this
threshold, the bufferpool will then begin reusing these pages by subsequent
sequential access. By doing this, the bufferpool will avoid becoming
completely saturated by sequential processes (and can be used in a better
way by random I/O processes).

One thing that is generally not known is that sequential detection
does not always honor the VPSEQT threshold. I found this by observing some
application processes that completely saturated the bufferpool that they
used. An example of a query that did this was an orphan check for a child
table. The following query was found to quickly occupy most of the pages
in a bufferpool:

SELECT foreign_key
FROM child_table
WHERE NOT EXISTS (
SELECT 1 FROM PARENT_TABLE
WHERE foreign_key = primary_key)
FETCH FIRST 1 ROWS ONLY;

In the above query, when the "FETCH FIRST 1 ROWS ONLY" is missing, the I/O
will be sequential (via the optimized access path) and will honor the
VPSEQT threshold. When this clause is added, sequential detection will be
used and this query will quickly saturate a bufferpool (as long as it runs
completely without returning any rows).

Here is another query that I ran that quickly saturated the bufferpool:


SELECT * FROM LARGE_TABLE
WHERE COL1 = X'01'
OPTIMIZE FOR 1 ROWS;

Once again, the predicate filters all the rows and sequential detection is
invoked to resolve the query. When I ran similar queries to these that
returned most of the rows but still invoked sequential detection, they
seemed to honor the VPSEQT threshold.

Since the filter factor seems to dictate whether or not VPSEQT is honored,
I tried changing the above query to a stage 2 predicate (while still
maintaining a 100% filter factor). The query then became:

SELECT * FROM LARGE_TABLE
WHERE SUBSTR(COL1,1,1) = X'01'
OPTIMIZE FOR 1 ROWS;

This query also returned no rows but the VPSEQT threshold was honored by
this query.

I am hoping that this is something that will be changed (and am working
through the requirements submission process to accomplish this), but this
seems to be the way that DB2 works for now. My own belief is that it would
be far easier to configure the bufferpools when sequential access would
ALWAYS honor the VPSEQT threshold. I manage several pagesets that have a
combinition of random and sequential detection I/O. I really do not have a
good way of bufferpool management for these pagesets other than to put them
in a really big bufferpool so the random I/O can benefit by buffer hits.
In my own mind, this seems to be a poor way to use virtual storage.

Larry Kirkpatrick
Database Consultant
Mutual of Omaha

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Avram Friedman

Re: Some observations regarding the honoring of the VPSEQT threshold
(in response to Larry Kirkpatrick)
VPSEQT controls prefetch, even if prefetch is disabled there is nothing
that will
prevent a single thread from using all the pages in the buffer pool.
Buffer pool
pages are usually reused on a LRU basis. If there is only one active
thread (or
a very small number of active threads) it can eat up all the pages of the
pool
assuming it accesses that many diffrent pages

[login to unmask email] wrote:
>
>DB2 community:
>
> I have been doing some additional testing of how sequential
detection
>works with the VPSEQT threshold and thought that I would share my testing
>results with you. Any collaboration that you can provide to either
confirm
>or deny these findings would be appreciated. My hope is that by doing
>this, more people will know how DB2 actually works, and also through this
>DB2 will become an even better product! Almost all of these observations
>are being verified with IBM. I am not yet 100% sure that these are indeed

>facts, but am fairly confident that this information is factual.
>
> Most people assume that the VPSEQT threshold is honored by all
>sequential access. In other words, whenever the portion of a bufferpool
>that is occupied by sequentially read pages meets or exceeds this
>threshold, the bufferpool will then begin reusing these pages by
subsequent
>sequential access. By doing this, the bufferpool will avoid becoming
>completely saturated by sequential processes (and can be used in a better
>way by random I/O processes).
>
> One thing that is generally not known is that sequential detection
>does not always honor the VPSEQT threshold. I found this by observing
some
>application processes that completely saturated the bufferpool that they
>used. An example of a query that did this was an orphan check for a child

>table. The following query was found to quickly occupy most of the pages
>in a bufferpool:
>
>SELECT foreign_key
>FROM child_table
>WHERE NOT EXISTS (
>SELECT 1 FROM PARENT_TABLE
>WHERE foreign_key = primary_key)
>FETCH FIRST 1 ROWS ONLY;
>
>In the above query, when the "FETCH FIRST 1 ROWS ONLY" is missing, the I/O

>will be sequential (via the optimized access path) and will honor the
>VPSEQT threshold. When this clause is added, sequential detection will be

>used and this query will quickly saturate a bufferpool (as long as it runs

>completely without returning any rows).
>
>Here is another query that I ran that quickly saturated the bufferpool:
>
>
>SELECT * FROM LARGE_TABLE
>WHERE COL1 = X'01'
>OPTIMIZE FOR 1 ROWS;
>
>Once again, the predicate filters all the rows and sequential detection is

>invoked to resolve the query. When I ran similar queries to these that
>returned most of the rows but still invoked sequential detection, they
>seemed to honor the VPSEQT threshold.
>
>Since the filter factor seems to dictate whether or not VPSEQT is honored,

>I tried changing the above query to a stage 2 predicate (while still
>maintaining a 100% filter factor). The query then became:
>
>SELECT * FROM LARGE_TABLE
>WHERE SUBSTR(COL1,1,1) = X'01'
>OPTIMIZE FOR 1 ROWS;
>
>This query also returned no rows but the VPSEQT threshold was honored by
>this query.
>
>I am hoping that this is something that will be changed (and am working
>through the requirements submission process to accomplish this), but this
>seems to be the way that DB2 works for now. My own belief is that it
would
>be far easier to configure the bufferpools when sequential access would
>ALWAYS honor the VPSEQT threshold. I manage several pagesets that have a
>combinition of random and sequential detection I/O. I really do not have
a
>good way of bufferpool management for these pagesets other than to put
them
>in a really big bufferpool so the random I/O can benefit by buffer hits.
>In my own mind, this seems to be a poor way to use virtual storage.
>
>Larry Kirkpatrick
>Database Consultant
>Mutual of Omaha
>
>--------------------------------------------------------------------------
-------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
>
>



--
NOTICE: If received in error, please destroy and notify sender. Sender does not waive confidentiality or privilege, and use is prohibited.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Avram Friedman

Re: Some observations regarding the honoring of the VPSEQT threshold
(in response to Avram Friedman)
Group,
I am sorry I should of re-read the manuals before opening my mouth.
VPSPEC can control what percentage of pages are used by Sequential
processing.

However I still think that in low use systems a single
thread can use 100% of the buffer regardless of the setting of
VPSPEC.

From the V7 ADMIN Guide
"This threshold is checked before stealing a buffer for a sequentially
accessed page instead
of accessing the page in the virtual buffer pool. If the threshold has
been exceeded,
DB2 tries to steal a buffer holding a sequentially accessed page
rather than one holding a randomly accessed page."

The way I read this is DB2 will prefer to steal a sequential page rather
than a random page
if the threshold is exceeded. Whats the preference between a sequential
page vs a free page
in this case ... I belive it is the free page.


--
NOTICE: If received in error, please destroy and notify sender. Sender does not waive confidentiality or privilege, and use is prohibited.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Joel Goldstein

Re: Some observations regarding the honoring of the VPSEQT threshold
(in response to Avram Friedman)
Hi Larry,

It's been observed for years that DynPref not only can flood the pool, but
usually
causes many pages to be read into the pool that are not accessed by the
application,
generating very low and often negative system hit ratios. Certainly this
has made
sense (although not happliy) in transaction based systems where DynPref is
turned on
and off frequently. I haven't looked recently, but there was some doc at
one time
that stated Vpseqt would manage the sequential pages for "one scanner", so
even
multiple large scanners could easily use 100% of the pool pages. Also,
turning DynPref off
and then back on again would logically have to start from the beginning to
track sequential pages,
and allow many more of them to remain in the existing pool pages beyond the
Vpseqt for the overall pool.
Pages can also be moved back and forth between the internal random and
sequential LRU queues
depending upon their last access.

Your testing is very interesting, implying that the resulting filter factor
also comes into play determining
whether Vpseqt is applied or not. This might explain a few processing
anomolies I've seen over the years.
Assuming (dangerous) that COL1 has no index, what did Explain show for the
access path when you changed
your query to a stage 2 predicate?

Certainly any feedback you receive from IBM will be of great help.

I certainly also agree that pool management would be made much easier and
more realistic if Vpseqt
was applied in a much stricter form.

Regards,
Joel




Message text written by DB2 Database Discussion list at IDUG
>DB2 community:

I have been doing some additional testing of how sequential detection
works with the VPSEQT threshold and thought that I would share my testing
results with you. Any collaboration that you can provide to either confirm
or deny these findings would be appreciated. My hope is that by doing
this, more people will know how DB2 actually works, and also through this
DB2 will become an even better product! Almost all of these observations
are being verified with IBM. I am not yet 100% sure that these are indeed
facts, but am fairly confident that this information is factual.

Most people assume that the VPSEQT threshold is honored by all
sequential access. In other words, whenever the portion of a bufferpool
that is occupied by sequentially read pages meets or exceeds this
threshold, the bufferpool will then begin reusing these pages by subsequent
sequential access. By doing this, the bufferpool will avoid becoming
completely saturated by sequential processes (and can be used in a better
way by random I/O processes).

One thing that is generally not known is that sequential detection
does not always honor the VPSEQT threshold. I found this by observing some
application processes that completely saturated the bufferpool that they
used. An example of a query that did this was an orphan check for a child
table. The following query was found to quickly occupy most of the pages
in a bufferpool:

SELECT foreign_key
FROM child_table
WHERE NOT EXISTS (
SELECT 1 FROM PARENT_TABLE
WHERE foreign_key = primary_key)
FETCH FIRST 1 ROWS ONLY;

In the above query, when the "FETCH FIRST 1 ROWS ONLY" is missing, the I/O
will be sequential (via the optimized access path) and will honor the
VPSEQT threshold. When this clause is added, sequential detection will be
used and this query will quickly saturate a bufferpool (as long as it runs
completely without returning any rows).

Here is another query that I ran that quickly saturated the bufferpool:


SELECT * FROM LARGE_TABLE
WHERE COL1 = X'01'
OPTIMIZE FOR 1 ROWS;

Once again, the predicate filters all the rows and sequential detection is
invoked to resolve the query. When I ran similar queries to these that
returned most of the rows but still invoked sequential detection, they
seemed to honor the VPSEQT threshold.

Since the filter factor seems to dictate whether or not VPSEQT is honored,
I tried changing the above query to a stage 2 predicate (while still
maintaining a 100% filter factor). The query then became:

SELECT * FROM LARGE_TABLE
WHERE SUBSTR(COL1,1,1) = X'01'
OPTIMIZE FOR 1 ROWS;

This query also returned no rows but the VPSEQT threshold was honored by
this query.

I am hoping that this is something that will be changed (and am working
through the requirements submission process to accomplish this), but this
seems to be the way that DB2 works for now. My own belief is that it would
be far easier to configure the bufferpools when sequential access would
ALWAYS honor the VPSEQT threshold. I manage several pagesets that have a
combinition of random and sequential detection I/O. I really do not have a
good way of bufferpool management for these pagesets other than to put them
in a really big bufferpool so the random I/O can benefit by buffer hits.
In my own mind, this seems to be a poor way to use virtual storage.

Larry Kirkpatrick
Database Consultant
Mutual of Omaha<

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ron Root

Re: Some observations regarding the honoring of the VPSEQT threshold
(in response to Joel Goldstein)
While on the subject...
Does anyone have any sage advise for setting VPSEQT (without benefit of
Joel's product)? Most of our application buffer pools are split about 50-50
between sequential and random in the long haul, with spikes for each in the
batch cycle. We currently have it set at 80 for most of the application
buffer pools.
(DB2 V6 with V7 in the wings).

Ron Root
DB2 Performance and Capacity Planning
Texas Comptroller of Public Accounts


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Joel Goldstein
Sent: Sunday, January 11, 2004 8:35 PM
To: [login to unmask email]
Subject: Re: Some observations regarding the honoring of the VPSEQT
threshold


Hi Larry,

It's been observed for years that DynPref not only can flood the pool, but
usually
causes many pages to be read into the pool that are not accessed by the
application,
generating very low and often negative system hit ratios. Certainly this
has made
sense (although not happliy) in transaction based systems where DynPref is
turned on
and off frequently. I haven't looked recently, but there was some doc at
one time
that stated Vpseqt would manage the sequential pages for "one scanner", so
even
multiple large scanners could easily use 100% of the pool pages. Also,
turning DynPref off
and then back on again would logically have to start from the beginning to
track sequential pages,
and allow many more of them to remain in the existing pool pages beyond the
Vpseqt for the overall pool.
Pages can also be moved back and forth between the internal random and
sequential LRU queues
depending upon their last access.

Your testing is very interesting, implying that the resulting filter factor
also comes into play determining
whether Vpseqt is applied or not. This might explain a few processing
anomolies I've seen over the years.
Assuming (dangerous) that COL1 has no index, what did Explain show for the
access path when you changed
your query to a stage 2 predicate?

Certainly any feedback you receive from IBM will be of great help.

I certainly also agree that pool management would be made much easier and
more realistic if Vpseqt
was applied in a much stricter form.

Regards,
Joel




Message text written by DB2 Database Discussion list at IDUG
>DB2 community:

I have been doing some additional testing of how sequential detection
works with the VPSEQT threshold and thought that I would share my testing
results with you. Any collaboration that you can provide to either confirm
or deny these findings would be appreciated. My hope is that by doing
this, more people will know how DB2 actually works, and also through this
DB2 will become an even better product! Almost all of these observations
are being verified with IBM. I am not yet 100% sure that these are indeed
facts, but am fairly confident that this information is factual.

Most people assume that the VPSEQT threshold is honored by all
sequential access. In other words, whenever the portion of a bufferpool
that is occupied by sequentially read pages meets or exceeds this
threshold, the bufferpool will then begin reusing these pages by subsequent
sequential access. By doing this, the bufferpool will avoid becoming
completely saturated by sequential processes (and can be used in a better
way by random I/O processes).

One thing that is generally not known is that sequential detection
does not always honor the VPSEQT threshold. I found this by observing some
application processes that completely saturated the bufferpool that they
used. An example of a query that did this was an orphan check for a child
table. The following query was found to quickly occupy most of the pages
in a bufferpool:

SELECT foreign_key
FROM child_table
WHERE NOT EXISTS (
SELECT 1 FROM PARENT_TABLE
WHERE foreign_key = primary_key)
FETCH FIRST 1 ROWS ONLY;

In the above query, when the "FETCH FIRST 1 ROWS ONLY" is missing, the I/O
will be sequential (via the optimized access path) and will honor the
VPSEQT threshold. When this clause is added, sequential detection will be
used and this query will quickly saturate a bufferpool (as long as it runs
completely without returning any rows).

Here is another query that I ran that quickly saturated the bufferpool:


SELECT * FROM LARGE_TABLE
WHERE COL1 = X'01'
OPTIMIZE FOR 1 ROWS;

Once again, the predicate filters all the rows and sequential detection is
invoked to resolve the query. When I ran similar queries to these that
returned most of the rows but still invoked sequential detection, they
seemed to honor the VPSEQT threshold.

Since the filter factor seems to dictate whether or not VPSEQT is honored,
I tried changing the above query to a stage 2 predicate (while still
maintaining a 100% filter factor). The query then became:

SELECT * FROM LARGE_TABLE
WHERE SUBSTR(COL1,1,1) = X'01'
OPTIMIZE FOR 1 ROWS;

This query also returned no rows but the VPSEQT threshold was honored by
this query.

I am hoping that this is something that will be changed (and am working
through the requirements submission process to accomplish this), but this
seems to be the way that DB2 works for now. My own belief is that it would
be far easier to configure the bufferpools when sequential access would
ALWAYS honor the VPSEQT threshold. I manage several pagesets that have a
combinition of random and sequential detection I/O. I really do not have a
good way of bufferpool management for these pagesets other than to put them
in a really big bufferpool so the random I/O can benefit by buffer hits.
In my own mind, this seems to be a poor way to use virtual storage.

Larry Kirkpatrick
Database Consultant
Mutual of Omaha<

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Larry Kirkpatrick

Re: Some observations regarding the honoring of the VPSEQT threshold
(in response to Ron Root)
Joel,

Here is probably more information than you really wanted to see about
the testing that I performed. This is the same docs that I am sharing with
IBM.

To get a better handle on what happens with the VPSEQT threshold, I set up
a laboratory situation. To do this I set up the following table:

CREATE TABLE PDBR111.TDBR_NUM_TEST
(NUM_COL1 INTEGER NOT NULL)
IN @DTK312.DBRTBNBF
AUDIT NONE ;

One index was created on this table defined as follows:

CREATE UNIQUE INDEX @DTK312.BUFTN1
ON PDBR111.TDBR_NUM_TEST
( NUM_COL1 ASC )
USING STOGROUP DBRIX001
PRIQTY 7200
SECQTY 7200
ERASE NO
FREEPAGE 0
PCTFREE 5
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP29
CLOSE YES
PIECESIZE 2 G
COPY NO
DEFINE YES ;

The bufferpool, BP29 is defined as:

VPSIZE(04000) VPTYPE(PRIMARY) VPSEQT(10) DWQT(02) VDWQT(00) VPPSEQT(0)

The table PDBR111.TDBR_NUM_TEST was populated with all even numbers from 2
to 1,600,000 (in other words, a total of 800,000 rows). This populated the
index, @DTK312.BUFTN1 with 2299 leaf pages and 2310 actual pages. This
means that the entire index will easily fit into the assigned bufferpool.

An application was then written that performed the following loop:

1) Get the current time,
2) Do 1000 random existence check queries using a specific key on table
PDBR111.TDBR_NUM_TEST,
3) Get the current time,
4) Report the difference between the two times.

This four step process was repeated with a 20 second delay (sleep period)
between iterations. The query used for process #2 was:

SELECT SUBSTR('Y',1,1) INTO :WRK-EXISTS-IND
FROM SYSIBM.SYSDUMMY1 A
WHERE EXISTS
(SELECT 1 FROM PDBR111.TDBR_NUM_TEST
WHERE NUM_COL1 =
(SELECT INTEGER(RAND() * 1600000)
FROM SYSIBM.SYSDUMMY1)
AND A.IBMREQD = A.IBMREQD)

By running this process, I could determine how the bufferpool affected
the response time. When no other process was running, the average duration
for the 1000 calls became 100 milliseconds. (This baseline test was done
with the index @DTK312.BUFTN1 as the sole occupant of bufferpool BP29.

I then placed a very large tablespace into BP29 (in addition to table
PDBR111.TDBR_NUM_TEST). While I ran the process described above, I ran the
following query against a table called LARGE_TABLE (This is the table
that is now the second occupant of BP29 ):

SELECT * FROM LARGE_TABLE.

When I did this, explain shows sequential prefetch being used for this
competing query and the performance on my original (random) benchmark
process remained unchanged. I continued to realize about 1000 calls in 100
milliseconds.

I then changed the competing query to:

SELECT * FROM LARGE_TABLE
WHERE COL1 = X'01' ;

In the above query, COL1 is not found in any indexes and very few (like
none) if any rows have a value of X'01' on column COL1. Once again,
explain showed an access path with sequential prefetch in effect. When I
ran the above query with my original process, the performance on my
original (random) benchmark once again remained unchanged. I continued to
realize about 1000 calls in 100 milliseconds.

I then changed the competing query to:

SELECT * FROM LARGE_TABLE
WHERE COL1 = X'01'
OPTIMIZE FOR 1 ROWS;

The explain for this query showed no sequential prefetch with a tablespace
scan. When I ran the above query with my original process, the performance
on my benchmark degraded to about 1000 calls in 1200 milliseconds. I found
that bufferpool BP29 had now become overrun with pages from table
LARGE_TABLE . This overrun did not help the performance of the competing
query but caused a twelve-fold degradation to the benchmark process. This
test shows how sequential prefetch will no longer honor VPSEQT.

I then changed the competing query to:

SELECT * FROM LARGE_TABLE
WHERE COL1 >= X'00'
OPTIMIZE FOR 1 ROWS;

The explain for this query also showed no sequential prefetch with a
tablespace scan. When I ran the above query with my original process, the
performance on my benchmark once again showed the original benchmark of
about 1000 calls in 100 milliseconds. I found that the occupation of BP29
from "LARGE_TABLE" pages had honored the VPSEQT threshold.

I then changed the competing query to:

SELECT * FROM LARGE_TABLE
WHERE SUBSTR(COL1,1,1) = X'01'
OPTIMIZE FOR 1 ROWS;

The explain for this query also showed no sequential prefetch with a
tablespace scan. When I ran the above query with my original process, the
performance on my benchmark continued to show the original benchmark of
about 1000 calls in 100 milliseconds. I found that the occupation of BP29
from "LARGE_TABLE" pages had once again honored the VPSEQT threshold.

Larry Kirkpatrick
Database Consultant
Mutual of Omaha




"Joel Goldstein"
<[login to unmask email] To: [login to unmask email]
USERVE.COM> cc:
Sent by: "DB2 Data Subject: Re: Some observations regarding the honoring of the VPSEQT threshold
Base Discussion
List"
<[login to unmask email]
>


01/11/2004 08:35 PM
Please respond to
"DB2 Database
Discussion list at
IDUG"






Hi Larry,

It's been observed for years that DynPref not only can flood the pool, but
usually
causes many pages to be read into the pool that are not accessed by the
application,
generating very low and often negative system hit ratios. Certainly this
has made
sense (although not happliy) in transaction based systems where DynPref is
turned on
and off frequently. I haven't looked recently, but there was some doc at
one time
that stated Vpseqt would manage the sequential pages for "one scanner", so
even
multiple large scanners could easily use 100% of the pool pages. Also,
turning DynPref off
and then back on again would logically have to start from the beginning to
track sequential pages,
and allow many more of them to remain in the existing pool pages beyond the
Vpseqt for the overall pool.
Pages can also be moved back and forth between the internal random and
sequential LRU queues
depending upon their last access.

Your testing is very interesting, implying that the resulting filter factor
also comes into play determining
whether Vpseqt is applied or not. This might explain a few processing
anomolies I've seen over the years.
Assuming (dangerous) that COL1 has no index, what did Explain show for the
access path when you changed
your query to a stage 2 predicate?

Certainly any feedback you receive from IBM will be of great help.

I certainly also agree that pool management would be made much easier and
more realistic if Vpseqt
was applied in a much stricter form.

Regards,
Joel




Message text written by DB2 Database Discussion list at IDUG
>DB2 community:

I have been doing some additional testing of how sequential detection
works with the VPSEQT threshold and thought that I would share my testing
results with you. Any collaboration that you can provide to either confirm
or deny these findings would be appreciated. My hope is that by doing
this, more people will know how DB2 actually works, and also through this
DB2 will become an even better product! Almost all of these observations
are being verified with IBM. I am not yet 100% sure that these are indeed
facts, but am fairly confident that this information is factual.

Most people assume that the VPSEQT threshold is honored by all
sequential access. In other words, whenever the portion of a bufferpool
that is occupied by sequentially read pages meets or exceeds this
threshold, the bufferpool will then begin reusing these pages by subsequent
sequential access. By doing this, the bufferpool will avoid becoming
completely saturated by sequential processes (and can be used in a better
way by random I/O processes).

One thing that is generally not known is that sequential detection
does not always honor the VPSEQT threshold. I found this by observing some
application processes that completely saturated the bufferpool that they
used. An example of a query that did this was an orphan check for a child
table. The following query was found to quickly occupy most of the pages
in a bufferpool:

SELECT foreign_key
FROM child_table
WHERE NOT EXISTS (
SELECT 1 FROM PARENT_TABLE
WHERE foreign_key = primary_key)
FETCH FIRST 1 ROWS ONLY;

In the above query, when the "FETCH FIRST 1 ROWS ONLY" is missing, the I/O
will be sequential (via the optimized access path) and will honor the
VPSEQT threshold. When this clause is added, sequential detection will be
used and this query will quickly saturate a bufferpool (as long as it runs
completely without returning any rows).

Here is another query that I ran that quickly saturated the bufferpool:


SELECT * FROM LARGE_TABLE
WHERE COL1 = X'01'
OPTIMIZE FOR 1 ROWS;

Once again, the predicate filters all the rows and sequential detection is
invoked to resolve the query. When I ran similar queries to these that
returned most of the rows but still invoked sequential detection, they
seemed to honor the VPSEQT threshold.

Since the filter factor seems to dictate whether or not VPSEQT is honored,
I tried changing the above query to a stage 2 predicate (while still
maintaining a 100% filter factor). The query then became:

SELECT * FROM LARGE_TABLE
WHERE SUBSTR(COL1,1,1) = X'01'
OPTIMIZE FOR 1 ROWS;

This query also returned no rows but the VPSEQT threshold was honored by
this query.

I am hoping that this is something that will be changed (and am working
through the requirements submission process to accomplish this), but this
seems to be the way that DB2 works for now. My own belief is that it would
be far easier to configure the bufferpools when sequential access would
ALWAYS honor the VPSEQT threshold. I manage several pagesets that have a
combinition of random and sequential detection I/O. I really do not have a
good way of bufferpool management for these pagesets other than to put them
in a really big bufferpool so the random I/O can benefit by buffer hits.
In my own mind, this seems to be a poor way to use virtual storage.

Larry Kirkpatrick
Database Consultant
Mutual of Omaha<

---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm





---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Max Scarpa

Re: Some observations regarding the honoring of the VPSEQT threshold
(in response to Larry Kirkpatrick)
Larry,

while I'm waiting Miss January I'm trying to do the same kind of tests you
performed, the final scope is to monitor via IFCIDs the behaviour of the
bufferpool. I'd like to know some infos to be sure the test is similar.

I defined a table equal TDBR_NUM_TEST, its index and I populated them with
the same 800.000 rows, 2299 leaf page (even numbers). I tested the queries
for a big table (52619 pages, is it enough ?) and I obtained the same
access path (no 'S' for queries with optimize for 1 row), I use the column
'subsystem' with only 33 values for subsystem 'DB2T' out of 859104 values
for subsystem 'DB2P'. Is it enough similar to your test ?

I'm writing (in REXX) an equivalent (even if very slow) procedure to
execute the 4 steps as you did, I only slightly changed the query from

SELECT SUBSTR('Y',1,1) INTO :WRK-EXISTS-IND
FROM SYSIBM.SYSDUMMY1 A
WHERE EXISTS
..........
to

SELECT SUBSTR('Y',1,1)
FROM SYSIBM.SYSDUMMY1 A
WHERE EXISTS
.............

ie I eliminated the output host variable for 'Y' if the value exists, I add
a counter in the REXX to see which of the 1000 iterations gives a positive
check. I think that this small modification doesn't matter, isn't it ?

I defined a BP (BP10) for the index (for now it's the only object using
that BP) and probably tomorrow I'll start the tests, moving the large
tablespace to BP10 following your order and starting a GTF trace.

BTW which machine do you have ? Which monitor do you use to check BP pages
?

regards
Max Scarpa

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Larry Kirkpatrick

Re: Some observations regarding the honoring of the VPSEQT threshold
(in response to Max Scarpa)
Max,

Here are my comments on your test:

1) The size of your big table looks to be large enough to make the
test work. I figure (roughly) that it will take about 15 minutes to fetch
all the contents of your table, which should give you enough elapsed time
for running the random process several times.

2) If you are using "WHERE SUBSYSTEM = 'DB2T' " as your predicate,
it should give you enough filtering to duplicate my results. You may wish
to retest with " WHERE SUBSYSTEM = 'XXXX' " to see if you get any different
results when all rows are filtered out.

3) Your random query should be fine.

4) Two factors that may get in the way of duplicating my results are
1) Since you are running in REXX and I was running a COBOL program for my
random application, you will have some extra elapsed time built in to the
process just to run the REXX code. You will also be using dynamic SQL,
which will be adding some elapsed time to the process. Where I realized a
tenfold difference in elapsed time, you may not see as much. 2) be sure
to do your testing when the mainframe is not really busy with other
processes. My results that I am documenting come from a time when the
computer could actively service my processes.

5) Be sure to stop and start objects as you move them from
bufferpool to bufferpool. There is nothing worse than performing a test
and really having an object in a bufferpool other than the one that you
think that it is in.

6) According to my systems guy, we are running on a model 1C8, Z900
series box. He says that each processor has about 202 mips that can be
used. (and there are 5 out ot the 8 of those engines on our MVS LPAR).
One other factor that may make a difference is the DASD that is used.

7) The command that I have been using to view the pages used by each
pageset in a bufferpool is "-DISPLAY BUFFERPOOL(BP29) LSTATS(*) ". I
have been sending GTF traces to IBM, but I have not been looking through
them myself. Also, Omegamon for DB2 has been helpful for me to look at
bufferpool residency.

Larry



[login to unmask email]
Sent by: "DB2 To: [login to unmask email]
Data Base cc:
Discussion List" Subject: Re: Some observations regarding the honoring of the VPSEQT threshold
<[login to unmask email]
ORG>


01/13/2004 10:13
AM
Please respond to
"DB2 Database
Discussion list
at IDUG"






Larry,

while I'm waiting Miss January I'm trying to do the same kind of tests you
performed, the final scope is to monitor via IFCIDs the behaviour of the
bufferpool. I'd like to know some infos to be sure the test is similar.

I defined a table equal TDBR_NUM_TEST, its index and I populated them with
the same 800.000 rows, 2299 leaf page (even numbers). I tested the queries
for a big table (52619 pages, is it enough ?) and I obtained the same
access path (no 'S' for queries with optimize for 1 row), I use the column
'subsystem' with only 33 values for subsystem 'DB2T' out of 859104 values
for subsystem 'DB2P'. Is it enough similar to your test ?

I'm writing (in REXX) an equivalent (even if very slow) procedure to
execute the 4 steps as you did, I only slightly changed the query from

SELECT SUBSTR('Y',1,1) INTO :WRK-EXISTS-IND
FROM SYSIBM.SYSDUMMY1 A
WHERE EXISTS
..........
to

SELECT SUBSTR('Y',1,1)
FROM SYSIBM.SYSDUMMY1 A
WHERE EXISTS
.............

ie I eliminated the output host variable for 'Y' if the value exists, I add
a counter in the REXX to see which of the 1000 iterations gives a positive
check. I think that this small modification doesn't matter, isn't it ?

I defined a BP (BP10) for the index (for now it's the only object using
that BP) and probably tomorrow I'll start the tests, moving the large
tablespace to BP10 following your order and starting a GTF trace.

BTW which machine do you have ? Which monitor do you use to check BP pages
?

regards
Max Scarpa

---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Max Scarpa

Re: Some observations regarding the honoring of the VPSEQT threshold
(in response to Larry Kirkpatrick)
Hi Larry

Thank you very much for your comment. I just finished a first test, of
course zeroing BP statistics via STOP/START objects in BP10. But according
my MainView monitor (and from explain), after RUNSTATS the query on the
table with even numbers table, seems to execute a TS scan as well so the
index didn't perform any I/O.....while before I had an index access...maybe
I did something wrong. Anyway your rough estimate seems to be correct.

Tomorrow I'll be off, I'll check & retry the tests thursday. Thank you
again for your comments. I'll let you know the results (if any.....).

Best regards

Max Scarpa

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Larry Kirkpatrick

Re: Some observations regarding the honoring of the VPSEQT threshold
(in response to Max Scarpa)
Ron,

I notice that you have not had any response to this, so I thought
that I might give it a try. If I give you really bad advice, I am sure
that corrections will be coming.

Here are some of my thoughts:

1) I would not have a 50-50 mix of sequential and random access within
most of my bufferpools (as a general rule). You should separate out those
objects that usually use sequential and the bufferpools that are assigned
to those objects should have a very high VPSEQT (like about 95). Separate
out your DSNDB07 (or whatever you call the DB2 sort work database) into a
bufferpool assigned to those objects. Since they will be using sequential
access much of the time, you can use 95 for that VPSEQT.

2) DB2 objects that realize random access should be separated according to
how skewed that access is. If you have objects that are very large with
truly random access, you should put those objects together in one
bufferpool and never expect to see a good buffer hit ratio in that pool.
In addition, since objects in that pool use random access, the VPSEQT
should be lower (like 10).

3) If you have a OLTP DB2 platform, and at the same time have objects that
are very infrequently used (like overnight only), I would suggest that
those objects (that are infrequently used) go into a bufferpool just for
them. In that way, when a process is running against those infrequently
used objects, the OLTP buffers will not be hammered.

4) Consider that if one thread is using sequential access against a
tablespace (or indexspace) and nothing else is doing sequential access in
that bufferpool, then 64 pages assigned to sequential access is as many as
you would need. (note that this is a made up scenario, you generally want
to allow multiple threads doing sequential access, or you want to allow
multiple objects being accessed sequentially). Lets say you have a
bufferpool with 10,000 pages in it. If you set the VPSEQT=10, then you
have 1000 pages that can be used by sequential processes before those 1000
pages are reused. This means that you can support up to 15 concurrent
sequential processes within that pool. The bottom line to this thought
process is don't overstate the VPSEQT parameter. For bufferpools that have
some but very little sequential activity, set VPSEQT small enough that the
sequential activity doesn't flush the bufferpool.

5) If you have objects that generally experience sequential detection,
they will be very "buffer UNfriendly". I try to isolate these objects as a
general rule.

Of course, there are many more rules of thumb in working with bufferpools,
this is just a few that relate to the VPSEQT parameter.

Larry




"Ron Root"
<[login to unmask email] To: [login to unmask email]
TE.TX.US> cc:
Sent by: "DB2 Subject: Re: Some observations regarding the honoring of the VPSEQT threshold
Data Base
Discussion List"
<[login to unmask email]
ORG>


01/12/2004 09:09
AM
Please respond to
"DB2 Database
Discussion list
at IDUG"






While on the subject...
Does anyone have any sage advise for setting VPSEQT (without benefit of
Joel's product)? Most of our application buffer pools are split about
50-50
between sequential and random in the long haul, with spikes for each in the
batch cycle. We currently have it set at 80 for most of the application
buffer pools.
(DB2 V6 with V7 in the wings).

Ron Root
DB2 Performance and Capacity Planning
Texas Comptroller of Public Accounts


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Joel Goldstein
Sent: Sunday, January 11, 2004 8:35 PM
To: [login to unmask email]
Subject: Re: Some observations regarding the honoring of the VPSEQT
threshold


Hi Larry,

It's been observed for years that DynPref not only can flood the pool, but
usually
causes many pages to be read into the pool that are not accessed by the
application,
generating very low and often negative system hit ratios. Certainly this
has made
sense (although not happliy) in transaction based systems where DynPref is
turned on
and off frequently. I haven't looked recently, but there was some doc at
one time
that stated Vpseqt would manage the sequential pages for "one scanner", so
even
multiple large scanners could easily use 100% of the pool pages. Also,
turning DynPref off
and then back on again would logically have to start from the beginning to
track sequential pages,
and allow many more of them to remain in the existing pool pages beyond the
Vpseqt for the overall pool.
Pages can also be moved back and forth between the internal random and
sequential LRU queues
depending upon their last access.

Your testing is very interesting, implying that the resulting filter factor
also comes into play determining
whether Vpseqt is applied or not. This might explain a few processing
anomolies I've seen over the years.
Assuming (dangerous) that COL1 has no index, what did Explain show for the
access path when you changed
your query to a stage 2 predicate?

Certainly any feedback you receive from IBM will be of great help.

I certainly also agree that pool management would be made much easier and
more realistic if Vpseqt
was applied in a much stricter form.

Regards,
Joel




Message text written by DB2 Database Discussion list at IDUG
>DB2 community:

I have been doing some additional testing of how sequential detection
works with the VPSEQT threshold and thought that I would share my testing
results with you. Any collaboration that you can provide to either confirm
or deny these findings would be appreciated. My hope is that by doing
this, more people will know how DB2 actually works, and also through this
DB2 will become an even better product! Almost all of these observations
are being verified with IBM. I am not yet 100% sure that these are indeed
facts, but am fairly confident that this information is factual.

Most people assume that the VPSEQT threshold is honored by all
sequential access. In other words, whenever the portion of a bufferpool
that is occupied by sequentially read pages meets or exceeds this
threshold, the bufferpool will then begin reusing these pages by subsequent
sequential access. By doing this, the bufferpool will avoid becoming
completely saturated by sequential processes (and can be used in a better
way by random I/O processes).

One thing that is generally not known is that sequential detection
does not always honor the VPSEQT threshold. I found this by observing some
application processes that completely saturated the bufferpool that they
used. An example of a query that did this was an orphan check for a child
table. The following query was found to quickly occupy most of the pages
in a bufferpool:

SELECT foreign_key
FROM child_table
WHERE NOT EXISTS (
SELECT 1 FROM PARENT_TABLE
WHERE foreign_key = primary_key)
FETCH FIRST 1 ROWS ONLY;

In the above query, when the "FETCH FIRST 1 ROWS ONLY" is missing, the I/O
will be sequential (via the optimized access path) and will honor the
VPSEQT threshold. When this clause is added, sequential detection will be
used and this query will quickly saturate a bufferpool (as long as it runs
completely without returning any rows).

Here is another query that I ran that quickly saturated the bufferpool:


SELECT * FROM LARGE_TABLE
WHERE COL1 = X'01'
OPTIMIZE FOR 1 ROWS;

Once again, the predicate filters all the rows and sequential detection is
invoked to resolve the query. When I ran similar queries to these that
returned most of the rows but still invoked sequential detection, they
seemed to honor the VPSEQT threshold.

Since the filter factor seems to dictate whether or not VPSEQT is honored,
I tried changing the above query to a stage 2 predicate (while still
maintaining a 100% filter factor). The query then became:

SELECT * FROM LARGE_TABLE
WHERE SUBSTR(COL1,1,1) = X'01'
OPTIMIZE FOR 1 ROWS;

This query also returned no rows but the VPSEQT threshold was honored by
this query.

I am hoping that this is something that will be changed (and am working
through the requirements submission process to accomplish this), but this
seems to be the way that DB2 works for now. My own belief is that it would
be far easier to configure the bufferpools when sequential access would
ALWAYS honor the VPSEQT threshold. I manage several pagesets that have a
combinition of random and sequential detection I/O. I really do not have a
good way of bufferpool management for these pagesets other than to put them
in a really big bufferpool so the random I/O can benefit by buffer hits.
In my own mind, this seems to be a poor way to use virtual storage.

Larry Kirkpatrick
Database Consultant
Mutual of Omaha<

----------------------------------------------------------------------------

-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
.
The IDUG List Admins can be reached at [login to unmask email]
Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ron Root

Re: Some observations regarding the honoring of the VPSEQT threshold
(in response to Larry Kirkpatrick)
Larry,
Thanks for your comments. I am new to this shop and intuitively believed we
were overstating VPSEQT. Your comments support that belief. I was already
considering segregating objects with heavy sequential access. Of course the
complicating factor is the different access patterns between batch and
online. But that is another story.

Ron

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of [login to unmask email]
Sent: Thursday, January 15, 2004 3:39 PM
To: [login to unmask email]
Subject: Re: Some observations regarding the honoring of the VPSEQT
threshold


Ron,

I notice that you have not had any response to this, so I thought
that I might give it a try. If I give you really bad advice, I am sure
that corrections will be coming.

Here are some of my thoughts:

1) I would not have a 50-50 mix of sequential and random access within
most of my bufferpools (as a general rule). You should separate out those
objects that usually use sequential and the bufferpools that are assigned
to those objects should have a very high VPSEQT (like about 95). Separate
out your DSNDB07 (or whatever you call the DB2 sort work database) into a
bufferpool assigned to those objects. Since they will be using sequential
access much of the time, you can use 95 for that VPSEQT.

2) DB2 objects that realize random access should be separated according to
how skewed that access is. If you have objects that are very large with
truly random access, you should put those objects together in one
bufferpool and never expect to see a good buffer hit ratio in that pool.
In addition, since objects in that pool use random access, the VPSEQT
should be lower (like 10).

3) If you have a OLTP DB2 platform, and at the same time have objects that
are very infrequently used (like overnight only), I would suggest that
those objects (that are infrequently used) go into a bufferpool just for
them. In that way, when a process is running against those infrequently
used objects, the OLTP buffers will not be hammered.

4) Consider that if one thread is using sequential access against a
tablespace (or indexspace) and nothing else is doing sequential access in
that bufferpool, then 64 pages assigned to sequential access is as many as
you would need. (note that this is a made up scenario, you generally want
to allow multiple threads doing sequential access, or you want to allow
multiple objects being accessed sequentially). Lets say you have a
bufferpool with 10,000 pages in it. If you set the VPSEQT=10, then you
have 1000 pages that can be used by sequential processes before those 1000
pages are reused. This means that you can support up to 15 concurrent
sequential processes within that pool. The bottom line to this thought
process is don't overstate the VPSEQT parameter. For bufferpools that have
some but very little sequential activity, set VPSEQT small enough that the
sequential activity doesn't flush the bufferpool.

5) If you have objects that generally experience sequential detection,
they will be very "buffer UNfriendly". I try to isolate these objects as a
general rule.

Of course, there are many more rules of thumb in working with bufferpools,
this is just a few that relate to the VPSEQT parameter.

Larry




"Ron Root"
<[login to unmask email] To:
[login to unmask email]
TE.TX.US> cc:
Sent by: "DB2 Subject: Re: Some
observations regarding the honoring of the VPSEQT threshold
Data Base
Discussion List"
<[login to unmask email]
ORG>


01/12/2004 09:09
AM
Please respond to
"DB2 Database
Discussion list
at IDUG"






While on the subject...
Does anyone have any sage advise for setting VPSEQT (without benefit of
Joel's product)? Most of our application buffer pools are split about
50-50
between sequential and random in the long haul, with spikes for each in the
batch cycle. We currently have it set at 80 for most of the application
buffer pools.
(DB2 V6 with V7 in the wings).

Ron Root
DB2 Performance and Capacity Planning
Texas Comptroller of Public Accounts


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Joel Goldstein
Sent: Sunday, January 11, 2004 8:35 PM
To: [login to unmask email]
Subject: Re: Some observations regarding the honoring of the VPSEQT
threshold


Hi Larry,

It's been observed for years that DynPref not only can flood the pool, but
usually
causes many pages to be read into the pool that are not accessed by the
application,
generating very low and often negative system hit ratios. Certainly this
has made
sense (although not happliy) in transaction based systems where DynPref is
turned on
and off frequently. I haven't looked recently, but there was some doc at
one time
that stated Vpseqt would manage the sequential pages for "one scanner", so
even
multiple large scanners could easily use 100% of the pool pages. Also,
turning DynPref off
and then back on again would logically have to start from the beginning to
track sequential pages,
and allow many more of them to remain in the existing pool pages beyond the
Vpseqt for the overall pool.
Pages can also be moved back and forth between the internal random and
sequential LRU queues
depending upon their last access.

Your testing is very interesting, implying that the resulting filter factor
also comes into play determining
whether Vpseqt is applied or not. This might explain a few processing
anomolies I've seen over the years.
Assuming (dangerous) that COL1 has no index, what did Explain show for the
access path when you changed
your query to a stage 2 predicate?

Certainly any feedback you receive from IBM will be of great help.

I certainly also agree that pool management would be made much easier and
more realistic if Vpseqt
was applied in a much stricter form.

Regards,
Joel




Message text written by DB2 Database Discussion list at IDUG
>DB2 community:

I have been doing some additional testing of how sequential detection
works with the VPSEQT threshold and thought that I would share my testing
results with you. Any collaboration that you can provide to either confirm
or deny these findings would be appreciated. My hope is that by doing
this, more people will know how DB2 actually works, and also through this
DB2 will become an even better product! Almost all of these observations
are being verified with IBM. I am not yet 100% sure that these are indeed
facts, but am fairly confident that this information is factual.

Most people assume that the VPSEQT threshold is honored by all
sequential access. In other words, whenever the portion of a bufferpool
that is occupied by sequentially read pages meets or exceeds this
threshold, the bufferpool will then begin reusing these pages by subsequent
sequential access. By doing this, the bufferpool will avoid becoming
completely saturated by sequential processes (and can be used in a better
way by random I/O processes).

One thing that is generally not known is that sequential detection
does not always honor the VPSEQT threshold. I found this by observing some
application processes that completely saturated the bufferpool that they
used. An example of a query that did this was an orphan check for a child
table. The following query was found to quickly occupy most of the pages
in a bufferpool:

SELECT foreign_key
FROM child_table
WHERE NOT EXISTS (
SELECT 1 FROM PARENT_TABLE
WHERE foreign_key = primary_key)
FETCH FIRST 1 ROWS ONLY;

In the above query, when the "FETCH FIRST 1 ROWS ONLY" is missing, the I/O
will be sequential (via the optimized access path) and will honor the
VPSEQT threshold. When this clause is added, sequential detection will be
used and this query will quickly saturate a bufferpool (as long as it runs
completely without returning any rows).

Here is another query that I ran that quickly saturated the bufferpool:


SELECT * FROM LARGE_TABLE
WHERE COL1 = X'01'
OPTIMIZE FOR 1 ROWS;

Once again, the predicate filters all the rows and sequential detection is
invoked to resolve the query. When I ran similar queries to these that
returned most of the rows but still invoked sequential detection, they
seemed to honor the VPSEQT threshold.

Since the filter factor seems to dictate whether or not VPSEQT is honored,
I tried changing the above query to a stage 2 predicate (while still
maintaining a 100% filter factor). The query then became:

SELECT * FROM LARGE_TABLE
WHERE SUBSTR(COL1,1,1) = X'01'
OPTIMIZE FOR 1 ROWS;

This query also returned no rows but the VPSEQT threshold was honored by
this query.

I am hoping that this is something that will be changed (and am working
through the requirements submission process to accomplish this), but this
seems to be the way that DB2 works for now. My own belief is that it would
be far easier to configure the bufferpools when sequential access would
ALWAYS honor the VPSEQT threshold. I manage several pagesets that have a
combinition of random and sequential detection I/O. I really do not have a
good way of bufferpool management for these pagesets other than to put them
in a really big bufferpool so the random I/O can benefit by buffer hits.
In my own mind, this seems to be a poor way to use virtual storage.

Larry Kirkpatrick
Database Consultant
Mutual of Omaha<

----------------------------------------------------------------------------

-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
.
The IDUG List Admins can be reached at [login to unmask email]
Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

----------------------------------------------------------------------------
-----

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Max Scarpa

Re: Some observations regarding the honoring of the VPSEQT threshold
(in response to Ron Root)
Larry

I just performed some preliminary tests and I was unable to mimic your
result,until now.

For example in my test the 'random' query after the first 8 random pages
turns to Dyn prefetch (value 'D' for IFCID 006) so honoring the 5/8 rule
(it's nice to see it so clearly) even if access path shows clearly 'I'
(index access) as access method.
This is confirmed by IFCID007 (after 8 random I/Os I see 31/32 pages reads
- it's amazing how a trivial REXX can manage IFCIDs and confirm many
things you only read before !!)

I can see via MainView at dataset/BP level that after the index is loaded
(2301 pages) in BP, when I start the query for the big table a lot of index
pages are kicked off (only 424 approx remain for the index) and the BP is
flooded by sequential pages (3544 approx) even if the flag in IFCID 006 is
'S' and VPSEQT is 10 (but even if it's 90......). If the big query is
started first the index NEVER load all the pages. (only 425/454).

I didn't test the case with OPTIMIZE FOR 1 ROW, I've to check if the
'random' query on even number (executed via REXX and DB2WWWX interface) and
the query on the big table (a spufi batch) can induce some modifications.

Just first quick result from quick tests.

Best regards
Max Scarpa

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm