Home » RDBMS Server » Performance Tuning » filesystemio_options=SETALL is slower than ASYNCH, why? (11gr2 hp redhat)
filesystemio_options=SETALL is slower than ASYNCH, why? [message #541158] Sat, 28 January 2012 14:51 Go to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
we have discovered that with filesystemio_options=setall, data is delivered to/from our databases at half speed. When we set filesystemio_options=asynch then we get twice the rate.

This seems backwards to me. I always thought SETALL was a superior setting and there would not be any adverse affects from using it vs. ASYNCH. So I am hoping someone can give me an explanation of circumstances where using SETALL would cause I/O to be slower than ASYNCH. There are several guys on the web who have said they have seen this many times, but no one actually gives a rational which is what I an looking for. I want to understand better for when I go back to the performance table for other databases.

Thanks, Kevin
Re: filesystemio_options=SETALL is slower than ASYNCH, why? [message #541183 is a reply to message #541158] Sun, 29 January 2012 04:33 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I doubt that I will be able to contribute any useful information, but I suspect that filesystemio_options cannot be considered without also considering the file system. What are file system are you using? For example, I remember getting different results on Solaris with UFS or ZFS file systems.
How are you measuring the performance? Do you think that dbms_resource_manager.calibrate_io gives meaningful results?
var al number
var mi number
var mm number
exec dbms_resource_manager.calibrate_io(max_iops=>:mi,max_mbps=>:mm,actual_latency=>:al)
print ml
print mi
print mm

Re: filesystemio_options=SETALL is slower than ASYNCH, why? [message #542708 is a reply to message #541183] Thu, 09 February 2012 17:31 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
UPDATE:

Turns out there is a bug with this on 11gR2 11.2.0.2.0, which is fixed (they say) in 11.2.0.3.0. So we have started a testing process that will take several weeks to complete.

Thanks for your help so far, will keep posted.

Kevin
Re: filesystemio_options=SETALL is slower than ASYNCH, why? [message #553721 is a reply to message #542708] Tue, 08 May 2012 02:50 Go to previous messageGo to next message
pviljoen
Messages: 1
Registered: May 2012
Location: Cape Town, South Africa
Junior Member
Did you get to any answer with your testing. I'm having the same issues with filesystemio_options.
Thanks
Pieter
Re: filesystemio_options=SETALL is slower than ASYNCH, why? [message #554006 is a reply to message #553721] Wed, 09 May 2012 13:05 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
In my testing, performance of SETALL and ASYNC were sensitive to the mount options(mincache,convosync in my env), so be sure to check them. In my case a badly behaved vendor app that does thousands of FTS [full table scan] followed by a few small deletes deletes, and the performance improved significantly by enabling filesystem cacheing (repeated FTS were mush faster even though deletes were slower). Be sure to check if specific operations have performance issues.




cape town rocks!

[Updated on: Fri, 11 May 2012 10:16]

Report message to a moderator

Re: filesystemio_options=SETALL is slower than ASYNCH, why? [message #554155 is a reply to message #554006] Thu, 10 May 2012 21:02 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks for taking the time to comment Andrew. Unfortunately your use of abbreviations leaves your comment un-intelligble. What is f/s and FST?

Kevin

[Updated on: Thu, 10 May 2012 21:03]

Report message to a moderator

Re: filesystemio_options=SETALL is slower than ASYNCH, why? [message #554233 is a reply to message #554155] Fri, 11 May 2012 10:15 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Appologies for not proof reading. I changed f/s to filesystem and FST to FTS (full table scan). Support doc 'File System's Buffer Cache versus Direct I/O [ID 462072.1]' may be of use.
Re: filesystemio_options=SETALL is slower than ASYNCH, why? [message #554235 is a reply to message #554233] Fri, 11 May 2012 10:27 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks, I get it now.

Kevin
Previous Topic: Query hang
Next Topic: PLSQL taking long time
Goto Forum:
  


Current Time: Thu Mar 28 18:07:46 CDT 2024