pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
Log Message:
-----------
Speed up CREATE DATABASE by deferring the fsyncs until after copying
all the data and using posix_fadvise to nudge the OS into flushing it
earlier. This also hopefully makes CREATE DATABASE avoid spamming the
cache.
Tests show a big speedup on Linux at least on some filesystems.
Idea and patch from Andres Freund.
Modified Files:
--------------
pgsql/src/backend/storage/file:
fd.c (r1.153 -> r1.154)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/storage/file/fd.c?r1=1.153&r2=1.154)
pgsql/src/include/storage:
fd.h (r1.66 -> r1.67)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/storage/fd.h?r1=1.66&r2=1.67)
pgsql/src/port:
copydir.c (r1.25 -> r1.26)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/port/copydir.c?r1=1.25&r2=1.26)
stark@postgresql.org (Greg Stark) writes:
Log Message:
-----------
Speed up CREATE DATABASE by deferring the fsyncs until after copying
all the data and using posix_fadvise to nudge the OS into flushing it
earlier. This also hopefully makes CREATE DATABASE avoid spamming the
cache.
The buildfarm indicates that this patch has got some serious issues.
regards, tom lane
I wrote:
The buildfarm indicates that this patch has got some serious issues.
Actually, looking closer, some of the Windows machines started failing
after the *earlier* patch to add directory fsyncs.
regards, tom lane
On Monday 15 February 2010 08:13:32 Tom Lane wrote:
I wrote:
The buildfarm indicates that this patch has got some serious issues.
Actually, looking closer, some of the Windows machines started failing
after the *earlier* patch to add directory fsyncs.
And not only the windows machines. Seems sensible to add a configure check
whether directory-fsyncing works.
But at least I am not capable of writing good m4/configure.in/whatever without
strong supervision...
Will try if nobody else with more knowledge does and if somebody will look
over it afterwards.
Andres
On Mon, Feb 15, 2010 at 9:36 AM, Andres Freund <andres@anarazel.de> wrote:
On Monday 15 February 2010 08:13:32 Tom Lane wrote:
Actually, looking closer, some of the Windows machines started failing
after the *earlier* patch to add directory fsyncs.And not only the windows machines. Seems sensible to add a configure check
whether directory-fsyncing works.
It looks like a thing that can be filesystem-dependent. Maybe a kind
of runtime check?
Greetings
Marcin Mańk
Hi Marcin,
Sounds rather unlikely to me. Its likely handled at an upper layer (vfs in linux' case) and only overloaded when an optimized implementation is available.
Which os do you see implementing that only on a part of the filesystems?
A runtime check would be creating, fsyncing and deleting a directory for every directory youre fsyncing because they could be on a different fs...
Andres
--
Sent from a mobile phone - please excuse brevity and formatting.
----- Ursprüngliche Mitteilung -----
Show quoted text
On Mon, Feb 15, 2010 at 9:36 AM, Andres Freund <andres@anarazel.de> wrote:
On Monday 15 February 2010 08:13:32 Tom Lane wrote:
Actually, looking closer, some of the Windows machines started failing
after the *earlier* patch to add directory fsyncs.And not only the windows machines. Seems sensible to add a configure check
whether directory-fsyncing works.It looks like a thing that can be filesystem-dependent. Maybe a kind
of runtime check?Greetings
Marcin Mańk--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Feb 15, 2010 at 10:02 AM, Andres Freund <andres@anarazel.de> wrote:
Hi Marcin,
Sounds rather unlikely to me. Its likely handled at an upper layer (vfs in linux' case) and only overloaded when an optimized implementation is available.
Which os do you see implementing that only on a part of the filesystems?A runtime check would be creating, fsyncing and deleting a directory for every directory youre fsyncing because they could be on a different fs...
We could just not check the result code of the fsync. Or print a
warning the first time and stop trying subsequently.
When do we cut the alpha? If I look at it at about 10-11pm EST is that too late?
--
greg
On Mon, Feb 15, 2010 at 11:02 AM, Andres Freund <andres@anarazel.de> wrote:
Hi Marcin,
Sounds rather unlikely to me. Its likely handled at an upper layer (vfs in linux' case) and only overloaded when an optimized implementation is available.
Which os do you see implementing that only on a part of the filesystems?
I have a Windows XP dev machine, which runs virtualbox, which runs
ubuntu, which mounts a windows directory through vboxfs
fsync does error out on directories inside that mount.
btw: 8.4.2 initdb won`t work there too, So this is not a regression.
The error is:
DEBUG: creating and filling new WAL file
LOG: could not link file "pg_xlog/xlogtemp.2367" to
"pg_xlog/000000010000000000000000" (initialization of log file 0,
segment 0): Operation not permitted
FATAL: could not open file "pg_xlog/000000010000000000000000" (log
file 0, segment 0): No such file or directory
But I would not be that sure that eg. NFS or something like that won`t complain.
Ignoring the return code seems the right choice.
Greetings
Marcin Mańk
On Mon, Feb 15, 2010 at 11:34 AM, marcin mank <marcin.mank@gmail.com> wrote:
LOG: could not link file "pg_xlog/xlogtemp.2367" to
"pg_xlog/000000010000000000000000" (initialization of log file 0,
This is not related -- it seems your filesystem doesn't support hard
links. I thought we used "junctions" on versions of Windows that
support them which I would have expected would include XP but my
knowledge of Windows is thin and obsolete.
--
greg
On Monday 15 February 2010 12:34:44 marcin mank wrote:
On Mon, Feb 15, 2010 at 11:02 AM, Andres Freund <andres@anarazel.de> wrote:
Hi Marcin,
Sounds rather unlikely to me. Its likely handled at an upper layer (vfs
in linux' case) and only overloaded when an optimized implementation is
available. Which os do you see implementing that only on a part of the
filesystems?I have a Windows XP dev machine, which runs virtualbox, which runs
ubuntu, which mounts a windows directory through vboxfs
btw: 8.4.2 initdb won`t work there too, So this is not a regression.
The error is:
DEBUG: creating and filling new WAL file
LOG: could not link file "pg_xlog/xlogtemp.2367" to
"pg_xlog/000000010000000000000000" (initialization of log file 0,
segment 0): Operation not permitted
FATAL: could not open file "pg_xlog/000000010000000000000000" (log
file 0, segment 0): No such file or directory
That does seem to be a different issue. Currently there are no fsyncs on
directories at all, so likely your setup is hosed anyway ;-)
But I would not be that sure that eg. NFS or something like that won`t
complain.
It does not.
Ignoring the return code seems the right choice.
And the error hiding one as well. With delayed allocation you theoretically
could error out on fsync with -ENOSPC ...
Andres
On Monday 15 February 2010 12:45:39 Greg Stark wrote:
On Mon, Feb 15, 2010 at 11:34 AM, marcin mank <marcin.mank@gmail.com> wrote:
LOG: could not link file "pg_xlog/xlogtemp.2367" to
"pg_xlog/000000010000000000000000" (initialization of log file 0,This is not related -- it seems your filesystem doesn't support hard
links. I thought we used "junctions" on versions of Windows that
support them which I would have expected would include XP but my
knowledge of Windows is thin and obsolete.
If I understood him correctly marcin seems to mount a windows share on linux
via some vbox-proprietary pseudo filesystem. That wont get detected and thus
no junctions will be used... (I have doubts you even can create them via
vboxfs (or even smb)).
I would consider that a unsupported setup. Agreed?
Andres
On Mon, Feb 15, 2010 at 11:50 AM, Andres Freund <andres@anarazel.de> wrote:
If I understood him correctly marcin seems to mount a windows share on linux
via some vbox-proprietary pseudo filesystem. That wont get detected and thus
no junctions will be used... (I have doubts you even can create them via
vboxfs (or even smb)).
I would consider that a unsupported setup. Agreed?
I'm not sure which versions of Windows we support in general. But on
further thought I thought we only used hard links for xlog files on
systems where we knew they worked and just did a rename() on systems
without them. So I'm puzzled why we're trying to hard link on this
system. Perhaps we need to make this a run-time check instead of just
making it depend on the system.
--
greg
On Monday 15 February 2010 12:55:36 Greg Stark wrote:
On Mon, Feb 15, 2010 at 11:50 AM, Andres Freund <andres@anarazel.de> wrote:
If I understood him correctly marcin seems to mount a windows share on
linux via some vbox-proprietary pseudo filesystem. That wont get
detected and thus no junctions will be used... (I have doubts you even
can create them via vboxfs (or even smb)).
I would consider that a unsupported setup. Agreed?I'm not sure which versions of Windows we support in general. But on
further thought I thought we only used hard links for xlog files on
systems where we knew they worked and just did a rename() on systems
without them. So I'm puzzled why we're trying to hard link on this
system. Perhaps we need to make this a run-time check instead of just
making it depend on the system.
Well, I guess linux is normally a system where hardlinking is considered safe.
And I dont really see a problem with that - for example we require ntfs on
windows as well...
In the end its only some strange filesystem whats causing the issue here...
Andres
Yes, the issue with initdb failing is unrelated (and I have no problem
about the fs being unsupported). But fsync still DOES fail on
directories from the mount.
But I would not be that sure that eg. NFS or something like that won`t
complain.It does not.
What if someone mounts a NFS share from a system that does not support
directory fsync (per buildfarm: unixware, AIX) on Linux? I agree that
this is asking for trouble, but...
Greetings
Marcin Mańk
On Monday 15 February 2010 14:50:03 marcin mank wrote:
Yes, the issue with initdb failing is unrelated (and I have no problem
about the fs being unsupported). But fsync still DOES fail on
directories from the mount.But I would not be that sure that eg. NFS or something like that won`t
complain.It does not.
What if someone mounts a NFS share from a system that does not support
directory fsync (per buildfarm: unixware, AIX) on Linux? I agree that
this is asking for trouble, but...
Then nothing. The fsync via nfs or such is a local operation. There is nothing
like a "fsync" command transported - i.e. the fsync controls the local cache
not the remote one...
Andres
2010/2/15 Greg Stark <stark@mit.edu>:
On Mon, Feb 15, 2010 at 11:34 AM, marcin mank <marcin.mank@gmail.com> wrote:
LOG: could not link file "pg_xlog/xlogtemp.2367" to
"pg_xlog/000000010000000000000000" (initialization of log file 0,This is not related -- it seems your filesystem doesn't support hard
links. I thought we used "junctions" on versions of Windows that
support them which I would have expected would include XP but my
knowledge of Windows is thin and obsolete.
Junctions are for symbolic links, and only valid for directories. NTFS
has "real" hardlinks though CreateLink(). No idea if that works on
remote filesystems though.
But AFAIK, we don't use that on Windows. But the rest of the thread
has indicated why this shows up anyway :)
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Monday 15 February 2010 01:50:57 Greg Stark wrote:
Log Message:
-----------
Speed up CREATE DATABASE by deferring the fsyncs until after copying
all the data and using posix_fadvise to nudge the OS into flushing it
earlier. This also hopefully makes CREATE DATABASE avoid spamming the
cache.Tests show a big speedup on Linux at least on some filesystems.
Idea and patch from Andres Freund.
I just found a relatively big problem with one of your modifications on the
patch - you removed the
FreeDir(xldir);
xldir = AllocateDir(fromdir);
pair - unfortunately its crucial because otherwise the DIR does not get
rewound - that resulted in *no* files getting fsync()ed (otherwise the loop
above wouldn't have finished yet...).
I think that was also causing the problems I pointed out in " Directory fsync
and other fun"...
You removed it because you didn't want to open the directory twice? I think
doing that is simpler than using rewinddir - I have no idea how usable that
one is on windows for example
Could you add it back?
Andres
On Sun, Feb 21, 2010 at 11:43 PM, Andres Freund <andres@anarazel.de> wrote:
Could you add it back?
Oops, sorry. Sigh. Done.
--
greg
Andres Freund <andres@anarazel.de> writes:
I just found a relatively big problem with one of your modifications on the
patch - you removed the
FreeDir(xldir);
xldir = AllocateDir(fromdir);
pair - unfortunately its crucial because otherwise the DIR does not get
rewound - that resulted in *no* files getting fsync()ed (otherwise the loop
above wouldn't have finished yet...).
I think that was also causing the problems I pointed out in " Directory fsync
and other fun"...
Actually, that code had *multiple* problems including stat'ing the wrong
file entirely, not to mention that this last commit failed to even
compile. I also think it should scan the todir not the fromdir, just on
general principles to avoid any possibility of race conditions.
regards, tom lane
On Mon, Feb 22, 2010 at 2:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Actually, that code had *multiple* problems including stat'ing the wrong
file entirely, not to mention that this last commit failed to even
compile. I also think it should scan the todir not the fromdir, just on
general principles to avoid any possibility of race conditions.
Argh. I'll be less careless in the future, I promise.
I had concluded that scanning the original directory was odd but
better because it served to double-check that all the original files
actually made it and also because if there were any unrelated files
present there was no need to fsync them. But I agree it's odd and not
very general for copydir if we decide to use it elsewhere other than
create database.
--
greg
Greg Stark <gsstark@mit.edu> writes:
On Mon, Feb 22, 2010 at 2:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I also think it should scan the todir not the fromdir, just on
general principles to avoid any possibility of race conditions.
I had concluded that scanning the original directory was odd but
better because it served to double-check that all the original files
actually made it and also because if there were any unrelated files
present there was no need to fsync them.
Well, just for the record: if that was actually intentional then both of
you erred seriously by not including a comment that explained that the
coding was intentional (and giving the reasoning). Any reader of the
code would have assumed that it was a copy-and-paste error, as I did.
But I agree it's odd and not
very general for copydir if we decide to use it elsewhere other than
create database.
Yeah, to me it seems more likely to cause problems down the road than
to catch anything. If the system is missing directory entries during
ReadDir then we have problems far beyond what copydir can deal with.
The point of the fsync loop is just to force the copy results down to
the platter, not to cross-check that the source directory isn't
changing. (And, what's more, I don't believe that the source directory
can't change during CREATE DATABASE. Consider delayed cleanup of
deleted relations during checkpoints.)
regards, tom lane
BTW, I notice that after allegedly fixing things, we are now seeing
fsync failures during CREATE DATABASE in the installcheck phase of
buildfarm runs on (apparently) all the Windows critters, plus a
couple of other platforms too. This mystifies me. I could believe
that there was something still wrong with copydir.c, but then how
come these machines are getting through the earlier "make check"
phase?
I made a couple of code tweaks just now to try to get more information
--- the reported EBADF error numbers seem fairly implausible in
themselves, so I wondered if that's *really* what fsync is reporting.
I don't have a lot of hope for that though.Any theories about what is happening?
regards, tom lane
I wrote:
Any theories about what is happening?
Hah --- the AIX failures, at least, are explained at
http://publib.boulder.ibm.com/infocenter/aix/v6r1/index.jsp?topic=/com.ibm.aix.basetechref/doc/basetrf1/fsync.htm
which says
Error Codes
The fsync or fsync_range subroutine is unsuccessful if one or more of the following are true:
EIO An I/O error occurred while reading from or writing to the file system.
EBADF The FileDescriptor parameter is not a valid file descriptor open for writing.
EINVAL The file is not a regular file.
EINTR The subroutine was interrupted by a signal.
So the problem is that fsync_fname is trying to fsync a file it's opened
O_RDONLY. I don't know whether Windows is similarly picky, but we'll
soon find out.
Now, this doesn't mean that all is fine and dandy. I believe that a
majority of Unixen will reject attempts to open directories for writing,
so this solution puts us even further away from being able to fsync the
directories. I would bet however that the platforms that reject this
are ones that don't need fsync on directories. Maybe we just have to
have two different code paths depending on platform :-(
regards, tom lane
Hi Tom,
On Tuesday 23 February 2010 06:37:26 Tom Lane wrote:
I wrote:
Any theories about what is happening?
Now, this doesn't mean that all is fine and dandy. I believe that a
majority of Unixen will reject attempts to open directories for writing,
so this solution puts us even further away from being able to fsync the
directories. I would bet however that the platforms that reject this
are ones that don't need fsync on directories. Maybe we just have to
have two different code paths depending on platform :-(
Cool.
You can't open a directory for writing under linux as well though - so that
wont be the decisive argument. Do you have a better idea than a configure
test?
Andres
On Tue, Feb 23, 2010 at 5:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
So the problem is that fsync_fname is trying to fsync a file it's opened
O_RDONLY. I don't know whether Windows is similarly picky, but we'll
soon find out.
Argh, now I feel silly. I had actually found that in my searches after
the first batch of problems. But somehow i didn't connect that to the
current problems. Sorry.
There are other similarly confused OSes that don't allow fsync on
read-only file descriptors:
http://svn.haxx.se/dev/archive-2006-02/0488.shtml
(I wonder if some of them are doing fsync wrong and only syncing
changes written to this file descriptor and not any file descriptor
for this file?)
The plan I was thinking of was to pass a flag indicating if it's a
directory to fsync_fname() and open it RD_ONLY if it's a directory and
RDRW if it's a file. Then ignore any error returns (or at least EBADF
and EINVAL) iff the flag indicating it was a directory was true.
I don't like using configure tests for this because I fear someone
could compile Postgres on a system with one set of behaviour and then
switch to a different kernel version with a different set of
behaviour. In the worst case it could be filesystem dependent whether
you can open directories or whether they accept fsyncs.
--
greg
Greg Stark <stark@mit.edu> writes:
I don't like using configure tests for this because I fear someone
could compile Postgres on a system with one set of behaviour and then
switch to a different kernel version with a different set of
behaviour. In the worst case it could be filesystem dependent whether
you can open directories or whether they accept fsyncs.
Yeah, and there's also the problem of cross-compilation. I don't want
a configure test either if we can avoid it.
The plan I was thinking of was to pass a flag indicating if it's a
directory to fsync_fname() and open it RD_ONLY if it's a directory and
RDRW if it's a file. Then ignore any error returns (or at least EBADF
and EINVAL) iff the flag indicating it was a directory was true.
Works for me, but let's first try just ignoring EBADF, which is the only
value we saw in the recent buildfarm failures. If we got past the fd<0
test then EBADF could only indicate a rdonly failure, whereas it's less
clear what EINVAL might cover.
regards, tom lane
I wrote:
BTW, I notice that after allegedly fixing things, we are now seeing
fsync failures during CREATE DATABASE in the installcheck phase of
buildfarm runs on (apparently) all the Windows critters, plus a
couple of other platforms too. This mystifies me. I could believe
that there was something still wrong with copydir.c, but then how
come these machines are getting through the earlier "make check"
phase?
BTW, although things seem to be going green with the RDONLY->RDWR
change, I'm still mystified why these machines didn't fail at
"make check". Is it possible that "make check" runs the postmaster
with fsync disabled? I don't see that in the code anywhere...
regards, tom lane
On Tue, Feb 23, 2010 at 3:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
The plan I was thinking of was to pass a flag indicating if it's a
directory to fsync_fname() and open it RD_ONLY if it's a directory and
RDRW if it's a file. Then ignore any error returns (or at least EBADF
and EINVAL) iff the flag indicating it was a directory was true.Works for me, but let's first try just ignoring EBADF, which is the only
value we saw in the recent buildfarm failures. If we got past the fd<0
test then EBADF could only indicate a rdonly failure, whereas it's less
clear what EINVAL might cover.
So I'm thinking of something like this.
Ignore ESDIR when opening a directory (and return immediately)
and ignore EBADF when trying to fsync a directory.
--
greg
Attachments:
create-database-fsync-dirs-context.difftext/x-patch; charset=US-ASCII; name=create-database-fsync-dirs-context.diffDownload
*** a/src/port/copydir.c
--- b/src/port/copydir.c
***************
*** 37,43 ****
static void copy_file(char *fromfile, char *tofile);
! static void fsync_fname(char *fname);
/*
--- 37,43 ----
static void copy_file(char *fromfile, char *tofile);
! static void fsync_fname(char *fname, bool isdir);
/*
***************
*** 121,132 **** copydir(char *fromdir, char *todir, bool recurse)
errmsg("could not stat file \"%s\": %m", tofile)));
if (S_ISREG(fst.st_mode))
! fsync_fname(tofile);
}
FreeDir(xldir);
- #ifdef NOTYET
-
/*
* It's important to fsync the destination directory itself as individual
* file fsyncs don't guarantee that the directory entry for the file is
--- 121,130 ----
errmsg("could not stat file \"%s\": %m", tofile)));
if (S_ISREG(fst.st_mode))
! fsync_fname(tofile, false);
}
FreeDir(xldir);
/*
* It's important to fsync the destination directory itself as individual
* file fsyncs don't guarantee that the directory entry for the file is
***************
*** 135,142 **** copydir(char *fromdir, char *todir, bool recurse)
*
* However we can't do this just yet, it has portability issues.
*/
! fsync_fname(todir);
! #endif
}
/*
--- 133,139 ----
*
* However we can't do this just yet, it has portability issues.
*/
! fsync_fname(todir, true);
}
/*
***************
*** 216,235 **** copy_file(char *fromfile, char *tofile)
/*
* fsync a file
*/
static void
! fsync_fname(char *fname)
{
! int fd = BasicOpenFile(fname,
! O_RDWR | PG_BINARY,
! S_IRUSR | S_IWUSR);
!
! if (fd < 0)
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not open file \"%s\": %m", fname)));
! if (pg_fsync(fd) != 0)
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not fsync file \"%s\": %m", fname)));
--- 213,256 ----
/*
* fsync a file
+ *
+ * Try to fsync directories but ignore errors that indicate the OS
+ * just doesn't allow/require fsyncing directories.
*/
static void
! fsync_fname(char *fname, bool isdir)
{
! int fd;
! int returncode;;
!
! if (!isdir)
! fd = BasicOpenFile(fname,
! O_RDWR | PG_BINARY,
! S_IRUSR | S_IWUSR);
! else
! fd = BasicOpenFile(fname,
! O_RDONLY | PG_BINARY,
! S_IRUSR | S_IWUSR);
!
! /* Some OSs don't allow us to open directories at all */
! if (fd < 0 && isdir && errno == EISDIR)
! return;
!
! else if (fd < 0)
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not open file \"%s\": %m", fname)));
! returncode = pg_fsync(fd);
!
! /* Some OSs don't allow us to fsync directories */
! if (returncode != 0 && isdir && errno == EBADF)
! {
! close(fd);
! return;
! }
!
! if (returncode != 0)
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not fsync file \"%s\": %m", fname)));
Greg Stark <stark@mit.edu> writes:
So I'm thinking of something like this.
Ignore ESDIR when opening a directory (and return immediately)
and ignore EBADF when trying to fsync a directory.
Seems reasonable, but get rid of the comment "However we can't do this
just yet, it has portability issues"; and you've got a double semicolon
in one place. It might also be worth commenting the BasicOpenFile calls
along the lines of "Many OSs don't let us open directories RDWR, while
some reject fsync on files opened RDONLY, so we need two cases."
regards, tom lane