Notices by ilja :pumpkin_owo: (ilja@ilja.space), page 2

ilja :pumpkin_owo: (ilja@ilja.space)'s status on Monday, 13-Feb-2023 00:06:48 JST ilja :pumpkin_owo:
in reply to
@lain @sarvo @duponin Not that I could find. What I did find is that you can pass a list with what you want to restore and in what order. But it still takes some extra steps and even with these steps added to the docs, people still just did a dump and restore like they were used to (and then understandably still complained that restoration goes super slow).
To get the list
pg_restore -l pleroma.pgdump > db.list
Then find the line where the index is restored and move it. Then restore using the new list with
sudo -Hu postgres pg_restore -L db.list -d pleroma -v -1 pleroma.pgdump

In conversation Monday, 13-Feb-2023 00:06:48 JST from ilja.space permalink
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Monday, 13-Feb-2023 00:06:46 JST ilja :pumpkin_owo:
in reply to
@lain @duponin @sarvo I was also thinking for a while that maybe postgresql should be more smarter in dumping, but then I realised that this is really just a very edge-case thing, so I didn't bother filing a feature request for it.

In conversation Monday, 13-Feb-2023 00:06:46 JST from ilja.space permalink
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Sunday, 12-Feb-2023 23:55:35 JST ilja :pumpkin_owo:
in reply to
@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 from ilja.space permalink
Attachments
1. No result found on File_thumbnail lookup.
  
  activities_visibility_index Slowness — FSE Blog
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Sunday, 12-Feb-2023 23:53:14 JST ilja :pumpkin_owo:
in reply to
@duponin @lain @sarvo https://akkoma.dev/AkkomaGang/akkoma/pulls/427
In conversation Sunday, 12-Feb-2023 23:53:14 JST from ilja.space 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.
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Sunday, 12-Feb-2023 23:53:10 JST ilja :pumpkin_owo:
in reply to
@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 from ilja.space 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" ... ```
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Sunday, 12-Feb-2023 02:33:57 JST ilja :pumpkin_owo:

There's a masto instance whith 150 users and they ask a monthly fee to get an account, and that fee is more expensive than getting your own instance from masto.host :/
I swear, sometimes you just have to be not afraid to ask money, and we're all idiots for not doing that…

In conversation Sunday, 12-Feb-2023 02:33:57 JST from ilja.space permalink
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Sunday, 12-Feb-2023 02:33:53 JST ilja :pumpkin_owo:
in reply to
- 「 Fristi 」
@fristi Oh, it gets even better :) From the about page:

"For December 2022 and January 2023, [Name of instance] is being generously sponsored by our hosts, [Name of host]! "

In conversation Sunday, 12-Feb-2023 02:33:53 JST from ilja.space permalink
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Sunday, 12-Feb-2023 02:33:50 JST ilja :pumpkin_owo:
in reply to
- ilja :pumpkin_owo:
- 「 Fristi 」
@fristi Anyhow, regardless, people know what they get and they can always move if they want, so it's not that I think this is bad or anything. But it is a bit surprising to me that this actually works. But that's really more a "me" problem than a "them" problem.

In conversation Sunday, 12-Feb-2023 02:33:50 JST from ilja.space permalink
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Saturday, 11-Feb-2023 00:43:19 JST ilja :pumpkin_owo:

"skunk" in Dutch is "stinkdier", which literally translates to "stinky animal"

In conversation Saturday, 11-Feb-2023 00:43:19 JST from ilja.space permalink
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Friday, 10-Feb-2023 12:02:10 JST ilja :pumpkin_owo:
in reply to
- LS
@lain cosmos has a postgresql api, so that's three :blobfox3c: (not sure how "complete" it is, though)

In conversation Friday, 10-Feb-2023 12:02:10 JST from ilja.space permalink
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Thursday, 09-Feb-2023 14:42:57 JST ilja :pumpkin_owo:
in reply to
- LS
@lain Sounds about right. I have a rule-of-thumb that (assuming 2% inflation yearly) prices double every ~30 year (every 35 year to be more precise). Which is quite wild when you think about it, because that means that by the time you retire, you've seen prices go ~ times 4 in your life!

