[SPAM] Re: [SPAM] Re: [GENERAL] WAL directory size calculation

[Moreno Andreo]

On Fri, Jul 29, 2016 at 10:35 AM, Moreno Andreo

After Andreas post and thinking about it a while, I went to the decision
that it's better not to use RAM but another persistent disk, because there
can be an instant between when a WAL is written and it's fsync'ed, and if a
failure happens in this instant the amount of data not fsync'ed is lost. Am
I right?

With the usual configuration, fsync on, etc.. what postgres does is to
write and sync THE WAL before commit, but it does not sync the table
pages. Should anything bad (tm) happen it can replay the synced wal to
recover. If you use a ram disk for WAL and have a large enough ram
cache you can lose a lot of data, not just from the last sync. At the
worst point you could start a transaction, create a database, fill it
and commit and have everything in the ram-wal and the hd cache, then
crash and have nothing on reboot.
Francisco Olarte.

This is another good point.

I'm ending up with a 10 GB SSD dedicated to WAL files. I'm starting with
a small slice of my clients for now, to test production environment, and
as traffic will grow, I'll see if my choice was good or has to be improved.

Should I keep fsync off? I'd think it would be better leaving it on, right?

[Michael Paquier]

Post by Moreno Andreo
Should I keep fsync off? I'd think it would be better leaving it on, right?

From the docs: https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS
While turning off fsync is often a performance benefit, this can
result in unrecoverable data corruption in the event of a power
failure or system crash. Thus it is only advisable to turn off fsync
if you can easily recreate your entire database from external data.

So if you care about your data, that's to set in any case to on, as is
full_page_writes.

[Francisco Olarte]

Hi Moreno:

On Wed, Aug 3, 2016 at 1:07 PM, Moreno Andreo <***@evolu-s.it> wrote:

It's already been answered, but as it seems to be answering a chunk of
my mail...

Post by Moreno Andreo
Should I keep fsync off? I'd think it would be better leaving it on, right?

Yes. If you have to ask wether fsync should be on, it should.

I mean, you only take it off when you are absolutely sure of where you
are doing, fsync off goes against the D in acid.

You normally only turn it off in counted cases. As an example we have
an special postgresql.conf for full cluster restores, with fsync=off.
Wehen we need it we stop the cluster, boot it with that, restore, stop
it again and reboot with the normal fsync=on config. In this case we
do not mind losing data as we are doing a full restore anyway.

But normally, its a bad idea. As a classic photo caption says,
fsync=off => DBAs running with scissors.

Francisco Olarte.