Re: Using Direct I/O Filesystem for Oracle Database
Date: Mon, 14 Jan 2008 21:54:27 -0800 (PST)
Message-ID: <643596.56147.qm@web45106.mail.sp1.yahoo.com>
BismillahirRehmanirRahim
Hi David Miller,
The output i pasted in my last email was taken from someone posted , and i gave the link to that post. so i just wanted to know the authencity of the claimed the poster made that "Oracle still dont use direct I/O with filesystemoption_io=setall."
Our Filesystem is ufs, we are running Oracle 10g R1 on Sun Sparc V890 Solaris 2.9.
After doing little research on Direct I/O i found out that filesystemoption_io=setall is only required thing to enable oracle to use Direct I/O for datafile Access. [ you also suggested that ]
And there is no need to mount Datafiles ufs filesystem with forcedirectio option.
Infact our Vendor Recommended to mount Oracle Datafiles ufs Filesystems with forcedirectio, whereas i decided to handle direct I/O at Oracle Level.
In my understanding its more efficient and appropriate to use filesystemoption_io=setall than to mount whole filesystem with forcedirectio, as it will do direct I/O on per datafile basis instead of per filesystem basis [ as in case of forcedirectio mount option.]
Moreover with filesystemio_options=setall , the backups and other command [ e.g. cp , mv , compress ] would still use buffered I/O and wont get slower.
I need experts comments on using filesystemio_options instead of forcedirectio mount option.
also i need to know how can ensure that Oracle is using Direct I/O with filesystemio_options=setall ,
and how can i compare the I/O performance with filesystemio_options=setall and with forcedirectio mount option ? which is recommended .
the output of /etc/release is pasted below.
Thanks for your support & time.
Your DBA Colleague
Muhammad Salman Faheem
[oracle_at_sun:ORCL oracle]$ more /etc/release
Solaris 9 9/05 s9s_u8wos_05 SPARC
Copyright 2005 Sun Microsystems, Inc. All Rights Reserved.
Use is subject to license terms.
Assembled 04 August 2005
David Miller <David.J.Miller_at_Sun.COM> wrote: Hi Muhammad,
A couple of comments and a couple of questions.
First, what kind of filesystem is this? Is it ufs, vxfs, zfs, other? directio really only works with ufs. So if it's some other kind of filesystem, the semantics will be different.
Second, the open returns file descriptor 11, but the following directio call and ioctl call refer to file descriptor 271 (0x10f). Do you have the directio and/or ioctl call for the same descriptor as the open?
And third, what exact version of Solaris are you running? Can you send the data in /etc/release?
Regards,
Dave
Junior DBA wrote, On 01/10/08 14:17:
>
> BismillahirRehmanirRahim
>
> Hi David Miller,
>
> Thanks for the detail reply.
>
> During my search i found following link :
>
> ----------------------------------------------------------------------------------------------------------
> http://orafaq.com/usenet/comp.databases.oracle.server/2007/07/12/0854.raw
>
>
> [ first flush the buffer cache ]
> sys_at_xxxxxxxxx> alter system flush buffer_cache;
>
> System altered.
>
> [ Then issue (as user oracle) ]
> oracle_at_ironman:~$ truss -f -t open,ioctl -u ':directio' sqlplus user/
> pass
>
> ... (lots of output here)
>
> [ Then type ]
> create table a as select * from big_table;
>
> You will see that for every data file opened, directio is explicitly
> turned off:
>
> 24399: open("/u04/oradata/BIA/APM_DATA13.dbf", O_RDWR|O_DSYNC) = 11
> 24399: -> libc:directio(0x10f,
> 0x0, 0x1, 0x0)
> 24399: ioctl(271, 0x2000664C, 0x00000000) = 0
>
> The 3rd parameter to the ioctl() call is 0 for directio_off, and 1 for
> directio_on.
> ------------------------------------------------------------
>
> Please comment on above post.
>
> Also i have following Queries:
>
> (*) How to confirm [any script/command/procedure] that when filesystemio_option=setall then
> Oracle uses Direct I/O when opening Datafiles.
>
> Best Regards
> Your DBA Colleague
> Muhammad Salman Faheem
>
>
> */David Miller /* wrote:
>
> Hi,
>
> You should only need to modify filesystemio_options to setall. That will
> use directio where possible without requiring it on the mount options.
> In fact it's best to not put it in the mount options, because then
> activities
> like backups or redo log archiving does not have to use directio and can
> benefit from the filesystem readahead.
>
> One change you may need to look at, however, is increasing the SGA
> since you
> no longer will have the filesystem buffering blocks, so you'll need
> to increase
> the size of the SGA so Oracle can buffer more blocks there.
>
> You do not need to change dbwr_io_slaves or db_writer_processes and
> leaving them
> just as default may be best, unless you have a specific reason to
> change them.
>
> If you are still seeing Disk I/O performance issues, make sure you
> have enough
> physical disk spindles to handle the I/O you're trying to do.
> directio can
> help get around some filesystem limitations (like the single-writer lock
> required by Posix) but won't make the disks spin faster or handle
> more I/O.
> So if that's your problem, you'll need to find a way to add more
> spindles
> to the Oracle objects that need more I/O.
>
> Regards,
>
> Dave Miller
>
> Junior DBA wrote, On 01/08/08 23:41:
> > BismillahirRehmanirRahim
> >
> > Hi All,
> >
> > We are running Oracle 10g R1 on Sun SPARC Solaris 9 Sun Fire V890
> Server.
> >
> > By default we have following setting in the Database:
> >
> > disk_asynch_io = TRUE
> > filesystemio_options= asynch &
> > db_writer_processes=1
> > dbwr_io_slaves =0
> >
> > To improve Disk I/O Performance We re-mount Oracle Database
> FileSystems
> > [ containing Datafiles,logfiles & archive logs] with
> > forcedirectio,noatime option.
> >
> > After remounting the Oracle Filesystems with forcedirectio,noatime i
> > changed the filesystemio_options to setall i.e.
> >
> > filesystemio_options=setall
> >
> > (1) I need to know if i missed something or made any mistake.
> >
> > (2) Do i need to change dbwr_io_slaves or db_writer_processes values
> >
> > (3) What else i could do to improve DISK I/O performance
> >
> > Appreciate your valuable feedback.
> >
> > Best Regards,
> >
> > Your DBA Colleague
> > Muhammad Salman Faheem
> >
> >
> >
> ------------------------------------------------------------------------
> > Looking for last minute shopping deals? Find them fast with Yahoo!
> > Search.
> >
> >
>
>
> ------------------------------------------------------------------------
> Never miss a thing. Make Yahoo your homepage.
>
Looking for last minute shopping deals? Find them fast with Yahoo! Search.
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jan 14 2008 - 23:54:27 CST