Hi ,

My system is a two node rac on redhat linux 2.1 , 4 ia64 cpus ,
8gb.ram , ocfs and emc raid 10

The db_block_size is 16kb. , current multi_block_read_Count is 64
(which makes 1mb. of read)


Below is some samples for different multiblock read counts . It seems
when i decrease the multblock read count , performance increases:

Sample query :

select /*+parallel(m,8)*/count(*) from taniadm.MERKEZ_CIKIS_34 m

the table is stored in a tablespace within locally managed tablespace
.. The tablespace uses uniform extent sizes of 10mb. ( I have just
created it for this test, and have chosen this large extent size in
order to allow dbserver to use multiblock effectively.)



parallel 8 , 64 multiblock takes : 1:07 min.

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
svctm %util
sdh1 43890.20 0.00 896.80 1.40 50860.00 1.40 56.63 119.07 131.83 1.09
100.00

parallel 8 , 32 multiblock takes : 59 sec

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
svctm %util
sdh1 56885.00 0.00 1051.60 1.20 58651.60 1.20 55.71 138.40 130.24 0.93
100.00

parallel 8 , 16 multiblock takes : 45 secs

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
svctm %util
sdh1 66734.20 0.00 1347.80 1.80 76781.60 1.80 56.89 48.62 36.00 0.72
100.00

parallel 8 , 8 multiblcok takes: 44 sec

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
svctm %util
sdh1 66598.40 0.00 1371.40 2.00 77467.20 2.00 56.41 24.22 17.66 0.71
100.02


1.It seems when i decrease the multiblock read count parameter , the
rsec/s increases , but i expect to see the opposite.What's wrong with
this?

2. Are the values in r/s normal? Or do they point that my disks are
saturated. I have read an article explaining that 100 or 200 read per
second is enough to saturate disks but here i see large values .

3. Related to my second question , since i use raid 10 (striping +
mirroring ) is it possible to get higher io rates ? Looking at the
iostat values , (rsec/s) / (r/s) more or less equals to 28kb. (This is
the kb. read in each read.)It seems a very low value to me. On a sun
solaris ufs file system for instance , i can achive 128KB or even 1MB.
per read by playing with the parameters , i dont understand why my
linux box is different.


Any help will be appreciated.
Kind Regards,
tolga

utkanbir wrote:
> Hi ,
>
> My system is a two node rac on redhat linux 2.1 , 4 ia64 cpus ,
> 8gb.ram , ocfs and emc raid 10
>
> The db_block_size is 16kb. , current multi_block_read_Count is 64
> (which makes 1mb. of read)
>
>
> Below is some samples for different multiblock read counts . It seems
> when i decrease the multblock read count , performance increases:
>
> Sample query :
>
> select /*+parallel(m,8)*/count(*) from taniadm.MERKEZ_CIKIS_34 m
>
> the table is stored in a tablespace within locally managed tablespace
> . The tablespace uses uniform extent sizes of 10mb. ( I have just
> created it for this test, and have chosen this large extent size in
> order to allow dbserver to use multiblock effectively.)
>
>
>
> parallel 8 , 64 multiblock takes : 1:07 min.
>
> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
> svctm %util
> sdh1 43890.20 0.00 896.80 1.40 50860.00 1.40 56.63 119.07 131.83 1.09
> 100.00
>
> parallel 8 , 32 multiblock takes : 59 sec
>
> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
> svctm %util
> sdh1 56885.00 0.00 1051.60 1.20 58651.60 1.20 55.71 138.40 130.24 0.93
> 100.00
>
> parallel 8 , 16 multiblock takes : 45 secs
>
> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
> svctm %util
> sdh1 66734.20 0.00 1347.80 1.80 76781.60 1.80 56.89 48.62 36.00 0.72
> 100.00
>
> parallel 8 , 8 multiblcok takes: 44 sec
>
> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
> svctm %util
> sdh1 66598.40 0.00 1371.40 2.00 77467.20 2.00 56.41 24.22 17.66 0.71
> 100.02
>
>
> 1.It seems when i decrease the multiblock read count parameter , the
> rsec/s increases , but i expect to see the opposite.What's wrong with
> this?
>
> 2. Are the values in r/s normal? Or do they point that my disks are
> saturated. I have read an article explaining that 100 or 200 read per
> second is enough to saturate disks but here i see large values .
>
> 3. Related to my second question , since i use raid 10 (striping +
> mirroring ) is it possible to get higher io rates ? Looking at the
> iostat values , (rsec/s) / (r/s) more or less equals to 28kb. (This is
> the kb. read in each read.)It seems a very low value to me. On a sun
> solaris ufs file system for instance , i can achive 128KB or even 1MB.
> per read by playing with the parameters , i dont understand why my
> linux box is different.
>
>
> Any help will be appreciated.
> Kind Regards,
> tolga

