[z/OS]High Getpages & Buffer Updates for Insert on one member in Data Sharing

Adrian Collett

[z/OS]High Getpages & Buffer Updates for Insert on one member in Data Sharing
Hi,

We are seeing abnormally high Getpages AND Buffer Updates on Data pages
for an Insert Process.

For example: for about 6K inserts we are seeing more than 6 million
Getpages and 6 million Page Updates.

N.B. All of these getpages/updates are in the Buffer Pool dedicated to
the Data Pages, not the index, which is in another pool.

However, we only see this if we run the insert on one particular member
of the Data Sharing Group.
When we run the same job on the other member of the group the getpages
and buffer updates drop down to 3K and 15K for more than 10K inserts.
Which is roughly what I would expect.

Here's some further info:
2-way Data Sharing, DB2 V8 NFM, Partitioned Tablespace, Row-Level
Locking. About 5million rows in the partition being inserted into.

Is this behaviour a "feature" of Data Sharing ???????

Also, can somebody tell me where I can get a copy of the insert
algorithms ??? Once upon a time they were in the Diagnosis Guide but I
can't seem to find them any more...

We think it may be a bug, but before opening a problem with IBM we
wanted to check with the experts...:-)))

Thanks in advance for any help.

Ciao,
Adrian Collett.

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Dee Reins

Re: [z/OS]High Getpages & Buffer Updates for Insert on one member in Data Sharing
(in response to Adrian Collett)
I have been under the impression that Row-Level locking was not to be used in data sharing. Data sharing facility can only lock to the page level. Could the additional i/o be cause by one data sharing subsystem accessing the other data sharing subsystem to check on the row level lock?



-------------- Original message ----------------------
From: Adrian Collett <[login to unmask email]>
> Hi,
>
> We are seeing abnormally high Getpages AND Buffer Updates on Data pages
> for an Insert Process.
>
> For example: for about 6K inserts we are seeing more than 6 million
> Getpages and 6 million Page Updates.
>
> N.B. All of these getpages/updates are in the Buffer Pool dedicated to
> the Data Pages, not the index, which is in another pool.
>
> However, we only see this if we run the insert on one particular member
> of the Data Sharing Group.
> When we run the same job on the other member of the group the getpages
> and buffer updates drop down to 3K and 15K for more than 10K inserts.
> Which is roughly what I would expect.
>
> Here's some further info:
> 2-way Data Sharing, DB2 V8 NFM, Partitioned Tablespace, Row-Level
> Locking. About 5million rows in the partition being inserted into.
>
> Is this behaviour a "feature" of Data Sharing ???????
>
> Also, can somebody tell me where I can get a copy of the insert
> algorithms ??? Once upon a time they were in the Diagnosis Guide but I
> can't seem to find them any more...
>
> We think it may be a bug, but before opening a problem with IBM we
> wanted to check with the experts...:-)))
>
> Thanks in advance for any help.
>
> Ciao,
> Adrian Collett.
>
> ______________________________________________________________________
>
> * IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You
> should have gotten an e-mail with the temporary password assigned to your
> account. Please log in and update your member profile. If you are not already an
> IDUG.org member, please register at
> http://www.idug.org/component/juser/register.html

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Ted MacNEIL

Re: [z/OS]High Getpages & Buffer Updates for Insert on one member in Data Sharing
(in response to Dee Reins)
>I have been under the impression that Row-Level locking was not to be used in data sharing.
>Data sharing facility can only lock to the page level.
>Could the additional i/o be cause by one data sharing subsystem accessing the other data sharing subsystem to check on the row level lock?

When we first went to data-sharing under DB2 (or IMS -- PI), we were told there was NO option.
All locks were at the page level.
You could not control it.
Has that changed?
If so, it is NOT a good thing!
-
Too busy driving to stop for gas!

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Robert Catterall

Re: [z/OS]High Getpages & Buffer Updates for Insert on one member in Data Sharing
(in response to Ted MacNEIL)
The row-level locking could have something to do with this, Adrian.

