Обсуждение: PostgreSQL NetApp and NFS

I just found out that my company is planning on migrating my databases from our current ISCSI storage solution to NetApps connected via NFS.  I knew about the NetApp migration, but always assumed (and shame on me) that I would have direct attachments to the servers.

Well, I am very uncomfortable with the NFS attachement.  I have been searching the archives and see a lot of anocdotal stories of NFS horrors, but so far, nothing but general stories and statements.

I need to know if anyone out there is/has run their PostgreSQL on NetApp arrays via NFS.  My particular situation is RH Linux 4 servers running Postgresql 8.1.  I need to provide our Operations manager with specific reasons why we should not run PostgreSQL over NetApp NFS.  Otherwise, they will go forward with this.

If you have any real life good or bad stories, I'd love to hear it.  Given the NetApp arrays supposedly being very good NFS platforms, overall, is this a recommended way to run PostgreSQL, or is it recommended to not run this way.


Feel free to reply directly if you are not comfortable talking to this on the list, but list replies would be preferred so others in my shoes can find this information.

Thanks,

Chris
--
Come see how to SAVE money on fuel, decrease harmful emissions, and even make MONEY. Visit http://colafuelguy.mybpi.com and join the revolution!

Chris Hoover wrote:
> If you have any real life good or bad stories, I'd love to hear it.  Given
> the NetApp arrays supposedly being very good NFS platforms, overall, is this
> a recommended way to run PostgreSQL, or is it recommended to not run this
> way.

We do have an NFS section in our documentation at the bottom of this
page:

    http://www.postgresql.org/docs/8.3/static/creating-cluster.html

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

My experience postgresql work good on NFS. Of course, use NFS over TCP, and use noac if you want to protect your database even more (my experience is NFS client caching doesn't lead to an irrecoverable database however)

I've encountered problems with RHEL4 as a database server and a client of a Netapp filer, due to a bug in the (redhat nfs client)
Postgresql uses BSD read/write semantics. The BSD semantics mean an IO call (either read or write) is atomic.
Linux uses system V read/write semantics. The system V semantics mean an IO is NOT atomic and can be interrupted.
A read call got interrupted (due to the bug in the nfs client), which meant the IO call kept waiting until infinity.
It even caused all other IO done against the inode to be waiting, leading to a situation where the server needed a reboot to be able to function propertly.

frits

Bruce Momjian <bruce@momjian.us> writes:
> Chris Hoover wrote:
>> If you have any real life good or bad stories, I'd love to hear it.  Given
>> the NetApp arrays supposedly being very good NFS platforms, overall, is this
>> a recommended way to run PostgreSQL, or is it recommended to not run this
>> way.

> We do have an NFS section in our documentation at the bottom of this
> page:
>     http://www.postgresql.org/docs/8.3/static/creating-cluster.html

Aside from what's said there, I'd note that it's a seriously bad idea
to use a "soft mount" or any arrangement wherein it's possible for
Postgres to be running while the NFS disk is not mounted.  Joe Conway
can still show you the scars from learning that lesson, I believe.
See the archives...

            regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Chris Hoover wrote:
> >> If you have any real life good or bad stories, I'd love to hear it.  Given
> >> the NetApp arrays supposedly being very good NFS platforms, overall, is this
> >> a recommended way to run PostgreSQL, or is it recommended to not run this
> >> way.
>
> > We do have an NFS section in our documentation at the bottom of this
> > page:
> >     http://www.postgresql.org/docs/8.3/static/creating-cluster.html
>
> Aside from what's said there, I'd note that it's a seriously bad idea
> to use a "soft mount" or any arrangement wherein it's possible for
> Postgres to be running while the NFS disk is not mounted.  Joe Conway
> can still show you the scars from learning that lesson, I believe.
> See the archives...

Do the docs need updating for this?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> Aside from what's said there, I'd note that it's a seriously bad idea
>> to use a "soft mount" or any arrangement wherein it's possible for
>> Postgres to be running while the NFS disk is not mounted.

> Do the docs need updating for this?

Wouldn't be a bad idea to mention it, if we're going to have a section
pointing out NFS risks.

            regards, tom lane

On Thu, Mar 20, 2008 at 8:32 PM, Chris Hoover <revoohc@gmail.com> wrote:
> I just found out that my company is planning on migrating my databases from
> our current ISCSI storage solution to NetApps connected via NFS.  I knew
> about the NetApp migration, but always assumed (and shame on me) that I
> would have direct attachments to the servers.

It is also possible to present block devices from NetApp over iSCSI or FC
(I am not sure about licensing model though).  You get all the goodies
like thin provisioning (only non-zero blocks are allocated), snapshots and
all, but you see it as a block device.  Works fine.

It is also worth to mention that NetApp utilizes somewhat "copy on write"
write strategy -- so whenever you modify a block, new version of the block
is written on its WAFL filesystem.  In practical terms it is quite resilient to
random writes (and that read performance is not stellar ;)).

