Cleaning up the database

Submitted ⁨⁨21⁩ ⁨hours⁩ ago⁩ by ⁨adminsforum@hubzilla.org [bot]⁩ to ⁨adminsforum@hubzilla.org⁩

Hans van ZijstHans van Zijst wrote the following post Sun, 04 Oct 2026 16:53:47 +0200

Cleaning up the database

I need a little help with my database. It's been growing and growing and growing, and I can't seem to stop that.

I'm the only user on this system, my uploads are not in the database but on disk, and the policy is that stuff older than 170 days is removed.

Yet the database is 173GB at this moment. That can't be right, can it? Even if it's been online since 2019.

Where do I start? Is there an SQL statement that can show me ow many messages there are that should have been dropped long ago? Is there other stuff to look at that can be removed?

I'm about to migrate this installation to another machine, but I can't claim 180GB for just Hubzilla's database. I'd want to migrate my own posts and uploads, and the reactions to them. I don't know who to tell how much space those reactions have eaten up, but I can't imagine it would be more than 10 or 20 GB.

If anybody can tell me what I should look for, and what stuff I could/should remove and what to leave alone, I'd be very grateful 🙏

original

Comments

Sort:hotnew top

sk@utsukta.org ⁨21⁩ ⁨hours⁩ ago
if this is a postgres database this might be a bug that @Harald Eilertsen encountered some time ago (his db size exploded).
original
- hans@social.woefdram.nl ⁨21⁩ ⁨hours⁩ ago
  @Saiwal Ah, no, I should have included that bit of rather important information...
  
  It's MariaDB. Version 10.3, it's a pretty old installation, and that's one of the reasons why I want to migrate it to another machine.
  original
  - sk@utsukta.org ⁨7⁩ ⁨hours⁩ ago
    I'm also running mariadb and I free up a significant amount of space by doing OPTIMIZE TABLE dreport, session, term, iconfig, xconfig, item; ran today and disk size reduced from 17GB to 12GB
    original
harald@hub.volse.no@hub.volse.no ⁨21⁩ ⁨hours⁩ ago
@Hans van Zijst There's a few things I can think of: Check your xconfig table. I've had issues with it growing a lot for different reasons in the past. Sometimes the real size of the data may be way smaller than the space they occupy on disk, due to frequent updates leading to fragmentation.

You can find some useful queries in framagit issue #1927.

In that case it seemed some indexes had disappeared from the database, which is also worth checking. That caused a lot of duplicate entries in the db.

Another issue I've seen more recently was that the queueworker built up a huge number of jobs. This seems to be due to trying to fetch parents and all downstream comments from messages arriving at the hub somehow. Check the queueworkers page in admin, and the workerq table in the db.

Again, this table is updated frequently, so may be subject to fragmentation.

See framagit issue #1972 for some more background on this.

Other than that. Just see which table are taking up the space in your DB. It should be the items table, and pretty much everything else should be small in comparison. If that's not the case, some investigation is needed.

Good luck!
original