In conversation Thursday, 09-Feb-2023 14:42:57 JST from ilja.space permalink
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Tuesday, 07-Feb-2023 12:08:16 JST ilja :pumpkin_owo:

i now have a :mozart: emoji \o/

In conversation Tuesday, 07-Feb-2023 12:08:16 JST from ilja.space permalink
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Sunday, 05-Feb-2023 01:32:21 JST ilja :pumpkin_owo:

on fedi, everyone is subtitled :blobfoxuwu:

In conversation Sunday, 05-Feb-2023 01:32:21 JST from ilja.space permalink
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Saturday, 04-Feb-2023 10:27:08 JST ilja :pumpkin_owo:

how can companies even have intellectual property, they aren't sentient, they don't have intelect

In conversation Saturday, 04-Feb-2023 10:27:08 JST from ilja.space permalink
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Friday, 03-Feb-2023 06:26:26 JST ilja :pumpkin_owo:
in reply to
- LS
@lain lain is responsible for mutti, not mutti herself :blobfox3cevil:

In conversation Friday, 03-Feb-2023 06:26:26 JST from ilja.space permalink
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Friday, 03-Feb-2023 01:59:34 JST ilja :pumpkin_owo:

It's important for adults to plan ahead in life. I have therefore decide to plan tomorrows breakfast be ice cream :blobfoxnomicecream:

In conversation Friday, 03-Feb-2023 01:59:34 JST from ilja.space permalink
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Saturday, 28-Jan-2023 15:10:45 JST ilja :pumpkin_owo:

people sometimes talk about fedi as if things happen in waves. But sometimes people also talk about fedi having momentum. This proves that fedi is actually not just a wave, but also a particle :blobfoxuwu:

In conversation Saturday, 28-Jan-2023 15:10:45 JST from ilja.space permalink
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Friday, 27-Jan-2023 01:40:23 JST ilja :pumpkin_owo:

I'm ilja professionally :blobfoxuwu:

RE: https://prodromou.pub/users/evan/statuses/109747539711745258
In conversation Friday, 27-Jan-2023 01:40:23 JST from ilja.space permalink
Attachments
1. Evan Prodromou (@evan@prodromou.pub)
  
  from Evan Prodromou
  
  So many people are on fediverse instances related to their profession that it feels like we're medieval English villagers with last names matching our jobs. Dick Baker? He's a baker. Jane Carpenter? She's a carpenter. dick@infosec.exchange? He's in infosec. jane@hci.social? She does HCI.
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Friday, 27-Jan-2023 01:38:51 JST ilja :pumpkin_owo:

periodic reminder that #yunohost is awesome.

I wanted to get some inspiration on writing some docs aimed at (new) developers, and just look at what ynh has https://yunohost.org/en/dev It's inviting, it tells a story, it's clear, it points to all the right resources from the start. I really love this
In conversation Friday, 27-Jan-2023 01:38:51 JST from ilja.space permalink
Attachments
1. No result found on File_thumbnail lookup.
  
  Contributing to the YunoHost core | Yunohost Documentation
  
  Yunohost Documentation
ilja :pumpkin_owo: (ilja@ilja.space)'s status on Sunday, 22-Jan-2023 05:58:28 JST ilja :pumpkin_owo:

I added a thing to the prune_objects job to also prune orphaned activities (read: activities who don't have any corresponding object any more).

Last week I ran it and it ran for over 24h, oops. But it had to prune 2 years worth of orphaned activities, so there's that.

Today I ran it again and it only ran ~30minutes.

Now I changed the retention days parameter to keep remote posts with no interaction on the thread for no more than ~6 months (which is probably a lot already) and I have a crontab to run it daily. I want to run it for a while to see I don't have nasty side effects and just to see how it runs, but if all goes well, I can take it out of wip soon 🎉

This should help in keeping DB sizes lower without real loss of quality.

In conversation Sunday, 22-Jan-2023 05:58:28 JST from ilja.space permalink

After
Before

Public

Notices by ilja :pumpkin_owo: (ilja@ilja.space), page 2

User actions

Following 0

Followers 0

Groups 0

Statistics

Feeds