Re: Use of fsync; was Re: Pg_upgrade speed for many tables - Mailing list pgsql-hackers

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@momjian.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@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +