473,327 Members | 2,069 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.

Prefetching only with >1 container??

Helpful folks,

I ran across a statement I found rather confusing in the IBM-published
Advanced DBA Certification Guide and Reference for UDB V8. On page 107
the author is explaining why the a very low amount of prefetching may
occur in a database. One of the reasons given for possible low
prefetch activity is:
"The table spaces in the database are set up with only one container
each so that prefetching cannot normally take place."
I was quite surprised by this assertion. I had thought prefetching can
take place regardless of the number of containers.

Is the book correct?
Nov 12 '05 #1
10 1920
Sean, By default DB2 will not prefetch if there is only one container.

To allow prefetching with only one container (especially if you have a RAID
file system)
you need to set the DB2 registry variable DB2_PARALLEL_IO= x where x is the
table space ID (or where x=* for all table spaces).

Thanks,
....Dwaine Snow

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

I ran across a statement I found rather confusing in the IBM-published
Advanced DBA Certification Guide and Reference for UDB V8. On page 107
the author is explaining why the a very low amount of prefetching may
occur in a database. One of the reasons given for possible low
prefetch activity is:
"The table spaces in the database are set up with only one container
each so that prefetching cannot normally take place."
I was quite surprised by this assertion. I had thought prefetching can
take place regardless of the number of containers.

Is the book correct?

Nov 12 '05 #2
"dbtnews.torolab.ibm.com" <ds***@ca.ibm.com> wrote in message
news:c2**********@hanover.torolab.ibm.com...
Sean, By default DB2 will not prefetch if there is only one container.

To allow prefetching with only one container (especially if you have a RAID file system)
you need to set the DB2 registry variable DB2_PARALLEL_IO= x where x is the table space ID (or where x=* for all table spaces).

Thanks,
...Dwaine Snow

Can you explain the rationale for this?
Nov 12 '05 #3

"Mark A" <ma@switchboard.net> wrote in message
news:_c***************@news.uswest.net...
"dbtnews.torolab.ibm.com" <ds***@ca.ibm.com> wrote in message
news:c2**********@hanover.torolab.ibm.com...
Sean, By default DB2 will not prefetch if there is only one container.

To allow prefetching with only one container (especially if you have a

RAID
file system)
you need to set the DB2 registry variable DB2_PARALLEL_IO= x where x is

the
table space ID (or where x=* for all table spaces).

Thanks,
...Dwaine Snow

Can you explain the rationale for this?


The rationale behind prefetching is twofold:
1) To bring rows into the bufferpool before they are needed by the query
(prevents the query from waiting on I/O)
2) To take advantage of the parallel nature of the underlying disk
subsystem.

Case 1: One container on one physical disk.

Here, doing N prefetch reads won't speed up the query since the reads will
be executed serially at the physical disk level. In many cases, this will
actually slow things down since the OS/hardware is dealing with a barrage of
disk requests that just queue up. In this setup, it would be faster to do
the N reads serially.

Case 2: One container on a RAID array (multiple physical disks)

Here, it is advantageous to do N prefetch reads, especially if you've set up
extensize and prefetchsize to be a multiple of your RAID stripe size. If
configured properly, each of the N prefetch reads will be done on a separate
phsyical disk of the RAID array. There is no serialization of disk queries,
and therefore no performance penalty in the disk subsystem. Since DB2
doesn't know the physical configuration of your RAID array, you have to set
DB2_PARALLEL_IO in order to enable parallel I/O (prefetching).

Case 3: Multiple containers on many physical disks

Since DB2 allocates extents on a round-robin basis among containers, DB2
knows by default that reading consecutive extents (for prefetch) will be
done on separate physical disks -- no potential for a bottleneck on the
physical disk. Thus, it enables prefetching automatically.

You may see some non-IBM publications advising you to always create multiple
containers, even when using a single physical disk. The only reason for
this suggestion is so that prefetching gets enabled automatically (Case 3).
However, performance is often sub-optimal because of the serialization of
requests at the disk layer (as in Case 1).

Hope this helps.

--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab
Nov 12 '05 #4

"Matt Emmerton" <me******@yahoo.com> wrote in message news:7tS1c.90751> The
rationale behind prefetching is twofold:
1) To bring rows into the bufferpool before they are needed by the query
(prevents the query from waiting on I/O)
2) To take advantage of the parallel nature of the underlying disk
subsystem.

Case 1: One container on one physical disk.

