Skip to main content

Recovering disk space in MariaDB
I'm out of disk space again. With MySQL it used to be that I could manually delete rows from the photo table, then rebuild the database and recover the space. It was no fun at all but it worked.

Now that I've moved to mariadb, it seems like "innodb_file_per_table = on" doesn't do anything: all the space is used by a single storage.idb file. I tried deleting half the photos and rebuilding the entire database from a dump, but it didn't seem to help. Now that I no longer have tables in individual files, I can't even tell how much space each table is taking up.

Is there any way to manage this disk space? I'd like to find out which tables are taking up the space, and then after deleting unnecessary data from those, recover the disk space on my filesystem.

!Friendica Support
!Friendica Support

Friendica Support reshared this.

in reply to Matthew Exon

Matthew Exon
Matthew Exon
•
I never run "optimize table", partly because I learned that it doesn't do anything useful in mysql. I can certainly try that now. That still leaves the problem of measuring what difference it made.

Friendica Support reshared this.

in reply to Matthew Exon

Matthew Exon
Matthew Exon
•
"Table does not support optimize, doing recreate + analyze instead". It didn't seem to free up any space.

Friendica Support reshared this.

Unknown parent

Matthew Exon
Matthew Exon
•
Ah, I misinterpreted what storage.idb means: some schema change means that the vast majority of storage lives there now, rather than directly in the photo table. So now that I've delete a bunch of rows from the photo table, is there any way to figure out which rows in the storage table are no longer attached to anything and should be deleted too? Simply running "optimize table storage" doesn't free up anything.

Friendica Support reshared this.

Unknown parent

Matthew Exon
Matthew Exon
•
Moving to the filesystem and back sounds like a good idea. I managed to remove my 10,000 photos from the storage table by using a backup, magic™, and a lot of swearing, and after optimising the table recovered 1 of the 12GB it was using. This strongly suggests there's a lot more orphaned crap in there that might get purged in the roundtrip to the filesystem. Thanks for the suggestion!

Friendica Support reshared this.

Unknown parent

Matthew Exon
Matthew Exon
•
I think I burned through July's sysadminning budget already, so I'm not quite ready to make the switch to the develop branch today 😅 But this is all good to know. In the short term, temporarily switching to filesystem and then back again probably will relieve the immediate pressure. But the new features in develop sound like they're moving in the right direction.

Friendica Support reshared this.

⇧