Re: Avoiding smgrimmedsync() during nbtree index builds - Mailing list pgsql-hackers

From Melanie Plageman
Subject Re: Avoiding smgrimmedsync() during nbtree index builds
Date
Msg-id CAAKRu_ZmkSg1_ZuvA_jK1uda9T1Jj4BrkBGTOFWq9ys9THtC3A@mail.gmail.com
Whole thread Raw
In response to Re: Avoiding smgrimmedsync() during nbtree index builds  (Melanie Plageman <melanieplageman@gmail.com>)
Responses Re: Avoiding smgrimmedsync() during nbtree index builds
Re: Avoiding smgrimmedsync() during nbtree index builds
List pgsql-hackers
On Mon, May 3, 2021 at 5:24 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> So, I've written a patch which avoids doing the immediate fsync for
> index builds either by using shared buffers or by queueing sync requests
> for the checkpointer. If a checkpoint starts during the index build and
> the backend is not using shared buffers for the index build, it will
> need to do the fsync.

I've attached a rebased version of the patch (old patch doesn't apply).

With the patch applied (compiled at O2), creating twenty empty tables in
a transaction with a text column and an index on another column (like in
the attached SQL [make a test_idx schema first]) results in a fairly
consistent 15-30% speedup on my laptop (timings still in tens of ms -
avg 50 ms to avg 65 ms so run variation affects the % a lot).
Reducing the number of fsync calls from 40 to 1 was what likely causes
this difference.

- Melanie

Attachment

pgsql-hackers by date:

Previous
From: Marcos Pegoraro
Date:
Subject: Re: update with no changes
Next
From: Thomas Munro
Date:
Subject: Re: [PATCH] Make ENOSPC not fatal in semaphore creation