Here, doing N prefetch reads won't speed up the query since the reads will
be executed serially at the physical disk level. In many cases, this will
actually slow things down since the OS/hardware is dealing with a barrage of disk requests that just queue up. In this setup, it would be faster to do
the N reads serially.

Case 2: One container on a RAID array (multiple physical disks)

Here, it is advantageous to do N prefetch reads, especially if you've set up extensize and prefetchsize to be a multiple of your RAID stripe size. If
configured properly, each of the N prefetch reads will be done on a separate phsyical disk of the RAID array. There is no serialization of disk queries, and therefore no performance penalty in the disk subsystem. Since DB2
doesn't know the physical configuration of your RAID array, you have to set DB2_PARALLEL_IO in order to enable parallel I/O (prefetching).

Case 3: Multiple containers on many physical disks

Since DB2 allocates extents on a round-robin basis among containers, DB2
knows by default that reading consecutive extents (for prefetch) will be
done on separate physical disks -- no potential for a bottleneck on the
physical disk. Thus, it enables prefetching automatically.

You may see some non-IBM publications advising you to always create multiple containers, even when using a single physical disk. The only reason for
this suggestion is so that prefetching gets enabled automatically (Case 3). However, performance is often sub-optimal because of the serialization of
requests at the disk layer (as in Case 1).

Hope this helps.

--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab

Thanks for detailed explanation.

Regarding Case 1, I am still a bit confused. On OS/390, prefetch is enabled
and works on tablespaces that essentially have one container (one VSAM
dataset on a single DASD volume). So I don't quite understand why it doesn't
work on other platforms, but I am not a disk sub-system expert.
Nov 12 '05 #5
Mark,

For a table space with only one container on a single disk, if we enable
prefetching on this one physical disk, then you will see a continual stram
of I/O requests to the same disk, and this is not efficient, and does not
take advantage of the parallelism and asynchronous operation that
prefetching is intended to provide.

If you have the single container on a SAN array, or stripe set, with
multiple underlying physical disks, then you can enable prefetching for the
single container table space using the DB2 registry variable DB2_PARALLEL_IO
I described previously.

Hope this helps,
..Dwaine


"Mark A" <ma@switchboard.net> wrote in message
news:_c***************@news.uswest.net...
"dbtnews.torolab.ibm.com" <ds***@ca.ibm.com> wrote in message
news:c2**********@hanover.torolab.ibm.com...
Sean, By default DB2 will not prefetch if there is only one container.

To allow prefetching with only one container (especially if you have a

RAID
file system)
you need to set the DB2 registry variable DB2_PARALLEL_IO= x where x is

the
table space ID (or where x=* for all table spaces).

Thanks,
...Dwaine Snow

Can you explain the rationale for this?

Nov 12 '05 #6
"dbtnews.torolab.ibm.com" <ds***@ca.ibm.com> wrote in message
news:c2**********@hanover.torolab.ibm.com...
Mark,

For a table space with only one container on a single disk, if we enable
prefetching on this one physical disk, then you will see a continual stram
of I/O requests to the same disk, and this is not efficient, and does not
take advantage of the parallelism and asynchronous operation that
prefetching is intended to provide.

If you have the single container on a SAN array, or stripe set, with
multiple underlying physical disks, then you can enable prefetching for the single container table space using the DB2 registry variable DB2_PARALLEL_IO I described previously.

Hope this helps,
..Dwaine

Mainframe DASD (at least the old kind like 3380, etc) is not striped and the
tablespaces are usually on one volume (unless they are too large to fit or
are partitioned) and prefetch has been on DB2 for OS/390 for at least 10
years (probably closer to 15 years).

I do understand the issue of parallelism with multiple disks and it would
obviously work better this way, but I would think (like on OS/390) that the
asynchronous prefetch would also work on one container. Perhaps the problem
is that the disk controllers get flooded with requests faster than they can
process them on mid-range platforms.
Nov 12 '05 #7
"Matt Emmerton" <me******@yahoo.com> wrote in message news:<7t******************@twister01.bloor.is.net. cable.rogers.com>...
[...]
Hope this helps.


The explanation was very clear, thank you. I have a question, though.
Is there any difference between these two implementations? (Regarding
performance)

1. One container on a RAID and set DB2_PARALLEL_IO=*.
or
2. Two containers on a RAID, leave DB2_PARALLEL_IO unset.
Nov 12 '05 #8
Guys,
Thanks for all the feedback, with some very useful detail. Most of my
experience has been on the mainframe where, as Mark A. noted,
prefetching on single containers has been the norm for years. I share
his bewilderment that this would not also be true on smaller
platforms. Guess if I want a given TS to be prefetch-able, I'll have
to add more containers.