To answer your questions really requires a StatsPack or AWR Report

Upgrading to RedHat 3 will likely improve things substantially

Depending on your hardware you could look at ethernet bonding to
increase through-put. Depends on what the limiting factor is.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

utkanbir wrote:
> Hi ,
>
> My system is a two node rac on redhat linux 2.1 , 4 ia64 cpus ,
> 8gb.ram , ocfs and emc raid 10
>
> The db_block_size is 16kb. , current multi_block_read_Count is 64
> (which makes 1mb. of read)
>
>
> Below is some samples for different multiblock read counts . It seems
> when i decrease the multblock read count , performance increases:
>
> Sample query :
>
> select /*+parallel(m,8)*/count(*) from taniadm.MERKEZ_CIKIS_34 m
>
> the table is stored in a tablespace within locally managed tablespace
> . The tablespace uses uniform extent sizes of 10mb. ( I have just
> created it for this test, and have chosen this large extent size in
> order to allow dbserver to use multiblock effectively.)
>
>
>
> parallel 8 , 64 multiblock takes : 1:07 min.
>
> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
> svctm %util
> sdh1 43890.20 0.00 896.80 1.40 50860.00 1.40 56.63 119.07 131.83 1.09
> 100.00
>
> parallel 8 , 32 multiblock takes : 59 sec
>
> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
> svctm %util
> sdh1 56885.00 0.00 1051.60 1.20 58651.60 1.20 55.71 138.40 130.24 0.93
> 100.00
>
> parallel 8 , 16 multiblock takes : 45 secs
>
> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
> svctm %util
> sdh1 66734.20 0.00 1347.80 1.80 76781.60 1.80 56.89 48.62 36.00 0.72
> 100.00
>
> parallel 8 , 8 multiblcok takes: 44 sec
>
> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
> svctm %util
> sdh1 66598.40 0.00 1371.40 2.00 77467.20 2.00 56.41 24.22 17.66 0.71
> 100.02
>
>
> 1.It seems when i decrease the multiblock read count parameter , the
> rsec/s increases , but i expect to see the opposite.What's wrong with
> this?
>
> 2. Are the values in r/s normal? Or do they point that my disks are
> saturated. I have read an article explaining that 100 or 200 read per
> second is enough to saturate disks but here i see large values .
>
> 3. Related to my second question , since i use raid 10 (striping +
> mirroring ) is it possible to get higher io rates ? Looking at the
> iostat values , (rsec/s) / (r/s) more or less equals to 28kb. (This is
> the kb. read in each read.)It seems a very low value to me. On a sun
> solaris ufs file system for instance , i can achive 128KB or even 1MB.
> per read by playing with the parameters , i dont understand why my
> linux box is different.
>
>
> Any help will be appreciated.
> Kind Regards,
> tolga

To answer your questions really requires a StatsPack or AWR Report

Upgrading to RedHat 3 will likely improve things substantially

Depending on your hardware you could look at ethernet bonding to
increase through-put. Depends on what the limiting factor is.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

One possible reason could because of your global cache management. If
you shutdown one node, maybe result will be diffirent.

One question, is your OCFS doing directIO read? Else filesystem cache
can mask your result.

One possible reason could because of your global cache management. If
you shutdown one node, maybe result will be diffirent.