In a data sharing environment, when row-level locking is in effect for a
tablespace, DB2 will acquire page-level physical locks (page P-locks) on
data pages that are to be updated and which belong to tablespaces that are
group buffer pool-dependent at the time. This in turn can lead to a great
deal of local buffer pool and group buffer pool activity, especially when
there are insert "hot spots" in the tablespace (as when the clustering key
value is continuously-ascending or when the tablespace doesn't have "holes"
that can accommodate new rows, so that all new rows go to the "end" of the
space). When you run the insert job on member A versus member B, it may be
that the tablespace is not GBP-dependent (thus no data page P-locking
associated with the inserts).

If I were you, I'd want to first evaluate whether or not row-level locking
is really necessary for the tablespace. If it isn't (that is, if you can
use standard page-level locking without running into timeout and deadlock
problems), go with LOCKSIZE PAGE. If you must use row-level locking for the
tablespace in question, you could:

- Take steps to ensure that the tablespace will not be group buffer
pool-dependent when the insert job runs, or
- Change the table's clustering sequence and/or partitioning key (not
trivial, I know) so that insert activity is more evenly spread across the
tablespace (or ensure through PCTFREE and periodic REORGS that there are
"holes" for new rows, if your clustering key is not continuously-ascending),
or
- Try the MEMBER CLUSTER option to reduce page P-lock contention (it's
possible that a lot of the buffer pool update activity you see has to do
with P-lock contention related to space map pages - something else that
doesn't happen if the page set is not group buffer pool-dependent).

Again, though, I'd first want to see if I could use LOCKSIZE PAGE without
timeout and deadlock issues (and if I did get a lot of timeouts and/or
deadlocks with LOCKSIZE PAGE in effect, I'd want to know why and I'd want to
see if application code modification could remedy the situation). If you
went with LOCKSIZE PAGE and still saw the high GETPAGE activity when the job
runs on the one member versus the other, that could indicate that contention
is on space map versus data pages.

There can also be big jumps in GETPAGE activity related to inserts into a
partitioned tablespace that holds varying-length rows (which is the case
when the tablespace is compressed, even without VARCHAR columns in the
table), but that is not a data sharing issue (it has to do with the nature
of space map pages in partitioned tablespaces prior to DB2 V9 universal
tablespaces) and you have indicated that the problem you see goes away when
the insert job runs on one member versus another in your data sharing group.

Robert


On Tue, Jan 20, 2009 at 1:04 PM, Adrian Collett <[login to unmask email]>wrote:

> Hi,
>
> We are seeing abnormally high Getpages AND Buffer Updates on Data pages for
> an Insert Process.
>
> For example: for about 6K inserts we are seeing more than 6 million
> Getpages and 6 million Page Updates.
>
> N.B. All of these getpages/updates are in the Buffer Pool dedicated to the
> Data Pages, not the index, which is in another pool.
>
> However, we only see this if we run the insert on one particular member of
> the Data Sharing Group.
> When we run the same job on the other member of the group the getpages and
> buffer updates drop down to 3K and 15K for more than 10K inserts.
> Which is roughly what I would expect.
>
> Here's some further info:
> 2-way Data Sharing, DB2 V8 NFM, Partitioned Tablespace, Row-Level Locking.
> About 5million rows in the partition being inserted into.
>
> Is this behaviour a "feature" of Data Sharing ???????
> Also, can somebody tell me where I can get a copy of the insert algorithms
> ??? Once upon a time they were in the Diagnosis Guide but I can't seem to
> find them any more...
>
> We think it may be a bug, but before opening a problem with IBM we wanted
> to check with the experts...:-)))
>
> Thanks in advance for any help.
>
> Ciao,
> Adrian Collett.
>
> ______________________________________________________________________
>
> * IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You
> should have gotten an e-mail with the temporary password assigned to your
> account. Please log in and update your member profile. If you are not
> already an IDUG.org member, please register at
> http://www.idug.org/component/juser/register.html
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Robert Catterall

Re: [z/OS]High Getpages & Buffer Updates for Insert on one member in Data Sharing
(in response to Robert Catterall)
Row-level locking can indeed be used in a data sharing environment - there
is no technical restriction on the use of LOCKSIZE ROW in a DB2 data sharing
system.

