473,327 Members | 1,967 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Bufferpool access on insert

Can anyone explain why an insert might cause multiple logical
bufferpool data reads?

Here's a situation that has me scratching my head.

Table A's data resides in tablespace B
Table A's indexes reside in tablespace C
Table A has no long data
No other data resides in either of these tablespaces.

According to the dynamic SQL snapshot there are four different
statements accounting for access to table A: An insert and three
select statements. The first select statement is qualified by the
primary key and rows read = number of executions. The second and
third statements are qualified by two different sets of indexed
columns and these queries read and average of 3 rows per execution
according to the dynamic cache snapshot.

From a referential integrity point of view: Table A has three parent
tables and no children.

According to the tablespace and table snapshots (see below), I see an
average of over 100 logical bufferpool data reads per row read/write.
Having observed these same statistics during times of read access
only, it appears that the bulk of the bufferpool access occurs as a
result of the inserts. Extrapolating from my read-only observations,
I estimate that nearly 1,000 bufferpool reads are executed for every
row inserted.

Comparing this to the same statistics for other tables in the database
I can find nothing that even remotely approaches these kinds of
ratios.

Running DB2/UDB v 7.2 on AIX.

Any and all insights or theories are welcome.

Table Name = A
Table Type = User
Rows Read = 387568
Rows Written = 51750
Overflows = 0
Page Reorgs = 45

Tablespace name = B
Buffer pool data logical reads = 50468678
Buffer pool data physical reads = 5835113
Asynchronous pool data page reads = 0
Buffer pool data writes = 1862
Asynchronous pool data page writes = 1855
Buffer pool index logical reads = 0
Buffer pool index physical reads = 0
Asynchronous pool index page reads = 0
Buffer pool index writes = 0
Asynchronous pool index page writes = 0
Total buffer pool read time (ms) = 30975627
Total buffer pool write time (ms) = 1648125
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 1602559
Asynchronous read requests = 0
Direct reads = 0
Direct writes = 0
Direct read requests = 0
Direct write requests = 0
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 0
Number of files closed = 0
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Tablespace name = C
Buffer pool data logical reads = 1197
Buffer pool data physical reads = 215
Asynchronous pool data page reads = 0
Buffer pool data writes = 3
Asynchronous pool data page writes = 3
Buffer pool index logical reads = 1779535
Buffer pool index physical reads = 321165
Asynchronous pool index page reads = 1170
Buffer pool index writes = 69250
Asynchronous pool index page writes = 69084
Total buffer pool read time (ms) = 1453091
Total buffer pool write time (ms) = 99786
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 99384
Asynchronous read requests = 0
Direct reads = 0
Direct writes = 0
Direct read requests = 0
Direct write requests = 0
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 0
Number of files closed = 0
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Nov 12 '05 #1
5 4097
"Paul Shaw" <pa*******@eds.com> wrote in message
news:ee**************************@posting.google.c om...
Can anyone explain why an insert might cause multiple logical
bufferpool data reads?

Here's a situation that has me scratching my head.

Table A's data resides in tablespace B
Table A's indexes reside in tablespace C
Table A has no long data
No other data resides in either of these tablespaces.

According to the dynamic SQL snapshot there are four different
statements accounting for access to table A: An insert and three
select statements. The first select statement is qualified by the
primary key and rows read = number of executions. The second and
third statements are qualified by two different sets of indexed
columns and these queries read and average of 3 rows per execution
according to the dynamic cache snapshot.

From a referential integrity point of view: Table A has three parent
tables and no children.

According to the tablespace and table snapshots (see below), I see an
average of over 100 logical bufferpool data reads per row read/write.
Having observed these same statistics during times of read access
only, it appears that the bulk of the bufferpool access occurs as a
result of the inserts. Extrapolating from my read-only observations,
I estimate that nearly 1,000 bufferpool reads are executed for every
row inserted.

Comparing this to the same statistics for other tables in the database
I can find nothing that even remotely approaches these kinds of
ratios.

Running DB2/UDB v 7.2 on AIX.

Any and all insights or theories are welcome.

Is there a clustering index on the table? Is DB2 trying to insert the data
into the correct data page (and looking for a place to insert it) or is it
always inserting at the end of the table (append mode or no clustering
index).

Is there enough freespace for indexes inserted (all index rows are put in
the correct sequence).

Is there any lock contention issues where DB2 keeps checking for the lock to
be released?

One thing I would try is to define percent free for the table and indexes
and then reorg both. Then execute runstats with full statistics and run the
test again. Also check to make sure that the indexes are be used properly
and there are no tablespace scans or full index scans (not using the
b-tree).
Nov 12 '05 #2
If DB2 believes any of your parent tables to be near-empty, it could
scan them rather than use an index. Make sure RUNSTATS has been run on
the insert table plus all parent tables.
DG
Nov 12 '05 #3
Thanks for the insight. Your comments about clustering indexes and
free space were right on the mark.

I've altered and reorg'ed the table. Tests run since then have
produced remarkably better results.

Follow up question ...

Is there any way to determine the average free space per page ... or
some similar metric that would identify a need to reorg a table in
this situation?


"Mark A" <ma@switchboard.net> wrote in message news:<QI*****************@news.uswest.net>...

....

Is there a clustering index on the table? Is DB2 trying to insert the data
into the correct data page (and looking for a place to insert it) or is it
always inserting at the end of the table (append mode or no clustering
index).

....
Nov 12 '05 #4
> "Mark A" <ma@switchboard.net> wrote in message
news:<QI*****************@news.uswest.net>...
Is there a clustering index on the table? Is DB2 trying to insert the data into the correct data page (and looking for a place to insert it) or is it always inserting at the end of the table (append mode or no clustering
index).

