Conversation

Notices

Embed this notice
on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ (lain@lain.com)'s status on Sunday, 12-Feb-2023 02:28:00 JST on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ

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:28:00 JST from lain.com permalink
- Embed this notice
  your new favourite fungus (duponin@udongein.xyz)'s status on Sunday, 12-Feb-2023 14:23:38 JST your new favourite 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:38 JST permalink
  
  on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ likes this.
- Embed this notice
  on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ (lain@lain.com)'s status on Sunday, 12-Feb-2023 14:25:19 JST on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ
  in reply to
  - your new favourite fungus
  @duponin you can just delete them en masse with psql
  
  In conversation Sunday, 12-Feb-2023 14:25:19 JST permalink
- Embed this notice
  ilja (ilja@ilja.space)'s status on Sunday, 12-Feb-2023 23:53:27 JST ilja
  in reply to
  - Sarvo
  - your new favourite fungus
  @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:27 JST permalink
  Attachments
  1. Domain not in remote thumbnail source whitelist: akkoma.dev
    
    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" ... ```
  on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ likes this.
- Embed this notice
  your new favourite fungus (duponin@udongein.xyz)'s status on Sunday, 12-Feb-2023 23:53:29 JST your new favourite fungus
  in reply to
  - ilja
  - Sarvo
  @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:29 JST permalink
- Embed this notice
  ilja (ilja@ilja.space)'s status on Sunday, 12-Feb-2023 23:53:30 JST ilja
  in reply to
  - Sarvo
  - your new favourite fungus
  @duponin @lain @sarvo https://akkoma.dev/AkkomaGang/akkoma/pulls/427
  In conversation Sunday, 12-Feb-2023 23:53:30 JST permalink
  Attachments
  1. Domain not in remote thumbnail source whitelist: akkoma.dev
    
    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.
- Embed this notice
  your new favourite fungus (duponin@udongein.xyz)'s status on Sunday, 12-Feb-2023 23:53:31 JST your new favourite fungus
  in reply to
  - Sarvo
  @sarvo @lain it's only pruning objects, not activities
  
  In conversation Sunday, 12-Feb-2023 23:53:31 JST permalink
- Embed this notice
  Sarvo (sarvo@novoa.nagoya)'s status on Sunday, 12-Feb-2023 23:53:33 JST Sarvo
  in reply to
  - your new favourite fungus
  @duponin@udongein.xyz @lain@lain.com I thought the prune function was for that?
  
  In conversation Sunday, 12-Feb-2023 23:53:33 JST permalink