Here's how row-level locking affects overhead in a data sharing environment:
as I mentioned in responding to Adrian's question, when a tablespace is
defined with LOCKSIZE ROW, and a data page is to be updated (via UPDATE,
INSERT, or DELETE), and the page set (or partition) is group buffer pool
dependent (which generally means that there is currently inter-DB2
read/write interest in the page set or partition), the updating DB2 will
request a physical lock on the page in addition to requesting the logical
(i.e., "regular") lock on the row that will be inserted/updated/deleted. If
page-level locking is used for the tablespace, that page P-lock won't be
requested when the page is to be updated - only the page-level logical lock
will be requested. There is extra overhead associated with requesting the
page P-lock (necessary for data coherency protection when the page can't be
logically locked, as it can't when logical locking is at the row level), but
what can really make overhead jump is when another program running on a
different DB2 member wants to change a different row on the same page. It
also requests the page P-lock on that page, and through page P-lock
negotiation the page gets sent via the group buffer pool from the one DB2
member (the one that first acquired the P-lock on the page) to the other
member. If rows on the same page are being changed with great frequency by
programs running on two or more members of the data sharing group, the page
in question will be shipped back and forth between the systems with great
frequency.

Keep in mind that page-level P-locking will occur in a data sharing group
regardless of whether or not you have any tablespaces defined with LOCKSIZE
ROW - you'll get page P-locks on space map pages and index pages when they
are updated. So, row-level locking doesn't introduce page-level P-locking,
but it does add to the volume of page P-lock requests.

In some cases, row-level locking carries with it a relatively low overhead
cost, even in a data sharing environment, and it can be a very useful means
of alleviating timeout and deadlock problems, just as in a non-data sharing
environment. In other cases (e.g., when certain pages of a tablespace can
become very "hot"), row-level locking can significantly increase data
sharing overhead.

Bottom line: you CAN user row-level locking in a data sharing environment,
and sometimes it's a good idea to do so. That said, you ONLY want to use
row-level locking in a data sharing environment if you NEED to use it,
because you have to ensure that the benefit will outweigh the cost.

Robert


On Tue, Jan 20, 2009 at 2:17 PM, Dee Reins <[login to unmask email]> wrote:

> I have been under the impression that Row-Level locking was not to be used
> in data sharing. Data sharing facility can only lock to the page level.
> Could the additional i/o be cause by one data sharing subsystem accessing
> the other data sharing subsystem to check on the row level lock?
>
>
>
> -------------- Original message ----------------------
> From: Adrian Collett <[login to unmask email]>
> > Hi,
> >
> > We are seeing abnormally high Getpages AND Buffer Updates on Data pages
> > for an Insert Process.
> >
> > For example: for about 6K inserts we are seeing more than 6 million
> > Getpages and 6 million Page Updates.
> >
> > N.B. All of these getpages/updates are in the Buffer Pool dedicated to
> > the Data Pages, not the index, which is in another pool.
> >
> > However, we only see this if we run the insert on one particular member
> > of the Data Sharing Group.
> > When we run the same job on the other member of the group the getpages
> > and buffer updates drop down to 3K and 15K for more than 10K inserts.
> > Which is roughly what I would expect.
> >
> > Here's some further info:
> > 2-way Data Sharing, DB2 V8 NFM, Partitioned Tablespace, Row-Level
> > Locking. About 5million rows in the partition being inserted into.
> >
> > Is this behaviour a "feature" of Data Sharing ???????
> >
> > Also, can somebody tell me where I can get a copy of the insert
> > algorithms ??? Once upon a time they were in the Diagnosis Guide but I
> > can't seem to find them any more...
> >
> > We think it may be a bug, but before opening a problem with IBM we
> > wanted to check with the experts...:-)))
> >
> > Thanks in advance for any help.
> >
> > Ciao,
> > Adrian Collett.
> >
> > ______________________________________________________________________
> >
> > * IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events*
> > ______________________________________________________________________
> >
> >
> >
> >
> > IDUG.org was recently updated requiring members to use a new password.
> You
> > should have gotten an e-mail with the temporary password assigned to your
> > account. Please log in and update your member profile. If you are not
> already an
> > IDUG.org member, please register at
> > http://www.idug.org/component/juser/register.html
>
> ______________________________________________________________________
>
> * IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You
> should have gotten an e-mail with the temporary password assigned to your
> account. Please log in and update your member profile. If you are not
> already an IDUG.org member, please register at
> http://www.idug.org/component/juser/register.html
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Adrian Collett

