Reducing hubzilla's database to 30% when using Mariadb/MySQL -

Short Downtime 2025-05-25. Note: It's possible that all sessions will be lost, so you might have to relogin. Kurze Downtime am Sonntag, 2025-05-25. Es besteht die Möglichkeit dass es notwendig ist sich neu einzuloggen

Reducing hubzilla's database to 30% when using Mariadb/MySQL

2025-05-20 14:45:38

Hubzilla Support Forum

adminsforum@hubzilla.org

Mark Nowiasz schrieb den folgenden Beitrag Tue, 20 May 2025 14:46:08 +0200

Reducing hubzilla's database to 30% when using Mariadb/MySQL

@!Hubzilla Support Forum

I've discovered something by coincidence - how to reduce Hubzllla's database without any information loss when using MariaDB or MySQL as a database backend.

I was moving the database to another host (got new ARM64 servers at Netcup) and I had a closer look at my database settings (and setting them via ansible when installing the new servers).

I've made some changes and discovered something impressive:InnoDB Page Compression ( https://mariadb.com/kb/en/innodb-page-compression/). I dumped my database on the old host and imported it into the new host, with page compression active.

Well, let's have a look at the results:
Old db host:

du -sh hub_netzgemeinde_eu
147G    hub_netzgemeinde_eu

New db host after importing the dump:

du -sh hub_netzgemeinde_eu
46G     hub_netzgemeinde_eu

Now, that's what I call impressive :-) Not data loss, the information are identical - just nearly 70% space saved.

I'm using lz4 als compression algorithm and I have encounted no perfomance impact by doing so.

I'm using these settings:

innodb_compression_default                      = ON
innodb_compression_algorithm                    = lz4

However, lz4 is an additonal algorithm, standard are zlib and gzip. Depending on your distribution you may have to install the plugin and (in my case) set it up:
plugin_load_add = provider_lz4

There are two caveats:

1.) The filesyste should support sparse files, xfs, btrfs, ext3/4 work fine
2.) xtrabackup cannot cope with compressed pages, however mariadb-backup (which is the better choice anyway) has no problems with it.

Since I've dumped data into a freshly, empty mariadb (with thse settings), how about converting an existing one to page_compression?

I can only guess, but I think a mariadb-check -o --all-databases (or mysqloptimize --all-databases) would to the trick - it recreates (with no downtime) all tables and the newly created tables should have compression enabled, so this is also the way back if you don't like compression

show all 11 comments

2025-05-20 15:08:51

Harald Eilertsen

harald@hub.volse.no

That's cool! But I wonder how much of that reduction is due to just importing the data into a fresh database to begin with? That makes a pretty big difference on my hub running Postgres at least. Without any (change in) compression.

2025-05-20 15:17:14

BauerUlli

bauerulli@hub.netzgemeinde.eu

Maybe a stupid question. How do you know that no data is lost?

2025-05-20 15:24:04

Mark Nowiasz

buckaroo@hub.netzgemeinde.eu

@Harald Eilertsen Not very much - so an export/import is tantamount to an mysqloptimize, only that idbdata1 is also kind of defragmented. Since I've done the optimize some days ago there would only a small gain, say 1-3 GBs. The funny thing is: the size of the database files (well, sparse in case of the new server) are slightly different. Some a slightly larger (63 GB instead of 62GB), some a slightly smaller. Well, this could be due to the difference in the architecture (ARM64 vs Intel) or fs (I'm using xfs for the databases on the new servers, on the old servers btrfs with NoCow).

Oh, edited: an optimize is roughly the same as a vacuum full, the main difference: an optimize can be done without any downtimes.

2025-05-20 15:26:27

Mark Nowiasz

buckaroo@hub.netzgemeinde.eu

@BauerUlli Well, the size of the (sparse) files - are roughly the same, furthermore the backupfiles are also roughly the same size. Anyway, I think if compression would really mean losing data there would be an outcry from people using it and discovering that the data has been lost :-)

2025-05-20 15:34:29

Harald Eilertsen

harald@hub.volse.no

@Mark Nowiasz Cool! Thanks for the extra info!

2025-05-20 17:19:14

BauerUlli

bauerulli@hub.netzgemeinde.eu

@Mark Nowiasz Da habe ich was falsch verstanden. Es geht um komprimieren? Das kenne ich von MS Access. Da gehen natürlich in der Regel keine Informationen verloren.

2025-05-20 17:40:23

Max Kostikov

kostikov@zotum.net

@Hubzilla Support Forum

innodb_compression_algorithm = lz4

Sounds good unless you are using ZFS, which supports dataset compression.
Here is the @Zotum's case.

root@zotum:/usr/local/www/zotum.net # zfs list -t all | grep mysql
zroot/var/mysql                   152G   185G  31.3G  /var/mysql
zroot/var/mysql@20250520         98.3M      -  31.3G  -
zroot/var/mysql/innodb            120G   185G   116G  /var/mysql/innodb
zroot/var/mysql/innodb@20250520  4.16G      -   116G  -
zroot/var/mysql/logs              875M   185G   596M  /var/mysql/logs
zroot/var/mysql/logs@20250520     279M      -   279M  -
root@zotum:/usr/local/www/zotum.net # zfs get all zroot/var/mysql/innodb | grep -E ' compress(ion|ratio)'
zroot/var/mysql/innodb  compressratio         3.20x                     -
zroot/var/mysql/innodb  compression           lz4                       inherited from zroot
root@zotum:/usr/local/www/zotum.net # zfs get all zroot/var/mysql/logs | grep -E ' compress(ion|ratio)'
zroot/var/mysql/logs  compressratio         2.34x                     -
zroot/var/mysql/logs  compression           lz4                       inherited from zroot
root@zotum:/usr/local/www/zotum.net # zfs get all zroot/var/mysql | grep -E ' compress(ion|ratio)'
zroot/var/mysql  compressratio         3.14x                     -
zroot/var/mysql  compression           lz4                       inherited from zroot

Just FYI it also supports zstd

an extremely flexible algorithm nearly rivaling lz4 in speed (the fast levels can actually achieve similar benchmarks). Ratio is much better than any of the prior algorithms, even the default zstd-3 is very fast and achieves better compression ratio than gzip-9 can.

2025-05-20 18:02:13

Mark Nowiasz

buckaroo@hub.netzgemeinde.eu

@Max Kostikov Yeah, well, zfs is rather problematic on Linux (due to license issues). And I'd love to use zstd. unfortunately it's not available for InnoDB right now, altough there's a feature request regarding that.

2025-05-20 23:28:55

*_jayrope

jrp@hub.kliklak.net

@Mark Nowiasz I am using MariaDB in the first place, and still find the DB way oversized. Hmm.

2025-05-21 01:16:07

Scott M. Stolz

scott@loves.tech

Side note: This might be a great topic for an article.

2025-05-21 08:55:13

Mark Nowiasz

buckaroo@hub.netzgemeinde.eu

@Scott M. Stolz In fact I'm planning something like that (a couple of years ago I've already written an article who to improve Hubzilla's speed, which is a littlle bit outdated right now). I think I'm going to document my setup, including the mariadb/postgresql options, backup jobs, session handling and so on.

I think I'm going to do this after I've finished the server shuffling :-)