Thread: can postgres run well on NFS mounted partitions?

can postgres run well on NFS mounted partitions?

From
anj patnaik
Date:
Can anyone advise if there are problems running postgres over NFS mounted partitions?

I do need reliability and high speed.

Thank you.

Re: can postgres run well on NFS mounted partitions?

From
Patric Bechtel
Date:
-----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-----


Re: can postgres run well on NFS mounted partitions?

From
John McKown
Date:
On Mon, Nov 9, 2015 at 1:09 PM, Patric Bechtel <patric.bechtel@gmail.com> wrote:
-----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.

​I agree. This was discussed, in depth, a while back over on the SQLite forum. Basically NFS does not reliably implement all the file system protocols, such as locking, which are really needed by a data base. NFS is excellent for stuff like backups and even source code repositories.​

 

/Patric

--

Schrodinger's backup: The condition of any backup is unknown until a restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

Re: can postgres run well on NFS mounted partitions?

From
Albe Laurenz
Date:
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

Re: can postgres run well on NFS mounted partitions?

From
anj patnaik
Date:
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.


Thanks for any feedback.

On Tue, Nov 10, 2015 at 4:29 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
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

Re: can postgres run well on NFS mounted partitions?

From
Scott Mead
Date:

On Tue, Nov 10, 2015 at 6: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.


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.

--Scott
 

Thanks for any feedback.

On Tue, Nov 10, 2015 at 4:29 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
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


Re: can postgres run well on NFS mounted partitions?

From
Scott Marlowe
Date:
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.


Re: can postgres run well on NFS mounted partitions?

From
Albe Laurenz
Date:
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

Re: can postgres run well on NFS mounted partitions?

From
John R Pierce
Date:
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



Re: can postgres run well on NFS mounted partitions?

From
Albe Laurenz
Date:
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

Re: can postgres run well on NFS mounted partitions?

From
anj patnaik
Date:
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!



On Tue, Nov 10, 2015 at 9:59 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
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.

Re: can postgres run well on NFS mounted partitions?

From
Scott Marlowe
Date:
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.


Re: can postgres run well on NFS mounted partitions?

From
Scott Marlowe
Date:
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.


Re: can postgres run well on NFS mounted partitions?

From
John McKown
Date:
On Thu, Nov 12, 2015 at 12:49 PM, 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.

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.

-- 

Schrodinger's backup: The condition of any backup is unknown until a restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

Re: can postgres run well on NFS mounted partitions?

From
John R Pierce
Date:
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



Re: can postgres run well on NFS mounted partitions?

From
Albe Laurenz
Date:
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

Re: can postgres run well on NFS mounted partitions?

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

I have tried running multiple readers/writers, inserted data and verified the byte count from doing fetches. I also ran a reboot and checked that the db came back up after it with data. I still haven't done power failure test since I'm remote and the systems are UPS'd.

Let me know how to detect corruption. Thank you all.

On Thu, Nov 12, 2015 at 1:50 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
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.

Re: can postgres run well on NFS mounted partitions?

From
Albe Laurenz
Date:
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

Re: can postgres run well on NFS mounted partitions?

From
Melvin Davidson
Date:
Another good idea is to do a

grep FATAL: your_postgres_log

and see if PostgreSQL is complaining about anything relating to table. index or WAL access,
otherwise, usually the only "acceptable" FATAL's are related to pg_hba.conf authorization or other connection
problems.

On Mon, Nov 16, 2015 at 3:43 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.