Thread: Postgres fsync off (not needed) with NetApp
All, So I thought I'd pose this question: If I have a pg database attached to a powervault (PV) with just an off-the-shelf SCSI card I generally want fsync on to prevent data corruption in case the PV should loose power. However, if I have it attached to a NetApp that ensures data writes to via the NVRAM can I safely turn fsync off to gain additional performance? Best Regards, Dan Gorman
On Wed, 14 Jun 2006 14:48:04 -0700 Dan Gorman <dgorman@hi5.com> wrote: > If I have a pg database attached to a powervault (PV) with just an > off-the-shelf SCSI card I generally want fsync on to prevent data > corruption in case the PV should loose power. > However, if I have it attached to a NetApp that ensures data writes > to via the NVRAM can I safely turn fsync off to gain additional > performance? I wouldn't. Remember, you still have to get the data to the NetApp. You don't want things sitting in the computer's buffers when it's power goes down. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
No. You need fsync on in order to force the data to get TO the NetApp at the right time. With fsync off, the data gets cached in the operating system. -- Mark Lewis On Wed, 2006-06-14 at 14:48 -0700, Dan Gorman wrote: > All, > So I thought I'd pose this question: > > If I have a pg database attached to a powervault (PV) with just an > off-the-shelf SCSI card I generally want fsync on to prevent data > corruption in case the PV should loose power. > However, if I have it attached to a NetApp that ensures data writes > to via the NVRAM can I safely turn fsync off to gain additional > performance? > > Best Regards, > Dan Gorman > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
Mark Lewis <mark.lewis@mir3.com> writes: > On Wed, 2006-06-14 at 14:48 -0700, Dan Gorman wrote: > > > > However, if I have it attached to a NetApp that ensures data writes > > to via the NVRAM can I safely turn fsync off to gain additional > > performance? > > No. You need fsync on in order to force the data to get TO the NetApp > at the right time. With fsync off, the data gets cached in the > operating system. In fact the benefit of the NVRAM is precisely that it makes sure you *don't* have any reason to turn fsync off. It should make the fsync essentially free. -- greg
On 14 Jun 2006 23:33:53 -0400, Greg Stark <gsstark@mit.edu> wrote: > In fact the benefit of the NVRAM is precisely that it makes sure you *don't* > have any reason to turn fsync off. It should make the fsync essentially free. Having run PostgreSQL on a NetApp with input from NetApp, this is correct. fsync should be turned on, but you will not incur the *real* direct-to-disk cost of the sync, it will be direct-to-NVRAM. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
That makes sense. Speaking of NetApp, we're using the 3050C with 4 FC shelfs. Any generic advice other than the NetApp (their NFS oracle tuning options) that might be useful? (e.g. turning off snapshots) Regards, Dan Gorman On Jun 14, 2006, at 10:14 PM, Jonah H. Harris wrote: > On 14 Jun 2006 23:33:53 -0400, Greg Stark <gsstark@mit.edu> wrote: >> In fact the benefit of the NVRAM is precisely that it makes sure >> you *don't* >> have any reason to turn fsync off. It should make the fsync >> essentially free. > > Having run PostgreSQL on a NetApp with input from NetApp, this is > correct. fsync should be turned on, but you will not incur the *real* > direct-to-disk cost of the sync, it will be direct-to-NVRAM. > > -- > Jonah H. Harris, Software Architect | phone: 732.331.1300 > EnterpriseDB Corporation | fax: 732.331.1301 > 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com > Iselin, New Jersey 08830 | http://www.enterprisedb.com/
On 6/15/06, Dan Gorman <dgorman@hi5.com> wrote: > shelfs. Any generic advice other than the NetApp (their NFS oracle > tuning options) that might be useful? (e.g. turning off snapshots) I was using PostgreSQL on a 980c, but feature-wise they're probably pretty close. What type of application are you running? OLTP? If so, what type of transaction volume? Are you planning to use any Flex* or Snap* features? What type of volume layouts are you using? -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
On 6/15/06, Jonah H. Harris <jonah.harris@gmail.com> wrote: > On 6/15/06, Dan Gorman <dgorman@hi5.com> wrote: > > shelfs. Any generic advice other than the NetApp (their NFS oracle > > tuning options) that might be useful? (e.g. turning off snapshots) > > I was using PostgreSQL on a 980c, but feature-wise they're probably > pretty close. > > What type of application are you running? OLTP? If so, what type of > transaction volume? Are you planning to use any Flex* or Snap* > features? What type of volume layouts are you using? Also, you mentioned NFS... is that what you were planning? If you licensed iSCSI, it's a bit better for the database from a performance angle. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
Dan Gorman wrote: > That makes sense. Speaking of NetApp, we're using the 3050C with 4 FC > shelfs. Any generic advice other than the NetApp (their NFS oracle > tuning options) > that might be useful? (e.g. turning off snapshots) I'm not sure if this is in the tuning advice you already have, but we use a dedicated gigabit interface to the NetApp, with jumbo (9K) frames, and an 8K NFS blocksize. We use this for both Oracle and Postgres when the database resides on NetApp. Joe
Currently I have jumbo frames enabled on the NA and the switches and also are using a the 32K R/W NFS options. Everything is gigE. Regards, Dan Gorman On Jun 14, 2006, at 10:51 PM, Joe Conway wrote: > Dan Gorman wrote: >> That makes sense. Speaking of NetApp, we're using the 3050C with 4 >> FC shelfs. Any generic advice other than the NetApp (their NFS >> oracle tuning options) >> that might be useful? (e.g. turning off snapshots) > > I'm not sure if this is in the tuning advice you already have, but > we use a dedicated gigabit interface to the NetApp, with jumbo (9K) > frames, and an 8K NFS blocksize. We use this for both Oracle and > Postgres when the database resides on NetApp. > > Joe
On Thu, Jun 15, 2006 at 01:14:26AM -0400, Jonah H. Harris wrote: > On 14 Jun 2006 23:33:53 -0400, Greg Stark <gsstark@mit.edu> wrote: > >In fact the benefit of the NVRAM is precisely that it makes sure you > >*don't* > >have any reason to turn fsync off. It should make the fsync essentially > >free. > > Having run PostgreSQL on a NetApp with input from NetApp, this is > correct. fsync should be turned on, but you will not incur the *real* > direct-to-disk cost of the sync, it will be direct-to-NVRAM. Just so there's no confusion... this applies to any caching RAID controller as well. You just need to ensure that the cache in the controller absolutely will not be lost in the event of a power failure or what-have-you. On most controllers this is accomplished with a simple battery backup; I don't know if the higher-end stuff takes further steps (such as flashing the cache contents to flash memory on a power failure). -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461