"Paul Shaw" <pa*******@eds.com> wrote in message
news:ee**************************@posting.google.c om... Thanks for the insight. Your comments about clustering indexes and
free space were right on the mark.

I've altered and reorg'ed the table. Tests run since then have
produced remarkably better results.

Follow up question ...

Is there any way to determine the average free space per page ... or
some similar metric that would identify a need to reorg a table in
this situation?

You can run the reorgchk command. Check the Command Reference for the
meaning of the output report.

Another way to approach the issue is to figure out what percent of the table
is inserted within a given time period (say a week, for example). If the
table grows by 10% per week, then having a percent free of 15% (5% extra
margin of safety) and doing a reorg once per week (to recreate the percent
free) would probably work well. The more frequently you reorg, the less
percent free is required.

However, you do need to take into consideration whether the distribution of
inserts is truly random (according to the clustering index), and also take
into consideration the number of deletes (which free up space on the page)
in the same time period. Updates to varchar columns can also have an adverse
effect because the rows may not fit back in the old space on the page.

As mentioned previously (IIRC), setting the table to append might ensure
better insert performance since rows will be inserted at the end of the
table. However, this will not help updates of varchar columns, and could
create a hotspot if a large number of users are trying to insert at the same
time. Using append could also hurt select performance if the table is not
clustered in an optimal manner.
Nov 12 '05 #5

"Mark A" <ma@switchboard.net> wrote in message
news:o2****************@news.uswest.net...
"Mark A" <ma@switchboard.net> wrote in message news:<QI*****************@news.uswest.net>...
Is there a clustering index on the table? Is DB2 trying to insert the data into the correct data page (and looking for a place to insert it) or
is it always inserting at the end of the table (append mode or no clustering
index).

"Paul Shaw" <pa*******@eds.com> wrote in message
news:ee**************************@posting.google.c om...
Thanks for the insight. Your comments about clustering indexes and
free space were right on the mark.

I've altered and reorg'ed the table. Tests run since then have
produced remarkably better results.

Follow up question ...

Is there any way to determine the average free space per page ... or
some similar metric that would identify a need to reorg a table in
this situation?

You can run the reorgchk command. Check the Command Reference for the
meaning of the output report.

Another way to approach the issue is to figure out what percent of the

table is inserted within a given time period (say a week, for example). If the
table grows by 10% per week, then having a percent free of 15% (5% extra
margin of safety) and doing a reorg once per week (to recreate the percent
free) would probably work well. The more frequently you reorg, the less
percent free is required.

However, you do need to take into consideration whether the distribution of inserts is truly random (according to the clustering index), and also take
into consideration the number of deletes (which free up space on the page)
in the same time period. Updates to varchar columns can also have an adverse effect because the rows may not fit back in the old space on the page.

As mentioned previously (IIRC), setting the table to append might ensure
better insert performance since rows will be inserted at the end of the
table. However, this will not help updates of varchar columns, and could
create a hotspot if a large number of users are trying to insert at the same time. Using append could also hurt select performance if the table is not
clustered in an optimal manner.

I forgot to add that you should also check the percent free on indexes the
same way. Index are always stored in the exact order of the index key and
percent free is very important for indexes when there is insert activity.
Also make sure you don't have any low cardinality indexes such as sex (m/f),
or division (where there are only a few divisions). Low cardinality indexes
require a lot of work for DB2 to add new rows, and these indexes will not be
used by DB2 to increase performance.

Note, this does not apply to situations where a column such a "division" is
part of a multi-column index. In the case of multi-column indexes, the
cardinality of the entire index key is important, and not of just any one
column in the index.
Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Jagdip Singh | last post by:
Hi all, Is there any recommended bufferpool size while designing database I mean some equation which governs bufferpool size regards, Jagdip
5
by: Paul | last post by:
Hi, is there any way to know the content of the bufferpool : - space usage not present in snapshot - tables actually cached in and number of pages, same for index ... thx
1
by: Christian Berg | last post by:
Hi, I have got a problem with resizing the bufferpool of a DB2 v.8.2 instance. The DB2 runs on an AIX 5.x platform. Problem is that an "ALTER BUFFERPOOL ..." command is not persistent if...
5
by: Hemant Shah | last post by:
Folks, I am not sure what I am doing wrong, but We have an transaction that does some serious calculation on small chink of data over and over again. It selects few rows from the table...
20
by: Hemant Shah | last post by:
Folks, I am using DB2 UDB 8.2 on AIX 5.1. How large of a bufferpool can you create? I tried to create a 4GB bufferpool db2 complained that is cannot allocate enogth memory. I have 16GB on this...
3
by: dotyet | last post by:
Hi Everyone, I am in a very strange situation, and am looking for suggestions to tackle it. I have a 10 gig database on 64-bit windows 2003 running platform. The database has about 5 gigs of...
9
by: aczapran | last post by:
Everything I read on DB2 indicates that the bufferpool is a pool of database pages used to speed up database access. It is controlled by us, when we specify how many pages we would like to have...
3
by: Mark A | last post by:
<dcruncher4@aim.comwrote in message news:fuevgh02ug6@drn.newsguy.com... I would try these: db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON (this will override CHNGPGS_THRESH) db2set...
3
by: dunleav1 | last post by:
In 9.1 and 9.5 (Linux 64 bit) when a buffer pool is set to self- tuning, how are blocks configured in respect to blocked vs non-blocked when self-tuning is set to on? (ie) I have one bufferpool...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.