Re: how unsafe (or worst scenarios) when setting fsync OFF for postgresql

a) I have absolutely no idea regarding price tags when it comes to SUN hardware, last time I worked with SUN gear was in ´01 so you'll have to check with your local (SUN-)supplier for uptodate prices.

b) Same here (no idea). But I'd be surprised if UFS (and ZFS) was unable to take advantage of battery backed write cache...

Regards,
Mikael

-----Original Message-----
From: Guoping Zhang [mailto:guoping(dot)zhang(at)nec(dot)com(dot)au]
Sent: den 28 april 2006 07:35
To: Mikael Carneholm; pgsql-performance(at)postgresql(dot)org
Cc: Guoping Zhang (E-mail)
Subject: RE: [PERFORM] how unsafe (or worst scenarios) when setting fsync OFF for postgresql

Hi, Mikael,

We have not looked at this option yet, but very good direction though.

Two issues are unsure:
a) we are on SUN SPARC platform, unsure what the price tag for such a hardware device with SUN brand?

b) how well does UFS (or a new ZFS) work with the device (as ext3 can mount with data=writeback)?

Cheers and regards,
Guoping

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org]On Behalf Of Mikael Carneholm
Sent: 2006Äê4ÔÂ27ÈÕ 17:43
To: guoping(dot)zhang(at)nec(dot)com(dot)au; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] how unsafe (or worst scenarios) when setting fsync OFF for postgresql

Get a SCSI controller with a battery backed cache, and mount the disks with data=writeback (if you use ext3). If you loose power in the middle of a transaction, the battery will ensure that the write operation still completes. With asynch writing setup like this, fsync operations will return almost immidiately giving you performance close to that of running with fsync off.

Regards,
Mikael

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Guoping Zhang
Sent: den 27 april 2006 08:31
To: pgsql-performance(at)postgresql(dot)org
Cc: Guoping Zhang (E-mail)
Subject: [PERFORM] how unsafe (or worst scenarios) when setting fsync OFF for postgresql

Hi,.

We are new to Postgresql. I am appreciated if the following question can be answered.

Our application has a strict speed requirement for DB operation. Our tests show that it takes about 10secs for the operation when setting fsync off, but takes about 70 seconds when setting fsync ON (with other WAL related parametered tuned).

We have to looking at setting fsync OFF option for performance reason, our questions are

a) if we set fsync OFF and anything (very low chance though) like OS crash, loss of power, or hardware fault happened, can postgresql rolls back to the state that the last checkpoint was done ( but all the operations after that is lost)

b) Does this roll back to last checkpoint can ensure the database back to consistent state?

c) What is worst scenarios if setting fsync OFF in term of database safety. We try to avoid to restore the database from nightly backup.

We view our application is not that data loss critical, say loss of five minutes of data and operation occasionally, but the database integrity and consistency must be kept.

Can we set fsync OFF for the performance benefit, have the risk of only
5 minutes data loss or much worse?

Thanks in advance.

Regards,

Guoping

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings