Re: [SPAM] Re: [SPAM] Re: [GENERAL] WAL directory size calculation
Hi Moreno: On Wed, Aug 3, 2016 at 1:07 PM, Moreno Andreo wrote: It's already been answered, but as it seems to be answering a chunk of my mail... > 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [SPAM] Re: [SPAM] Re: [SPAM] Re: [GENERAL] WAL directory size calculation
Il 03/08/2016 14:12, Michael Paquier ha scritto: On Wed, Aug 3, 2016 at 8:07 PM, Moreno Andreo wrote: 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. Got it. Thanks! Moreno -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [SPAM] Re: [SPAM] Re: [GENERAL] WAL directory size calculation
On Wed, Aug 3, 2016 at 8:07 PM, Moreno Andreo wrote: > 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. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [SPAM] Re: [SPAM] Re: [GENERAL] 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 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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
