Conversation

Notices

1. Embed this notice
   Trash Panda (raccoon@stereophonic.space)'s status on Wednesday, 15-Feb-2023 23:44:06 JST Trash Panda

   • :suya:
   @newt Hey dude, timeline is giving me some json error and notifications are straight up not working. Dunno if it's something on the server side.
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Wednesday, 15-Feb-2023 23:43:53 JST :blobcathug:

     in reply to

     • :suya:
     • Kumicota :euphohug:
     • Quad ?
     @newt @raccoon @kumicota @quad ok? our index is half as big as the data, how did that happen in your DB?
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Wednesday, 15-Feb-2023 23:43:54 JST :suya:

     in reply to

     • Kumicota :euphohug:
     • Quad ?
     @kumicota @raccoon @quad it's not even the data itself. It's how the pleroma schema is structured and what indexes it creates. Right now, we actually have more indexes than actual data in our database. I mean it. Indexes occupy more space than data.
   • Embed this notice
     Kumicota :euphohug: (kumicota@weeaboo.space)'s status on Wednesday, 15-Feb-2023 23:43:55 JST Kumicota :euphohug:

     in reply to

     • :suya:
     • Quad ?
     @newt @quad @raccoon
     
     the worst thing about database rot is that it spread with time and there's not a lot of online guides to fix or delet the rot data.
     
     I tried to get into pleroma development once but because I didn't liked a lot funcional programming (mostly due skill issues) I dropped it
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Wednesday, 15-Feb-2023 23:43:56 JST :suya:

     in reply to

     • Kumicota :euphohug:
     • Quad ?
     @kumicota @raccoon @quad it is possible to somewhat manage this by restructuring pleroma database schema. I have some ideas, but I actually need to dedicate time for getting into pleroma development, and right now my hands are quite full.
   • Embed this notice
     Kumicota :euphohug: (kumicota@weeaboo.space)'s status on Wednesday, 15-Feb-2023 23:43:57 JST Kumicota :euphohug:

     in reply to

     • :suya:
     • Quad ?
     @newt @raccoon
     
     F, because this sounds a bit like what weeaboo.space has and according to @quad he wasn't able to find a easy way to fix it, so if the error is the same, an automated tool could help more than 1 instance
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Wednesday, 15-Feb-2023 23:43:58 JST :suya:

     in reply to

     • Kumicota :euphohug:
     @kumicota @raccoon lol no idea. I've no clue what will work and what won't until I actually do things.
   • Embed this notice
     Kumicota :euphohug: (kumicota@weeaboo.space)'s status on Wednesday, 15-Feb-2023 23:43:59 JST Kumicota :euphohug:

     in reply to

     • :suya:
     @newt @raccoon will the fix gonna be automated?
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Wednesday, 15-Feb-2023 23:44:00 JST :suya:

     in reply to
     @raccoon database rot. It has become too big over the years.
   • Embed this notice
     Trash Panda (raccoon@stereophonic.space)'s status on Wednesday, 15-Feb-2023 23:44:01 JST Trash Panda

     in reply to

     • :suya:
     @newt How did it get so bad? Are there raccoons inside the server?
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Wednesday, 15-Feb-2023 23:44:02 JST :suya:

     in reply to
     @raccoon there's a chance that I can fix this, but it could require taking the instance down for like a week or two. And I'm still hoping to avoid this.
   • Embed this notice
     Trash Panda (raccoon@stereophonic.space)'s status on Wednesday, 15-Feb-2023 23:44:04 JST Trash Panda

     in reply to

     • :suya:
     @newt
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Wednesday, 15-Feb-2023 23:44:05 JST :suya:

     in reply to
     @raccoon it is :akkosad:
     
     I've been trying to fix this for the past week, with varying success.
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Wednesday, 15-Feb-2023 23:49:04 JST :blobcathug:

     in reply to

     • :suya:
     • Kumicota :euphohug:
     • Quad ?
     @newt @raccoon @kumicota @quad i guessed that, when i restored it to the new server...
     let me check...
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Wednesday, 15-Feb-2023 23:49:06 JST :suya:

     in reply to

     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @Jain @raccoon @kumicota @quad :akkoshrug:
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Wednesday, 15-Feb-2023 23:53:56 JST :blobcathug:

     in reply to

     • :suya:
     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @newt @kumicota @quad @raccoon
     total: 111GB
     indexes: 41 GB
     relation: 69 GB
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Wednesday, 15-Feb-2023 23:54:55 JST :blobcathug:

     in reply to

     • :suya:
     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @kumicota @newt @quad @raccoon :blobcatgoogly: how did you end up with this numbers? im pretty sure we already talked about repack and you did that right?
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Wednesday, 15-Feb-2023 23:56:29 JST :blobcathug:

     in reply to

     • :suya:
     • Kumicota :euphohug:
     • Quad ?
     @kumicota @newt @raccoon @quad btw i actually disagree, indexes can have a big size and these can become large especially in complex relations and this does not necessarily mean that the schema is bad
   • Embed this notice
     Kumicota :euphohug: (kumicota@weeaboo.space)'s status on Wednesday, 15-Feb-2023 23:56:30 JST Kumicota :euphohug:

     in reply to

     • :suya:
     • Quad ?
     @newt @quad @raccoon
     
     > Indexes occupy more space than data.
     
     this sounds like a bad designed database where they didn't future proof it for big tables
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Wednesday, 15-Feb-2023 23:57:16 JST :blobcathug:

     in reply to

     • :suya:
     • Kumicota :euphohug:
     • Quad ?
     @newt @raccoon @kumicota @quad no... because its not just one index... there are multiple ones and each of them has its own purpose
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Wednesday, 15-Feb-2023 23:57:17 JST :suya:

     in reply to

     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @Jain @raccoon @kumicota @quad if you index is almost the size of the table itself, this kinda defeats the purpose. Don't you think so?
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Wednesday, 15-Feb-2023 23:58:42 JST :blobcathug:

     in reply to

     • :suya:
     • Kumicota :euphohug:
     • Quad ?
     @newt @raccoon @kumicota @quad hmm... you dont have additional 80GB? may i ask on which hoster you ran this stuff?
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Wednesday, 15-Feb-2023 23:58:43 JST :suya:

     in reply to

     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @Jain @raccoon @kumicota @quad nope. We don't have the space on this server. I'll do a reindex first, then we'll see.
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Thursday, 16-Feb-2023 00:03:34 JST :blobcathug:

     in reply to

     • :suya:
     • Kumicota :euphohug:
     • Quad ?
     @newt @raccoon @kumicota @quad :blobcatgoogly: thats fine tho... https://reorg.github.io/pg_repack/
     > Performing a full-table repack requires free disk space about twice as large as the target table(s) and its indexes
     so start with just activites and then object
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Thursday, 16-Feb-2023 00:03:35 JST :suya:

     in reply to

     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @Jain @raccoon @kumicota @quad I have 80 gigs. I don't have 160 gigs.
     
     We use liteserver, with 260 gigs in total on this VPS. Media is hosted separately on another server.
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Thursday, 16-Feb-2023 00:05:06 JST :blobcathug:

     in reply to

     • :suya:
     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @newt @kumicota @quad @raccoon dumb me... i didnt read the index part
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Thursday, 16-Feb-2023 00:05:34 JST :blobcathug:

     in reply to

     • :suya:
     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @kumicota @newt @quad @raccoon so yes, maybe a reindex frees up enough space to perform a repack
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Thursday, 16-Feb-2023 00:06:28 JST :blobcathug:

     in reply to

     • :suya:
     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @kumicota @newt @quad @raccoon and dont forget that you can repack all the other tables and indexes, that might free up more space
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Thursday, 16-Feb-2023 00:07:52 JST :blobcathug:

     in reply to

     • :suya:
     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @kumicota @newt @quad @raccoon beside of that, if you are able to rent a server for like a day or two, you can use a temporary server to perform such tasks
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Thursday, 16-Feb-2023 00:10:44 JST :blobcathug:

     in reply to

     • :suya:
     • Kumicota :euphohug:
     • Quad ?
     @newt @raccoon @kumicota @quad ah yes, i think we talked about that already, sorry
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Thursday, 16-Feb-2023 00:10:45 JST :suya:

     in reply to

     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @Jain @raccoon @kumicota @quad exactly. I'll do a test run on an offline copy later this week.
     
     My other idea is to do a repack on a replicated slave copy of the database, then after it's done, do a switcheroo.
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Thursday, 16-Feb-2023 00:11:25 JST :blobcathug:

     in reply to

     • :suya:
     • Kumicota :euphohug:
     • Quad ?
     @newt @raccoon @kumicota @quad haha, do you think? how big is your oban_jobs table
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Thursday, 16-Feb-2023 00:11:27 JST :suya:

     in reply to

     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @Jain @raccoon @kumicota @quad pleroma effectively has two main tables -- activities and objects. Everything else is negligible when considering space.
   • Embed this notice
     karolat (karolat@stereophonic.space)'s status on Thursday, 16-Feb-2023 00:13:58 JST karolat

     in reply to

     • :suya:
     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @newt @Jain @kumicota @quad @raccoon we could do pg_repack with the --only-indexes option first :blobidea: we probably have enough space for it to complete that operation at least
     :blobcathug: likes this.
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Thursday, 16-Feb-2023 00:14:10 JST :blobcathug:

     in reply to

     • :suya:
     • karolat
     • Kumicota :euphohug:
     • Quad ?
     @karolat @newt @raccoon @kumicota @quad good idea
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Thursday, 16-Feb-2023 00:17:47 JST :blobcathug:

     in reply to

     • :suya:
     • Kumicota :euphohug:
     • Quad ?
     @newt @raccoon @kumicota @quad hmm... thats unexpected low... i thought it might be much higher in your case
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Thursday, 16-Feb-2023 00:17:48 JST :suya:

     in reply to

     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @Jain @raccoon @kumicota @quad 3 gigs. Might need to nuke it, it's useless afaik.
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Friday, 17-Feb-2023 00:08:01 JST :blobcathug:

     in reply to

     • :suya:
     • karolat
     • Kumicota :euphohug:
     • Quad ?
     @karolat @newt @raccoon @kumicota @quad :blobcatrainbow:
   • Embed this notice
     karolat (karolat@stereophonic.space)'s status on Friday, 17-Feb-2023 00:08:02 JST karolat

     in reply to

     • :suya:
     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @newt @Jain @kumicota @quad @raccoon we can run it online though!!!
     :blobcathug: likes this.
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Friday, 17-Feb-2023 00:08:03 JST :suya:

     in reply to

     • karolat
     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @karolat @Jain @kumicota @quad @raccoon might be a good idea. If reindexing will be over a day, I'll consider that.
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Friday, 17-Feb-2023 00:13:23 JST :blobcathug:

     in reply to

     • :suya:
     • karolat
     • Kumicota :euphohug:
     • Quad ?
     @newt @karolat @raccoon @kumicota @quad actually having twice the table size is the worst case, i wouldnt be surprised when it only takes ~1.2 times the space
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Friday, 17-Feb-2023 00:13:24 JST :suya:

     in reply to

     • karolat
     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @karolat @Jain @kumicota @quad @raccoon are you sure? Because the docs clearly say about space requirements being twice the table size.
     
     There's a copy of the database on our media server. You can download it and try experimenting with pg_repack offline if you want to.
   • Embed this notice
     karolat (karolat@stereophonic.space)'s status on Friday, 17-Feb-2023 00:16:27 JST karolat

     in reply to

     • :suya:
     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @newt @Jain @kumicota @quad @raccoon it says that for the full-table repack. If we just used it on the indexes first I would guess we would only need 2 times the space of the indexes, which I believe we have :blobcatcomfthink:
     :blobcathug: likes this.
   • Embed this notice
     :blobcathug: (jain@blob.cat)'s status on Friday, 17-Feb-2023 00:17:17 JST :blobcathug:

     in reply to

     • :suya:
     • karolat
     • Kumicota :euphohug:
     • Quad ?
     @karolat @newt @raccoon @kumicota @quad i agree, i would just try out repack all the indexes, then all the other tables and then activities and objects
   • Embed this notice
     :suya: (newt@stereophonic.space)'s status on Friday, 17-Feb-2023 00:18:57 JST :suya:

     in reply to

     • karolat
     • :blobcathug:
     • Kumicota :euphohug:
     • Quad ?
     @karolat @Jain @kumicota @quad @raccoon welp... you can try it out on your copy. If it works. we'll do that on stereophonic :comfyfingerguns:
     :blobcathug: likes this.