MIME-Version: 1.0
References: <AS1PR05MB9105F5A24271EEC357DA5D919F9B2@AS1PR05MB9105.eurprd05.prod.outlook.com>
 <202409111239.n2dybum5hcwj@alvherre.pgsql>
In-Reply-To: <202409111239.n2dybum5hcwj@alvherre.pgsql>
From: Thomas Munro <thomas.munro@gmail.com>
Date: Thu, 12 Sep 2024 09:36:50 +1200
Message-ID: <CA+hUKGKM16mbwCvyRCJZHM-LdC+ufK0b09Wnmh9ivGQDi6bv6g@mail.gmail.com>
Subject: Re: Error:could not extend file " with FileFallocate(): No space left
 on device
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: =?UTF-8?B?UGVjc8O2ayBKw6Fu?= <jan.pecsok@profinit.eu>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>, Andres Freund <andres@anarazel.de>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BhUKGKM16mbwCvyRCJZHM-LdC%2BufK0b09Wnmh9ivGQDi6bv6g%40mail.gmail.com>
Precedence: bulk

On Thu, Sep 12, 2024 at 12:39=E2=80=AFAM Alvaro Herrera <alvherre@alvh.no-i=
p.org> wrote:
>> On 2024-Sep-11, Pecs=C3=B6k J=C3=A1n wrote:
> > In our case:
> > Kernel: Linux version 4.18.0-513.18.1.el8_9.ppc64le (mockbuild@ppc-hv-1=
3.build.eng.rdu2.redhat.com) (gcc version 8.5.0 20210514 (Red Hat 8.5.0-20)=
 (GCC)) #1 SMP Thu Feb 1 02:52:53 EST 2024
> > File syst=C3=A9m type:xfs
>
> Can you please share the output of xfs_info for the filesystem(s) used?
>
> Apparently, it's possible for allocation groups to be suboptimally laid
> out in a way that leads to ENOSPC with space still available.

Hmm, I have no clues about that, though I do remember reports of
spurious ENOSPC errors from xfs many years ago on some other database
I was around maybe in the era of that kernel or a bit older.

Actually I was already wondering if we need to add a tunable to
control that the heuristic that redirects to posix_fallocate():

https://www.postgresql.org/message-id/flat/CAMazQQfp%2B3f8tD_Q23rCR%3DO%2BJ=
j4jouSRVigbD8OmrTOfHV%2B8gA%40mail.gmail.com

There's no confirmation that writing zeros would be a useful
workaround here, though.  Two things changed in 16: the fallocate()
path was invented, but also we started extending by more than one
block at a time, which might take the pwritev() path or the
fallocate() path, for bulk insertion via COPY.  That btrfs user would
prefer pwritev() always IIRC, but if some version of xfs is alergic to
this pattern I don't know if it's the size or the system call that's
triggering it...

Is COPY used here?

And just for curiosity (I don't see any particular connection or what
to do about it either way in the short term), are we talking about
really big tables with lots of 1GB files named N.1, N.2, N.3, ...
files, or millions of smaller tables?  I kinda wonder if xfs (and any
file system really) would really prefer us to use large files instead
(patches exist for this), and when many-terabyte clusters start
working with huge numbers of segments, we reach fun new kinds of
internal resource exhaustion, or something like that....

. o O { I particularly dislike our habit of synthesising fake ENOSPC
errors in a few code paths... grumble }