public inbox for [email protected]help / color / mirror / Atom feed
Re: Preallocation changes in Postgresql 16 4+ messages / 3 participants [nested] [flat]
* Re: Preallocation changes in Postgresql 16 @ 2024-04-25 21:25 Thomas Munro <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Thomas Munro @ 2024-04-25 21:25 UTC (permalink / raw) To: Riku Iki <[email protected]>; +Cc: [email protected] 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Preallocation changes in Postgresql 16 @ 2024-04-26 23:15 Riku Iki <[email protected]> parent: Thomas Munro <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Riku Iki @ 2024-04-26 23:15 UTC (permalink / raw) To: Thomas Munro <[email protected]>; +Cc: [email protected] 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. > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Preallocation changes in Postgresql 16 @ 2024-05-03 03:11 Riku Iki <[email protected]> parent: Riku Iki <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Riku Iki @ 2024-05-03 03:11 UTC (permalink / raw) To: Thomas Munro <[email protected]>; +Cc: [email protected] 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. >> > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Preallocation changes in Postgresql 16 @ 2024-12-26 23:16 Pierre Barre <[email protected]> parent: Riku Iki <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Pierre Barre @ 2024-12-26 23:16 UTC (permalink / raw) To: Riku Iki <[email protected]>; Thomas Munro <[email protected]>; +Cc: [email protected] 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-12-26 23:16 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-04-25 21:25 Re: Preallocation changes in Postgresql 16 Thomas Munro <[email protected]> 2024-04-26 23:15 ` Riku Iki <[email protected]> 2024-05-03 03:11 ` Riku Iki <[email protected]> 2024-12-26 23:16 ` Pierre Barre <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox