Thread: can postgres run well on NFS mounted partitions?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi Anj,

anj patnaik schrieb am 06.11.2015 um 23:37:
> Can anyone advise if there are problems running postgres over NFS mounted partitions?
>
> I do need reliability and high speed.

then don't do this. Period. Put postgres where your data is.

/Patric
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.22 (GNU/Linux)
Comment: GnuPT 2.5.2

iEYEARECAAYFAlZA71gACgkQfGgGu8y7ypDjNACgkpuw2HyU19o2t0ezjU3uW01b
Ie8AoJYPq0ugX/LvGf7rqwoUY+/s5Pvm
=MM8H
-----END PGP SIGNATURE-----

anj patnaik wrote:
> Can anyone advise if there are problems running postgres over NFS mounted partitions?
> 
> I do need reliability and high speed.

I have got the advice not to use NFS from a number of people who should know,
but there are also knowledgable people who use PostgreSQL with NFS.

You need hard foreground mounts, and you need an NFS server that is
guaranteed not to lose data that the client has synced to disk.

You should probably only consider storage systems that directly
support NFS, and you should run performance and reliability tests.

Yours,
Laurenz Albe

On Tue, Nov 10, 2015 at 4:26 PM, anj patnaik <patna73@gmail.com> wrote:
> Thanks for the feedback. I have setup a second Linux VM (running RHEL 5.11)
> and Postgres 9.4. I ran some insertions today from a client running on
> Windows. The client does a loop of 30 updates.
>
> I am seeing about 10-20% increase in latency in the case where DB is on NFS
> (over TCP) compared to directly on disk.
>
> The other machine I am using to compare is running RHEL 6.5 and Postgres
> 9.4.
>
> Are there any specific tests that are recommended to test that postgres over
> NFS works well?
>
> I am planning on doing a few large data inserts and fetches.
>
> With the little testing, the DB over NFS appears fine.

You need to do a power failure test. While running something like
pgbench for a few minutes, run a checkpoint command and then pull the
plug on the NFS server and / or the pg server. Bring it back up. Is
your db corrupted? Then there's something that needs fixing.

Scott Mead wrote:
> Don't do it.  Period.   I've used 4 big-vendor appliances with NFS as well as my own server.  With
> maybe 3 exceptions, most of the 'total-data-loss' scenarios I've dealt with regarding transactional
> data was due to NFS.

Can you share more details?
What happened and what had caused the problem?

While researching the same topic, I came across two scenarios where people
had encountered problems:

1) A background mounted NFS failed to mount in time.
   A smart startup script triggered initdb on the empty mount point,
   then while PostgreSQL was running, the mount completed
   and data corruption ensued.
2) The NFS server implementation didn't actually sync the data
   (or keep it in a battery powered buffer) when it said it had
   synced them.

It would be good to know of other pitfalls; I (and no doubt not only I)
keep getting asked why we shouldn't run PostgreSQL on NFS when Oracle
has no problem with it (and don't tell me that Oracle does not care
about data corruption).

Yours,
Laurenz Albe

On 11/11/2015 12:37 AM, Albe Laurenz wrote:
> It would be good to know of other pitfalls; I (and no doubt not only I)
> keep getting asked why we shouldn't run PostgreSQL on NFS when Oracle
> has no problem with it (and don't tell me that Oracle does not care
> about data corruption).

Last I looked, Oracle had a very specific set of recommendations for
qualified NFS storage.    Like, NetApp Filers, with everything setup
just so.


--
john r pierce, recycling bits in santa cruz

John R Pierce wrote:
> On 11/11/2015 12:37 AM, Albe Laurenz wrote:
> > It would be good to know of other pitfalls; I (and no doubt not only I)
> > keep getting asked why we shouldn't run PostgreSQL on NFS when Oracle
> > has no problem with it (and don't tell me that Oracle does not care
> > about data corruption).
> 
> Last I looked, Oracle had a very specific set of recommendations for
> qualified NFS storage.    Like, NetApp Filers, with everything setup
> just so.

