Обсуждение: The case of PostgreSQL on NFS Server

Hi

This is shikase.
I have a question about PostgreSQL on NFS Server as follows.
Please let me know advice about that. If I posted the wrong mailing-list,
please let me know.


I found the manual 17.2.1 Network File System.

17.2.1 Network File System

If client and server NFS implementations have non-standard
 semantics, this can cause reliability problems (see http://www.time-travellers.org/shane/papers/NFS_considered_harmful.html). Specifically, delayed
 (asynchronous) writes to the NFS server can cause reliability
problems; if possible, mount NFS file systems synchronously
(without caching) to avoid this. Also, soft-mounting NFS is
not recommended.

Particularly, I pay attention the following sentense:
if possible, mount NFS file systems synchronously (without caching)
to avoid this.

In my environment, Database cluster is in NFS server. The instance
of PostgreSQL is working in the one server. not in another server.
I guess that, In my environment,  the mount options, system synchronously
and without cache does not need. I means that the sufficient option is
system synchronously only.Because  the NFS caching problem might not be
 occurred in the case. It also has the good influence about the performance.

But I found it in the manual, So I want to make the following question.

If I mount the database cluster with caching in my environment, What kind of
 problem I will meet? Please give the information about the problem you met.

Thank you very much.

Iwao Shikase.
shikase@air.co.jp

On 24/06/10 12:42, Iwao Shikase wrote:

> In my environment, Database cluster is in NFS server.

So you are mounting an nfs file system shared by "localhost" ?

Why not run PostgreSQL directly on the underlying file system, rather
than via nfs?

> I guess that, In my environment,  the mount options, system synchronously
> and without cache does not need.

I would still expect to lose some written data if the system crashed or
lost power and nfs write caching was enabled. Because nfs's caching
doesn't guarantee write ordering, this data loss would probably horribly
corrupt your database.

If you can get your NFS implementation to guarantee write ordering then
it's quite safe to cache. Good luck proving that it's doing the right
thing, though.

Even if you're certain it's correct, you need to do a lot of testing
where you abuse the server and see how it copes. Unplug it unexpectedly.
Press the reset switch. 'kill -9' the backends. Crash the operating
system. Etc. See if there's any sign of damage to the PostgreSQL storage
after repeated abuse of the server like that.

> If I mount the database cluster with caching in my environment, What kind of
>  problem I will meet? Please give the information about the problem you met.

It varies so much by NFS implementation and version that it is really
hard to say.

Personally, I would never, EVER run a database of any kind over nfs, but
perhaps I'm just scarred by Linux's NFS implementation.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/

Craig Ringer wrote:
> On 24/06/10 12:42, Iwao Shikase wrote:
>
[.......]
>> If I mount the database cluster with caching in my environment, What kind of
>>  problem I will meet? Please give the information about the problem you met.
>
> It varies so much by NFS implementation and version that it is really
> hard to say.
>
> Personally, I would never, EVER run a database of any kind over nfs, but
> perhaps I'm just scarred by Linux's NFS implementation.
>

Hello, I agree with Craig 100%.

The experience we have had with different NFS versions/configurations in Linux in
the past , tells us that this is not a reliable solution for databases.

Others will say the oposite, but running PostgreSQL via NFS in Linux is from my
point of view, a disaster waiting to happen. I have to say too that we have not
tried this lately and that maybe things have improved since last time we testet.

regards,
--
Rafael Martinez, <r.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/

Hello,

Thank you for the information . I understood you never recommend the case.
I have another question. I also use Linux in the case.
There are NFS mount options , noac and sync  which is concerned with
caching.
Which option means the keyword 'without caching' in the manual.?
And the case you tested, which option you use when mounting.

Thank you very much.

Shikase.

(2010年06月24日 16:52), Rafael Martinez wrote:
> Craig Ringer wrote:
>
>> On 24/06/10 12:42, Iwao Shikase wrote:
>>
>>
> [.......]
>
>>> If I mount the database cluster with caching in my environment, What kind of
>>>   problem I will meet? Please give the information about the problem you met.
>>>
>> It varies so much by NFS implementation and version that it is really
>> hard to say.
>>
>> Personally, I would never, EVER run a database of any kind over nfs, but
>> perhaps I'm just scarred by Linux's NFS implementation.
>>
>>
> Hello, I agree with Craig 100%.
>
> The experience we have had with different NFS versions/configurations in Linux in
> the past , tells us that this is not a reliable solution for databases.
>
> Others will say the oposite, but running PostgreSQL via NFS in Linux is from my
> point of view, a disaster waiting to happen. I have to say too that we have not
> tried this lately and that maybe things have improved since last time we testet.
>
> regards,
>


--
株式会社エアー
システムソリューション事業部
システム開発グループ
鹿瀬　巌
email:shikase@air.co.jp
tel:06-6368-6080

On Thursday 24 June 2010 06:42:52 Iwao Shikase wrote:
> Hi
>
> This is shikase.
> I have a question about PostgreSQL on NFS Server as follows.
> Please let me know advice about that. If I posted the wrong mailing-list,
> please let me know.

Why not use a real Cluster Filesystem?
http://en.wikipedia.org/wiki/Clustered_file_system

NFS is nice for sharing files between multiple systems, but most server
products specifically mention it is not compatible with file sharing protocols
like NFS/CIFS/....

--
J. Roeleveld

shikase@air.co.jp (Iwao Shikase) writes:
> If I mount the database cluster with caching in my environment, What kind of
>  problem I will meet? Please give the information about the problem you met.

If you use NFS, and are not absolutely certain of the semantics of the
implementation, then you are accepting the risk that in case of a
crash (for nearly any reason), your database may wind up destroyed.

Let me say that more directly:

  The kind of problem you may meet is that your database may be
  destroyed.

Few people have sufficient confidence in the semantics of their NFS
implementations, and the use of NFS is generally not recommended, as a
consequence.
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www3.sympatico.ca/cbbrowne/unix.html
In case you weren't aware, "ad homineum" is not latin for "the user of
this technique is a fine debater." -- Thomas F. Burdick

Hi  Roeleveld-san,

Thank you for your advice. But My purpose is to test PostgreSQL
which data cluster is in NFS server.
As your says, Cluster Filesystem is one of answer of sharing files.
But my company still want to use NFS server. So I want to know
how to use PostgreSQL using NFS.

By the way,The manual says, "if possible, mount NFS file systems synchronously (without caching) to avoid this".

I want to know  what means synchronously with out caching.
In other word, which NFS options we must use follwoing the manual.
sync ? or noac ? or both ?

Thank you ,
Iwao Shikase



 
(2010年06月24日 17:38), J. Roeleveld wrote:

On Thursday 24 June 2010 06:42:52 Iwao Shikase wrote: 

Hi

This is shikase.
I have a question about PostgreSQL on NFS Server as follows.
Please let me know advice about that. If I posted the wrong mailing-list,
please let me know.   

Why not use a real Cluster Filesystem?
http://en.wikipedia.org/wiki/Clustered_file_system

NFS is nice for sharing files between multiple systems, but most server 
products specifically mention it is not compatible with file sharing protocols 
like NFS/CIFS/....

--
J. Roeleveld
 

email:shikase@air.co.jp

On 25/06/2010 8:23 AM, Iwao Shikase wrote:
> Hi  Roeleveld-san,
>
> Thank you for your advice. But My purpose is to test PostgreSQL
> which data cluster is in NFS server.
> As your says, Cluster Filesystem is one of answer of sharing files.
> But my company still want to use NFS server. So I want to know
> how to use PostgreSQL using NFS.
>
> By the way,The manual says, "if possible, mount NFS file systems
> synchronously (without caching) to avoid this".

It depends on the NFS implementation. Are you using a Linux NFS server?
Linux NFS client? What versions? Are they on the same machine and if
not, how are they connected? You're not providing enough information for
anyone to give you a useful answer.

In general, you should assume that NFS is just NOT SAFE, unless you can
prove a particular configuration IS safe by rigourous testing of both
client- and server- failure scenarios, including power loss, OS crashes,
etc.

> I want to know  what means synchronously with out caching.
> In other word, which NFS options we must use follwoing the manual.
> sync ? or noac ? or both ?

Both.

You might be OK with async,ac *IF* you can guarantee that neither client
nor server will ever crash, lose their network connection to each other,
lose power, or otherwise malfunction in any way. In other words, even if
they're on the same computer, just don't do it.

For that matter, even with "sync,ac" it's hard to know if the NFS server
will really provide the proper write ordering. Your writes will probably
reach the NFS server in order, but whether the NFS server then writes
them to its disk in order - who knows. So if your NFS server crashes or
loses power, then EVEN if you disable all caching in the NFS procotol
you might still have data corruption.

NFS and databases: Just don't do it.

If your company only has NFS, now is a good opportunity to ask them for
iSCSI, ATA-over-Ethernet, network block device, a suitable clustering
file system, or SOME other way to access storage. It's incredibly hard,
if possible, to guarantee that a database will work properly over NFS
unless you know a lot about the details of the NFS client and server
involved - and any configuration that's safe will almost certainly also
be glacially slow.

--
Craig Ringer

On Friday 25 June 2010 02:23:17 Iwao Shikase wrote:
> Hi  Roeleveld-san,
>
> Thank you for your advice. But My purpose is to test PostgreSQL
> which data cluster is in NFS server.
> As your says, Cluster Filesystem is one of answer of sharing files.
> But my company still want to use NFS server. So I want to know
> how to use PostgreSQL using NFS.
>
> By the way,The manual says, "if possible, mount NFS file systems
> synchronously (without caching) to avoid this".
>
> I want to know  what means synchronously with out caching.
> In other word, which NFS options we must use follwoing the manual.
> sync ? or noac ? or both ?
>
> Thank you ,
> Iwao Shikase

Hi Shikase-san,

I am not fully familiar with all the NFS mount options.
What the manual means is that when using NFS (Or any network-file-system) is
that you need to make sure that all write-activities are done in the sequence
they are sent and also that there is no write-caching done anywhere.

In other words, both on the NFS-client and on the NFS-server side, you need to
be certain that write-caching is disabled and that all write-actions are in
sync.

I myself only use NFS for document-storage. All the storage for server-
software (Database, email,....) are all on local disks.
If I would need to store them on a different system (NAS/SAN) then I would be
using a sharing-method that exposes the raw disk, like the methods mentioned
by Craig Ringer.

What is the actual reason why your company wants to use an NFS server to store
the database-files?
If it's for backups, why not schedule backups to be written to the NFS server?
This has the added benefit of being able to easily restore and upgrade to newer
versions without too much hassle.

--
Joost Roeleveld

Craig Ringer wrote:
> On 24/06/10 12:42, Iwao Shikase wrote:
>
> > In my environment, Database cluster is in NFS server.
>
> So you are mounting an nfs file system shared by "localhost" ?
>
> Why not run PostgreSQL directly on the underlying file system, rather
> than via nfs?
>
> > I guess that, In my environment,  the mount options, system synchronously
> > and without cache does not need.
>
> I would still expect to lose some written data if the system crashed or
> lost power and nfs write caching was enabled. Because nfs's caching
> doesn't guarantee write ordering, this data loss would probably horribly
> corrupt your database.
>
> If you can get your NFS implementation to guarantee write ordering then
> it's quite safe to cache. Good luck proving that it's doing the right
> thing, though.

"Safe" meaning it will not corrupt your database, but you could lose
committed transactions after a server crash.

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

  + None of us is going to be here forever. +