Re: How much RAM is to much

From: Jamey Johnston <jj_at_jameyj.com>
Date: Mon, 18 Apr 2011 20:18:04 -0500
Message-Id: <F7BA8939-036C-442E-9970-D931B1E905C7_at_jameyj.com>

Be careful with running DBs with large I/O loads on Solaris without using Direct I/O. It uses a lot of CPU cycles! We would have our servers go to load averages of over 200-300 and become virtually unresponsive.

jbj2

--

Jamey Johnston

On Apr 18, 2011, at 7:58 PM, kyle Hailey <kylelf_at_gmail.com> wrote:



> 

> Testing on Solaris, I got direct I/O if either NFS mount was set forcedirectio or Oracle had the parameter filesystemio_options=directio

> 

> The only case I got unix files system caching was when the mount was done without forcedirectio and filesystemio_options were either none or asynch.

> 

> - Kyle

> http://dboptimizer.com

> 

> PS used dtrace on solaris to watch the file system access to see whether the query was going to disk or not and watching the number of physical reads with autotrace in sqlplus. The query was definitely doing the same physical reads in all cases and in all cases the disks were accessed except when the NFS mount was done without forcedirectio and filesystemio_options were either none or asynch. Query was doing

> 87129 consistent reads

> 77951 physical reads 

> Of course the response time of the query was a good indicator. The second execution of the query with Unix caching was about 5 seconds, with direct I/O and 32K resize/wsize on the NFS mount it was 60 seconds and with 1M rsize/wsize on the NFS mounts it was 30 seconds. (Looks like the rsize/wsize can have a big impact )

> For this table, when cached in the buffer cache, it too 2 seconds, ie no physical reads.

> 

> 

> 

> On Fri, Feb 11, 2011 at 3:11 PM, D'Hooge Freek <Freek.DHooge_at_uptime.be> wrote:

> Gaja,

> X-archive-position: 34399

> X-ecartis-version: Ecartis v1.0.0

> Sender: oracle-l-bounce_at_freelists.org

> Errors-to: oracle-l-bounce_at_freelists.org

> X-original-sender: Freek.DHooge_at_uptime.be

> Precedence: normal

> Reply-To: Freek.DHooge_at_uptime.be

> List-help: <mailto:ecartis_at_freelists.org?Subject=help>

> List-unsubscribe: <oracle-l-request_at_freelists.org?Subject=unsubscribe>

> List-software: Ecartis version 1.0.0

> List-Id: oracle-l <oracle-l.freelists.org>

> X-List-ID: oracle-l <oracle-l.freelists.org>

> List-subscribe: <oracle-l-request_at_freelists.org?Subject=subscribe>

> List-owner: <mailto:steve.adams_at_ixora.com.au>

> List-post: <mailto:oracle-l_at_freelists.org>

> List-archive: <http://www.freelists.org/archives/oracle-l>

> X-list: oracle-l

> 

> This explains why the forcedirectio mount option is required with NFS on solaris.

> But I always thought that setting the filesystemio_options parameter to directIO or setall caused the processes to open the files with the O_DIRECT flag. If so, would this then not cause the file to be accessed with directio despite any setting on the filesystem?

> 

> I'm working mainly on linux these days (either with nfs or asm), so not much chance in testing this.

> 

> 

> Regards,

> 

> 

> Freek D'Hooge

> Uptime

> Oracle Database Administrator

> email: freek.dhooge_at_uptime.be

> tel +32(0)3 451 23 82

> http://www.uptime.be

> disclaimer: www.uptime.be/disclaimer

> ---

> From: Gaja Krishna Vaidyanatha [mailto:gajav_at_yahoo.com]

> Sent: vrijdag 11 februari 2011 22:47

> To: D'Hooge Freek

> Cc: Oracle-L List

> Subject: Re: How much RAM is to much

> 

> Hi Freek,

> 

> What you said is true for filesystems that do NOT allow "direct I/O" mount options in their respective mount commands. But for those filesystems that do (i.e. vxfs, jfs etc) support the relevant direct I/O mount options, the direct I/O mount option has always (in my experience) been required in addition to setting filesystemio_options to SETALL. Setting just the filesystemio_options in the init.ora (in those cases) did not create the desired result. 

> 

> If you have observed the "lack of the mount option" in recent times on those filesystems where direct I/O mount options ARE supported (i.e. vxfs, jfs etc), please advise. There is always something to learn new each day :)

> 

> Cheers,

> 

> Gaja

>  

> Gaja Krishna Vaidyanatha,

> Founder/Principal, DBPerfMan LLC

> http://www.dbperfman.com

> Phone - 001-(650)-743-6060

> Co-author:Oracle Insights:Tales of the Oak Table - http://www.apress.com/book/bookDisplay.html?bID=314

> Co-author:Oracle Performance Tuning 101 - http://www.amazon.com/gp/reader/0072131454/ref=sib_dp_pt/102-6130796-4625766

> --

> http://www.freelists.org/webpage/oracle-l

> 

> 

> 


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 18 2011 - 20:18:04 CDT