Increased insert time

Mike Flint

Increased insert time
*Re-Send* (not sure it got through before)
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Hello,

We're in the process of running through a large number of updates to a new
database.

On the test system, the inserts to a particular table went fine, but on the
'live' system the inserts to this table are performing very poorly.
Everything looks okay (indexes, stats, plans, extents, ...) but the profile
of the insert seems different.

This table has 300 million rows on the live system; on the test system it
only had 4 million.
The table is in 8 partitions on live, but only 1 on test.

On live, 15% of the time is being spent (on the INSERT) in DSNISMRS (Read &
Search Space Manager), and 4% of the time in DSNB1GET (retrieve page);
On test, all the time is just DASD, the only DB2 module involved is DSNISRTI
(Insert record).

(these figures from Strobe/DB2)

Any ideas on what's causing the high DSNISMRS/DSNB1GET usage? Does this
point at some problem with the index or table space?

The table has only 8 columns, and one index. The inserts are either 'random'
into existing ranges of rows, or adding new rows above the high end of the
primary key; most inserts are of the 'high key' type.

DB2 is V6.1 on MVS v2.10.

Useful ideas appreciated...

Regards,
Mike Flint
Systems Consultant,
Experian.



=======================
Information in this email and any attachments are confidential, and may
not be copied or used by anyone other than the addressee, nor disclosed
to any third party without our permission. There is no intention to
create any legally binding contract or other commitment through the use
of this email.

Experian Limited (registration number 653331).
Registered office: Talbot House, Talbot Street, Nottingham NG1 5HF



Sushanta K Dash

Re: Increased insert time
(in response to Mike Flint)
Hi Mike, the time taken to search the space manager indicates that the
target page is not near page. So the search is being taken at space map to
look at free space within the ranges of pages covered within this space map
page. If sufficient space is not there it would again search other
successive space map pages available within that partition to insert the
row. Again if sufficient space is not there with the other space map pages
the search would continue with the space map pages for the entire
partitions. This indicates more spacing is needed on page. I.e play with
PCTFREE and FREEPAGE. Also wait for others comments before taking final
decisions.

With thanks
Dash S.K.

EDS/India Solution Center
Saturn/AAT Data Base Administration
Hello 28113801 through 28113815 ext-2366
e-mail: [login to unmask email] <mailto:[login to unmask email]>



