Dear Mahesh & list,
Guy has nailed the main concept perfect. Thanks Guy.
Just wanted to add to what Guy has already said.
When 16 drives are configured in a logical volume, the
OS sees 1 logical device, and given that there is no
benefit in having multiple datafiles for the same
tablespace on this 1 logical volume (unless the max.
size of the datafile is an issue), we typically end up
with 1 datafile. And for our discussion, it really
would not matter even if you did have multiple
datafiles.
Thus the 1 Oracle datafile is striped across 16 disks,
and multiple PQ processes get to work on this 1 file.
When the degree of parallelism is increased beyond a
certain point, it results in 2 things:
a. There is no additional response time benefit with
the increased number of PQ processes
and
b. Things actually start to slow down due to the
increased occurrence of I/O bottlenecks.
An I/O bottleneck is defined as a state where
read/write requests consistently incur more than 20ms
of total response time (service time + wait time) on
the logical device. This response time, for example,
is measured from the time a process issues a "read
system call", to the time it actually gets the data it
requested.
The real issue here is that there is contention
created "by design", as every PQ process has to work
with the same datafile (which contains all the extents
of the segment) and thus can potentially engage all 16
disks supporting the logical device, for every I/O
operation. Although in theory that is good, reality
may prove otherwise - The biggest enemy for
parallelism is concurrency and vice versa.
The I/O sub-system thus spends more time doing "seeks"
i.e., aligning the disk head to the offset on the disk
where the I/O needs to be performed. This offset will
be different for each PQ process potentially on
different drives.
Net result - the amount of time spending "seeking"
increases as each PQ process is trying to read a
different physical location of the table, which causes
the disk(s) head(s) to move much more frequently. If
you trying to "paint a picture" of the disk head in
your own head, imagine a "referee during a very long
rally in a tennis match".
Given that every I/O request has 3 phases (seek,
latency and tranfer) and that "the seek" is typically
40-60% of the time for each I/O request, the increased
time spent on seeks will cause I/O bottlenecks and
hence contention. Mind you, the goal of any I/O
sub-system design should be to "reduce the amount of
time seeking", which has a direct impact on better I/O
throughput.
The physics of the drive, will allow only 1 seek at
any given time, resulting in multiple PQ slaves
waiting in the "Wait Queue" for this device. As the
amount spent on the Wait Queue increases, total
response time also increases, resulting in poor I/O,
poor PQ and poor application performance.
Now in the other configuraion, everything else
remaining constant, we have 4 logical volumes with 4
drives each. Since we have 4 independent logical
devices, we can create 4 datafiles for the tablespace
in question, thus creating an Oracle-striped
tablespace.
This results in a striped tablespace with 4 datafiles
supporting this tablespace. The extents for any
segment in this tablespace are allocated in a
round-robin fashion, i.e., extent 0 on datafile1,
extent 1 on datafile2, extent 2 on datafile3 etc. This
is true since Oracle 7.3. This reduces contention by a
factor of 4, as the same number of PQ processes now
have 4 independent devices to work with and the
physical separation of the extents across multiple
logical devices, has a huge positive impact.
Mind you, even if the total number of seeks did not
change between the one-16 drive solution and the
four-4 drive solution, what will change for sure is
the amount of time spent waiting in the Wait Queue and
"seeking". As mentioned before, this has a huge impact
on the response time for an I/O request. In the latter
configuration, every I/O request for the PQ example
will spend more time transfering data (and rightfully
so) rather than spending time in the "Wait Queue" to
seek.
End Result - The four-4 drive logical volumes will
support a much higher degree of parallelism because
the design supports for less I/O contention for PQ.
CAVEAT: This is a very involved and complex topic and
there is more to this than what I am writing here.
However, I do not want anyone to walk away with the
notion that "I am opposed to striping".
I am all for striping and I can fully appreciate the
benefits of striping. However, the concept of
"creating 1 logical volume with every drive in the
storage array" without any heed paid to separating
DATA and INDX in the least, without any consideration
for separating datafiles and redologfiles, without any
thought given for Oracle PQ and without any planning
for "segment isolation and separation" in an
Oracle-partitioned environment, potentially is a
"receipe for I/O bottlenecks", and thus a chronic
performance problem. Not all I/O sub-systems are
created the SAME.
In parting, I am not suggesting that you create 16
logical volumes with 1 drive each, neither am I
suggesting that you create 1 logical volume with 16
drives. I am trying to get to a "happy medium". And
that medium, may be different in each of your
environment. It is upto you to determine that. After
all, isn't happiness one of the pre-requisites for
life. Hope that helps.
Love, peace and harmony,
Gaja
--- Guy Hammond <[EMAIL PROTECTED]> wrote:
> Because a single datafile may be striped across 16
> disks. In the case of
> 16 datafiles on 16 disks, Oracle knows that they are
> separate files, but
> it is down to you to set the degree of parallelism,
> because Oracle can't
> know about your underlying physical configuration.
> With a stripe set, a
> single data file is likely to span all disks,
> therefore all heads would
> be used in when Oracle wants to read a lot of blocks
> sequentially. The
> only scope for acceleration on such a configuration
> would be for the
> RAID drivers to make the OS reads underlying the
> Oracle reads faster,
> effectively getting filesystem blocks in parallel
> (rather than Oracle
> blocks) and passing them on.
>
> g
>
>
>
> -----Original Message-----
> Sent: Thursday, September 20, 2001 3:20 PM
> To: Multiple recipients of list ORACLE-L
>
>
> List,
>
> While reading Gaja's excellent document on
> Implementing Raid on Oracle
> Systems, Gaja mentions the following in his
> document.
>
> "The degree of striping in the DATA and INDX volumes
> should also
> consider
> factors such as data/index partitioning,
> availability requirements and
> support for parallel operations. Please note that 16
> datafiles placed on
> 16
> individual drives can support a 16-way parallel
> operation (if you have
> memory and CPU to support it and your controller is
> not out of capacity
> because all 16 drives engaged at the same time). The
> same cannot be
> concluded with 16 datafiles placed on a 16-way RAID
> volume with 16
> drives.
> The degree of parallelism that you deploy on a
> 16-way volume will be
> significantly less, when compared to the former
> configuration of 16
> individual drives. This factor should be factored,
> when making "degree
> of
> striping" decisions, which in turn controls the
> number of volumes on
> your
> system. "
>
> Question:
>
> Why the degree of parallelism is less on a 16-way
> volume compared to 16
> individual drives?
>
> Thanks,
>
> Rao
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Rao, Maheswara
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
--------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Guy Hammond
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
--------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
=====
Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101
http://www.osborne.com/database_erp/0072131454/0072131454.shtml
__________________________________________________
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger.
http://im.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gaja Krishna Vaidyanatha
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
