Re: The case of PostgreSQL on NFS Server (II)
| От | Octavi Fors |
|---|---|
| Тема | Re: The case of PostgreSQL on NFS Server (II) |
| Дата | |
| Msg-id | CAJEYUR8GvJWWK_0FDCJv8FzL8X7YLBqe+JU8XDznhd8g=FdXWg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: The case of PostgreSQL on NFS Server (II) ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Ответы |
Re: The case of PostgreSQL on NFS Server (II)
(John McKown <john.archie.mckown@gmail.com>)
|
| Список | pgsql-general |
Hi David, John et al.,
as far as I understand eSATA is not an option for me. First because I already have bought the DS2415+ NAS, which does not has eSATA I/O :(
And second, because I need the database to be accessible from two computers in the same LAN.
This is new - and while the desire for using your multi-terrabyte device may make the risk of running over NFS potentially worthwhile there is no reasonable way to have two running databases share data files. You can share the NAS device and have each PostgreSQL instance consume an unshared portion of its resources.You appear to either be "mis-speaking" or omitting potentially critically important details here...
My situation is the following:
-Two computers (C1 & C2) and NAS (with no eSATA I/O) on the same LAN.
-C1 acquires images from a telescope and periodically stores them via NFS in the NAS (no database involved here, just in the ext4 filesystem).
-C2 is a 12 xeon core-class server designed to analyze the stored images in the NAS, and compute astrometry & photometry measurements (catalogs & light curves) for every star & image. These measurements are inserted in the catalogs database inside the NAS.
-C2 is a 12 xeon core-class server designed to analyze the stored images in the NAS, and compute astrometry & photometry measurements (catalogs & light curves) for every star & image. These measurements are inserted in the catalogs database inside the NAS.
Therefore there's only *one* computer (C2) which will run postgresql server with the tablespace onNAS.
So does this approach sound like feasible if the NFS parameters are set properly?
I have a small NAS, but my main 2Tb disk is eSATA. My fstab entry for
the NAS just looks like:
smartstor1:/VOLUME1/DV7T /SmartStor1/Volume1/DV7T nfs
user,rw,noauto,noexec,nosuid 0 0
which is basically what you have, performance wise, as best as I can tell.
I see this message in this list any ideas suggesting to use the NFS parameters 'sync' (for synchronizing changes to a file) and 'noac' (for no caching).
Could you confirm that
nas_ip:/volume1/data /home/ofors/Documents/nas nfs noac,sync
would be good options for /etc/fstab?
Any additional NFS parameter?
Note, the most important aspect of that fact is that your WAL gets written to your data directory and not to the tablespace on which the database tables reside. (i.e. WAL does not make it to NAS - unless you setup wal shipping).
first time I hear about the importance of WAL and NFS.
I googled some and found this discussion about the topic.
Any ideas on how to include the options they mention (archinve_mode?) into NAS or /etc/fstab?
Thanks a lot in advance,
Octavi Fors
On Thu, Apr 2, 2015 at 8:47 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Thanks John for your extensive and helpful response.I have a NAS box. But I would worry about responsiveness. What is
better, IMO, is an external SATA connected DAS box. DAS is "Direct
Attached Storage". Many PCs have a eSATA port on the back side.as far as I understand eSATA is not an option for me. First because I already have bought the DS2415+ NAS, which does not has eSATA I/O :(
And second, because I need the database to be accessible from two computers in the same LAN.This is new - and while the desire for using your multi-terrabyte device may make the risk of running over NFS potentially worthwhile there is no reasonable way to have two running databases share data files. You can share the NAS device and have each PostgreSQL instance consume an unshared portion of its resources.You appear to either be "mis-speaking" or omitting potentially critically important details here...1) could you confirm that I don't have to mount --bind /var/lib/postgresql/9.2/main ?/var/lib/... is not on the NAS but, likely, on whatever your primary internal hard drive is. Note, the most important aspect of that fact is that your WAL gets written to your data directory and not to the tablespace on which the database tables reside. (i.e. WAL does not make it to NAS - unless you setup wal shipping).2) on my my /etc/fstab here is the current line for my NAS:
nas_ip:/volume1/data /home/ofors/Documents/nas nfsWhich NFS client and server (on NAS side) options/arguments do you suggest for optimizing performance? Or in other words, for minimizing database corruption in case of NAS (note that NAS drives are in RAID6) or computer failure?I am a little out of my league here but the main risk is that incomplete data is sent/received by/from the NAS. Once the data is in the NAS it is really no different than any other storage medium in terms of durability concerns. I do not really know how checkpoints and transient failed reads interact with PostgreSQL and what circumstances would prevent properly recorded WAL from being used to restore should a read/write failure occur.David J.
В списке pgsql-general по дате отправления:
Следующее
От: Alban HertroysДата:
Сообщение: Re: Why doesn't `RAISE EXCEPTION` provide error context?