public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pierre Barre <[email protected]>
To: Riku Iki <[email protected]>
To: Thomas Munro <[email protected]>
Cc: [email protected]
Subject: Re: Preallocation changes in Postgresql 16
Date: Fri, 27 Dec 2024 00:16:53 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAMazQQer1aLg3+KE_nGWttXC177Lh6Aq3OBD4eUGHd5GXUg9LQ@mail.gmail.com>
References: <CAMazQQfp+3f8tD_Q23rCR=O+Jj4jouSRVigbD8OmrTOfHV+8gA@mail.gmail.com>
<CA+hUKGLu-oDokjt=Hyb8cLPTm19s5HZSq0PY-jXBP6-u2cqMEg@mail.gmail.com>
<CAMazQQewmq3Zt+mF1JOTTpJyNyhKQT7C_G7z+MjGxs-xRq0Mxw@mail.gmail.com>
<CAMazQQer1aLg3+KE_nGWttXC177Lh6Aq3OBD4eUGHd5GXUg9LQ@mail.gmail.com>
Hello,
It seems that I am running into this issue as well.
Is it likely that this would ever be a config option?
Best,
Pierre Barre
On Fri, May 3, 2024, at 05:11, Riku Iki wrote:
> I did the testing and confirmed that this was the issue.
>
> I run following query:
>
> create table t as select '1234567890' from generate_series(1, 1000000000);
>
> I commented if (numblocks > 8) codeblock, and see the following results from "compsize /dbdir/" command.
>
>
> Before my changes:
>
> Processed 1381 files, 90007 regular extents (90010 refs), 15 inline.
> Type Perc Disk Usage Uncompressed Referenced
> TOTAL 97% 41G 42G 42G
> none 100% 41G 41G 41G
> zstd 14% 157M 1.0G 1.0G
> prealloc 100% 16M 16M 16M
>
>
>
> After the changes:
>
> Processed 1381 files, 347328 regular extents (347331 refs), 15 inline.
> Type Perc Disk Usage Uncompressed Referenced
> TOTAL 3% 1.4G 42G 42G
> none 100% 80K 80K 80K
> zstd 3% 1.4G 42G 42G
>
> It is clearly visible that files created with fallocate are not compressed, and disk usage is much larger.
> I am wondering if there is a way to have some feature request to have this parameter user configurable..
>
> On Fri, Apr 26, 2024 at 4:15 PM Riku Iki <[email protected]> wrote:
>> Thank you, I have such a system. I think my task would be to compile PG from sources(need to learn this), and see how it works with and without that code block.
>>
>> On Thu, Apr 25, 2024 at 2:25 PM Thomas Munro <[email protected]> wrote:
>>> On Fri, Apr 26, 2024 at 4:37 AM Riku Iki <[email protected]> wrote:
>>> > I am wondering if there were preallocation related changes in PG16, and if it is possible to disable preallocation in PostgreSQL 16?
>>>
>>> I have no opinion on the btrfs details, but I was wondering if someone
>>> might show up with a system that doesn't like that change. Here is a
>>> magic 8, tuned on "some filesystems":
>>>
>>> /*
>>> * If available and useful, use posix_fallocate() (via
>>> * FileFallocate()) to extend the relation. That's often more
>>> * efficient than using write(), as it commonly won't cause the kernel
>>> * to allocate page cache space for the extended pages.
>>> *
>>> * However, we don't use FileFallocate() for small extensions, as it
>>> * defeats delayed allocation on some filesystems. Not clear where
>>> * that decision should be made though? For now just use a cutoff of
>>> * 8, anything between 4 and 8 worked OK in some local testing.
>>> */
>>> if (numblocks > 8)
>>>
>>> I wonder if it wants to be a GUC.
view thread (4+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Preallocation changes in Postgresql 16
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox