Disk compression on VPS?

davide

This is a GreenCloud KVM with a minuscule 35GB disk that is getting increasingly tighter. Since it has 4GB of memory perhaps replacing the current filesystem from ext4 to OpenZFS could help, as it supports compression.

Most of the disk space is taken by Mysql, so I could assign a compressed ZFS partition to /var/lib/mysql/ and avoid the problem of configuring ZFS on the root mount point, keeping the current ext4 everywhere else.

Thoughts?

rm_

Btrfs sounds closer to reach (already in any Linux kernel), than ZFS. It also supports compression with various algorithms and levels. But the performance is going to get poor. It is recommended to use a simple non-CoW filesystem for databases, and CoW+Compression is the opposite of that.

davide

Performance will be terrible I know, I just don't need any performance, the DB data is appended constantly but very slowly, and is only read intensively from another computer via replication.

I'm considering mounting ZFS from a loop device stored on ext4 to keep it dumb simple and avoid any reliance on GreenCloud's rescue OS, which would be required if I had to repartition and resize the FS.

I'm considering ZFS over Btrfs due to terrible gotchas that until a week or so ago were not marked "obsolete", as that page claims now.

--

Edit: that's right, it was marked "obsolete" just this week: https://web.archive.org/web/20230306164924/btrfs.wiki.kernel.org/index.php/Gotchas
Not gonna believe anything from Oracle.

DBA

I would say stop being a pack rat / hoarder and delete the older data. Compression would just delay the day when you need to start cleaning out old data so may as well just do that now.

davide

Thanks... I guess

I wasn't expecting anything intelligent to come out of LET anyway.

MeAtExampleDotCom

@davide said: Most of the disk space is taken by Mysql, so I could assign a compressed ZFS partition to /var/lib/mysql/

mySQL supports compression itself: see https://dev.mysql.com/doc/refman/8.0/en/innodb-compression.html & https://dev.mysql.com/doc/refman/8.0/en/innodb-compression-internals.html and other documentation.

You'll need to rebuild your tables and indexes to enable it (https://dev.mysql.com/doc/refman/8.0/en/innodb-page-compression.html#:~:text=You can also enable page compression in an ALTER TABLE statement.) and that will temporarily consume more space, so it might not be an option if space is already too low.

ZFS may achieve better compression and use less extra RAM (InnoDB will sometimes hold both the compressed and uncompressed version of a page in RAM), or it may use more RAM (it is noted for being more memory hungry than other filesystems, especially without careful tuning, partly but not only because it manages its own chunk of cache) – you'd have to benchmark the two options with your data to see how that balance plays out. I'd be inclined to go with mysql's compression if going for one rather than testing both, it feels safer than messing around at the filesystem level and the change can be done more in-place (but still take a full backup before starting, and be aware that an in-place update may be slower than paving with a new FS and restoring your most recent backups).

AXYZE

Here is app which allows you to mount archive as filesystem
https://bitbucket.org/agalanin/fuse-zip/src/master/

I didnt tried but, so I cannot share my experiences. I just know it exists.
My idea is that you could put database into zip and mount it in desired path. Deflate algo is light, it can get the job done.
You may want to seek alternative with zstd/brotli for better speed/compression ratio.

Edit: from quick Google search it seems like you can transparently compress MySQL DB. Have you tried it?

RemarkableGuille

ZFS with lz4 compression probably would perform better than MySQL native compression or anything else (except maybe BRTS)
Are you sure MySQL usage is mostly data and not MySQL logs and files that can be tweaked to use less space, like binary logs, etc.?

rm_

@davide said: I'm considering ZFS over Btrfs due to terrible gotchas that until a week or so ago were not marked "obsolete", as that page claims now.

The gotchas are not marked obsolete, they are migrating the entire wiki somewhere else, so that's why.

Secondly, what is so terrible about those? Only first four still apply to modern kernels, and of those only fragmentation is relevant to your planned usage. And fragmentation with DBs on a CoW FS is not magically solved on ZFS, on the contrary it will be amplified in effect when using a file-based image (adding the image's fragmentation as well).

@AXYZE said: My idea is that you could put database into zip and mount it in desired path. Deflate algo is light, it can get the job done.

This will be nowhere near competitive to an FS native compression, be it Btrfs or ZFS. I know on Btrfs you can select ZSTD algorithm with a high ratio, so likely not even in the compression rate, and performance on writes and rewrites will be abysmal. Moreover, I suspect these ZIP FUSE FS tools might actually need to uncompress each zipped file entirely to somewhere in /var, while working on it.

@davide said: I wasn't expecting anything intelligent to come out of LET anyway.

You got two replies up until that point, and quick to mark both as not intelligent, gee thanks, very pleasant to participate in the thread.

CheepCluck

@davide said:
I wasn't expecting anything intelligent to come out of LET anyway.

Please run sfc /scannow

itsdeadjim

@davide said: Most of the disk space is taken by Mysql, so I could assign a compressed ZFS partition to /var/lib/mysql/ and avoid the problem of configuring ZFS on the root mount point, keeping the current ext4 everywhere else.

Using MariaDB over ZFS, works great. You may want to try zfs set compression=zstd-fast and zfs set atime=off

davide

Thanks for the tips! I browsed thru the MySQL documentation and I managed to implement compression with zlib level 6. It's a good tradeoff between CPU usage and space saving: the size of MySQL's data directory dropped to 45%, giving me another year of runway. The replica slaves are a bitch though and are still heavily resynching.

There's quite a few requirements that need to be met to enable compression, but this turned out to be a better solution for me than OpenZFS or fuse-zip (suggested by @AXYZE), as my overall DB downtime was only a few seconds, plus the individual tables that where locked / unlocked one by one.

I was headed toward OpenZFS initially because I was gaslighted by the results of this quick Bing query to get an overview of compression support under MySQL: "MySQL compress database". A quick glance at those results is deceitful.

MeAtExampleDotCom

@AXYZE said: Here is app which allows you to mount archive as filesystem

https://bitbucket.org/agalanin/fuse-zip/src/master/

Filesystems based on archive formats are intended as conveniences, not performance options. If they allow write operations they will be very slow and are likely to break write guarantee semantics that invalidate what the DB layer does to try enforce ACID and avoid data corruption. Arrive formats like .zip are not designed at all for efficient updates after creation (except perhaps adding new data to the end, certainly not arbitrary block writes within a large file as needed by a database).

A better option would be to create a block device over a blob-store (S3 compatible and the like) data store that supports compression, and store your DBs on that, but even then you'd need to read up on the write ordering & persistence guarantees of each layer before even considering it. That would also have too many moving parts for my taste (DB->Filesystem->BlockDevice->S3ish->(MaybeAnotherFS)->UnderlyingStorage), each adding latency and possible points of failure, so either ZFS (or maybe other filesystems with compression, though I know less about those options) or built-in compression are probably the only two options I'd seriously consider.