using file cloning in create database / initdb

Hi,

This thread started at https://www.postgresql.org/message-id/20220213021746.GM31460%40telsasoft.com
but is mostly independent, so I split the thread off

On 2022-02-12 20:17:46 -0600, Justin Pryzby wrote:
> On Sat, Feb 12, 2022 at 06:00:44PM -0800, Andres Freund wrote:
> > I bet using COW file copies would speed up our own regression tests noticeably
> > - on slower systems we spend a fair bit of time and space creating template0
> > and postgres, with the bulk of the data never changing.
> >
> > Template databases are also fairly commonly used by application developers to
> > avoid the cost of rerunning all the setup DDL & initial data loading for
> > different tests. Making that measurably cheaper would be a significant win.
>
> +1
>
> I ran into this last week and was still thinking about proposing it.
>
> Would this help CI

It could theoretically help linux - but currently I think the filesystem for
CI is ext4, which doesn't support FICLONE. I assume it'd help macos, but I
don't know the performance characteristics of copyfile(). I don't think any of
the other OSs have working reflink / file clone support.

You could prototype it for CI on macos by using the "template initdb" patch
and passing -c to cp.

On linux it might be worth using copy_file_range(), if supported, if not file
cloning. But that's kind of an even more separate topic...

> or any significant fraction of buildfarm ?

Not sure how many are on new enough linux / mac to benefit and use a suitable
filesystem. There are a few animals with slow-ish storage but running fairly
new linux. Don't think we can see the FS. Those would likely benefit the most.

> Or just tests run locally on supporting filesystems.

Probably depends on your storage subsystem. If not that fast, and running
tests concurrently, it'd likely help.

On my workstation, with lots of cores and very fast storage, using the initdb
caching patch modified to do cp --reflink=never / always yields the following
time for concurrent check-world (-j40 PROVE_FLAGS=-j4):

cp --reflink=never:

96.64user 61.74system 1:04.69elapsed 244%CPU (0avgtext+0avgdata 97544maxresident)k
0inputs+34124296outputs (2584major+7247038minor)pagefaults 0swaps
pcheck-world-success

cp --reflink=always:

91.79user 56.16system 1:04.21elapsed 230%CPU (0avgtext+0avgdata 97716maxresident)k
189328inputs+16361720outputs (2674major+7229696minor)pagefaults 0swaps
pcheck-world-success

Seems roughly stable across three runs.

Just comparing the time for cp -r of a fresh initdb'd cluster:
cp -a --reflink=never
real 0m0.043s
user 0m0.000s
sys 0m0.043s
cp -a --reflink=always
real 0m0.021s
user 0m0.004s
sys 0m0.018s

so that's a pretty nice win.

> Note that pg_upgrade already supports copy/link/clone. (Obviously, link
> wouldn't do anything desirable for CREATE DATABASE).

Yea. We'd likely have to move relevant code into src/port.

Greetings,

Andres Freund