-----Original Message-----
From: Flint, Mike [SMTP:[login to unmask email]
Sent: Wednesday, January 15, 2003 3:05 PM
To: [login to unmask email]
Subject: Increased insert time

*Re-Send* (not sure it got through before)
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Hello,

We're in the process of running through a large number of updates to
a new database.
On the test system, the inserts to a particular table went fine, but
on the 'live' system the inserts to this table are performing very poorly.
Everything looks okay (indexes, stats, plans, extents, ...) but the profile
of the insert seems different.
This table has 300 million rows on the live system; on the test
system it only had 4 million.
The table is in 8 partitions on live, but only 1 on test.
On live, 15% of the time is being spent (on the INSERT) in DSNISMRS
(Read & Search Space Manager), and 4% of the time in DSNB1GET (retrieve
page);
On test, all the time is just DASD, the only DB2 module involved is
DSNISRTI (Insert record).
(these figures from Strobe/DB2)
Any ideas on what's causing the high DSNISMRS/DSNB1GET usage? Does
this point at some problem with the index or table space?
The table has only 8 columns, and one index. The inserts are either
'random' into existing ranges of rows, or adding new rows above the high end
of the primary key; most inserts are of the 'high key' type.
DB2 is V6.1 on MVS v2.10.
Useful ideas appreciated...
Regards,
Mike Flint
Systems Consultant,
Experian.




=======================
Information in this email and any attachments are confidential, and
may not be copied or used by anyone other than the addressee, nor disclosed
to any third party without our permission. There is no intention to create
any legally binding contract or other commitment through the use of this
email.
Experian Limited (registration number 653331).
Registered office: Talbot House, Talbot Street, Nottingham NG1 5HF



visit the DB2-L webpage at http://listserv.ylassoc.com
< http://listserv.ylassoc.com > . The owners of the list can be reached at
[login to unmask email]
<mailto:[login to unmask email]> .



Walter Jani&#223;en

Re: Increased insert time
(in response to Sushanta K Dash)
Did you take a look at:

http://www7b.boulder.ibm.com/dmdd/library/techarticle/0203shibamiya/0203shib
amiya2.html

There are some interesting ideas about insert performance are covered



[login to unmask email]

Re: Increased insert time
(in response to Walter Janißen)
Mike,
What Dash has suggested is most likely. Since you indicate that most of
the inserts are taking place in the last partition, try to increase the
values for PCTFREE and decrease the value for FREEPAGE for that partition.
Also, are there any Foreign keys in Production that are not in test? If
there is too much DB2 RI on an insert, it tends to affect performance. You
may need to consider REORGing your tablespace and even rebalance your
partitions more often if there is too much of insert activity like this.

HTH
Jaiwant
IBM Certified Solutions Expert
DB2 V7.1 Database Administration for OS/390
DB2 UDB V7.1 Database Administration for UNIX, Windows and OS/2

On Wed, 15 Jan 2003 04:22:55 -0600, Dash, Sushanta <[login to unmask email]>
wrote:

>Hi Mike, the time taken to search the space manager indicates that the
>target page is not near page. So the search is being taken at space map to
>look at free space within the ranges of pages covered within this space map
>page. If sufficient space is not there it would again search other
>successive space map pages available within that partition to insert the
>row. Again if sufficient space is not there with the other space map pages
>the search would continue with the space map pages for the entire
>partitions. This indicates more spacing is needed on page. I.e play with
>PCTFREE and FREEPAGE. Also wait for others comments before taking final
>decisions.
>
>With thanks
>Dash S.K.
>
>EDS/India Solution Center
>Saturn/AAT Data Base Administration
>Hello 28113801 through 28113815 ext-2366
>e-mail: [login to unmask email] <mailto:[login to unmask email]>
>
>
>
> -----Original Message-----
> From: Flint, Mike [SMTP:[login to unmask email]
> Sent: Wednesday, January 15, 2003 3:05 PM
> To: [login to unmask email]
> Subject: Increased insert time
>
> *Re-Send* (not sure it got through before)
> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>
> Hello,
>
> We're in the process of running through a large number of updates
to
>a new database.
> On the test system, the inserts to a particular table went fine,
but
>on the 'live' system the inserts to this table are performing very poorly.
>Everything looks okay (indexes, stats, plans, extents, ...) but the profile
>of the insert seems different.
> This table has 300 million rows on the live system; on the test
>system it only had 4 million.
> The table is in 8 partitions on live, but only 1 on test.
> On live, 15% of the time is being spent (on the INSERT) in DSNISMRS
>(Read & Search Space Manager), and 4% of the time in DSNB1GET (retrieve
>page);
> On test, all the time is just DASD, the only DB2 module involved is
>DSNISRTI (Insert record).
> (these figures from Strobe/DB2)
> Any ideas on what's causing the high DSNISMRS/DSNB1GET usage? Does
>this point at some problem with the index or table space?
> The table has only 8 columns, and one index. The inserts are either
>'random' into existing ranges of rows, or adding new rows above the high
end
>of the primary key; most inserts are of the 'high key' type.
> DB2 is V6.1 on MVS v2.10.
> Useful ideas appreciated...
> Regards,
> Mike Flint
> Systems Consultant,
> Experian.
>
>
>
>
>=======================
> Information in this email and any attachments are confidential, and
>may not be copied or used by anyone other than the addressee, nor disclosed
>to any third party without our permission. There is no intention to create
>any legally binding contract or other commitment through the use of this
>email.
> Experian Limited (registration number 653331).
> Registered office: Talbot House, Talbot Street, Nottingham NG1 5HF
>
>
>
>visit the DB2-L webpage at http://listserv.ylassoc.com
> < http://listserv.ylassoc.com > . The owners of the list can be reached at
>[login to unmask email]
><mailto:[login to unmask email]> .
>
>
>
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Mike Flint

Re: Increased insert time
(in response to JJaiwant@AOL.COM)
Dash, Jaiwant,

Both, thanks for your suggestions. We have re-orged some partitions which
were quite fragmented, and have increased PCTFREE. Performance is now
returning to what was happening on our test system.

We have no RI (at the moment!), but will consider re-balancing for the last
partition where there is most insert activity.

We're still in the early stages of the initial data load (most was achieved
using LOAD utility. We're now working through those records which need to be
INSERTED), so the profile of work is different to what our normal running
conditions will be ... weighted much more heavily to inserting, rather than
retrieving. We're looking at our partitioning strategy on several tables
again.

And it all ran so beautifully with a few million records... :-)

Thanks again,
Mike.

-----Original Message-----
From: Jaiwant Jonathan [mailto:[login to unmask email]
Sent: 15 January 2003 14:27
To: [login to unmask email]
Subject: Re: Increased insert time


Mike,
What Dash has suggested is most likely. Since you indicate that most of
the inserts are taking place in the last partition, try to increase the
values for PCTFREE and decrease the value for FREEPAGE for that partition.
Also, are there any Foreign keys in Production that are not in test? If
there is too much DB2 RI on an insert, it tends to affect performance. You
may need to consider REORGing your tablespace and even rebalance your
partitions more often if there is too much of insert activity like this.

HTH
Jaiwant
IBM Certified Solutions Expert
DB2 V7.1 Database Administration for OS/390
DB2 UDB V7.1 Database Administration for UNIX, Windows and OS/2

On Wed, 15 Jan 2003 04:22:55 -0600, Dash, Sushanta <[login to unmask email]>
wrote:

>Hi Mike, the time taken to search the space manager indicates that the
>target page is not near page. So the search is being taken at space map to
>look at free space within the ranges of pages covered within this space map
>page. If sufficient space is not there it would again search other
>successive space map pages available within that partition to insert the
>row. Again if sufficient space is not there with the other space map pages
>the search would continue with the space map pages for the entire
>partitions. This indicates more spacing is needed on page. I.e play with
>PCTFREE and FREEPAGE. Also wait for others comments before taking final
>decisions.
>
>With thanks
>Dash S.K.
>
>EDS/India Solution Center
>Saturn/AAT Data Base Administration
>Hello 28113801 through 28113815 ext-2366
>e-mail: [login to unmask email] <mailto:[login to unmask email]>
>
>
<snip>



=======================
Information in this email and any attachments are confidential, and may
not be copied or used by anyone other than the addressee, nor disclosed
to any third party without our permission. There is no intention to
create any legally binding contract or other commitment through the use
of this email.

Experian Limited (registration number 653331).
Registered office: Talbot House, Talbot Street, Nottingham NG1 5HF