Re: Migration study, step 1: bulk write performance

On Tue, Mar 21, 2006 at 01:29:54PM +0100, Steinar H. Gunderson wrote:
> On Tue, Mar 21, 2006 at 06:18:39AM -0600, Jim C. Nasby wrote:
> > Basically, you need to know for certain that if PostgreSQL creates a
> > file and then fsync's it that that file is safely on disk, and that the
> > filesystem knows how to find it (ie: the metadata is also on disk in
> > some fashion).
>
> It seems to do, quoting Tom from
> http://archives.postgresql.org/pgsql-performance/2005-11/msg00184.php:

404 :(

>
> == snip ==
> its metadata is not changing. As long as the filesystem follows
> the minimal rule of syncing metadata about a file when it fsyncs the
> file, all the live WAL files should survive crashes OK.

And therin lies the rub: file metadata *must* commit to disk as part of
an fsync, and it's needed for both WAL and heap data. It's needed for
heap data because as soon as a checkpoint completes, PostgreSQL is free
to erase any WAL info about previous DDL changes.

On FreeBSD, if you're using softupdates, the filesystem will properly
order writes to the drive so that metadata must be written before file
data; this ensures that an fsync on the file will first write any
metadata before writing the data itself.

With fsync turned off, any metadata-changing commands will wait for the
metadata to commit to disk before returning (unless you run async...)

I'm not really sure how this all plays out on a journalling filesystem.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461