Conversation

Notices

1. Embed this notice
   J上校👮🇺🇦🏳️‍⚧️ (colonelj@bae.st)'s status on Friday, 26-Apr-2024 05:33:06 JST J上校👮🇺🇦🏳️‍⚧️
   fse is back (sort of) in a live-testing state
   https://blog.freespeechextremist.com/blog/update-and-roadmap.html
   • Embed this notice
     :blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 05:33:00 JST :blank:

     in reply to

     • Vivi Nella Verita
     @verita84eva @colonelj old box kept horribly overheating, DB being old enough for users to still have numeric ostatus id's didn't help either
     Pleroma-tan likes this.
   • Embed this notice
     Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 05:33:01 JST Vivi Nella Verita

     in reply to

     • :blank:
     @i @colonelj
     
     Anyone got a quick summary for why SPC went down? Did the DB just grow out of control?
     Pleroma-tan repeated this.
   • Embed this notice
     :blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 05:33:03 JST :blank:

     in reply to

     • Vivi Nella Verita
     @verita84eva @colonelj tldr
     Fediverse Contractor likes this.
   • Embed this notice
     Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 05:33:04 JST Vivi Nella Verita

     in reply to

     • :blank:
     @i @colonelj
     
     I can't read all of that
   • Embed this notice
     :blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 05:33:05 JST :blank:

     in reply to
     @colonelj and not even an rss update for it smh
   • Embed this notice
     :blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 05:33:39 JST :blank:

     in reply to

     • Vivi Nella Verita
     @verita84eva @colonelj if only someone would have been gracious enough to spoon feed you how to clear old activities objects manually
     ✙ dcc :pedomustdie: :phear_slackware: likes this.
   • Embed this notice
     Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 05:33:40 JST Vivi Nella Verita

     in reply to

     • :blank:
     @i @colonelj
     
     Ah, there still is not a good way to even maintain the Pleroma/Akkoma database. It took me two days to delete 157354 old posts using it's built-in commands and they just let the Activities table grow and grow without a way to clean it
   • Embed this notice
      (mint@ryona.agency)'s status on Friday, 26-Apr-2024 06:11:07 JST 

     in reply to

     • :blank:
     • Vivi Nella Verita
     @verita84eva @colonelj @i Mine is 70 GB right now and I've never pruned old posts or human activites since I launched it 2 years ago. Had a little bit of performance issues (postgres easting too much CPU), but managed to solve it by deleting all announce activities from relays (which accounted for 30% of activities table and 10 gigs on disk); this likely doesn't apply to DRC since you follow zero relays.
   • Embed this notice
     Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 06:11:08 JST Vivi Nella Verita

     in reply to

     • :blank:
     @i @colonelj
     
     Ty. DRC almost 90 GB and is growing fast. Local posts would not be deleted? Only Federated?
   • Embed this notice
     :blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 06:11:10 JST :blank:

     in reply to

     • Vivi Nella Verita
     @verita84eva @colonelj damn, the bulk would be in CREATE activities then, doubt much would break if you deleted them, since you're clearing the objects by age too
     
     time to make a dump and report back if anything breaks after deleting a few months of them
     
     pleroma=> select count(*), date_trunc('month', inserted_at) from activities where data->>'type' = 'Create' and local = 'f' group by date_trunc('month', inserted_at) order by date_trunc('month', inserted_at);
     count | date_trunc
     ---------+---------------------
     882593 | 2023-03-01 00:00:00
     3404857 | 2023-04-01 00:00:00
     3695948 | 2023-05-01 00:00:00
     3067730 | 2023-06-01 00:00:00
     4033487 | 2023-07-01 00:00:00
     4050590 | 2023-08-01 00:00:00
     604380 | 2023-09-01 00:00:00
     19969 | 2023-10-01 00:00:00
     712353 | 2023-11-01 00:00:00
     1842379 | 2023-12-01 00:00:00
     4198836 | 2024-01-01 00:00:00
     3653179 | 2024-02-01 00:00:00
     4249558 | 2024-03-01 00:00:00
     2461312 | 2024-04-01 00:00:00
     (14 rows)
     
     pleroma=> select count(*) from activities where data->>'type' = 'Create' and local = 'f' and inserted_at < now() - interval '365 days';
     count
     ---------
     3778169
     (1 row)
   • Embed this notice
     Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 06:11:11 JST Vivi Nella Verita

     in reply to

     • :blank:
     @i @colonelj
     
     lol yeah I been doing that daily since you told me via cron. I have no idea how to do the Activities table safely. Your suggestion for said tables does not really do much. It's yuge!
   • Embed this notice
      (mint@ryona.agency)'s status on Friday, 26-Apr-2024 06:13:15 JST 

     in reply to

     • :blank:
     • Vivi Nella Verita

     @verita84eva @colonelj @i This might take a shitton of time if you're doing it on a running instance. Try turning it off, then SET session_replication_role = replica, delete them (which should take maybe up to 20 minutes as compared to days when doing it live), SET session_replication_role = DEFAULT and start the instance back.

   • Embed this notice
     Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 06:13:16 JST Vivi Nella Verita

     in reply to

     • :blank:
     @i @colonelj
     
     Running:
     
     ```
     detroitriotcity=# delete from activities where data->>'type' = 'Create' and local = 'f' and inserted_at < now() - interval '365 days';
     ```
     
     Now
   • Embed this notice
     :blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 06:13:17 JST :blank:

     in reply to

     • Vivi Nella Verita
     @verita84eva @colonelj yeah that's what the local = 'f' bit means, same as the like/boost pruning in the image
     
     i'll test it on my own install first, or you can go ahead and try in parallel if you trust your own backups too
   • Embed this notice
     :blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 06:16:03 JST :blank:

     in reply to

     • 
     • Vivi Nella Verita
     @mint @colonelj @verita84eva yeah i wonder where the space is spent, a screenshot of https://detroitriotcity.com/phoenix/live_dashboard/ecto_stats?nav=total_table_size could help
     
     but still, the activities tables has no right to be 44GB of millions of CREATE url json blobs
      likes this.
   • Embed this notice
      (mint@ryona.agency)'s status on Friday, 26-Apr-2024 06:20:28 JST 

     in reply to

     • :blank:
     • Vivi Nella Verita
     @i @colonelj @verita84eva Weird. Objects table on mine is bigger than activites, and that's with most of federation happening from ~170 relays.
     Screenshot_20240426_001646.png
   • Embed this notice
     Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 06:24:39 JST Vivi Nella Verita

     in reply to

     • 
     • :blank:
     @mint @colonelj @i
     
     We did follow a lot. I just recently turned it off . Database grew form 60gb to 93gb in a ween
      likes this.
   • Embed this notice
     Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 06:25:41 JST Vivi Nella Verita

     in reply to

     • 
     • :blank:
     @mint @colonelj @i
     
     Ah is that why deletes are always slow and takes days like with the original commands you gave me ?
      likes this.
   • Embed this notice
      (mint@ryona.agency)'s status on Friday, 26-Apr-2024 06:28:44 JST 

     in reply to

     • :blank:
     • Vivi Nella Verita
     @verita84eva @colonelj @i I don't remember myself personally giving you the commands, but yes, I think deleting a row live causes a bunch of indexes to get recalculated, which raises the query time exponentially if you're deleting millions of rows. Setting session role to replica stops that from happening but it shouldn't ever be done on live instance as those indexes are crucial to the operation.
   • Embed this notice
      (mint@ryona.agency)'s status on Friday, 26-Apr-2024 06:31:01 JST 

     in reply to

     • :blank:
     • Vivi Nella Verita
     @i @colonelj @verita84eva Maybe deletion spam from mastodon? It's known to bringing low-power instances to a halt, happened even to SPC a while ago, and rejecting deletes goes against ackoma crew's social contract.
     ✙ dcc :pedomustdie: :phear_slackware: likes this.
   • Embed this notice
     :blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 06:31:03 JST :blank:

     in reply to

     • 
     • Vivi Nella Verita
     @verita84eva @colonelj @mint a week? is someone out there trolling instances they hate somehow, disqordia died when their DB started inflating suspiciously quickly
   • Embed this notice
     :blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 06:31:33 JST :blank:

     in reply to

     • 
     • Vivi Nella Verita
     @mint @colonelj @verita84eva mint is an alt of everyone else confirmed
      likes this.
   • Embed this notice
     Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 06:33:30 JST Vivi Nella Verita

     in reply to

     • 
     • :blank:
     @mint @colonelj @i oh boy. Try deleting shit from your own instance and oban_jobs filles up and kills your disk io . That was a first big issue with DRc when trying to clean it up
      likes this.
   • Embed this notice
      (mint@ryona.agency)'s status on Friday, 26-Apr-2024 09:45:07 JST 

     in reply to

     • :blank:
     • Vivi Nella Verita
     @verita84eva @colonelj @i Did you vacuum or repack the DB after that? It would still claim the free space otherwise.
   • Embed this notice
     Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 09:45:08 JST Vivi Nella Verita

     in reply to

     • 
     • :blank:
     @mint @colonelj @i
     
     I ran it , even ran it again to 30 days. Activities size didn't change. Heavy DIsk IO when running so it was doing something
   • Embed this notice
     Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 09:46:05 JST Vivi Nella Verita

     in reply to

     • 
     • :blank:
     @mint @colonelj @i
     
     will do repack now. Doing a dump first with pg_backup to see if size is smaller
      likes this.
   • Embed this notice
      (mint@ryona.agency)'s status on Friday, 26-Apr-2024 09:47:19 JST 

     in reply to

     • :blank:
     • Vivi Nella Verita
     @verita84eva @colonelj @i It shouldn't.
   • Embed this notice
     Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 09:47:20 JST Vivi Nella Verita

     in reply to

     • 
     • :blank:
     @mint @colonelj @i
     
     pg_backup will not include all the deleted data right?
   • Embed this notice
     Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 15:01:41 JST Vivi Nella Verita

     in reply to

     • 
     • 313Chris:hellokitty_headbang:
     • Christi Junior
     • :blank:
     @mint @colonelj @i @admin @ChristiJunior
     
     Some success. After Repack, Activities table went from 38GB to 15GB. I deleted Federated Activites up to the last 10 days. WTF happened in the last 10 days on Fedi lol
     
     Total Disk Footprint of the Database Dir is now 34GB. It was 93GB before we started the work today.
     
     Thanks everyone!
      likes this.
   • Embed this notice
      (mint@ryona.agency)'s status on Friday, 26-Apr-2024 16:56:48 JST 

     in reply to

     • 313Chris:hellokitty_headbang:
     • Christi Junior
     • :blank:
     • Vivi Nella Verita
     @verita84eva @colonelj @i @ChristiJunior @admin 15 gigs out of 38 accounting just for activities in the last 10 days is still pretty weird, it shouldn't grow that fast.
   • Embed this notice
      (mint@ryona.agency)'s status on Friday, 26-Apr-2024 17:23:41 JST 

     in reply to

     • 313Chris:hellokitty_headbang:
     • Christi Junior
     • :blank:
     • Vivi Nella Verita

     @verita84eva @colonelj @i @ChristiJunior @admin Maybe try sorting by actor, there ought to be some clue on which servers take the most space.

     select data->>'actor', count(data->>'actor') as count from activities group by data->>'actor' order by count desc;
     NEETzsche likes this.
   • Embed this notice
     :blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 18:34:25 JST :blank:

     in reply to

     • 
     • 313Chris:hellokitty_headbang:
     • Christi Junior
     • Vivi Nella Verita
     @verita84eva @colonelj @ChristiJunior @admin @mint lol i think i know why, Create activities with more than 5000 mentions, i bet the indexing blows up too on them
     
     like, https://writeworks.uk/@/the_kyiv_independent/ created 13MB in the DB with just 100 activities
     
     wonder what
     
     select actor, pg_size_pretty(sum(pg_column_size(activities.*))), count(*) from activities group by actor order by sum(pg_column_size(activities.*)) desc limit 100;
     
     says for you, and if there's not a really degenerate software bug out there silently killing ick'oma database
      likes this.