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

Il 29/07/2016 17:26, Francisco Olarte ha scritto:
> Hi:
>
> On Fri, Jul 29, 2016 at 10:35 AM, Moreno Andreo
> <moreno(dot)andreo(at)evolu-s(dot)it> wrote:
>> 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?