Egregoros

Here are the hoops I have to jump through: https://git.pleroma.social/pleroma/pleroma/issues/2338#issuecomment-115204

@silverpill Does mitra not suffer from this? @phnt was saying in the issue:

Yes, this is a problem for any database running on PostgreSQL that is constantly being written to. Database growing in size and database bloat are unavoidable facts of running a database.

@taoeffect @phnt @silverpill mitra's database is much better structured, so it doesn't grow quite as aggressively for the same amount of posts, but also have you run it through https://github.com/reorg/pg_repack or a database vacuum to actually recover the deleted space?

sadly postgresql comes from a rich lineage, where a bigger computer is the end all solution

@i @phnt @silverpill Is pg_repack better or worse than the pg_squeeze mentioned in the issue?

https://www.cybertec-postgresql.com/en/pg_squeeze-optimizing-postgresql-storage/

@i @phnt @taoeffect @silverpill As far as I know nobody also subscribed Mitra to multiple large Mastodon relays. The relay issue where Pleroma stores activities that are of no use to it (the ghost repeat), is something I have added to my list of issues (as I always forget about it). I just have to get to it one day.

@phnt @phnt @taoeffect @silverpill upstream https://gitgud.io/ryonagency/pleroma/-/blob/lazarus/lib/pleroma/web/activity_pub/mrf/relay_optimization.ex

@i @phnt @taoeffect @silverpill That is not the only fix needed. The Transmogrifier gets in the way I think. Besides that, having that as an optional MRF is dumb, it needs to be automatic so admins don't need to enable it and at that point it being an MRF doesn't make sense.

@phnt @phnt @taoeffect @silverpill fedi just needs a better way to federate too (tfw no nigentropy )

@taoeffect @phnt

mitra.social (4.5 years old instance):

# du -sh /var/lib/postgresql/
1.2G    /var/lib/postgresql/

@silverpill @taoeffect @phnt cum.salon is not even that old and we had to migrate the server cuz the db was too big (60gb)

@pernia @taoeffect @phnt @silverpill still only ~160gb here, could handle a lot more but backfilling is a mess

@i @taoeffect @pernia @silverpill Probably deserves a relay cleanup which I didn't do in months. But repacking the DB takes like 6 hours on this crappy VPS. Instance is almost 3 years old.
image.png

@phnt @taoeffect @i @silverpill heh, i remember when i tried to prune/vaccuum/repack on oracle vps and after 3 weeks nothing happened

@phnt @i @silverpill @taoeffect had to dump it and do it from my desktop

@taoeffect Just out of curiosity, how big is your database?

@phnt I dont remember, and it depends when you ask. Early this morning it was ~60GB I think, now it's down to like ~20GB after vacuuming

@taoeffect 20GB is way too much for 3 months of posts which is the default post age to be deleted.

Was that after running the --prune-orphaned-activities option?

@phnt I didn't use that option, I used the regular prune command without any options and it took ~3GB off. Maybe that's why?

@taoeffect That would be why. There are still references to the old, now deleted, posts in the database. Run the prune manually once with that option, vacuum full the activities table and that should fix it.

You can run a prune with that option less regularly as it is slower than without it and by quite a lot.

@phnt can it be run while the site is running or no? Should I still keep running the table level vacuum too?

@taoeffect The prune task no matter its settings can be run when the the site is running (except the --full option), if your system can handle the disk IO. The vacuum full should run with Pleroma stopped.