Re: [z/OS]High Getpages & Buffer Updates for Insert on one member in Data Sharing
(in response to Robert Catterall)
Thanks for the help Robert,
I think you're probably right, it has to be something to do with RLL and
the Partition being GBP - hoiwever, what is really puzzling me is the
reason for so many getpages/updates ... for a simple insert we are
seeing an average of 1000 getpages/updates...when the process runs
"normally" the average is 2 !!

Even if all the rows are inserted on the same page with all the extra
page p-locking I can't see why we should be getting 1000 getpages per
insert.... any ideas ??

Thanks once again.

Ciao,
Adrian




Robert Catterall ha scritto:
> Row-level locking can indeed be used in a data sharing environment -
> there is no technical restriction on the use of LOCKSIZE ROW in a DB2
> data sharing system.
>
> Here's how row-level locking affects overhead in a data sharing
> environment: as I mentioned in responding to Adrian's question, when a
> tablespace is defined with LOCKSIZE ROW, and a data page is to be
> updated (via UPDATE, INSERT, or DELETE), and the page set (or
> partition) is group buffer pool dependent (which generally means that
> there is currently inter-DB2 read/write interest in the page set or
> partition), the updating DB2 will request a physical lock on the page
> in addition to requesting the logical (i.e., "regular") lock on the
> row that will be inserted/updated/deleted. If page-level locking is
> used for the tablespace, that page P-lock won't be requested when the
> page is to be updated - only the page-level logical lock will be
> requested. There is extra overhead associated with requesting the
> page P-lock (necessary for data coherency protection when the page
> can't be logically locked, as it can't when logical locking is at the
> row level), but what can really make overhead jump is when another
> program running on a different DB2 member wants to change a different
> row on the same page. It also requests the page P-lock on that page,
> and through page P-lock negotiation the page gets sent via the group
> buffer pool from the one DB2 member (the one that first acquired the
> P-lock on the page) to the other member. If rows on the same page are
> being changed with great frequency by programs running on two or more
> members of the data sharing group, the page in question will be
> shipped back and forth between the systems with great frequency.
>
> Keep in mind that page-level P-locking will occur in a data sharing
> group regardless of whether or not you have any tablespaces defined
> with LOCKSIZE ROW - you'll get page P-locks on space map pages and
> index pages when they are updated. So, row-level locking doesn't
> introduce page-level P-locking, but it does add to the volume of page
> P-lock requests.
>
> In some cases, row-level locking carries with it a relatively low
> overhead cost, even in a data sharing environment, and it can be a
> very useful means of alleviating timeout and deadlock problems, just
> as in a non-data sharing environment. In other cases (e.g., when
> certain pages of a tablespace can become very "hot"), row-level
> locking can significantly increase data sharing overhead.
>
> Bottom line: you CAN user row-level locking in a data sharing
> environment, and sometimes it's a good idea to do so. That said, you
> ONLY want to use row-level locking in a data sharing environment if
> you NEED to use it, because you have to ensure that the benefit will
> outweigh the cost.
>
> Robert
>


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Robert Catterall

Re: [z/OS]High Getpages & Buffer Updates for Insert on one member in Data Sharing
(in response to Adrian Collett)
You might want to make sure that you're not hitting the data manager
critical threshold for the buffer pool to which the tablespace is assigned
when you see the very high GETPAGE counts (DMTH is hit when 95% of a pool's
buffers are non-stealable, either because they are in-use or they have been
updated but not yet externalized). You can check this out via your DB2
monitor or the DB2 command -DISPLAY BUFFERPOOL (BPn) DETAIL (where BPn would
of course be replaced by the name of the buffer pool in question). You can
use DETAIL(INTERVAL) if you want to look at a more limited time period
(otherwise you get information for the period of time since the BP was last
allocated). It's conceivable that a buffer pool sized at X on member A of a
data sharing group would be smaller on member B of the same group. When
DMTH is reached, there's a GETPAGE for every change made to a page, versus
the normal situation in which DB2 can do a GETPAGE for a data page on behalf
of an inserting process and then perform multiple inserts before releasing
the page. That can lead to a very large increase in GETPAGEs.

