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 ****