Re: Disable page writes when fsync off, add GUC

This patch disables page writes to WAL when fsync is off, because with
no fsync guarantee, the page write recovery isn't useful.

This also adds a full_page_writes GUC to turn off page writes to WAL.
Some people might not want full_page_writes, but still might want fsync.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

* Bruce Momjian (pgman(at)candle(dot)pha(dot)pa(dot)us) wrote:
> This patch disables page writes to WAL when fsync is off, because with
> no fsync guarantee, the page write recovery isn't useful.

This doesn't seem quite right to me. What happens with PITR? And
Postgres crashes? While many people seriously distrust running w/ fsync
off, I'm sure there's quite a few folks which do.

> This also adds a full_page_writes GUC to turn off page writes to WAL.
> Some people might not want full_page_writes, but still might want fsync.

Adding an option to not do page writes to WAL seems fine to me, but I
think WAL writes should be on by default, even in the fsync=off case.
If people want to turn it off, fine, for either case since we expect
they understand what it means to have it turned off, but I don't think
the two options should be coupled as is being proposed.

Thanks,

Stephen

Stephen Frost wrote:
-- Start of PGP signed section.
> * Bruce Momjian (pgman(at)candle(dot)pha(dot)pa(dot)us) wrote:
> > This patch disables page writes to WAL when fsync is off, because with
> > no fsync guarantee, the page write recovery isn't useful.
>
> This doesn't seem quite right to me. What happens with PITR? And

PITR doesn't need page writes at all because it has a full backup the
file system to start with. In fact with PITR the crashed file system
isn't used at all (restored from backup). In fact there is a TODO to
exclude full page writes from the PITR backup of WAL.

> Postgres crashes? While many people seriously distrust running w/ fsync
> off, I'm sure there's quite a few folks which do.
>
> > This also adds a full_page_writes GUC to turn off page writes to WAL.
> > Some people might not want full_page_writes, but still might want fsync.
>
> Adding an option to not do page writes to WAL seems fine to me, but I
> think WAL writes should be on by default, even in the fsync=off case.
> If people want to turn it off, fine, for either case since we expect
> they understand what it means to have it turned off, but I don't think
> the two options should be coupled as is being proposed.

That is a question I had in my mind. I added documentation that turning
off fsync also disables full_page_writes, but we could decouple them and
tell people to consider disableing full_pages_writes if they turn off
fsync, basically suggesting they make the second change.

Other opinions?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> This patch disables page writes to WAL when fsync is off, because
> with no fsync guarantee, the page write recovery isn't useful.
>
> This also adds a full_page_writes GUC to turn off page writes to WAL.
> Some people might not want full_page_writes, but still might want
> fsync.

Do you have some numbers to suggest that there is a performance benefit
to be had?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > This patch disables page writes to WAL when fsync is off, because
> > with no fsync guarantee, the page write recovery isn't useful.
> >
> > This also adds a full_page_writes GUC to turn off page writes to WAL.
> > Some people might not want full_page_writes, but still might want
> > fsync.
>
> Do you have some numbers to suggest that there is a performance benefit
> to be had?

Josh reported page writes to be a big hit (which we already knew), but I
don't have any with fsync off, though it seems like a no-brainer.
However, I am thinking decoupling them is best.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> That is a question I had in my mind. I added documentation that turning
> off fsync also disables full_page_writes, but we could decouple them and
> tell people to consider disableing full_pages_writes if they turn off
> fsync, basically suggesting they make the second change.

> Other opinions?

I'm for treating them as independent options.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > That is a question I had in my mind. I added documentation that turning
> > off fsync also disables full_page_writes, but we could decouple them and
> > tell people to consider disableing full_pages_writes if they turn off
> > fsync, basically suggesting they make the second change.
>
> > Other opinions?
>
> I'm for treating them as independent options.

Agreed. I will modify and apply.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> This also adds a full_page_writes GUC to turn off page writes to WAL.
> Some people might not want full_page_writes.

Fsync linkage removed, patch attached and applied.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

> Bruce Momjian wrote:
>> This also adds a full_page_writes GUC to turn off page writes to WAL.
>> Some people might not want full_page_writes.
>
> Fsync linkage removed, patch attached and applied.

...
+ When this option is on, the <productname>PostgreSQL</> server
+ writes full pages to WAL when they first modified after a checkpoint
+ so full recovery is possible.

I believe this should be "when they _are_ first modified after".

Perhaps you should also mention power failure, not only an operating system
crash as disaster scenario, even if the latter includes the former.

Best Regards,
Michael Paesold

Michael Paesold wrote:
> Bruce Momjian wrote:
>
> > Bruce Momjian wrote:
> >> This also adds a full_page_writes GUC to turn off page writes to WAL.
> >> Some people might not want full_page_writes.
> >
> > Fsync linkage removed, patch attached and applied.
>
> ...
> + When this option is on, the <productname>PostgreSQL</> server
> + writes full pages to WAL when they first modified after a checkpoint
> + so full recovery is possible.
>
> I believe this should be "when they _are_ first modified after".
>
> Perhaps you should also mention power failure, not only an operating system
> crash as disaster scenario, even if the latter includes the former.
>

Thanks. Done.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073