Re: mount compress=zstd leaves files uncompressed, that used to compress well with before

[Dimitrios Apostolou <jimis@gmx.net>]

On Mon, 24 Mar 2025, Gerhard Wiesinger wrote:
> On 24.03.2025 20:11, Dimitrios Apostolou wrote:
>>  On Mon, 24 Mar 2025, Gerhard Wiesinger wrote:
>>
>>>  I guess you always had the issue but you didn't notice it.
>>
>>  I definitely didn't have it with PostgreSQL 15 on Linux 5.15 (Ubuntu
>>  22.04), I don't know for sure if I see it after upgraded to PostgreSQL 16,
>>  but I see the issue with PostgreSQL 17 on Linux 6.11 (HWE kernel in Ubuntu
>>  24.04).
>
> Yes, it occours when FALLOCATE (in any form) is used. So it looks like it was
> then introduced with PostgreSQL >= 16 and is NOT kernel dependend.
>
> Are there compile options to disable usage of FALLOCATE at all?
>

Probably -UHAVE_POSIX_FALLOCATE in CPPFLAGS. Haven't tried yet, so take it
with a grain of salt. :-)

I tested fallocate via its command line tool, and I see the issue in all
possible combinations:


Linux 6.11 - ext4

$ strace fallocate -v -l 1g blah-1g    # returned immediately

blah-1g: 1 GiB (1073741824 bytes) allocated.

fallocate(3, 0, 0, 1073741824)          = 0


Linux 6.11 - btrfs(compress-force=zstd:3)

# strace fallocate -v -l 1g blah-1g    # took ~10s

openat(AT_FDCWD, "blah-1g", O_RDWR|O_CREAT, 0666) = 3
fallocate(3, 0, 0, 1073741824)          = 0
fstat(1, {st_mode=S_IFCHR|0620, st_rdev=makedev(0x88, 0x9), ...}) = 0
write(1, "blah-1g: 1 GiB (1073741824 byte"..., 46blah-1g: 1 GiB
(1073741824 bytes) allocated.
) = 46

# compsize blah-1g
Processed 1 file, 4 regular extents (4 refs), 0 inline.
Type       Perc     Disk Usage   Uncompressed Referenced
TOTAL      100%      1.0G         1.0G         1.0G
prealloc   100%      1.0G         1.0G         1.0G


Linux 5.15 - btrfs(compress=zstd:3)

# strace fallocate -v -l 1g blah-1g    # returned immediately

openat(AT_FDCWD, "blah-1g", O_RDWR|O_CREAT, 0666) = 3
fallocate(3, 0, 0, 1073741824)          = 0
fsync(3)                                = 0
close(3)                                = 0

# compsize blah-1g
Processed 1 file, 4 regular extents (4 refs), 0 inline.
Type       Perc     Disk Usage   Uncompressed Referenced
TOTAL      100%      1.0G         1.0G         1.0G
prealloc   100%      1.0G         1.0G         1.0G


Both kernels (and libc, as I also tried fallocate --posix) seem to support
fallocate() on Btrfs, and btrfs always fails to compress.

Weirdly enough, I don't think I see the issue with PostgreSQL 16 on this
last case (Linux 5.15). The space growth of the database is looking like
it's well compressed. Can't tell for sure though, the compsize command I
issued more than a couple hours ago hasn't returned yet...

>>
>>  I see in your message that you are on Linux 6.5, but what is your
>>  PostgreSQL version?
>
> Didn't document exactly used version but I was using version PostgreSQL 16
> (see directory /var/lib/pgsql/16 on the mailing list posting).
>
> I was using the PGDG rpms from postgresql.org to get latest version. For
> support reasons with less upgrades I can recommend them anyway because
> distribution versions are often (far) behind.
>
>>
>>>  There is also another issue with BTRFS:
>>>  https://lore.kernel.org/linux-bcachefs/kgdutihyy6durmrtqi5dfk7lhl2duzm4wnf6mlyneiuphf3cck@fxulfyg2ugjf/T/
>>>
>>
>>  And then there is the issue of abysmal performance for buffered read(8KB)
>>  from compressed files:
>>
>>  https://www.spinics.net/lists/linux-btrfs/msg137200.html
>>
>>
> Ah, didn't know that. Did you also try ZFS and test also regarding
> performance issues?

No, haven't tested yet. I'm quite desperate though to find an
OK-performing compressed filesystem for postgreSQL, so I might look that
way.


Regards,
Dimitris