Conversation

Notices

1. Embed this notice
    (mint@ryona.agency)'s status on Friday, 28-Jun-2024 00:06:09 JST 
   Probably the only time someone would be happy to see so many rows being deleted. Took what, three days?
   Screenshot_20240627_180452.png
   • ✙ dcc :pedomustdie: :phear_slackware: likes this.
   • Embed this notice
      (mint@ryona.agency)'s status on Friday, 28-Jun-2024 00:06:44 JST 

     in reply to
     And now the autovacuum kicled in again.
   • Embed this notice
     ✙ dcc :pedomustdie: :phear_slackware: (dcc@annihilation.social)'s status on Friday, 28-Jun-2024 00:07:06 JST ✙ dcc :pedomustdie: :phear_slackware:

     in reply to
     @mint Jesus thats a lot of data :alex_lol:
      likes this.
   • Embed this notice
      (mint@ryona.agency)'s status on Friday, 28-Jun-2024 00:12:13 JST 

     in reply to

     • ✙ dcc :pedomustdie: :phear_slackware:
     @dcc Maybe 8% of the total size of activities table. Been running for so long I've already forgot the count at this point.
     ✙ dcc :pedomustdie: :phear_slackware: likes this.
   • Embed this notice
      (mint@ryona.agency)'s status on Friday, 28-Jun-2024 01:15:38 JST 

     in reply to
     Did a few smaller optimizations on users and notifications tables, now it's tome for pg_repack for good measure.
     ✙ dcc :pedomustdie: :phear_slackware: likes this.
   • Embed this notice
      (mint@ryona.agency)'s status on Friday, 28-Jun-2024 04:11:33 JST 

     in reply to
     Alright, the DB shenanigans are done.
     1. Purged remaining Announce activities from standalone relays, which amounted for about 1.8 million rows.
     2. Purged posts (Note objects) with no replies from bots at bae.st, milker.cafe and nicecrew.digital. Don't have full numbers, but I think it was around 3 million rows.
     3. Ran a query to purge orphaned activities referencing those newly deleted objects (and maybe some others, don't know if there's any). This took almost three days, but amounted for 3.3 million more rows.
     4. For a good measure also deleted empty remote actors, around 250k total (Mastodon often spams deletes over the whole network, which prompts the instance to fetch deleted users even if deletes themselves are rejected; this likely isn't the only reason they federate, but sure is more notable one). Also deleted like/repost/react notifications and all other read notifications older than three weeks.
     5. Unrelated to DB maintenance, but I patched frontend so that it would make background requests much less frequently, which might also improve performance and reduce the amount of 500s.
     Sadly I didn't bother getting any numbers before doing all that shit, but I suspect there's 15-20% decrease in total DB size. Right now it takes 59.3 GiB on disk.
   • Embed this notice
      (mint@ryona.agency)'s status on Friday, 28-Jun-2024 04:13:23 JST 

     in reply to
     Also turns out failures to fetch posts from random instances which included nicecrew weren't related to DB maintenance; apparently OpenNIC server I used as system DNS either is dying or already died. Switched to another DNS, should work fine now. Incidentally, opennic.org and opennicproject.org return 404 from whatever CDN those domains are now using.
     ✙ dcc :pedomustdie: :phear_slackware: likes this.
   • Embed this notice
     :archlinux: (lewdthewides@hidamari.apartments)'s status on Friday, 28-Jun-2024 04:22:43 JST :archlinux:

     in reply to
     @mint I've been having a weird issue lately where we would get the notifications that someone followed, but half the time it won't record on our follower list or follower count. Also their unfollows aren't being recorded, but their blocks work properly to "reset" the relationship
      likes this.
   • Embed this notice
      (mint@ryona.agency)'s status on Friday, 28-Jun-2024 04:24:34 JST 

     in reply to

     • :archlinux:
     @lewdthewides Happens sometimes. I once unfollowed someone, the record from following_relationships was gone, but the activity referencing the follow persisted and it showed up as following when I opened up his profile; manually adding the follow row and then unfollowing again fixed it.