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_at_gmail.com [mailto:dtseiler_at_gmail.com] On Behalf Of Don Seiler Sent: Friday, February 22, 2008 1:40 PM
To: finn.oracledba_at_gmail.com
Cc: Josh Collier; oracle-l_at_freelists.org 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_at_gmail.com> 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_at_banfield.net> 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_at_gmail.com]
> > Sent: Friday, February 22, 2008 7:01 AM
> > To: Josh Collier
> > Cc: oracle-l_at_freelists.org
> > 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_at_banfield.net> 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