Can I delete unused items from storage?
I'm behind on releases, I'm still on 2022.06. One thing preventing me upgrading is that I need to make a full backup for upgrading, but my database has blown up in size.
For me, the new storage table is pretty unfamiliar. But it seems to me that there's a lot of unused data there. Everything in storage must be either a photo or an attachment - right? I have 191433 rows in
Am I right that I could just delete those rows (except for the single row in
!Friendica Support
For me, the new storage table is pretty unfamiliar. But it seems to me that there's a lot of unused data there. Everything in storage must be either a photo or an attachment - right? I have 191433 rows in
storage
. I have 127206 rows in photo
, and just one in attach
. And it seems that the remaining rows are using 12GB, about half of the space of the whole DB:MariaDB [friendica]> select count(*), sum(octet_length(storage.data)) from storage left outer join photo on storage.id = photo.`backend-ref` where photo.id is null;
+----------+---------------------------------+
| count(*) | sum(octet_length(storage.data)) |
+----------+---------------------------------+
| 63459 | 13781405163 |
+----------+---------------------------------+
Am I right that I could just delete those rows (except for the single row in
attach
)?!Friendica Support
Roland Häder likes this.
Friendica Support reshared this.
utzer [Friendica]
in reply to Matthew Exon • • •Did you ever optimize the database? Do you have enough free space on you server for a copy of the largest table (optimize or update could require to copy a whole table). Did you try to gain some free space by purging old versions of updated packages?
You can do the backup to some ssh accessible storage by piping the dump straight to another server by ssh.
Friendica Support reshared this.
Matthew Exon
in reply to utzer [Friendica] • •sudo mysqlcheck -o --all-databases
, and it saved 1GB. But my dumps are 24GB and that's about the same as the database on disk, so it looks like that won't solve anything this time. Interestingly, the optimise succeeded despite claiming that it was rebuilding, and obviously there's not enough space to really rebuild. So something's fishy.It's just Digital Ocean, I could easily add another disk, but that's not really the point. The database is twice as big as it should be, and that's going to make every step from now on a lot more than twice as hard as it should be. I should fix the biggest problem first.
Friendica Support reshared this.
utzer [Friendica]
in reply to Matthew Exon • • •When you go to https://friendica.exon.name/friendica you can see that the update did not finish. So there is a problem.
Friendica Support reshared this.
utzer [Friendica]
in reply to utzer [Friendica] • • •https://friendi.ca/2022/06/11/friendica-2022-06-released/
So at least
bin/console dbstructure update
has to be run, but maybe alsobin/console postupdate
needs to be executed, but I am not sure about the later one and not sure about the best order to run these.Friendica Support reshared this.
Matthew Exon
in reply to utzer [Friendica] • •/friendica
diagnostics page.I did run
dbstructure update
when I updated in July. I even copied and pasted the terminal output into my notes so that I can prove it 😛 But I've never heard ofpostupdate
before, that does explain a lot.Right now when I run
dbstructure update
it does nothing. When I runpostupdate
I get this error:I can probably try to untangle this myself. Thanks for the hints!
Friendica Support reshared this.
utzer [Friendica]
in reply to Matthew Exon • • •Friendica Support reshared this.