You might want to check your group buffer pool activity, too, via -DISPLAY
GROUPBUFFERPOOL(GBPn) GDETAIL. Again, you can use GDETAIL(INTERVAL) to more
narrowly focus the time period for which data is reported. Look for write
failures and for cross-invalidations due to directory entry reclaims.
Ideally, you'll see zero for both. If either figure is non-zero you might
need to enlarge the group buffer pool.

Robert


On Wed, Jan 21, 2009 at 4:49 AM, Adrian Collett <[login to unmask email]>wrote:

> Thanks for the help Robert,
> I think you're probably right, it has to be something to do with RLL and
> the Partition being GBP - hoiwever, what is really puzzling me is the
> reason for so many getpages/updates ... for a simple insert we are seeing an
> average of 1000 getpages/updates...when the process runs "normally" the
> average is 2 !!
>
> Even if all the rows are inserted on the same page with all the extra page
> p-locking I can't see why we should be getting 1000 getpages per insert....
> any ideas ??
>
> Thanks once again.
>
> Ciao,
> Adrian
>
>
>
>
> Robert Catterall ha scritto:
>
>> Row-level locking can indeed be used in a data sharing environment -
>> there is no technical restriction on the use of LOCKSIZE ROW in a DB2 data
>> sharing system.
>>
>> Here's how row-level locking affects overhead in a data sharing
>> environment: as I mentioned in responding to Adrian's question, when a
>> tablespace is defined with LOCKSIZE ROW, and a data page is to be updated
>> (via UPDATE, INSERT, or DELETE), and the page set (or partition) is group
>> buffer pool dependent (which generally means that there is currently
>> inter-DB2 read/write interest in the page set or partition), the updating
>> DB2 will request a physical lock on the page in addition to requesting the
>> logical (i.e., "regular") lock on the row that will be
>> inserted/updated/deleted. If page-level locking is used for the tablespace,
>> that page P-lock won't be requested when the page is to be updated - only
>> the page-level logical lock will be requested. There is extra overhead
>> associated with requesting the page P-lock (necessary for data coherency
>> protection when the page can't be logically locked, as it can't when logical
>> locking is at the row level), but what can really make overhead jump is when
>> another program running on a different DB2 member wants to change a
>> different row on the same page. It also requests the page P-lock on that
>> page, and through page P-lock negotiation the page gets sent via the group
>> buffer pool from the one DB2 member (the one that first acquired the P-lock
>> on the page) to the other member. If rows on the same page are being
>> changed with great frequency by programs running on two or more members of
>> the data sharing group, the page in question will be shipped back and forth
>> between the systems with great frequency.
>>
>> Keep in mind that page-level P-locking will occur in a data sharing group
>> regardless of whether or not you have any tablespaces defined with LOCKSIZE
>> ROW - you'll get page P-locks on space map pages and index pages when they
>> are updated. So, row-level locking doesn't introduce page-level P-locking,
>> but it does add to the volume of page P-lock requests.
>>
>> In some cases, row-level locking carries with it a relatively low overhead
>> cost, even in a data sharing environment, and it can be a very useful means
>> of alleviating timeout and deadlock problems, just as in a non-data sharing
>> environment. In other cases (e.g., when certain pages of a tablespace can
>> become very "hot"), row-level locking can significantly increase data
>> sharing overhead.
>>
>> Bottom line: you CAN user row-level locking in a data sharing environment,
>> and sometimes it's a good idea to do so. That said, you ONLY want to use
>> row-level locking in a data sharing environment if you NEED to use it,
>> because you have to ensure that the benefit will outweigh the cost.
>>
>> Robert
>>
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
>
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You
> should have gotten an e-mail with the temporary password assigned to your
> account. Please log in and update your member profile. If you are not
> already an IDUG.org member, please register at
> http://www.idug.org/component/juser/register.html
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html