MySQL 5.1.34 on NFS w/NetApp

Is this still a bad idea? I know older versions of MySQL performed poorly with NFS. I imagine the issue lies with the usage of fsnc() and/or O_DIRECT. If the issues are mostly resolved, are there common pitfalls/gotchas, specifically around a large (multiple tables with tens of millions of records) InnoDB database that may see up to 20-50 reads/sec

The first piece of good news for you is that there is now more information in the etherspace than had you asked the same question earlier than a year ago. I know this from bitter experience.

Netapp's official line is that MySQL is supported across all three protocols.

ONTAP  MySQL Enterprise  NFS        iSCSI      FCP
7G     5.X               Supported  Supported  Supported
7G     4.X               Supported  Supported  Supported

MySQL Enterprise versions 4.X and 5.X are supported on all NetApp fabric
attached storage models running any release of Data ONTAP 7G for any server
platform supported by MySQL that is listed in the NetApp host compatibility
matrix for each protocol.

The seconds piece of good news is that you didn't say MyISAM. Otherwise it would be a much different and murkier story, consisting of lousy performance and no supporting information. There isn't much choice there but to use block-based iSCSI or FCP. Not that those don't work very well, but they are a slightly different kettle of fish to file-based.

Instead, Netapp have published some OLTP benchmarks of MySQL 5.0 across all three storage protocols. The results indicate that InnoDB engines performed well and in line with the protocol differences seen in Oracle. FCP came out in front in terms of throughput. Whilst iSCSI was 9% and NFS 16% behind FCP. Which isn't as much of a significant difference as we had anticipated.

Even more helpfully is that the same document details the NFS and InnoDB specific steps that they took to achieve that benchmark figure. These include modifying innodb_buffer_pool_size, innodb_flush_method, NFS's attribute caching timeout, no_atime_update on the source volume and (as Richard says above) specifying different mount points for logs.

Personally I wouldn't recommend storing the logs upon different storage altogether such as local disk. The logs are closely related to any data that has already gone down to disk. If you separate them entirely then you could be setting yourself up for a fall. Even more so if you wish to perform snapshots, change the machine running MySQL or your local disks prove less reliable than the filer.

With all that said, the next best thing that you can do is suck it and see. Setup an environment based on the best practices in the document and perform some benchmarks with your own data. Evaluate how it performs in comparison to your local disks today.

--

NB: The first link is NOW restricted. You don't say whether you are already a Netapp customer. You should be able to view the second link regardless though.

NFS4 would be best if you can use it. I would at least mount with noatime. You should also consider specifying log file locations on local disks. If you have no local storage (as is common with a VM) you might try to store logs on a mount point that routes through a different network interface than your primary storage.