On a new (rather big) setup we are looking on utilizing NFS for writing DB-dumps and AI files. The customer is hesitant to use a NFS share for storing this data. Any known technical reason for this? The use case for NFS is lower cost then primary DB storage.
n.
How big is "rather big"? Given the price of storage these days, why not provision as much as you need locally? That would include not just space for the DB and AI files, but also space for other things you do on a DB server: writing backups, restoring backups, D&L, archiving AI files, gzipping large files, index rebuild, etc. When you dump and load in the future do you want to be doing it across a network?
It is a good idea to have your AI files on separate storage from the rest of your database (data extents and BI). However, I would not mount AI extents on a network path like an NFS or SMB share. You won't have the same reliability or performance as on local or SAN storage.
I have tried using a remote directory on a DR box, mounted via NFS, as an AI archive directory (-aiarcdir) and that went badly. There was a network glitch, the mount became stale and didn't recover, and the AI daemon became unresponsive to any commands or signals. Ultimately I had to restart the DB to recover it. Now I configure the AI daemon to write filled AI extents to a local directory and then I move them across to the DR box.
If you want to copy database backup files or archived AI extents to a remote directory via NFS, that shouldn't be a problem as long as you have some monitoring and error handling in place for any automated processes (e.g. rsync scripts). But don't put any part of your database on a network file system.
"The customer is hesitant..."
Good for them! They should be. Cheaper means you're trading something for lower cost. Either performance or reliability. Or both. (If the proposed target is a NetApp "filer" then you are sacrificing both.)
I have, on occasion, used NFS as short-term storage when no other option could be found. Or as an intermediate step to a more robust target. But never as a routine, primary target for important data.
If you are doing something that is completely restartable and/or recoverable and don't mind that you may actually need to exercise that option, then NFS is an option. Otherwise you should not go there.
"rather big", in this case is not so much related to the database size, but the setup (DB< 5TBs). Remote distributed 2 node Power8 AIX cluster with Power HA and storage mirroring on the SAN layer. Writing DB-dumps and AI files directely to NFS will -
A: minimize the need for very pricy replicated primary storaga, used as secondary storage.
B: Time; a backup/dump vil be "valid" as soon as Progress has finished writing it to the NFS share. (whereas witing locally is not "valid" until another prosess has moved/copied it off to eksternal storage)
The NFS in question will perform very well on large sequential read/writes ops, and with multiple 10gig eth as media it should not be the bottleneck. For random IO the performance will not be anywhere close to primary storage. (it is based on EMC Isilon)
Not being too familiar with progress, what I meen by AI files are the equivalent of a MS SQL Log backup running every 15 mins or so. Are you saying that if the AI prosess cannot find/write to the NFS mount, it will render the DB in a non-usable state? same for writing the DB-dumps? (full backups..)
The Performance of the NFS share should be stellar for both backup/restore purposes (for sequential IO, at least on par with the primary storage for the spesific purpose)
Both NFS and "local" storage will in this case be on a SAN. The difference will be the media and protocoll. FC media, FC procoll, FC switches versus 10gig eth media, NFS protocoll, ethernet switches.
If the NFS mount fails and there are AI extents on it, then yes, the db will stall. AI files are part of the database if AI is enabled. Same thing if you would have a database on NFS, if NFS is no more, so is the database (gone).
So you have SAN, you have FC cards and FC switch - why are we talking about NFS here? Go FC.
/All/ processes that read from or write to an NFS mounted filesystem can block (until the I/O operation completes) for long periods of time. If that happens then whatever resources or locks that process owns are not available to other processes. In the worst case, this can lead to all programs connected to the database becoming blocked.
for example, an online backup that is writing the backup to an NFS filesystem can block on a write. then any process that wants to update a database block that has not yet been backed up will queue the block for backup and wait for it before proceeding to perform the update. Since the backup program is blocked, it is not servicing the queue.
the After-Image archiving daemon can block too. This can lead to all the ai extents becoming full so there is no room left to write new after-image log records.
This behaviour can occur with other types of storage devices too, even direct attached disk drives, but is much, much less likely.