One question, is your OCFS doing directIO read? Else filesystem cache
can mask your result.

Hi Chao ,

I have checked the statstpack output regarding to this query , in raw
trace file i see lots of 'global cache cr request ' wait events but
the time they take is very little comparing to the disk read events:


select /*+NOPARALLEL(M) */count(*)
from
taniadm.MERKEZ_CIKIS_34 m

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 3 0.00 0.01 1 1 0
0
Execute 3 0.00 0.00 0 0 0
0
Fetch 3 61.10 767.28 307181 307209 0
3
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 9 61.10 767.30 307182 307210 0
3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 46

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=102403 r=102399 w=0 time=262103077 us)
10133769 TABLE ACCESS FULL MERKEZ_CIKIS_34 (cr=102403 r=102399 w=0
time=257079273 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
SQL*Net message to client 8 0.00
0.00
SQL*Net message from client 8 78.11
173.33
global cache cr request 154082 0.12
6.73
db file scattered read 23984 1.01
707.97
latch free 6 0.03
0.05
SQL*Net break/reset to client 2 0.00
0.00
library cache lock 4 0.00
0.00
db file sequential read 3 0.01
0.02
************************************************** *****************************

Here the total times waited value for global cache cr request is large
but total waited is very small . The majority of query time spent in
disk io.

For the direct/io , i have checked the oracle executables, straced
them (especially open system calls) and saw the o_direct flag , and :

filesystemio_options is set to none. (for ocfs i was told it was not
necessary to set it, since ocfs uses direct io without this parameter)


Kind Regrads,






"chao_ping" <zhuchao@gmail.com> wrote in message news:<1116479070.144515.220410@g47g2000cwa.googleg roups.com>...
> One possible reason could because of your global cache management. If
> you shutdown one node, maybe result will be diffirent.
>
> One question, is your OCFS doing directIO read? Else filesystem cache
> can mask your result.

utkanbir wrote:
>
> My system is a two node rac on redhat linux 2.1 , 4 ia64 cpus ,
> 8gb.ram , ocfs and emc raid 10

time to move to RH3?

> The db_block_size is 16kb. , current multi_block_read_Count is 64
> (which makes 1mb. of read)

can mean nothing in Linux, read on...

>
> 1.It seems when i decrease the multiblock read count parameter , the
> rsec/s increases , but i expect to see the opposite.What's wrong with
> this?

I've got a funny feeling you just hit the 32K default Linux I/O limit.
You see, until kernel release 2.6 (or patched 2.4), Linux will
"secretly" transform any single I/O request for more than 32K bytes
into as many 32K requests as needed. This takes time and physical
overhead from the disk controller(s). When you reduce the dbfmr,
you reduce this overhead and paradoxically (my my, what a long
word for "D'uh!"...) you end up with a little more r/s. Read on.

> 3. Related to my second question , since i use raid 10 (striping +
> mirroring ) is it possible to get higher io rates ? Looking at the
> iostat values , (rsec/s) / (r/s) more or less equals to 28kb. (This
is
> the kb. read in each read.)It seems a very low value to me. On a sun
> solaris ufs file system for instance , i can achive 128KB or even
1MB.
> per read by playing with the parameters , i dont understand why my
> linux box is different.

There is a patch for RHAS at Oracle Metalink that gets rid of this
32K limitation. It applies AFAIK only to 2.4.21 onwards, until
RHAS4 whereupon the 2.6 kernel takes over and it's not a problem
anymore. However I'm not sure if this patch is compatible with
anything under Oracle 10g, so CHECK first with support.

If this is your problem you need first to upgrade to the adequate
level of RHAS3, *then* apply the Oracle patch. You'll need to
request it from Oracle support themselves.

Go here:
http://www.oracle.com/technology/dep...df/ora_lcs.pdf
for all the nasty details.
HTH

Hi Chao ,

I have checked the statstpack output regarding to this query , in raw
trace file i see lots of 'global cache cr request ' wait events but
the time they take is very little comparing to the disk read events:


select /*+NOPARALLEL(M) */count(*)
from
taniadm.MERKEZ_CIKIS_34 m

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 3 0.00 0.01 1 1 0
0
Execute 3 0.00 0.00 0 0 0
0
Fetch 3 61.10 767.28 307181 307209 0
3
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 9 61.10 767.30 307182 307210 0
3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 46

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=102403 r=102399 w=0 time=262103077 us)
10133769 TABLE ACCESS FULL MERKEZ_CIKIS_34 (cr=102403 r=102399 w=0
time=257079273 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
SQL*Net message to client 8 0.00
0.00
SQL*Net message from client 8 78.11
173.33
global cache cr request 154082 0.12
6.73
db file scattered read 23984 1.01
707.97
latch free 6 0.03
0.05
SQL*Net break/reset to client 2 0.00
0.00
library cache lock 4 0.00
0.00
db file sequential read 3 0.01
0.02
************************************************** *****************************

Here the total times waited value for global cache cr request is large
but total waited is very small . The majority of query time spent in
disk io.

For the direct/io , i have checked the oracle executables, straced
them (especially open system calls) and saw the o_direct flag , and :

filesystemio_options is set to none. (for ocfs i was told it was not
necessary to set it, since ocfs uses direct io without this parameter)


Kind Regrads,






"chao_ping" <zhuchao@gmail.com> wrote in message news:<1116479070.144515.220410@g47g2000cwa.googleg roups.com>...
> One possible reason could because of your global cache management. If
> you shutdown one node, maybe result will be diffirent.
>
> One question, is your OCFS doing directIO read? Else filesystem cache
> can mask your result.

utkanbir wrote:
>
> My system is a two node rac on redhat linux 2.1 , 4 ia64 cpus ,
> 8gb.ram , ocfs and emc raid 10

time to move to RH3?

> The db_block_size is 16kb. , current multi_block_read_Count is 64
> (which makes 1mb. of read)

can mean nothing in Linux, read on...

>
> 1.It seems when i decrease the multiblock read count parameter , the
> rsec/s increases , but i expect to see the opposite.What's wrong with
> this?

I've got a funny feeling you just hit the 32K default Linux I/O limit.
You see, until kernel release 2.6 (or patched 2.4), Linux will
"secretly" transform any single I/O request for more than 32K bytes
into as many 32K requests as needed. This takes time and physical
overhead from the disk controller(s). When you reduce the dbfmr,
you reduce this overhead and paradoxically (my my, what a long
word for "D'uh!"...) you end up with a little more r/s. Read on.

> 3. Related to my second question , since i use raid 10 (striping +
> mirroring ) is it possible to get higher io rates ? Looking at the
> iostat values , (rsec/s) / (r/s) more or less equals to 28kb. (This
is
> the kb. read in each read.)It seems a very low value to me. On a sun
> solaris ufs file system for instance , i can achive 128KB or even
1MB.
> per read by playing with the parameters , i dont understand why my
> linux box is different.

There is a patch for RHAS at Oracle Metalink that gets rid of this
32K limitation. It applies AFAIK only to 2.4.21 onwards, until
RHAS4 whereupon the 2.6 kernel takes over and it's not a problem
anymore. However I'm not sure if this patch is compatible with
anything under Oracle 10g, so CHECK first with support.

If this is your problem you need first to upgrade to the adequate
level of RHAS3, *then* apply the Oracle patch. You'll need to
request it from Oracle support themselves.

Go here:
http://www.oracle.com/technology/dep...df/ora_lcs.pdf
for all the nasty details.
HTH

Hi, Noons,
It is the first time I know about this:
>>I've got a funny feeling you just hit the 32K default Linux I/O
limit.
>>You see, until kernel release 2.6 (or patched 2.4), Linux will
>>"secretly" transform any single I/O request for more than 32K bytes
>>into as many 32K requests as needed
Can you provide some detail about this? For example, metalink id, or
URL about linux kernel about this.


And Utkanbir, since it is for test, can you shutdown one node and
perform your test again?
THanks