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? 10 1933
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.goo gle.com... 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?
"dbtnews.torola b.ibm.com" <ds***@ca.ibm.c om> wrote in message
news:c2******** **@hanover.toro lab.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?
"Mark A" <ma@switchboard .net> wrote in message
news:_c******** *******@news.us west.net... "dbtnews.torola b.ibm.com" <ds***@ca.ibm.c om> wrote in message news:c2******** **@hanover.toro lab.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
"Matt Emmerton" <me******@yahoo .com> wrote in message news:7tS1c.9075 1> 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.
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.us west.net... "dbtnews.torola b.ibm.com" <ds***@ca.ibm.c om> wrote in message news:c2******** **@hanover.toro lab.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?
"dbtnews.torola b.ibm.com" <ds***@ca.ibm.c om> wrote in message
news:c2******** **@hanover.toro lab.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.
"Matt Emmerton" <me******@yahoo .com> wrote in message news:<7t******* ***********@twi ster01.bloor.is .net.cable.roge rs.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.
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.torola b.ibm.com" <ds***@ca.ibm.c om> wrote in message news:c2******** **@hanover.toro lab.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.
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******* ***********@twi ster01.bloor.is .net.cable.roge rs.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 post. A container is this:
<CONTAINER>, in other words all the characters between the < and > and
including the < and > signs. (What is the correct...
|
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();
pic2on.src="pic2.gif";
picNon= new Image();
picNon.src="pic4.gif";
}
|
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?
|
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 type=submit value='Done'>
</form>
|
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
error: Expected ';'
| |
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 to know
why the <%# %> construct is necessary in locations where the other
<%= %> does not do. It seems to me that there is no equivalent of
the...
|
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 simple and schematic, to see if there is a simple Ajax
answer to this.
A. I have an HTML page which has some pure html/css code representing a
GRID...
|
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 object of generic type
T, that supports IEquatable<T>, and a method, DoComparisons(T obj) to
compare the member object to the object passed in.
|
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 container.
I have written both these operators as non-friend functions.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |