Re: Use of fsync; was Re: Pg_upgrade speed for many tables

On Sat, Nov 24, 2012 at 09:42:08PM -0800, Jeff Janes wrote:
> On Fri, Nov 23, 2012 at 7:22 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > On Mon, Nov 19, 2012 at 12:11:26PM -0800, Jeff Janes wrote:
> >
> >>
> >> Yes, it is with synchronous_commit=off. (or if it wasn't originally,
> >> it is now, with the same result)
> >>
> >> Applying your fsync patch does solve the problem for me on ext4.
> >> Having the new cluster be on ext3 rather than ext4 also solves the
> >> problem, without the need for a patch; but it would be nice to more
> >> friendly to ext4, which is popular even though not recommended.
> >
> > Do you have numbers with synchronous-commit=off, fsync=off, and both, on
> > ext4?
>
> for 5,000 tables like "create table fooNNNNN (x serial)", upgrading
> from 9.3dev to 9.3dev:
>
> Timings are in seconds, done twice. I had to hack pg_upgrade so that
> the pg_ctl stop command did -w -t 3600, otherwise
> I'd get an "database did not shut down" error for the first two.
>
> both on 648.29 608.42
> synchronous_commit off 250.24 366.50
> fsync off 46.91 43.96
> both off 41.44 44.81
>
> Also, I did a manual sync as soon as "Removing support functions from
> new cluster OK" appears, with synchronous_commit off bug fsync on:
> 45.96 46.46

OK, these very convincing numbers. I am going to modify initdb to have
an --fsync-only option, and have pg_upgrade use that. This is 9.3
material.

> >>
> >> In any event, I think the documentation should caution that the
> >> upgrade should not be deemed to be a success until after a system-wide
> >> sync has been done. Even if we use the link rather than copy method,
> >> are we sure that that is safe if the directories recording those links
> >> have not been fsynced?
> >
> > OK, the above is something I have been thinking about, and obviously you
> > have too. If you change fsync from off to on in a cluster, and restart
> > it, there is no guarantee that the dirty pages you read from the kernel
> > are actually on disk, because Postgres doesn't know they are dirty.
> > They probably will be pushed to disk by the kernel in less than one
> > minute, but still, it doesn't seem reliable. Should this be documented
> > in the fsync section?
> >
> > Again, another reason not to use fsync=off, though your example of the
> > file copy is a good one. As you stated, this is a problem with the file
> > copy/link, independent of how Postgres handles the files. We can tell
> > people to use 'sync' as root on Unix, but what about Windows?
>
> I'm pretty sure someone mentioned the way to do that on Windows in
> this list in the last few months, but I can't seem to find it. I
> thought it was the initdb fsync thread.

Yep, the code is already in initdb to fsync a directory --- we just need
a way for pg_upgrade to access it.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +