4

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

1
  • 20-50 reads per second is a pretty quiet database - depending on what they are of course. It might be helpful to respondents to say how big your database is expected to be (10M records isn't that much, but it depends how big the records are) in gigabytes.
    – MarkR
    Jun 25, 2009 at 7:12

5 Answers 5

6

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.

2
  • Please specify which logs you are concerned about separating. I would assume you mean the ib_logfile* and I can understand your concern. If I found that I needed to squeeze out a little extra performance I would still separate it as I said in my answer, and just understand that I will have to recover it from local storage (or where ever) to repair after a crash. However, I see no risk in separating the mysql-bin.* or (shudder) mysqld.log (aka:General Query Log) if you happen to use them. I would strongly advise against the latter. Jun 25, 2009 at 13:57
  • Yep, InnoDB's logs. There's no immediate /risk/ in moving the binlogs. Just the trade-off in convenience and consistency if you're snapshotting data. If you resurrect a snapshot then you will want all of the data and logs relating to a single point-in-time. Part of the reason we ensure that nothing is on DAS is so that we can recover quickly if we were to lose the physical machine and move the resources to another. If using an activity log in production then, well.. say no more ;)
    – Dan Carley
    Jun 25, 2009 at 14:35
1

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.

0

Personally, I would use iSCSI instead NFS when mounting disk from a NetApp for MySQL. Incredibly efficient and you get direct filesystem access to the LUN.

0

MySQL InnoDB commit performance depends largely on how fast the underlying IO can sync data to disc; this is hugely impacted by network latency, so running MySQL over NFS will have a massive hit no matter what the server is. Even if your network is as fast as possible, it is still likely to be a lot slower than a local disk (I'm assuming your local disc would be a battery backed raid controller here).

On the other hand, read performance is mostly based on how much of the database fits in ram. If you can fit the whole database in RAM (or most of it, depending on the usage patterns), then performance will be very good, as you'll just set your innodb_buffer_pool to be bigger than the data, and in theory no reads will ever need to be done on the NFS.

0

Just as long as you're not using berkleydb, MySQL/InnoDB works fine to NFS. I'm not sure what it says in the Netapp NFS documentation, but if you mount the NFS volume with the 'async' option, then the application will not be blocked waiting for writes to hit the disk.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .