RE: Db file scattered reads vs. direct path reads

Vxfs and solaris 10 don't support kernalized async, but they do support a 
software workaround: each aio call spawns a series of threads that operate on a 
piece of the io call, these threads use pread(). One would need to buy quick 
i/o option for vxfs to get the kernalized aio.
I verified that during the direct path read events that async i/o was operating 
as expected.

Its during the traditional reads from "db file scattered reads" which correlate 
to a synchronous read in the truss ouput that performance is slower. I'll check 
on the fdsync. I have tried it with directio on and off, it makes very little 
difference.

Thanks for your time!

Josh C.

-----Original Message-----
From: dtseiler@xxxxxxxxx [mailto:dtseiler@xxxxxxxxx] On Behalf Of Don Seiler
Sent: Friday, February 22, 2008 1:40 PM
To: finn.oracledba@xxxxxxxxx
Cc: Josh Collier; oracle-l@xxxxxxxxxxxxx
Subject: Re: Db file scattered reads vs. direct path reads

I do believe that I have async I/O on VxFS (no I don't have ODM).
From an earlier blog post [0]:

oracle:~/sr $ strace -c -p 12605
Process 12605 attached - interrupt to quit Process 12605 detached
% time     seconds  usecs/call     calls    errors syscall
-- ---- ---- --- --- ------
80.66    1.142811         298      3831           io_getevents
7.26    0.102820          51      2004           io_submit

Don.

[0] http://seilerwerks.wordpress.com/2007/12/19/a-sub-prime-io-primer/


On Fri, Feb 22, 2008 at 3:16 PM, Finn Jorgensen <finn.oracledba@xxxxxxxxx> 
wrote:
> As I understand it vxfs does not support async IO. When you say
> "asynch reads on the new system", I'm thinking you mean direct reads?
>
> When not using directio on vxfs and you look at your truss output are
> there a lot of "fdsync" calls, which takes a long time (like 0.01
> seconds per call)? If so, that's your culprit. Going to directio can
> eliminate these, but there's a level of patching on both Solaris 10
> and veritas together with a kernel setting that pretty much eliminates
> the need for that setup as the fdsync calls are made so fast they can
> be ignored. Have you SA's check into paching solaris 10 and veritas to 
> eliminate the slow fdsync calls.
>
> Finn
>
>
>
>
> On 2/22/08, Josh Collier <Josh.Collier@xxxxxxxxxxxx> wrote:
> >
> >
> > the old system is on ufs, the new is vxfs.
> >
> > I've narrowed it down to the difference in synchronous disk reads.
> > the
> async reads on the new system are much faster. the synchronous disk
> reads are slower.
> >
> >
> >
> > ________________________________
>  From: Finn Jorgensen [mailto:finn.oracledba@xxxxxxxxx]
> > Sent: Friday, February 22, 2008 7:01 AM
> > To: Josh Collier
> > Cc: oracle-l@xxxxxxxxxxxxx
> > Subject: Re: Db file scattered reads vs. direct path reads
> >
> >
> >
> > What type of filesystem are you on? What differences are there
> > between the
> old system and the new apart from the storage array?
> >
> > Finn
> >
> >
> > On 2/20/08, Josh Collier <Josh.Collier@xxxxxxxxxxxx> wrote:
> > >
> > >
> > > Hi,
> > >
> > > I've got a conundrum. We've got a new disk system. When I full
> > > scan a
> table with the nocache hint the trace files record lots of reads like
> this
> > >
> > > WAIT #1: nam='db file scattered read' ela= 38471 file#=777
> > > block#=238789
> blocks=64 obj#=2805234 tim=359927798919
> > >
> > > And the query takes 11 minutes for a 19gb table, much slower than
> > > our
> old system
> > >
> > > When I enforce direct path reads with the following parameter
> > > :alter session set "_serial_direct_read" = true
> > >
> > > The trace files record lots of reads like this
> > >
> > > WAIT #1: nam='direct path read' ela= 5 file number=777 first
> > > dba=238789
> block cnt=64 obj#=2805234 tim=343398354853
> > >
> > > And the query returns in 3 minutes
> > >
> > > What would cause the scattered reads to be so much slower than the
> direct path reads?
> > >
> > > I've enabled direct io on the db and verified it via truss.
> > >
> > > Version is 10.2.0.1
> > > Solaris 10
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>



--
Don Seiler
http://seilerwerks.wordpress.com
ultimate: http://www.mufc.us
--
http://www.freelists.org/webpage/oracle-l


Other related posts: