Database Size

2025-02-15 15:44:56

adminsforum@hubzilla.org

KlausR wrote the following post Sat, 15 Feb 2025 15:44:56 +0100

Database Size

The size of my database grew considerably over the last years.
I recently noticed that I had the value "0" in the field "Expiration period in days for imported (grid/network) content" of the admin site settings.
"0" means no expiration of imported content. Could this be a reason, why databases constantly grow?
I now changed the value to 90 days. Should the database now automatically reduce the size, as all the imported content older than 90 days will be automatically deleted from the database?

show all 9 comments

2025-02-15 18:50:03

Harald Eilertsen

harald@hub.volse.no

@KlausR It depends.

The database (not Hubzilla) manages the storage that it needs, and by default the database will not immediately reclaim the space by deleted or updated rows. For Postgres see the manual about vacuuming, and for Mariadb see the manual about defragmenting.

Hubzilla can perform an OPTIMIZE TABLE operation on the items database as part of expiring old items, but only on Mariadb/MySQL! (That is, it will try to perform the operation regardless of which DB backend youre using, but it is not a valid operation and will fail on Postgres.)

Enable this by setting the system.optimize_items config options from the terminal, like this:

./util/config system optimize_items 1

Not that this does not guarantee that the database storage will shrink!

2025-02-15 19:01:50

Markus

markus@hubzilla.markusgarlichs.de

@Harald Eilertsen is

./util/config system optimize_items 1

set by default?

2025-02-15 19:08:56

Harald Eilertsen

harald@hub.volse.no

@Markus No, and you may not want to enable it for larger sites either.

2025-02-16 11:48:16

KlausR

claus@hub.cloudlet.at

@Harald Eilertsen

Thank you for your detailed explanation. I use MySQL, so I can use the optimize_items parameter. I just enabled the parameter and will observe what happens with the database size over time.

2025-02-16 13:47:14

Harald Eilertsen

harald@hub.volse.no

@KlausR Please share your experience. It's probably interesting to more admins.

2025-02-16 16:08:59

dynamic_hubzilla

dynamic@hub.netzgemeinde.eu

@KlausR

I would also be interested in updates, and also in how large your database got when it started to be a problem.

2025-02-16 22:09:05

KlausR

claus@hub.cloudlet.at

@Harald Eilertsen @dynamic_hubzilla

I have a small hub with only a few channels. My database size increased to above 2.5GB. I didn't really have a problem with the database size, I was just wondering, why the size constantly increased, although it is a small hub.
Maybe the 2.5GB are still small compared to larger hubs, but nevertheless I wanted to find a way to decelerate the trend of constant increase.
As mentioned above I found the value "0" in the field "Expiration period in days for imported (grid/network) content" of the admin site settings and I wanted to understand, if this could be a reason for DB increase.
By the way, after a few hours of the enabled optimize_items parameter, the DB size already reduced to approx. 1.9GB.

2025-02-17 01:13:54

dynamic_hubzilla

dynamic@hub.netzgemeinde.eu

@KlausR

Thanks for the update. Is that optimize items + changing the expiration period, or just optimize items by itself?

2025-02-18 22:47:25

KlausR

claus@hub.cloudlet.at

@dynamic_hubzilla

I did both in parallel: I set the expiration period for imported content from 0 to 90 days and I enabled the optimize_items parameter.

What I understand from the explanation from @Harald Eilertsen and from the help #^Help: Hidden Configs both measures together lead to a reduction of the database size.