"Mark A" <ma@switchboard.net> wrote in message news:<5e****************@news.uswest.net>...
"dbtnews.torolab.ibm.com" <ds***@ca.ibm.com> wrote in message
news:c2**********@hanover.torolab.ibm.com...
Mark,

For a table space with only one container on a single disk, if we enable
prefetching on this one physical disk, then you will see a continual stram
of I/O requests to the same disk, and this is not efficient, and does not
take advantage of the parallelism and asynchronous operation that
prefetching is intended to provide.

If you have the single container on a SAN array, or stripe set, with
multiple underlying physical disks, then you can enable prefetching for

the
single container table space using the DB2 registry variable

DB2_PARALLEL_IO
I described previously.

Hope this helps,
..Dwaine

Mainframe DASD (at least the old kind like 3380, etc) is not striped and the
tablespaces are usually on one volume (unless they are too large to fit or
are partitioned) and prefetch has been on DB2 for OS/390 for at least 10
years (probably closer to 15 years).

I do understand the issue of parallelism with multiple disks and it would
obviously work better this way, but I would think (like on OS/390) that the
asynchronous prefetch would also work on one container. Perhaps the problem
is that the disk controllers get flooded with requests faster than they can
process them on mid-range platforms.

Nov 12 '05 #9
If I am correct with 2 or more containers 2 or more processors can be used.
Also asynchronous I/O , specially write, should be more optimal with
multiple containers.

Almund Sebi wrote:
"Matt Emmerton" <me******@yahoo.com> wrote in message news:<7t******************@twister01.bloor.is.net. cable.rogers.com>...

[...]
Hope this helps.


The explanation was very clear, thank you. I have a question, though.
Is there any difference between these two implementations? (Regarding
performance)

1. One container on a RAID and set DB2_PARALLEL_IO=*.
or
2. Two containers on a RAID, leave DB2_PARALLEL_IO unset.


--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands
Nov 12 '05 #10
db*****@yahoo.com (Sean C.) wrote in message news:<2f**************************@posting.google. com>...
Guys,
Thanks for all the feedback, with some very useful detail. Most of my
experience has been on the mainframe where, as Mark A. noted,
prefetching on single containers has been the norm for years. I share
his bewilderment that this would not also be true on smaller
platforms. Guess if I want a given TS to be prefetch-able, I'll have
to add more containers.


Don't be bewildered. Prefetch does occur on a single disk, even
without DB2_PARALLEL_IO. Whereas parallel i/o doesn't. For some reason
advice is muddling up parallel disk i/o and prefetch.
DG
Nov 12 '05 #11

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

Similar topics

3
by: blaine YOung | last post by:
I have a problem that I hope someone can explain why it is happening and how to fix it. In order to explain my problem, I am going to define the word "container" which i will use often in this...
5
by: Amir | last post by:
When prefetching images, why do I need the "if (document.images)" statement? if (document.images) { pic1on= new Image(300,300); pic1on.src="pic1.gif"; pic2on= new Image();...
9
by: Julia Briggs | last post by:
How do I construct a <iframe> or equivalent for FireFox/NS browsers, inside a screen centered <div> tag? Can it be done?
3
by: Ben | last post by:
Here's my form: <form name="aForm" method='post'> <input type=file name=file1 onkeypress='KeyPress()'><br> <a id='attachMoreLink' href='javascript:AddFileInput()">Attach More Files </a> <input...
4
by: Andrzej Wegrzyn | last post by:
Hi, I had a portal that worked before, and over 5 months period JavaScript errors started to show up on all forms where I have datagrids. Using: IE 6.0, WIN XP, IIS 5.1, Framework 1.1 ...
4
by: Neil Zanella | last post by:
Hello, I would like to know what the difference is among the constructs <%= %> for evaluating an expression and displaying the evaluated result on the page and <%# %>. In particular I would like...
7
by: pamelafluente | last post by:
The precious input given by Laurent, Martin, Benjamin about XMLHttpRequest in Javascript, has made me think that perhaps I could improve what I am currently doing by using Ajax. Let's make it...
5
by: taumuon | last post by:
I've got an object, Person, that supports IEquatable<Person>. It implements bool Equals(Person obj) as well as overriding bool Equals(object obj) I've got a container type that holds a member...
18
by: subramanian100in | last post by:
Consider a class that has vector< pair<int, string>* c; as member data object. I need to use operator>to store values into this container object and operator<< to print the contents of the...
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...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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.