From: | Riku Iki <riku(dot)iki(dot)x(at)gmail(dot)com> |
---|---|
To: | Thomas Munro <thomas(dot)munro(at)gmail(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Preallocation changes in Postgresql 16 |
Date: | 2024-05-03 03:11:19 |
Message-ID: | CAMazQQer1aLg3+KE_nGWttXC177Lh6Aq3OBD4eUGHd5GXUg9LQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <riku(dot)iki(dot)x(at)gmail(dot)com> 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 <thomas(dot)munro(at)gmail(dot)com>
> wrote:
>
>> On Fri, Apr 26, 2024 at 4:37 AM Riku Iki <riku(dot)iki(dot)x(at)gmail(dot)com> 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.
>>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Johnson | 2024-05-03 03:12:17 | Re: Ora2pg Delta Migration: Oracle to PostgreSQL |
Previous Message | Amit Sharma | 2024-05-03 00:28:04 | Ora2pg Delta Migration: Oracle to PostgreSQL |