new database files not compressed

Hamish Moffatt
2020-08-30T10:02:21Z

I am trying to store Firebird database files compressed on btrfs.
Although I have mounted the file system with -o compress-force, new
files created by Firebird are not being compressed according to
compsize. If I copy them, or use btrfs filesystem defrag, they compress
well.

Other files seem to be compressed automatically OK. Why are the Firebird
files different?

$ uname -a
Linux packer-debian-10-amd64 5.7.0-3-amd64 #1 SMP Debian 5.7.17-1
(2020-08-23) x86_64 GNU/Linux

$ sudo mkfs.btrfs -m single -f /dev/sdb
btrfs-progs v4.20.1
See http://btrfs.wiki.kernel.org for more information.

Label:              (null)
UUID:               949f7f3f-681b-40fb-97b9-522756d5d619
Node size:          16384
Sector size:        4096
Filesystem size:    29.98GiB
Block group profiles:
Data:             single            8.00MiB
Metadata:         single            8.00MiB
System:           single            4.00MiB
SSD detected:       no
Incompat features:  extref, skinny-metadata
Number of devices:  1
Devices:
ID        SIZE  PATH
1    29.98GiB  /dev/sdb

$ sudo mount -o compress-force /dev/sdb /mnt/test
$ sudo mkdir /mnt/test/db
$ cd /mnt/test/db

Now I restore a backup to create a database:

$ zcat ~/*.zip | gbak -REP stdin test.fdb
$ sudo compsize test.fdb
Type       Perc     Disk Usage   Uncompressed Referenced
TOTAL      100%      182M         182M         175M
none       100%      182M         182M         175M
$ cat test.fdb > test2.fdb
$ sudo compsize test2.fdb
Type       Perc     Disk Usage   Uncompressed Referenced
TOTAL       10%       18M         175M         175M
zlib        10%       18M         175M         175M

The same thing occurs if I create a brand new database:

$ isql-fb
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database 'test3.fdb';
SQL> ^D$
$ sync
$ sudo compsize test3.fdb
Type       Perc     Disk Usage   Uncompressed Referenced
TOTAL      100%      784K         784K         784K
none       100%      784K         784K         784K
$ cp test3.fdb test4.fdb
$ sync
$ sudo compsize test4.fdb
Type       Perc     Disk Usage   Uncompressed Referenced
TOTAL        7%       60K         784K         784K
zlib         7%       60K         784K         784K

If I create a database with SQLite it is compressed:

$ cat test.sql
create table test ( id integer primary key asc autoincrement, timestamp
text default (datetime()), data text);
$ sqlite3 foo.db < test.sql
$ sudo compsize foo.db
Type       Perc     Disk Usage   Uncompressed Referenced
TOTAL       33%      4.0K          12K          12K
zlib        33%      4.0K          12K          12K

I ran isql-fb in strace to see if there is something special in the
open(2) flags;

$ strace -o trace isql-fb
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database 'new.fdb';
SQL> ^D$
$ grep new.fdb trace
readlink("/mnt/test/db/new.fdb", 0x7ffd9cf70810, 4096) = -1 ENOENT (No
such file or directory)
stat("/mnt/test/db/new.fdb", 0x7ffd9cf705b0) = -1 ENOENT (No such file
or directory)
stat("/mnt/test/db/new.fdb", 0x7ffd9cf71480) = -1 ENOENT (No such file
or directory)
stat("/mnt/test/db/new.fdb", 0x7ffd9cf70e40) = -1 ENOENT (No such file
or directory)
openat(AT_FDCWD, "/mnt/test/db/new.fdb", O_RDWR) = -1 ENOENT (No such
file or directory)
openat(AT_FDCWD, "/mnt/test/db/new.fdb", O_RDONLY) = -1 ENOENT (No such
file or directory)
readlink("/mnt/test/db/new.fdb", 0x7ffd9cf70800, 4096) = -1 ENOENT (No
such file or directory)
stat("/mnt/test/db/new.fdb", 0x7ffd9cf705a0) = -1 ENOENT (No such file
or directory)
stat("/mnt/test/db/new.fdb", 0x7ffd9cf71470) = -1 ENOENT (No such file
or directory)
stat("/mnt/test/db/new.fdb", 0x7ffd9cf70dc0) = -1 ENOENT (No such file
or directory)
stat("/mnt/test/db/new.fdb", 0x7ffd9cf70eb0) = -1 ENOENT (No such file
or directory)
openat(AT_FDCWD, "/mnt/test/db/new.fdb", O_RDWR|O_CREAT|O_EXCL, 0666) = 6
readlink("/mnt/test/db/new.fdb", 0x7ffd9cf6ff00, 4096) = -1 EINVAL
(Invalid argument)

thanks

Hamish