Re: 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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

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

> 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.

We have been running Postgres over NFS to a NetApp since 7.1 and we have
nothing but good things to say. We have 75 databases in 3 clusters all
connected to one netapp. We don't store a huge amount of data, currently
~43Gig, but it is constantly updated.

We keep the pgsql/data directory on the netapp. If one of our db servers
ever have a problem, we can just swap out the box, mount the drive and
restart postgres.

We like our support we get from them, the only issue we ever have is having
a drives fail which they get replacements to us promptly. Our NetApp has an
uptime currently over 2 years.

By the way, I though NetApp boxes came with an iSCSI license. NetApp
downplayed the iSCSI with us because they said you cannot share drives
between servers, but for postgres you don't want that anyway. It could
have also been that the NetApp is better tuned for NFS throughput and they
want to steer the user toward that.

If you want more specifics, feel free to ask.

Woody
iGLASS Networks

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

On Thu, Mar 20, 2008 at 9:09 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> 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(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://postgres.enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

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

Bruce Momjian <bruce(at)momjian(dot)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(at)gmail(dot)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(at)gmail(dot)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(at)momjian(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

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

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(at)easyflex(dot)nl
W: http://www.easyflex.nl