How to I clean up my database?

Wed, 12 Apr 2023 22:58:00 +0200

hans@social.woefdram.nl

@Hubzilla Support Forum Hi,

I've run my own Hubzilla instance since 2019, but I'm having some problems lately. I think most of it is caused by an overloaded machine, so cleaning stuff up is my first port of call.

My database has grown to 25GB, and I'd like to make it a lot smaller. I'm the only user on my hub, so it shouldn't need to be this big.

I notice I have items in it that date back to 2017, 2 years before I started this hub. I thought I had configured it to expire old stuff, but it turns out I hadn't. So I changed it to 2 years, everything older than 2 year can be removed. Running the Cron.php doesn't help, so I looked at its source and noticed that it selects the items to remove by expiry date.

I think I can simply set an expiry date on items that were created over 2 years ago and then run Cron.php, but before I destroy my instance, I'd like to hear your opinion first.

If that's indeed the way to go, do I set an expiry date on records in "item", or are there other things that need attention too?

hubzilla

show all 19 comments

Thu, 13 Apr 2023 22:25:05 +0200

Hans

hans@federate.hopto.org

I think this post is not visible in the hubzilla support forum. Once might need to visit them and simply post there. I agree, the old way was easier.

Thu, 13 Apr 2023 22:30:37 +0200

Hans van Zijst

hans@social.woefdram.nl

Hehehe, I noticed. I reposted it on the forum about an hour ago.

Thu, 13 Apr 2023 22:45:46 +0200

Hans

hans@federate.hopto.org

B.t.w. you might have seen my posts about bots on the fediverse. Some tend to get some 40 percent of my traffic in once session, they only try to get as much data as possible.

If it smells like shit it probably is shit.

The last time you made a dump and reimported it resulted in lots of GB's less if I recall. Curious what they state on the forum :-)

Thu, 13 Apr 2023 23:06:48 +0200

Hans van Zijst

hans@social.woefdram.nl

Well, lots... I lost a few GB, but I think I've already got more back than what I lost then :-(

Thu, 13 Apr 2023 23:10:03 +0200

Hans

hans@federate.hopto.org

I would simply look at the item table and see what Mastodon users federate with you compared to other fediverse solutions.

Fri, 14 Apr 2023 00:10:56 +0200

Mario Vavti

mario@hub.somaton.com

Basically you should be fine setting an expire value either in settings for your channel or in admin/site for the hub. This will delete other channels content where you have not interacted with older than x days.
There is a slight chance though that expiration will not finish due to memory restrictions. Incidentally i have fixed this in dev branch today.
You might just try and if it does not work out, wait for the next release wich will be, as always, much better as the current one :slight_smile:

Fri, 14 Apr 2023 01:22:22 +0200

Hans van Zijst

hans@social.woefdram.nl

Memory, that sounds like the reason why setting an expiry value didn't do anything. The machine is full, even swap is 100% full.

Yes, yes, I know, I should have cleaned stuff up long ago, but you know how things work ;-)

But @Mario Vavti, you're saying I shouldn't need to manually add an expiration date to the records in the database, just setting it in the admin interface should do?

Fri, 14 Apr 2023 01:59:42 +0200

Hans van Zijst

hans@social.woefdram.nl

Well, that didn't work. Stopped a lot of programs to free up some memory, but running cron didn't remove anything.

I've bitten the bullet and added 2GB of RAM, making this machine even more expensive than it already was, but I think I had no choice. Rebuilding this machine on a much cheaper platform just became more urgent.

Fri, 14 Apr 2023 06:53:44 +0200

Mario Vavti

mario@hub.somaton.com

Cron starts expire just once a day. You can force expire to run though: php Zotlabs/Daemon/Master.php Expire. To help with the memory issue you can try a lower expire limit by setting util/config system expire_limit 500. The default limit is 5000 and is not suitable for everybody. It will take more runs though to clean up.

Fri, 14 Apr 2023 12:06:32 +0200

Hans van Zijst

hans@social.woefdram.nl

Changed the limit to 500 and ran Expire, but didn't see anything happen. So I ran it again a few times and when I count the number of items in my database, I see it decreased slightly.

So, I've put the Expire command in a loop and will check back in an hour or so :) I'll post the result later today.

Thanks for the help again, @Mario Vavti.

Fri, 14 Apr 2023 12:10:19 +0200

Hans van Zijst

hans@social.woefdram.nl

@Jelv 🎴 Hehehe, ik heb gewerkt bij PCextreme, dat is idd overgenomen. Ik heb daar overigens nog een machine draaien.

Ik zit zeker niet vast aan TransIP, maar het probleem is dat dit een machine is waar ongeveer alles op draait: mail, DNS, Nextcloud, Hubzilla, LDAP, noem maar op. Die verhuis je niet "even" naar een ander platform.

Ik heb al een paar jaar op m'n verlanglijstje staan om die machine opnieuw op te bouwen, op het platform ProcoliX, waar ik nu werk, maar dat kost tijd. En ik ben in het weekend niet meer zo fanatiek achter de PC als vroeger.

Fri, 14 Apr 2023 13:28:17 +0200

Hans van Zijst

hans@social.woefdram.nl

@Jelv 🎴 Dat is een beetje het punt, idd. Ik heb lang geleden van m'n hobby m'n beroep gemaakt, waar ik nog steeds erg blij om ben, maar na een drukke week systeembeheer heb ik de laatste tijd in m'n vrije tijd toch wat minder trek om dat zelfde nog eens te doen maar dan voor mezelf.

Fri, 14 Apr 2023 13:51:41 +0200

Koen de Jonge - SynQ

koen@procolix.social

@hans je werk is gewoon te leuk. Misschien moeten we je wat vaker Windows dingen laten doen. 🤪🤪

Fri, 14 Apr 2023 14:11:25 +0200

Hans van Zijst

hans@social.woefdram.nl

@Koen de Jonge - SynQ Hehehe, dat zou m'n enthousiasme wel temperen, ja.

Ik doe soms in het weekend zelf af en toe wat Windoze-dingen. Civilization6 of Cities:Skylines bijvoorbeeld :sunglasses:

Mon, 17 Apr 2023 02:05:57 +0200

Hans van Zijst

hans@social.woefdram.nl

No luck. Running Expire a few times didn't remove anything. I added an expiration date to items older than 2 years, and MariaDB reported that some 7000 records were changed. But running Expire again didn't remove anything.

Mon, 17 Apr 2023 12:02:51 +0200

Hans

hans@federate.hopto.org

Maybe it is a good idea to make a ticket? Others see it growing too.

Mon, 17 Apr 2023 12:26:37 +0200

Hans van Zijst

hans@social.woefdram.nl

Ticket? Where?

Mon, 17 Apr 2023 12:28:44 +0200

Hans

hans@federate.hopto.org

framagit hubzilla?

Mon, 17 Apr 2023 13:29:10 +0200

Mario Vavti

mario@hub.somaton.com

There is no need to set an expiration date on the items. Running expire as mentioned should do. If it does not do anything you should see something about that in the log...