Re: PostgreSQL Reliability when fsync = false on Linux-XFS

On 3 Sep 2003 at 23:36, Rod Taylor wrote:

> > - the way PostgreSQL expects data to be written to disk without the
> > fsync calls for things not to get corrupted in the event of a crash,
> > and
>
> If you want the filesystem to deal with this, I believe it is necessary
> for it to write the data out in the same order the write requests are
> supplied in between ALL PostgreSQL processes. If you can accomplish
> this, you do not need WAL.
>
> There are shortcuts which can be taken in the above, which is where WAL
> comes in. WAL writes are ordered between processes and WAL of a single
> process always hits disk prior to commit -- fsync forces both of these.
> Due to WAL being in place, data can be written at almost any time. The
> benefit to WAL is a single file fsync rather than the entire database
> requiring one (PostgreSQL pre-7.1 method).
>
> > I know that at the end of the day, if I value my data, I must (1) back
> > it up regularly, and (2) keep fsync enabled in PostgreSQL. However given
> > the significance performance hit (at least as far as massive INSERT or
>
> If you want good performance, invest in a SCSI controller that has
> battery backed write cache. A few megs will do it. You will find
> performance similar to fsync being off (you don't wait for disk
> rotation) but without the whole dataloss issue. Another alternative is
> to buy a small 15krpm disk dedicated for WAL. In theory you can achieve
> one commit per rotation.

Just wonderin. What if you symlink WAL to a directory which is on mounted USB
RAM drive?

Will that increase any throughput? I am sure a 256/512MB flash drive will cost
lot less than a SCSI disk. May be even a GB on flash drive would do..

Just a thought..

Bye
Shridhar

--
Ambition, n: An overmastering desire to be vilified by enemies while living and
made ridiculous by friends when dead. -- Ambrose Bierce