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

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

>>
>> 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.

Cheers,

Jeff