I had heard that before, so I looked and could not find any information
which NFS devices are supported. All you read is:
(http://docs.oracle.com/cd/E11882_01/install.112/e47689/app_nas.htm#LADBI1366)

  Before using the NAS device for the installation, verify that it is certified.
  For certification information refer to note 359515.1 on the My Oracle Support website

I did, and all that contains is a list of mount options you have to use, e.g.
for x86-64 Linux
rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,vers=3,timeo=600
(the "bg" weirds me out)

So I opened a service request about a year ago and asked them *what* NFS
storage is supported.

The answer (in unquotable English) was that it didn't matter what the
NFS server was, as long as the NFS client is on a UNIX/Linux certified for Oracle
database and you use the right mount options.
At least that's what I understood ...

Yours,
Laurenz Albe

On Thu, Nov 12, 2015 at 11:19 AM, anj patnaik <patna73@gmail.com> wrote:
> The Linux VM where postgres is running over NFS is in a different location
> than where I am. Both the NFS mounted storage and VM are on the same network
> connected via 1GB ethernet switch.
>
> The physical server for the Linux VM has UPS.
>
> Is there any specific test I can run to do power failure?
>
> Can I reboot my VM to test this or that wouldn't be good enough?
>
> Also, why does a checkpoint need to run? I used the graphical installer to
> install postgres so I assume it would start automatically when the server
> starts.
>
> I was also thinking of blackhole testing. If I do a blackhole to the NFS
> server would I be able to test this accurately?
>
> Folks in the other teams believe NFS should work fine for us so I need to
> check it out.
>
> Your ideas are  highly appreciated!

The point of the checkpoint is to make sure as much as possible is
being written to the data directory when you "pull the plug". But
without being able to pull the power plugs on the NAS or db server you
can't really test for reliability in case of power loss. So you can't
know that it'll survive one. Just disconnecting its network connection
etc means it can still write out cached data if it isn't properly
syncing it.

On Thu, Nov 12, 2015 at 11:49 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, Nov 12, 2015 at 11:19 AM, anj patnaik <patna73@gmail.com> wrote:
>> The Linux VM where postgres is running over NFS is in a different location
>> than where I am. Both the NFS mounted storage and VM are on the same network
>> connected via 1GB ethernet switch.
>>
>> The physical server for the Linux VM has UPS.
>>
>> Is there any specific test I can run to do power failure?
>>
>> Can I reboot my VM to test this or that wouldn't be good enough?
>>
>> Also, why does a checkpoint need to run? I used the graphical installer to
>> install postgres so I assume it would start automatically when the server
>> starts.
>>
>> I was also thinking of blackhole testing. If I do a blackhole to the NFS
>> server would I be able to test this accurately?
>>
>> Folks in the other teams believe NFS should work fine for us so I need to
>> check it out.
>>
>> Your ideas are  highly appreciated!
>
> The point of the checkpoint is to make sure as much as possible is
> being written to the data directory when you "pull the plug". But
> without being able to pull the power plugs on the NAS or db server you
> can't really test for reliability in case of power loss. So you can't
> know that it'll survive one. Just disconnecting its network connection
> etc means it can still write out cached data if it isn't properly
> syncing it.

Also note that a UPS doesn't preclude the machine losing its power
supplies etc, or the ever popular faulty power switch / reset button
etc. Which I have experienced on production machines. UPS does not
mean never having a power failure.

On 11/12/2015 10:19 AM, anj patnaik wrote:
> The Linux VM where postgres is running over NFS is in a different
> location than where I am. Both the NFS mounted storage and VM are on
> the same network connected via 1GB ethernet switch.

and earlier...

>
> I do need reliability and high speed.

you won't get very high speed over gigE.


--
john r pierce, recycling bits in santa cruz

John McKown wrote:
> All of the above make we curious about using NFS for the data files, but having the WAL files on a
> local, perhaps SSD, device.​ I am not knowledgeable about WAL. Of course, I don't know why the OP wants
> to put the database files on an NFS.

If the data file storage does not keep the promise that synced files are
actually on disk, you'd get in trouble at checkpoint time.

So if you don't trust NFS, that wouldn't be an option.

Yours,
Laurenz Albe

anj patnaik wrote:
> How do you tell if a database is corrupted? Are there specific error messages/symptoms to look for?

That's actually a pretty tough question.

The standard test is to run "pg_dumpall", see if it finishes without error
and if the dump can be restored without error.
That won't detect any index corruption though.

You could try:

COPY (SELECT * FROM tab ORDER BY ...) TO 'file1';
SET enable_seqscan=off;
COPY (SELECT * FROM tab ORDER BY ...) TO 'file2';

and see if "file1" and "file2" are identical. That would check the index
used in the second COPY statement.

I don't know, but maybe enabling checksums with the -k option of "initdb"
would make such corruption more obvious.

Yours,
Laurenz Albe