Conversation

Notices

LS (lain@lain.com)'s status on Sunday, 12-Feb-2023 02:27:51 JST LS

you might not know this, but pleroma includes a live dashboard with information about your server at /phoenix/live_dashboard. You'll need to be an admin to view it, though.

In conversation Sunday, 12-Feb-2023 02:27:51 JST from lain.com permalink
- HW Y2K-CHECKED FUNGUS (duponin@udongein.xyz)'s status on Sunday, 12-Feb-2023 14:23:19 JST HW Y2K-CHECKED FUNGUS
  in reply to
  
  @lain nice
  we really have to find a way to delete useless activities, 69GB of activities
  
  In conversation Sunday, 12-Feb-2023 14:23:19 JST permalink
  
  LS likes this.
- LS (lain@lain.com)'s status on Sunday, 12-Feb-2023 14:25:09 JST LS
  in reply to
  - HW Y2K-CHECKED FUNGUS
  @duponin you can just delete them en masse with psql
  
  In conversation Sunday, 12-Feb-2023 14:25:09 JST permalink
- ilja :pumpkin_owo: (ilja@ilja.space)'s status on Sunday, 12-Feb-2023 23:53:10 JST ilja :pumpkin_owo:
  in reply to
  - HW Y2K-CHECKED FUNGUS
  - Shrek
  @duponin @lain @sarvo You'll never going to believe this, but https://akkoma.dev/AkkomaGang/akkoma/pulls/455 :blobfoxsmirk: (should make restoration of DB significantly faster)
  In conversation Sunday, 12-Feb-2023 23:53:10 JST permalink
  Attachments
  1. Rename index for faster database restore
    
    from AkkomaGang
    
    fix https://akkoma.dev/AkkomaGang/akkoma/issues/440 I manually changed the index name. On a 10G dump it took ~30min to restore with this rename. I don't know exactly how long it took without renaming the index, but I tried and cancelled the operation after it was already stuck on the problematic index for over 1h. I also saw that the renamed index is indeed the very first index that's being restored (and before the "problematic" activities_visibility_index). Renaming the index: ```sh sudo -Hu postgres psql -d akkoma -c "ALTER INDEX public.\"users_ap_id_COALESCE_follower_address_index\" RENAME TO \"aa_users_ap_id_COALESCE_follower_address_index\";" ``` It's a bit dirty because we go away from naming conventions, but if it works, I think it's worth it. * [x] Write a migration for it (and make it clear in a comment why we do it) * [x] See that new installations can still create a DB from scratch (sometimes you need things outside of transactions, I'm unsure if this is also such one) * [x] Adapt docs (backup-restore doesn't need the note for this any more, maybe also add to changelog) * [x] Run in prod 🐶 * Cherry picked the commits, ran the migration and restarted my instance. Things still work * Stopped instance, did db dump, extracted the list to see the order of indexes, the renamed one is indeed the first. * Dropped database, recreate database, restore from dump. We see that the renamed index is restored as first index[1]. DB is 3G in size, restoration took less then 10 minutes. Afterwards I restarted the instance and things seem to still run smoothly. [1] Excerpt from the output from restoring the DB. We see the renamed index being the first index restored (2nd output line), and the "problematic" visibility index only later (last output line of this excerpt). ``` ... pg_restore: creating CONSTRAINT "public.users users_pkey" pg_restore: creating INDEX "public.aa_users_ap_id_COALESCE_follower_address_index" pg_restore: creating INDEX "public.activities_actor_id_DESC_NULLS_LAST_index" pg_restore: creating INDEX "public.activities_cc_index" pg_restore: creating INDEX "public.activities_context_index" pg_restore: creating INDEX "public.activities_create_objects_index" pg_restore: creating INDEX "public.activities_hosts" pg_restore: creating INDEX "public.activities_id_desc_nulls_last_local_index" pg_restore: creating INDEX "public.activities_in_reply_to" pg_restore: creating INDEX "public.activities_likes" pg_restore: creating INDEX "public.activities_local_index" pg_restore: creating INDEX "public.activities_recipients_index" pg_restore: creating INDEX "public.activities_to_index" pg_restore: creating INDEX "public.activities_unique_apid_index" pg_restore: creating INDEX "public.activities_visibility_index" ... ```
  LS likes this.
- HW Y2K-CHECKED FUNGUS (duponin@udongein.xyz)'s status on Sunday, 12-Feb-2023 23:53:11 JST HW Y2K-CHECKED FUNGUS
  in reply to
  - ilja :pumpkin_owo:
  - Shrek
  @ilja @lain @sarvo I should try it but Udongein's DB being such a burning ship, I don't want to try in production
  restoring the database takes ages (almost two weeks on my spare server, 22% restored)
  
  In conversation Sunday, 12-Feb-2023 23:53:11 JST permalink
