Re: new database files not compressed

A L
2020-08-31T03:15:15Z

---- From: Eric Wong <e@80x24.org> -- Sent: 2020-08-31 - 04:20 ----

> Hamish Moffatt <hamish-btrfs@moffatt.email> wrote:
>> 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?
>
> Maybe Firebird creates DB with the No_COW attribute?
> "lsattr -l /path/to/file" to check.

Could also be that it is using Direct I/O. DirectIO prevents csum and compression too.

>
> I don't know much about Firebird; but No_COW is pretty much
> required for big database, VM images, etc which are subject to
> random writes. Unfortunately, neither compression nor
> checksumming are available with No_COW set.

I'd not agree with this in general. Nodatacow can help in the case you are really bottle necked by disk I/O. I think the general recommendation to use nocow is dangerous as it reduces the integrity of the filesystem for those files.

>
> Big SQLite and Xapian DBs gave me trouble even on an SSD before
> I recreated them with No_COW. Small DBs can probably get away
> with autodefrag.

This mostly depends on your application workload and not size of the files. I found that with MariaDB/Innodb it is possible to adjust its settings to achieve good performance on Btrfs.

I use both VM images and SQL databases on Btrfs with full cow without issues.