Egregoros · Phoenix Framework

Post

Remote status

Context

Greg Slepak

I definitely don't recommend #pleroma. The database just keeps growing and figuring out how to keep it manageable is a huge hassle.

Fediverse instances should never require manual database admin.

Greg Slepak

@taoeffect@crib.social remote

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

Greg Slepak

@taoeffect@crib.social remote

@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.

silverpill

@silverpill@mitra.social remote

@taoeffect @phnt

mitra.social (4.5 years old instance):

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

YBG pern

@pernia@cum.salon remote

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

@i@declin.eu remote

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

Phantasm

@phnt@fluffytail.org remote

@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

Phantasm

@phnt@fluffytail.org remote

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

Greg Slepak

@taoeffect@crib.social remote

@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

Replying to @taoeffect@crib.social

Phantasm

@phnt@fluffytail.org remote

@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?

Replies

Replying to @phnt@fluffytail.org

Greg Slepak

@taoeffect@crib.social remote

@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?

Replying to @taoeffect@crib.social

Phantasm

@phnt@fluffytail.org remote

@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.

Replying to @phnt@fluffytail.org

Greg Slepak

@taoeffect@crib.social remote

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

Replying to @taoeffect@crib.social

Phantasm

@phnt@fluffytail.org remote

@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.