Re: [PERFORM] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

On Tuesday 29 December 2009 04:04:06 Michael Clemmons wrote:
> Maybe not crash out but in this situation.
> N=0
> while(N>=0):
>     CREATE DATABASE new_db_N;
> Since the fsync is the part which takes the memory and time but is
>  happening in the background want the fsyncs pile up in the background
>  faster than can be run filling up the memory and stack.
> This is very likely a mistake on my part about how postgres/processes
The difference should not be visible outside the "CREATE DATABASE ..." at all.
Currently the process simplifiedly works like:

------------
for file in source directory:
    copy_file(source/file, target/file);
    fsync(target/file);
------------

I changed it to:

-------------
for file in source directory:
    copy_file(source/file, target/file);

    /*please dear kernel, write this out, but dont block*/
    posix_fadvise(target/file, FADV_DONTNEED);

for file in source directory:
    fsync(target/file);
-------------

If at any point in time there is not enough cache available to cache anything
copy_file() will just have to wait for the kernel to write out the data.
fsync() does not use memory itself.

Andres