Ambrose,

This article is referring to databases stored on the ext2 linux filesystem, which is a traditional update-in-place fs with fixed locations for metadata. This means that every time a file is read or written the access time (atime) field in the directory entry is updated, causing a seek to the directory inode. Note that actual writes cause the last-modified field to also be changed, you can't do anything about that one.

So for transactional read intensive applications (such as databse reporting) on traditional filesystems (ext2/ext3/reiserfs/UFS/NTFS/JFS/AdvFS/XFS etc.), turning off the atime update for those files will cause a substantial reduction in both metadata processing CPU overhead and drive head seeking - hence the performance improvements noted.

You would actually see an even greater performance improvement for files held locally on a Windows server due to the NTFS MFT location, so those seeking to optimise a SQL Server installation on a local NTFS filesystem should consider turning off the atime update on their db files.

Everything changes however when you move the database to a remote WAFL filesystem on a NetApp Filer.

On Network Appliance's WAFL filesytem, we have no fixed locations for metadata and never update-in-place. All required metadata changes for the new consistent image, right back to the root inode, are written at each consistency point. So on NetApp, the access time updata simply causes a few extra blocks to be written at each consistency point (every 10 seconds unless a lot of incoming data writes are received as well). Since we these blocks are written to new locations we aren't seeking back to old spots so there's very little or no head additional head movement, and since the metadata processing is performed by the Filer there is ZERO difference in CPU overhead on the actual Oracle server (i.e. it has no visibility of the atime update setting which is local to the Filer). So the performance benefit to be gained by turning of the atime update on a filer volume hosting an Oracle database is minimal, and being able to track when files have been read may be useful to the administrator, particulary when performing security breach analysis.

NetApp do recommend that you turn this off in a limited range of environments, such a read-only library data warehouse where the atime updates are high compared to the regular write load and may cause unnecessary consistency points within the normal 10 second timeout, or add a significant number of blocks to the CP. In these cases you may see a performance improvement but it is still unlikely to be more than a few percent.

Note that if you are using the new SnapDrive VLD or LUN block I/O products with your Filer to host the database, you're back to a traditional filesystem managed by the attached Oracle host, so WAFL features don't offload the metadata processing CPU overhead like they do for NFS/CIFS access. However you will still gain some benefit from the block location virtualisation (the head seeks are eliminated because the VLD blocks are not actually overwritten in place, the host just thinks they are.... :o) )

Hope all this helps.

Alan McLachlan Senior Systems Engineer Storage Management Solutions NetAppCA, FCNE

ASI Solutions www.asi.com.au Ph +61 2 6230 1566 Fax +61 2 6230 5343 Mobile +61 428 655644 e-mail amclachlan@asi.com.au

-----Original Message----- From: Ambrose_Earle@shamrockfoods.com [mailto:Ambrose_Earle@shamrockfoods.com] Sent: Saturday, 30 November 2002 10:19 AM To: toasters@mathworks.com Subject: no_atime_update

Is anyone using this option with volumes containing Oracle or Lotus Notes files?

Have you tested it for any performance improvements?

How does this differ from mounting with noatime?

I read an article that claimed a nice performance increase when used with Oracle datafiles. Here's the link:

http://www.linuxjournal.com/article.php?sid=5840

**** ASI Solutions Disclaimer **** The material transmitted may contain confidential and/or privileged material and is intended only for the addressee. If you receive this in error, please notify the sender and destroy any copies of the material immediately. ASI will protect your Privacy according to the 10 Privacy Principles outlined under the new Privacy Act, Dec 2001.

This email is also subject to copyright. Any use of or reliance upon this material by persons or entities other than the addressee is prohibited.

E-mails may be interfered with, may contain computer viruses or other defects. Under no circumstances do we accept liability for any loss or damage which may result from your receipt of this message or any attachments. **** END OF MESSAGE ****