- ilja :pumpkin_owo: (ilja@ilja.space)'s status on Sunday, 12-Feb-2023 23:53:14 JST ilja :pumpkin_owo:
  in reply to
  - HW Y2K-CHECKED FUNGUS
  - Shrek
  @duponin @lain @sarvo https://akkoma.dev/AkkomaGang/akkoma/pulls/427
  In conversation Sunday, 12-Feb-2023 23:53:14 JST permalink
  Attachments
  1. Allow the prune_objects task to delete orphaned activities
    
    from AkkomaGang
    
    This builds further on https://akkoma.dev/AkkomaGang/akkoma/pulls/350 , but i made it a separate PR so we can keep changes smaller in the hope that this makes reviewing easier. Do let me know if it's better to keep the whole thing as one PR. After pruning objects, we still have a whole bunch of activities that don't link to anything existing any more. Here we find and delete those activities. * [x] Make a first working thing * [x] Make it work for also object-arrays * [x] Dogfood (woof woof) * The first run, it had to prune 2+ years worth of activities and took over 24h to complete * I then ran on a daily basis and it takes 20-30min each time * After vacuum full, my DB size went from ~10G to ~3G 🎉 * [x] Docs * [x] While we're at it, improve the docs to explain the vacuum full and mention autovacuum (could also be a separate MR). See https://akkoma.dev/AkkomaGang/akkoma/issues/436#issuecomment-6948 * https://docs.akkoma.dev/develop/administration/CLI_tasks/database/#prune-old-remote-posts-from-the-database * https://docs.akkoma.dev/develop/configuration/postgresql/ * Autovacumm should be on by default according to Postgresql docs, so it doesn't seem like something I should add here. * Maybe other places where it makes sense... * Also added it to the vacuum task. Otherwise the only postgresql references I see in the docs are in the install guides. I made this an option because it may cause extra load, time, and risk. Note that I also check what tables we have in the DB. As explained in the comments of the test, this is because the objects that the activities point to can be stored in different tables. Currently I see three of them, but it's possible we add more tables in the future. If those tables also hold objects and we don't check there, we may delete activities who shouldn't be deleted. For that reason I make a test fail when a new table is added.
- HW Y2K-CHECKED FUNGUS (duponin@udongein.xyz)'s status on Sunday, 12-Feb-2023 23:53:15 JST HW Y2K-CHECKED FUNGUS
  in reply to
  - Shrek
  @sarvo @lain it's only pruning objects, not activities
  
  In conversation Sunday, 12-Feb-2023 23:53:15 JST permalink
- Shrek (sarvo@novoa.nagoya)'s status on Sunday, 12-Feb-2023 23:53:18 JST Shrek
  in reply to
  - HW Y2K-CHECKED FUNGUS
  @duponin@udongein.xyz @lain@lain.com I thought the prune function was for that?
  
  In conversation Sunday, 12-Feb-2023 23:53:18 JST permalink
- ilja :pumpkin_owo: (ilja@ilja.space)'s status on Sunday, 12-Feb-2023 23:55:35 JST ilja :pumpkin_owo:
  in reply to
  - HW Y2K-CHECKED FUNGUS
  - Shrek
  @duponin @lain @sarvo the first one is quite simple. Activities link to objects. But there are three different tables holding objects. There's `activities`, `objects`, and `users`. So it links each activity to these tables. If an activity doesn't have an object in any of the tables, the activity can be deleted.
  
  The other one is building further on something p figured out, see https://blog.freespeechextremist.com/blog/activities-visibility-index-slowness.html
  "Essentially, the activities_visibility_index requires a full-table scan of the users table for each activity. I wrote another index to fix this and it has been merged, but the problem when you're doing a dump and restore is that the activities_visibility_index gets built first, so it can't avail itself of the other index."
  I just renamed the index that p made so that it gets restored first.
  In conversation Sunday, 12-Feb-2023 23:55:35 JST permalink
  Attachments
  1. No result found on File_thumbnail lookup.
    
    activities_visibility_index Slowness — FSE Blog
- HW Y2K-CHECKED FUNGUS (duponin@udongein.xyz)'s status on Sunday, 12-Feb-2023 23:55:36 JST HW Y2K-CHECKED FUNGUS
  in reply to
  - ilja :pumpkin_owo:
  - Shrek
  @ilja @lain @sarvo *pondering the psql*
  
  In conversation Sunday, 12-Feb-2023 23:55:36 JST permalink

Public

Conversation

Notices

Feeds