I didn't try putting database on NFS mount directly, but I know NetApp
advertises that such setups are being used with Oracle database
(and allegedly Oracle website's databases are on such setup).
So I would feel quite safe with such a setup.

Oh, and don't forget to set rsize and wsize to 8K-32K (test and write here
what gives best performance!).

   Regards,
      Dawid

"Dawid Kuroczko" <qnex42@gmail.com> writes:

> It is also possible to present block devices from NetApp over iSCSI or FC
> (I am not sure about licensing model though).  You get all the goodies
> like thin provisioning (only non-zero blocks are allocated), snapshots and
> all, but you see it as a block device.  Works fine.

Note that Postgres doesn't expect to get "out of space" errors on writes
specifically because it pre-allocates blocks. So this "thin provisioning"
thing sounds kind of dangerous.

> It is also worth to mention that NetApp utilizes somewhat "copy on write"
> write strategy -- so whenever you modify a block, new version of the block
> is written on its WAFL filesystem.  In practical terms it is quite resilient to
> random writes (and that read performance is not stellar ;)).

Again, Postgres goes to some effort to keep its reads sequential. So this
sounds like it destroys that feature. Random writes don't matter so much
because Postgres has its WAL which it writes sequentially. Writes to data
files aren't in the critical path and can finish after the transaction is
committed.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Tom Lane wrote:
> >> Aside from what's said there, I'd note that it's a seriously bad idea
> >> to use a "soft mount" or any arrangement wherein it's possible for
> >> Postgres to be running while the NFS disk is not mounted.
>
> > Do the docs need updating for this?
>
> Wouldn't be a bad idea to mention it, if we're going to have a section
> pointing out NFS risks.

Documentation mention added.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.409
diff -c -c -r1.409 runtime.sgml
*** doc/src/sgml/runtime.sgml    26 Feb 2008 18:01:26 -0000    1.409
--- doc/src/sgml/runtime.sgml    21 Mar 2008 14:23:10 -0000
***************
*** 183,189 ****
      Specifically, delayed (asynchronous) writes to the <acronym>NFS</>
      server can cause reliability problems;   if possible, mount
      <acronym>NFS</> file systems synchronously (without caching) to avoid
!     this.  (Storage Area Networks (<acronym>SAN</>) use a low-level
      communication protocol rather than <acronym>NFS</>.)
     </para>

--- 183,190 ----
      Specifically, delayed (asynchronous) writes to the <acronym>NFS</>
      server can cause reliability problems;   if possible, mount
      <acronym>NFS</> file systems synchronously (without caching) to avoid
!     this.  Also, soft-mounting <acronym>NFS</> is not recommended.
!     (Storage Area Networks (<acronym>SAN</>) use a low-level
      communication protocol rather than <acronym>NFS</>.)
     </para>

Well, we're not running PGSQL on a Netapp over NFS, but a DB2 Database.
But nevertheless, it runs quite well. NFS is not a bad choice for your
database, the big memory buffer that allocates the raid6 blocks makes it
all very quick, like you're working directly on a 1+ TB ramdisk.

One important thing to keep in mind, is to make sure the NFS protocol
used is at least V3 and to check your locking options.
This made our DB2 crash, because when configured wrong, the file locking
mechanism on an NFS mount behaves differently than that of the local
storage. These parameters can be forced from the client side (fstab).

But still, with our 100+ GB OLTP database, I'm still quite fond of our
netapp.

-R-

Chris Hoover wrote:
> I just found out that my company is planning on migrating my databases
> from our current ISCSI storage solution to NetApps connected via NFS.  I
> knew about the NetApp migration, but always assumed (and shame on me)
> that I would have direct attachments to the servers.

>
> Chris
> --
> Come see how to SAVE money on fuel, decrease harmful emissions, and even
> make MONEY. Visit http://colafuelguy.mybpi.com and join the revolution!

--
Easyflex diensten b.v.
Acaciastraat 16
4921 MA  MADE
T: 0162 - 690410
F: 0162 - 690419
E: info@easyflex.nl
W: http://www.easyflex.nl