3

There is database server with ip-address in external zone with mysql instances and nas-storage existing in internal zone. I need that database file is located in storage. I want write in my.cnf for example:

[mysqld1]
port       = 3306
datadir    = /data/sql/mysqld1

[mysqld2]
port       = 3307
datadir    = /nasstorage/sql_on_storage/mysqld2
bind-address = 0.0.0.0

I am planning to mount directory from storage to database server. Which is technology is better for this puspose: iSCSI or NFS?

Boris Salimov
  • 221
  • 2
  • 3
  • 8

1 Answers1

6

Network filesystems and databases can be a problematic combo, and at least InnoDB does not like NFS. From the MySQL documentation

If reliability is a consideration for your data, do not configure InnoDB to use data files or log files on NFS volumes. Potential problems vary according to OS and version of NFS, and include such issues as lack of protection from conflicting writes, and limitations on maximum file sizes.

So I would go ahead with iSCSI instead. Whatever you decide to use, please benchmark & stress-test it to see how reliable your choice is.

Janne Pikkarainen
  • 31,454
  • 4
  • 56
  • 78
  • that is true. However, some NFS vendos (no name, it's a network appliance, hint), do have best practices for running mysql on NFS volumes (see http://www.netapp.com/us/media/tr-3657.pdf, point 3.6: Linux nfs protocol, page 11). – natxo asenjo Jul 11 '16 at 10:53
  • 1
    I still suspect that under heavy load and some strange conditions that can be very error-prone thing to do. – Janne Pikkarainen Jul 11 '16 at 11:03
  • @natxoasenjo your link is to a NetApp paper from 2010 that deals with MySQL 5.0. This link might be more up-to-date: https://kb.netapp.com/support/s/article/ka21A0000000WkTQAU/what-are-the-mount-options-for-databases-on-netapp-nfs – Joshua Huber Oct 04 '17 at 20:35
  • Link update https://kb.netapp.com/Advice_and_Troubleshooting/Data_Storage_Software/ONTAP_OS/What_are_the_mount_options_for_databases_on_NetApp_NFS%3F. Found some optimization steps in a blog post https://www.ajohnstone.com/achives/optimizing-mysql-over-nfs-with-netapp/ – Dalibor Filus May 28 '20 at 10:40