pleroma issueshttps://git.pleroma.social/pleroma/pleroma/-/issues2024-03-13T13:12:48Zhttps://git.pleroma.social/pleroma/pleroma/-/issues/3218Slow notifications when instance blocking2024-03-13T13:12:48ZMatthieu RakotojaonaSlow notifications when instance blockingFirst of all, thanks a lot for Pleroma ! It's nice being able to run an ActivityPub software that doesn't eat all your resources and actually speaks the protocol.
### Environment
* Installation type (OTP or From Source): yunohost, so I...First of all, thanks a lot for Pleroma ! It's nice being able to run an ActivityPub software that doesn't eat all your resources and actually speaks the protocol.
### Environment
* Installation type (OTP or From Source): yunohost, so I guess it's a OPT install
* Pleroma version (could be found in the "Version" tab of settings in Pleroma-FE): 255
* Elixir version (`elixir -v` for from source installations, N/A for OTP): N/A
* Operating system: Debian
* PostgreSQL version (`psql -V`): Debian 13.13
### Warning
I think it's ok to put my own id, but there's also some accounts/instances that I block, for a good reason (racist stuff). I advise not checking them out.
### Bug description
I've been having slow (5-10s) notifications for a few weeks now, so I started investigating and it seems to be the database.
After setting the logs to :debug here's the relevant section:
```
ec 12 16:11:18 rako.space pleroma[2268146]: 16:11:18.737 request_id=F6AheyPiJNo6PgUA_B2x [debug] QUERY OK source="notifications" db=6815.4ms queue=0.4ms idle=1556.1ms
Dec 12 16:11:18 rako.space pleroma[2268146]: SELECT n0."id", n0."seen", n0."type", n0."user_id", n0."activity_id", n0."inserted_at", n0."updated_at", a1."id", a1."data", a1."local", a1."actor", a1."recipients", a1."inserted_at", a1."updated_at", o2."id", o2."data", o2."inserted_at", o2."updated_at" FROM "notifications" AS n0 INNER JOIN "activities" AS a1 ON a1."id" = n0."activity_id" LEFT OUTER JOIN "objects" AS o2 ON (o2."data"->>'id') = associated_object_id(a1."data") INNER JOIN "users" AS u3 ON u3."ap_id" = a1."actor" LEFT OUTER JOIN "thread_mutes" AS t4 ON (t4."user_id" = $1) AND (t4."context" = a1."data"->>'context') WHERE (n0."user_id" = $2) AND (u3."is_active") AND (NOT (a1."actor" = ANY($3))) AND (t4."user_id" IS NULL) AND (NOT (a1."actor" = ANY($4))) AND (NOT (substring(a1."actor" from '.*://([^/]*)') = ANY($5)) OR
Dec 12 16:11:18 rako.space pleroma[2268146]: a1."actor" = ANY(SELECT ap_id FROM users AS u INNER JOIN following_relationships AS fr
Dec 12 16:11:18 rako.space pleroma[2268146]: ON u.id = fr.following_id WHERE fr.follower_id = $6 AND fr.state = $7)
Dec 12 16:11:18 rako.space pleroma[2268146]: ) AND (n0."type" = ANY($8)) ORDER BY n0."id" desc nulls last LIMIT $9 ["ADVMFpVkzRniZTxHqC", "ADVMFpVkzRniZTxHqC", [], ["https://toot.community/users/pmroman", "https://pieville.net/users/charliebrownau"], ["pieville.net"], <<0, 0, 1, 125, 47, 220, 171, 120, 78, 29, 208, 24, 89, 222, 0, 0>>, 2, ["mention", "follow", "follow_request", "reblog", "favourite", "move", "pleroma:emoji_reaction", "poll", "update"], 20]
```
Which, reordered and with fields filled in, gives this (I assume the binary list is my id, because I don't know how to turn it into an id):
```
SELECT
n0."id",
n0."seen",
n0."type",
n0."user_id",
n0."activity_id",
n0."inserted_at",
n0."updated_at",
a1."id",
a1."data",
a1."local",
a1."actor",
a1."recipients",
a1."inserted_at",
a1."updated_at",
o2."id",
o2."data",
o2."inserted_at",
o2."updated_at"
FROM
"notifications" AS n0
INNER JOIN "activities" AS a1 ON a1."id" = n0."activity_id"
LEFT OUTER JOIN "objects" AS o2 ON (o2."data" ->> 'id') = associated_object_id(a1."data")
INNER JOIN "users" AS u3 ON u3."ap_id" = a1."actor"
LEFT OUTER JOIN "thread_mutes" AS t4 ON (t4."user_id" = '0000017d-2fdc-ab78-4e1d-d01859de0000')
AND (
t4."context" = a1."data" ->> 'context'
)
WHERE
(n0."user_id" = '0000017d-2fdc-ab78-4e1d-d01859de0000')
AND (u3."is_active")
AND (
NOT (
a1."actor" = ANY(ARRAY[''::text])
)
)
AND (t4."user_id" IS NULL)
AND (
NOT (
a1."actor" = ANY(ARRAY['https://toot.community/users/pmroman', 'https://pieville.net/users/charliebrownau'])
)
)
AND (
NOT (
substring(
a1."actor"
from
'.*://([^/]*)'
) = ANY(ARRAY['pieville.net'])
)
OR a1."actor" = ANY(
SELECT
ap_id
FROM
users AS u
INNER JOIN following_relationships AS fr ON u.id = fr.following_id
WHERE
fr.follower_id = '0000017d-2fdc-ab78-4e1d-d01859de0000'
AND fr.state = 2
)
)
AND (
n0."type" = ANY(ARRAY['mention'::notification_type, 'follow'::notification_type, 'follow_request'::notification_type, 'reblog'::notification_type, 'favourite'::notification_type, 'move'::notification_type, 'pleroma:emoji_reaction'::notification_type, 'poll'::notification_type, 'update'])
)
ORDER BY
n0."id" desc nulls last
LIMIT
20;
```
When run in my db it is indeed slow, here is the EXPLAIN ANALYZE VERBOSE of it:
```
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=127134.34..127134.39 rows=20 width=2051) (actual time=7208.379..7208.388 rows=20 loops=1)
Output: n0.id, n0.seen, n0.type, n0.user_id, n0.activity_id, n0.inserted_at, n0.updated_at, a1.id, a1.data, a1.local, a1.actor, a1.recipients, a1.inserted_at, a1.updated_at, o2.id, o2.data, o2.inserted_at, o2.updated_at
-> Sort (cost=127134.34..127135.50 rows=465 width=2051) (actual time=7123.124..7123.131 rows=20 loops=1)
Output: n0.id, n0.seen, n0.type, n0.user_id, n0.activity_id, n0.inserted_at, n0.updated_at, a1.id, a1.data, a1.local, a1.actor, a1.recipients, a1.inserted_at, a1.updated_at, o2.id, o2.data, o2.inserted_at, o2.updated_at
Sort Key: n0.id DESC NULLS LAST
Sort Method: top-N heapsort Memory: 161kB
-> Nested Loop Anti Join (cost=2663.89..127121.96 rows=465 width=2051) (actual time=4.663..7116.180 rows=2670 loops=1)
Output: n0.id, n0.seen, n0.type, n0.user_id, n0.activity_id, n0.inserted_at, n0.updated_at, a1.id, a1.data, a1.local, a1.actor, a1.recipients, a1.inserted_at, a1.updated_at, o2.id, o2.data, o2.inserted_at, o2.updated_at
Join Filter: ((t4.context)::text = (a1.data ->> 'context'::text))
-> Nested Loop Left Join (cost=2663.75..127105.66 rows=465 width=2051) (actual time=4.651..7112.875 rows=2670 loops=1)
Output: n0.id, n0.seen, n0.type, n0.user_id, n0.activity_id, n0.inserted_at, n0.updated_at, a1.id, a1.data, a1.local, a1.actor, a1.recipients, a1.inserted_at, a1.updated_at, o2.id, o2.data, o2.inserted_at, o2.updated_at
Inner Unique: true
-> Nested Loop (cost=2662.95..126472.83 rows=465 width=750) (actual time=3.593..6999.082 rows=2670 loops=1)
Output: n0.id, n0.seen, n0.type, n0.user_id, n0.activity_id, n0.inserted_at, n0.updated_at, a1.id, a1.data, a1.local, a1.actor, a1.recipients, a1.inserted_at, a1.updated_at
Inner Unique: true
-> Hash Join (cost=2662.52..125300.95 rows=1828 width=750) (actual time=3.511..6921.999 rows=2676 loops=1)
Output: n0.id, n0.seen, n0.type, n0.user_id, n0.activity_id, n0.inserted_at, n0.updated_at, a1.id, a1.data, a1.local, a1.actor, a1.recipients, a1.inserted_at, a1.updated_at
Hash Cond: (a1.id = n0.activity_id)
-> Seq Scan on public.activities a1 (cost=2532.70..121191.40 rows=1056385 width=689) (actual time=0.177..6600.496 rows=1055736 loops=1)
Output: a1.id, a1.data, a1.inserted_at, a1.updated_at, a1.local, a1.actor, a1.recipients
Filter: (((a1.actor)::text <> ALL ('{""}'::text[])) AND ((a1.actor)::text <> ALL ('{https://toot.community/users/pmroman,https://pieville.net/users/charliebrownau}'::text[])) AND (("substring"((a1.actor)::text, '.*://([^/]*)'::text) <> ALL ('{pieville.net}'::text[])) OR (hashed SubPlan 1)))
Rows Removed by Filter: 9
SubPlan 1
-> Nested Loop (cost=0.42..2531.92 rows=313 width=38) (never executed)
Output: u.ap_id
Inner Unique: true
-> Seq Scan on public.following_relationships fr (cost=0.00..14.20 rows=313 width=16) (never executed)
Output: fr.id, fr.follower_id, fr.following_id, fr.state, fr.inserted_at, fr.updated_at
Filter: ((fr.follower_id = '0000017d-2fdc-ab78-4e1d-d01859de0000'::uuid) AND (fr.state = 2))
-> Index Scan using users_pkey on public.users u (cost=0.42..8.04 rows=1 width=54) (never executed)
Output: u.ap_id, u.id
Index Cond: (u.id = fr.following_id)
-> Hash (cost=106.91..106.91 rows=1833 width=61) (actual time=2.733..2.734 rows=2677 loops=1)
Output: n0.id, n0.seen, n0.type, n0.user_id, n0.activity_id, n0.inserted_at, n0.updated_at
Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 283kB
-> Seq Scan on public.notifications n0 (cost=0.00..106.91 rows=1833 width=61) (actual time=0.028..1.404 rows=2677 loops=1)
Output: n0.id, n0.seen, n0.type, n0.user_id, n0.activity_id, n0.inserted_at, n0.updated_at
Filter: ((n0.user_id = '0000017d-2fdc-ab78-4e1d-d01859de0000'::uuid) AND (n0.type = ANY ('{mention,follow,follow_request,reblog,favourite,move,pleroma:emoji_reaction,poll,update}'::notification_type[])))
Rows Removed by Filter: 92
-> Index Scan using users_ap_id_index on public.users u3 (cost=0.42..0.64 rows=1 width=38) (actual time=0.026..0.026 rows=1 loops=2676)
Output: u3.ap_id
Index Cond: ((u3.ap_id)::text = (a1.actor)::text)
Filter: u3.is_active
Rows Removed by Filter: 0
-> Index Scan using objects_unique_apid_index on public.objects o2 (cost=0.80..1.36 rows=1 width=1301) (actual time=0.027..0.027 rows=1 loops=2670)
Output: o2.id, o2.data, o2.inserted_at, o2.updated_at
Index Cond: ((o2.data ->> 'id'::text) = (associated_object_id(a1.data))::text)
-> Materialize (cost=0.14..8.17 rows=1 width=516) (actual time=0.000..0.000 rows=0 loops=2670)
Output: t4.context
-> Index Only Scan using unique_index on public.thread_mutes t4 (cost=0.14..8.16 rows=1 width=516) (actual time=0.006..0.006 rows=0 loops=1)
Output: t4.context
Index Cond: (t4.user_id = '0000017d-2fdc-ab78-4e1d-d01859de0000'::uuid)
Heap Fetches: 0
Planning Time: 38.492 ms
JIT:
Functions: 49
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 36.191 ms, Inlining 0.000 ms, Optimization 2.982 ms, Emission 81.449 ms, Total 120.623 ms
Execution Time: 7386.114 ms
(59 rows)
```
I tried to hack around and I see it's this specific part that makes the whole thing crawl to a slow:
```
OR a1."actor" = ANY(
SELECT
ap_id
FROM
users AS u
INNER JOIN following_relationships AS fr ON u.id = fr.following_id
WHERE
fr.follower_id = '0000017d-2fdc-ab78-4e1d-d01859de0000'
AND fr.state = 2
)
```
When I remove it and run it, it is fast again. Here's the EXPLAIN ANALYZE VERBOSE:
```
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2.07..705.06 rows=20 width=2051) (actual time=0.239..2.793 rows=20 loops=1)
Output: n0.id, n0.seen, n0.type, n0.user_id, n0.activity_id, n0.inserted_at, n0.updated_at, a1.id, a1.data, a1.local, a1.actor, a1.recipients, a1.inserted_at, a1.updated_at, o2.id, o2.data, o2.inserted_at, o2.updated_at
-> Nested Loop Anti Join (cost=2.07..16311.38 rows=464 width=2051) (actual time=0.237..2.784 rows=20 loops=1)
Output: n0.id, n0.seen, n0.type, n0.user_id, n0.activity_id, n0.inserted_at, n0.updated_at, a1.id, a1.data, a1.local, a1.actor, a1.recipients, a1.inserted_at, a1.updated_at, o2.id, o2.data, o2.inserted_at, o2.updated_at
Join Filter: ((t4.context)::text = (a1.data ->> 'context'::text))
-> Nested Loop Left Join (cost=1.93..16295.10 rows=464 width=2051) (actual time=0.229..2.755 rows=20 loops=1)
Output: n0.id, n0.seen, n0.type, n0.user_id, n0.activity_id, n0.inserted_at, n0.updated_at, a1.id, a1.data, a1.local, a1.actor, a1.recipients, a1.inserted_at, a1.updated_at, o2.id, o2.data, o2.inserted_at, o2.updated_at
Inner Unique: true
-> Nested Loop (cost=1.13..15663.00 rows=464 width=750) (actual time=0.118..1.300 rows=20 loops=1)
Output: n0.id, n0.seen, n0.type, n0.user_id, n0.activity_id, n0.inserted_at, n0.updated_at, a1.id, a1.data, a1.local, a1.actor, a1.recipients, a1.inserted_at, a1.updated_at
Inner Unique: true
-> Nested Loop (cost=0.71..14492.76 rows=1824 width=750) (actual time=0.085..0.747 rows=20 loops=1)
Output: n0.id, n0.seen, n0.type, n0.user_id, n0.activity_id, n0.inserted_at, n0.updated_at, a1.id, a1.data, a1.local, a1.actor, a1.recipients, a1.inserted_at, a1.updated_at
Inner Unique: true
-> Index Scan using notifications_id_desc_nulls_last_index on public.notifications n0 (cost=0.28..259.33 rows=1833 width=61) (actual time=0.024..0.061 rows=20 loops=1)
Output: n0.id, n0.user_id, n0.activity_id, n0.seen, n0.inserted_at, n0.updated_at, n0.type
Filter: ((n0.user_id = '0000017d-2fdc-ab78-4e1d-d01859de0000'::uuid) AND (n0.type = ANY ('{mention,follow,follow_request,reblog,favourite,move,pleroma:emoji_reaction,poll,update}'::notification_type[])))
Rows Removed by Filter: 1
-> Index Scan using activities_pkey on public.activities a1 (cost=0.43..7.77 rows=1 width=689) (actual time=0.032..0.032 rows=1 loops=20)
Output: a1.id, a1.data, a1.inserted_at, a1.updated_at, a1.local, a1.actor, a1.recipients
Index Cond: (a1.id = n0.activity_id)
Filter: (((a1.actor)::text <> ALL ('{""}'::text[])) AND ((a1.actor)::text <> ALL ('{https://toot.community/users/pmroman,https://pieville.net/users/charliebrownau}'::text[])) AND ("substring"((a1.actor)::text, '.*://([^/]*)'::text) <> ALL ('{pieville.net}'::text[])))
-> Index Scan using users_ap_id_index on public.users u3 (cost=0.42..0.64 rows=1 width=38) (actual time=0.026..0.026 rows=1 loops=20)
Output: u3.ap_id
Index Cond: ((u3.ap_id)::text = (a1.actor)::text)
Filter: u3.is_active
-> Index Scan using objects_unique_apid_index on public.objects o2 (cost=0.80..1.36 rows=1 width=1301) (actual time=0.054..0.054 rows=1 loops=20)
Output: o2.id, o2.data, o2.inserted_at, o2.updated_at
Index Cond: ((o2.data ->> 'id'::text) = (associated_object_id(a1.data))::text)
-> Materialize (cost=0.14..8.17 rows=1 width=516) (actual time=0.001..0.001 rows=0 loops=20)
Output: t4.context
-> Index Only Scan using unique_index on public.thread_mutes t4 (cost=0.14..8.16 rows=1 width=516) (actual time=0.004..0.004 rows=0 loops=1)
Output: t4.context
Index Cond: (t4.user_id = '0000017d-2fdc-ab78-4e1d-d01859de0000'::uuid)
Heap Fetches: 0
Planning Time: 3.238 ms
Execution Time: 2.903 ms
(37 rows)
```
I don't know enough about the project and Elixir to be able to fix it myself right now, although I do wish I could. I also see there are some related issues ("slow query") but it doesn't look like they're related to notifications.
If anyone can have a look, thanks in advance !https://git.pleroma.social/pleroma/pleroma/-/issues/2966beam.smp eating cpu and ram2023-05-08T00:29:46Ztwlbeam.smp eating cpu and ram<!--
### Precheck
* For support use https://git.pleroma.social/pleroma/pleroma-support or [community channels](https://git.pleroma.social/pleroma/pleroma#community-channels).
* Please do a quick search to ensure no similar bug has been ...<!--
### Precheck
* For support use https://git.pleroma.social/pleroma/pleroma-support or [community channels](https://git.pleroma.social/pleroma/pleroma#community-channels).
* Please do a quick search to ensure no similar bug has been reported before. If the bug has not been addressed after 2 weeks, it's fine to bump it.
* Try to ensure that the bug is actually related to the Pleroma backend. For example, if a bug happens in Pleroma-FE but not in Mastodon-FE or mobile clients, it's likely that the bug should be filed in [Pleroma-FE](https://git.pleroma.social/pleroma/pleroma-fe/issues/new) repository.
-->
### Environment
* Installation type: Source
* Pleroma version: 2.4.3
* Elixir version: Erlang/OTP 23 [erts-11.1.8] [source] [64-bit] [smp:1:1] [ds:1:1:10] [async-threads:1]; Elixir 1.10.3 (compiled with Erlang/OTP 22)
* Operating system: Debian 11.5
* PostgreSQL version: 13.8
### Bug description
I'm on a vps with one dedicated core (AMD Ryzen 9 3900X) and 4GB of ram, pleroma-fe has become practically unusable, loading profiles, threads and other things cause the beam.smp process to consume up to 3GB of memory and 100% of cpu. Making a post (with or without attachments) or loading a large thread causes it to run out of memory and crash unless i use swap space.
What I've tried so far to troubleshoot is [disabling elixir's busy waiting](https://docs-develop.pleroma.social/backend/configuration/optimizing_beam/#virtual-machine-andor-few-cpu-cores), changed postgresql's settings to not use as much ram, but i haven't seen any improvements.
It was suggested to me to check the oban queue, so here is the output from that:
```
pleroma=# select count((state,args->'params'->'type')), (state,args->'params'->'type') from oban_jobs where state not in ('completed') group by (state,args->'params'->'type') order by count((state,args->'params'->'type'));
count | row
-------+------------------------------
1 | (executing,"""Undo""")
1 | (executing,"""View""")
6 | (executing,"""EmojiReact""")
17 | (discarded,"""Delete""")
18 | (retryable,"""Delete""")
25 | (executing,"""Like""")
42 | (executing,"""Create""")
49 | (executing,"""Delete""")
82 | (available,)
104 | (executing,"""Announce""")
221 | (executing,)
(11 rows)
pleroma=# select worker, count(worker) from oban_jobs where state not in ('completed') group by worker order by count(worker);
worker | count
------------------------------------------+-------
Pleroma.Workers.WebPusherWorker | 3
Pleroma.Workers.PublisherWorker | 54
Pleroma.Workers.Cron.DigestEmailsWorker | 82
Pleroma.Workers.AttachmentsCleanupWorker | 164
Pleroma.Workers.ReceiverWorker | 259
(5 rows)
```
I moved pleroma to this server about 12 days ago, but these issues started only a week ago, I'm not sure if it's relevant but I was not able to restore the database with ``pg_restore`` because it got stuck at ``create INDEX "public.activities_recipients_index"`` letting the CPU run at 100% but nothing was really happening, what i did instead was just rsync the postgres data directory from the old server to the new one and that seemed to work but I'm not sure if that caused any side-effects that's related to this issue.https://git.pleroma.social/pleroma/pleroma/-/issues/2890Local uploader: Make upload directory contain less direct children2023-05-08T01:14:50ZtusooaLocal uploader: Make upload directory contain less direct childrenCurrently the uploader uses a flat directory structure. This means, as more and more files are uploaded, the directory can have up to (number of all uploads) children.
This is not good for some filesystems, like glusterfs, which perform...Currently the uploader uses a flat directory structure. This means, as more and more files are uploaded, the directory can have up to (number of all uploads) children.
This is not good for some filesystems, like glusterfs, which performs pretty slow when I am trying to `ls` under the upload directory.
It turns out other software like mastodon and synapse uses a nested structure, where the files are stored like `a/ab/abc/(full file name)`. This largely reduces possible entries under the first level of the upload directory.https://git.pleroma.social/pleroma/pleroma/-/issues/2793Slow queries2024-02-15T01:07:49ZAlex GleasonSlow queriesUsing !3553 I'm finding some slow queries. Note that I have **omitted Oban queries**, so everything here is running as part of the request/response cycle pretty much.
Tracebacks are against this commit: https://gitlab.com/soapbox-pub/so...Using !3553 I'm finding some slow queries. Note that I have **omitted Oban queries**, so everything here is running as part of the request/response cycle pretty much.
Tracebacks are against this commit: https://gitlab.com/soapbox-pub/soapbox/-/tree/94eb61e8550c441afcfd293190b553373ae0b548
Home timeline:
```
Dec 06 15:24:13 gleasonator mix[171487]: 15:24:13.429 request_id=Fr4zyQTR03rNmNkACO_j [warn] Query took longer than 500ms!
Dec 06 15:24:13 gleasonator mix[171487]: Total time: 353378.574ms
Dec 06 15:24:13 gleasonator mix[171487]: "SELECT a0.\"id\", a0.\"data\", a0.\"local\", a0.\"actor\", a0.\"recipients\", a0.\"inserted_at\", a0.\"updated_at\", b2.\"id\", b2.\"user_id\", b2.\"activity_id\", b2.\"inserted_at\", b2.\"updated_at\", o1.\"id\", o1.\"data\", o1.\"inserted_at\", o1.\"updated_at\", NOT (t3.\"id\" IS NULL) FROM \"activities\" AS a0 INNER JOIN \"objects\" AS o1 ON (o1.\"data\"->>'id') = COALESCE(a0.\"data\"->'object'->>'id', a0.\"data\"->>'object') LEFT OUTER JOIN \"bookmarks\" AS b2 ON (b2.\"user_id\" = $1) AND (b2.\"activity_id\" = a0.\"id\") LEFT OUTER JOIN \"thread_mutes\" AS t3 ON (t3.\"user_id\" = $2) AND (t3.\"context\" = a0.\"data\"->>'context') WHERE ((($3 && a0.\"recipients\")) OR (a0.\"actor\" = $4)) AND (a0.\"data\"->>'type' = ANY($5)) AND (not (a0.\"actor\" = ANY($6))) AND (((not (a0.\"recipients\" && $7)) or a0.\"actor\" = $8)) AND ((recipients_contain_blocked_domains(a0.\"recipients\", $9) = false) or a0.\"actor\" = $10) AND (not (a0.\"data\"->>'type' = 'Announce' and a0.\"data\"->'to' ?| $11)) AND ((not (split_part(a0.\"actor\", '/', 3) = ANY($12))) or a0.\"actor\" = ANY($13)) AND ((not (split_part(o1.\"data\"->>'actor', '/', 3) = ANY($14))) or (o1.\"data\"->>'actor') = ANY($15)) AND (not (a0.\"actor\" = ANY($16))) AND (not (a0.\"data\"->>'type' = 'Announce' and a0.\"data\"->'to' ?| $17)) AND (not (a0.\"actor\" = ANY($18))) AND (not (a0.\"data\"->'to' ?| $19) or a0.\"actor\" = $20) AND (t3.\"user_id\" IS NULL) AND (not ( a0.\"data\"->>'type' = 'Announce' and a0.\"actor\" = ANY($21))) AND (a0.\"data\"->>'type' != 'Announce' or o1.\"data\"->>'actor' != $22) AND (NOT (a0.\"actor\" = ANY($23))) AND (not(o1.\"data\"->>'type' = 'Answer')) AND (not(o1.\"data\"->>'type' = 'ChatMessage')) AND (NOT (a0.\"actor\" = ANY($24))) ORDER BY a0.\"id\" desc nulls last LIMIT $25"
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Pagination, :fetch_paginated, 4,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/pagination.ex', line: 40]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.ActivityPub.ActivityPub, :fetch_activities, 3,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/activity_pub/activity_pub.ex', line: 492]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.TimelineController, :home, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: file: 'lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex',
Dec 06 15:24:13 gleasonator mix[171487]: line: 56
Dec 06 15:24:13 gleasonator mix[171487]: ]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.TimelineController, :action, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: file: 'lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex',
Dec 06 15:24:13 gleasonator mix[171487]: line: 5
Dec 06 15:24:13 gleasonator mix[171487]: ]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.TimelineController, :phoenix_controller_pipeline, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: file: 'lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex',
Dec 06 15:24:13 gleasonator mix[171487]: line: 5
Dec 06 15:24:13 gleasonator mix[171487]: ]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.Endpoint, :plug_builder_call, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/endpoint.ex', line: 5]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.Endpoint, :call, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/endpoint.ex', line: 5]}
Dec 06 15:24:13 gleasonator mix[171487]: ]
```
Home timeline again? This time in StatusView:
```
Dec 06 15:24:13 gleasonator mix[171487]: 15:24:13.451 request_id=Fr4zyQTR03rNmNkACO_j [warn] Query took longer than 500ms!
Dec 06 15:24:13 gleasonator mix[171487]: Total time: 6723.929ms
Dec 06 15:24:13 gleasonator mix[171487]: "SELECT a0.\"id\", a0.\"data\", a0.\"local\", a0.\"actor\", a0.\"recipients\", a0.\"inserted_at\", a0.\"updated_at\", o1.\"id\", o1.\"data\", o1.\"inserted_at\", o1.\"updated_at\" FROM \"activities\" AS a0 INNER JOIN \"objects\" AS o1 ON (o1.\"data\"->>'id') = COALESCE(a0.\"data\"->'object'->>'id', a0.\"data\"->>'object') WHERE (coalesce((a0.\"data\")->'object'->>'id', (a0.\"data\")->>'object') = ANY($1)) AND ((a0.\"data\")->>'type' = $2)"
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.StatusView, :get_replied_to_activities, 1,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/mastodon_api/views/status_view.ex', line: 55]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.StatusView, :render, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/mastodon_api/views/status_view.ex', line: 93]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.TimelineController, :action, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: file: 'lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex',
Dec 06 15:24:13 gleasonator mix[171487]: line: 5
Dec 06 15:24:13 gleasonator mix[171487]: ]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.TimelineController, :phoenix_controller_pipeline, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: file: 'lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex',
Dec 06 15:24:13 gleasonator mix[171487]: line: 5
Dec 06 15:24:13 gleasonator mix[171487]: ]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.Endpoint, :plug_builder_call, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/endpoint.ex', line: 5]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.Endpoint, :call, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/endpoint.ex', line: 5]}
Dec 06 15:24:13 gleasonator mix[171487]: ]
```
More stuff in StatusView...
```
Dec 06 15:24:13 gleasonator mix[171487]: Total time: 8229.138ms
Dec 06 15:24:13 gleasonator mix[171487]: "SELECT a0.\"id\", a0.\"data\", a0.\"local\", a0.\"actor\", a0.\"recipients\", a0.\"inserted_at\", a0.\"updated_at\", b2.\"id\", b2.\"user_id\", b2.\
"activity_id\", b2.\"inserted_at\", b2.\"updated_at\", o1.\"id\", o1.\"data\", o1.\"inserted_at\", o1.\"updated_at\", NOT (t3.\"id\" IS NULL) FROM \"activities\" AS a0 LEFT OUTER JOIN \"obje
cts\" AS o1 ON (o1.\"data\"->>'id') = COALESCE(a0.\"data\"->'object'->>'id', a0.\"data\"->>'object') LEFT OUTER JOIN \"bookmarks\" AS b2 ON (b2.\"user_id\" = $1) AND (b2.\"activity_id\" = a0
.\"id\") LEFT OUTER JOIN \"thread_mutes\" AS t3 ON (t3.\"user_id\" = $2) AND (t3.\"context\" = a0.\"data\"->>'context') WHERE (coalesce((a0.\"data\")->'object'->>'id', (a0.\"data\")->>'objec
t') = ANY($3)) AND ((a0.\"data\")->>'type' = $4)"
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.StatusView, :render, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/mastodon_api/views/status_view.ex', line: 103]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.TimelineController, :action, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: file: 'lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex',
Dec 06 15:24:13 gleasonator mix[171487]: line: 5
Dec 06 15:24:13 gleasonator mix[171487]: ]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.TimelineController, :phoenix_controller_pipeline, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: file: 'lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex',
Dec 06 15:24:13 gleasonator mix[171487]: line: 5
Dec 06 15:24:13 gleasonator mix[171487]: ]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.Endpoint, :plug_builder_call, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/endpoint.ex', line: 5]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.Endpoint, :call, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/endpoint.ex', line: 5]}
Dec 06 15:24:13 gleasonator mix[171487]: ]
```
Oh look, StatusView.
```
Dec 06 15:24:13 gleasonator mix[171487]: Total time: 4183.351ms
Dec 06 15:24:13 gleasonator mix[171487]: "SELECT u0.\"relationship_type\", u0.\"source_id\", u0.\"target_id\" FROM \"user_relationships\" AS u0 WHERE ((source_id = ANY($1) AND target_id = ANY($2) AND relationship_type = ANY($3)) OR (source_id = ANY($4) AND target_id = ANY($5) AND relationship_type = ANY($6)))"
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.UserRelationship, :view_relationships_option, 3,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/user_relationship.ex', line: 170]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.StatusView, :render, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/mastodon_api/views/status_view.ex', line: 120]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.TimelineController, :action, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: file: 'lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex',
Dec 06 15:24:13 gleasonator mix[171487]: line: 5
Dec 06 15:24:13 gleasonator mix[171487]: ]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.TimelineController, :phoenix_controller_pipeline, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: file: 'lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex',
Dec 06 15:24:13 gleasonator mix[171487]: line: 5
Dec 06 15:24:13 gleasonator mix[171487]: ]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.Endpoint, :plug_builder_call, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/endpoint.ex', line: 5]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.Endpoint, :call, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/endpoint.ex', line: 5]}
Dec 06 15:24:13 gleasonator mix[171487]: ]
```
Apparently instance favicons are very slow @lanodan
```
Dec 06 15:24:13 gleasonator mix[171487]: 15:24:13.489 [warn] Query took longer than 500ms!
Dec 06 15:24:13 gleasonator mix[171487]: Total time: 16620.625ms
Dec 06 15:24:13 gleasonator mix[171487]: "SELECT a0.\"id\", a0.\"data\", a0.\"local\", a0.\"actor\", a0.\"recipients\", a0.\"inserted_at\", a0.\"updated_at\", o1.\"id\", o1.\"data\", o1.\"inserted_at\", o1.\"updated_at\" FROM \"activities\" AS a0 LEFT OUTER JOIN \"objects\" AS o1 ON (o1.\"data\"->>'id') = COALESCE(a0.\"data\"->'object'->>'id', a0.\"data\"->>'object') WHERE ((a0.\"data\")->>'id' = $1)"
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.Federator, :perform, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/federator.ex', line: 81]}
Dec 06 15:24:13 gleasonator mix[171487]: ]
Dec 06 15:24:13 gleasonator mix[171487]: 15:24:13.494 request_id=Fr4zyQTR03rNmNkACO_j [warn] Query took longer than 500ms!
Dec 06 15:24:13 gleasonator mix[171487]: Total time: 1814.651ms
Dec 06 15:24:13 gleasonator mix[171487]: "SELECT i0.\"id\", i0.\"host\", i0.\"unreachable_since\", i0.\"favicon\", i0.\"favicon_updated_at\", i0.\"inserted_at\", i0.\"updated_at\" FROM \"instances\" AS i0 WHERE (i0.\"host\" = $1)"
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Instances.Instance, :get_or_update_favicon, 1,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/instances/instance.ex', line: 142]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.AccountView, :do_render, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/mastodon_api/views/account_view.ex', line: 230]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.StatusView, :render, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/mastodon_api/views/status_view.ex', line: 343]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.StatusView, :safe_render, 3,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/mastodon_api/views/status_view.ex', line: 6]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.StatusView, :safe_render_many, 4,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/mastodon_api/views/status_view.ex', line: 6]}
Dec 06 15:24:13 gleasonator mix[171487]: ]
```
StatusView & FollowingRelationship again:
```
Dec 06 15:24:13 gleasonator mix[171487]: 15:24:13.502 request_id=Fr4zyQTR03rNmNkACO_j [warn] Query took longer than 500ms!
Dec 06 15:24:13 gleasonator mix[171487]: Total time: 4029.287ms
Dec 06 15:24:13 gleasonator mix[171487]: "SELECT u1.\"follower_address\" FROM \"following_relationships\" AS f0 INNER JOIN \"users\" AS u1 ON f0.\"following_id\" = u1.\"id\" WHERE (f0.\"follower_id\" = $1) AND (f0.\"state\" = $2)"
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.FollowingRelationship, :following, 1,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/following_relationship.ex', line: 183]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.ActivityPub.Visibility, :visible_for_user?, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/activity_pub/visibility.ex', line: 85]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.StatusView, :render, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/mastodon_api/views/status_view.ex', line: 382]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.StatusView, :safe_render, 3,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/mastodon_api/views/status_view.ex', line: 6]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.StatusView, :safe_render_many, 4,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/mastodon_api/views/status_view.ex', line: 6]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.TimelineController, :action, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: file: 'lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex',
Dec 06 15:24:13 gleasonator mix[171487]: line: 5
Dec 06 15:24:13 gleasonator mix[171487]: ]}
Dec 06 15:24:13 gleasonator mix[171487]: ]
```
SideEffects:
```
Dec 06 15:24:13 gleasonator mix[171487]: 15:24:13.504 [warn] Query took longer than 500ms!
Dec 06 15:24:13 gleasonator mix[171487]: Total time: 1943.692ms
Dec 06 15:24:13 gleasonator mix[171487]: "SELECT o0.\"id\", o0.\"data\", o0.\"inserted_at\", o0.\"updated_at\" FROM \"objects\" AS o0 WHERE ((o0.\"data\")->>'id' = $1)"
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.ActivityPub.SideEffects, :handle, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/activity_pub/side_effects.ex', line: 269]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.ActivityPub.Pipeline, :do_common_pipeline, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/activity_pub/pipeline.ex', line: 50]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.ActivityPub.Pipeline, :common_pipeline, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/activity_pub/pipeline.ex', line: 28]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.ActivityPub.Transmogrifier, :handle_incoming, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/activity_pub/transmogrifier.ex', line: 476]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.Federator, :perform, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/federator.ex', line: 84]}
Dec 06 15:24:13 gleasonator mix[171487]: ]
```
Notifications:
```
Dec 06 15:24:13 gleasonator mix[171487]: Total time: 4561.205ms
Dec 06 15:24:13 gleasonator mix[171487]: "SELECT u0.\"id\", u0.\"bio\", u0.\"raw_bio\", u0.\"email\", u0.\"name\", u0.\"nickname\", u0.\"password_hash\", u0.\"keys\", u0.\"public_key\", u0.\"ap_id\", u0.\"avatar\", u0.\"local\", u0.\"follower_address\", u0.\"following_address\", u0.\"featured_address\", u0.\"tags\", u0.\"last_refreshed_at\", u0.\"last_digest_emailed_at\", u0.\"banner\", u0.\"background\", u0.\"note_count\", u0.\"follower_count\", u0.\"following_count\", u0.\"is_locked\", u0.\"is_confirmed\", u0.\"password_reset_pending\", u0.\"is_approved\", u0.\"registration_reason\", u0.\"confirmation_token\", u0.\"default_scope\", u0.\"domain_blocks\", u0.\"is_active\", u0.\"no_rich_text\", u0.\"ap_enabled\", u0.\"is_moderator\", u0.\"is_admin\", u0.\"show_role\", u0.\"mastofe_settings\", u0.\"uri\", u0.\"hide_followers_count\", u0.\"hide_follows_count\", u0.\"hide_followers\", u0.\"hide_follows\", u0.\"hide_favorites\", u0.\"email_notifications\", u0.\"mascot\", u0.\"emoji\", u0.\"pleroma_settings_store\", u0.\"fields\", u0.\"raw_fields\", u0.\"is_discoverable\", u0.\"invisible\", u0.\"allow_following_move\", u0.\"skip_thread_containment\", u0.\"actor_type\", u0.\"also_known_as\", u0.\"inbox\", u0.\"shared_inbox\", u0.\"accepts_chat_messages\", u0.\"last_active_at\", u0.\"disclose_client\", u0.\"accepts_email_list\", u0.\"pinned_objects\", u0.\"is_suggested\", u0.\"notification_settings\", u0.\"blocks\", u0.\"mutes\", u0.\"muted_reblogs\", u0.\"muted_notifications\", u0.\"subscribers\", u0.\"multi_factor_authentication_settings\", u0.\"inserted_at\", u0.\"updated_at\" FROM \"users\" AS u0 WHERE (u0.\"ap_id\" = ANY($1)) AND (NOT (u0.\"nickname\" IS NULL)) AND (NOT (u0.\"nickname\" LIKE 'internal.%')) AND (u0.\"local\" = $2)"
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Notification, :get_notified_from_activity, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/notification.ex', line: 512]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Notification, :do_create_notifications, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/notification.ex', line: 389]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.ActivityPub.SideEffects, :handle, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/activity_pub/side_effects.ex', line: 272]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.ActivityPub.Pipeline, :do_common_pipeline, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/activity_pub/pipeline.ex', line: 50]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.ActivityPub.Pipeline, :common_pipeline, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/activity_pub/pipeline.ex', line: 28]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.ActivityPub.Transmogrifier, :handle_incoming, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/activity_pub/transmogrifier.ex', line: 476]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.Federator, :perform, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/federator.ex', line: 84]}
Dec 06 15:24:13 gleasonator mix[171487]: ]
```
Did somebody say StatusView?
```
Dec 06 15:24:13 gleasonator mix[171487]: 15:24:13.605 request_id=Fr4zyQTR03rNmNkACO_j [warn] Query took longer than 500ms!
Dec 06 15:24:13 gleasonator mix[171487]: Total time: 4256.109ms
Dec 06 15:24:13 gleasonator mix[171487]: "SELECT u0.\"id\", u0.\"bio\", u0.\"raw_bio\", u0.\"email\", u0.\"name\", u0.\"nickname\", u0.\"password_hash\", u0.\"keys\", u0.\"public_key\", u0.\"ap_id\", u0.\"avatar\", u0.\"local\", u0.\"follower_address\", u0.\"following_address\", u0.\"featured_address\", u0.\"tags\", u0.\"last_refreshed_at\", u0.\"last_digest_emailed_at\", u0.\"banner\", u0.\"background\", u0.\"note_count\", u0.\"follower_count\", u0.\"following_count\", u0.\"is_locked\", u0.\"is_confirmed\", u0.\"password_reset_pending\", u0.\"is_approved\", u0.\"registration_reason\", u0.\"confirmation_token\", u0.\"default_scope\", u0.\"domain_blocks\", u0.\"is_active\", u0.\"no_rich_text\", u0.\"ap_enabled\", u0.\"is_moderator\", u0.\"is_admin\", u0.\"show_role\", u0.\"mastofe_settings\", u0.\"uri\", u0.\"hide_followers_count\", u0.\"hide_follows_count\", u0.\"hide_followers\", u0.\"hide_follows\", u0.\"hide_favorites\", u0.\"email_notifications\", u0.\"mascot\", u0.\"emoji\", u0.\"pleroma_settings_store\", u0.\"fields\", u0.\"raw_fields\", u0.\"is_discoverable\", u0.\"invisible\", u0.\"allow_following_move\", u0.\"skip_thread_containment\", u0.\"actor_type\", u0.\"also_known_as\", u0.\"inbox\", u0.\"shared_inbox\", u0.\"accepts_chat_messages\", u0.\"last_active_at\", u0.\"disclose_client\", u0.\"accepts_email_list\", u0.\"pinned_objects\", u0.\"is_suggested\", u0.\"notification_settings\", u0.\"blocks\", u0.\"mutes\", u0.\"muted_reblogs\", u0.\"muted_notifications\", u0.\"subscribers\", u0.\"multi_factor_authentication_settings\", u0.\"inserted_at\", u0.\"updated_at\" FROM \"users\" AS u0 WHERE (u0.\"ap_id\" = $1)"
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.User, :get_cached_by_ap_id, 1,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/user.ex', line: 1126]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.StatusView, :render, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/mastodon_api/views/status_view.ex', line: 162]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.StatusView, :safe_render, 3,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/mastodon_api/views/status_view.ex', line: 6]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.MastodonAPI.StatusView, :safe_render_many, 4,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/mastodon_api/views/status_view.ex', line: 6]}
Dec 06 15:24:13 gleasonator mix[171487]: ]
```
Ah, here is the query mentioned in #2792, !3554 ... WTF??? Over 1 minute...
```
Dec 06 15:24:13 gleasonator mix[171487]: 15:24:13.817 [warn] Query took longer than 500ms!
Dec 06 15:24:13 gleasonator mix[171487]: Total time: 77559.285ms
Dec 06 15:24:13 gleasonator mix[171487]: "SELECT u0.\"ap_id\" FROM \"users\" AS u0 WHERE (u0.\"is_active\" = FALSE) AND (NOT (u0.\"nickname\" IS NULL)) AND (NOT (u0.\"nickname\" LIKE 'internal.%'))"
Dec 06 15:24:13 gleasonator mix[171487]: [
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Activity, :restrict_deactivated_users, 1,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/activity.ex', line: 367]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Activity, :get_create_by_object_ap_id, 1,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/activity.ex', line: 255]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.ActivityPub.Transmogrifier, :fix_in_reply_to, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/activity_pub/transmogrifier.ex', line: 153]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.ActivityPub.Transmogrifier, :handle_incoming, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/activity_pub/transmogrifier.ex', line: 457]},
Dec 06 15:24:13 gleasonator mix[171487]: {Pleroma.Web.Federator, :perform, 2,
Dec 06 15:24:13 gleasonator mix[171487]: [file: 'lib/pleroma/web/federator.ex', line: 84]}
Dec 06 15:24:13 gleasonator mix[171487]: ]
```
I'm still identifying, but I would say we should probably look into StatusView.https://git.pleroma.social/pleroma/pleroma/-/issues/2787"MovePinnedActivitiesIntoPinnedObjects" migration takes 15 minutes2023-05-08T01:33:27ZAlex Gleason"MovePinnedActivitiesIntoPinnedObjects" migration takes 15 minutesAdded in: !3312
```
01:29:39.545 [info] == Running 20210205145000 Pleroma.Repo.Migrations.MovePinnedActivitiesIntoPinnedObjects.up/0 forward
01:44:39.583 [info] == Migrated 20210205145000 in 900.0s
```
900s aka 15 minutes is unreaso...Added in: !3312
```
01:29:39.545 [info] == Running 20210205145000 Pleroma.Repo.Migrations.MovePinnedActivitiesIntoPinnedObjects.up/0 forward
01:44:39.583 [info] == Migrated 20210205145000 in 900.0s
```
900s aka 15 minutes is unreasonably slowhttps://git.pleroma.social/pleroma/pleroma/-/issues/2658Reducing massive fetches(DDoS) with link previews2023-05-08T02:02:20ZHaelwennReducing massive fetches(DDoS) with link previewsThe load created by fetching previews has been an ongoing issue for a serious while, by default pleroma+mastodon is basically rendering some websites offline, enough that sometimes I avoid making some URLs an actual link.
I have these i...The load created by fetching previews has been an ongoing issue for a serious while, by default pleroma+mastodon is basically rendering some websites offline, enough that sometimes I avoid making some URLs an actual link.
I have these ideas:
- Shared proxies, maybe by reusing media proxies because technically you could put anything that you want as attachment URL, thus doing the same thing as previews but even worse, that said most users likely aren't going to use custom URLs so non-malicious use gets better.
- Embedding the preview data in the post, this means giving up on having reliable-enough data to show but with custom HTML + a false page you can confuse quite a lot of clients.
I put a false page on https://hacktivis.me/tmp/joinmastodon.org.html around 2020-02-14, it's just an HTML file with
```html
<meta property="og:image" content="https://hacktivis.me/datalove/img/meme/pleroma/mastodon%2C%20forbidden%20amuse%20yourself.jpeg" />
<meta property="og:title" content="Mastodon: Forbidden Amuse Yourself" />
<meta property="og:site_name" content="joinmastodon.org" />
<meta http-equiv="refresh" content="0; url=http://joinmastodon.org/">
```
And I'd *much* rather have link previews being disabled (by default?) until we figure out a better way.https://git.pleroma.social/pleroma/pleroma/-/issues/2516Link-Preview on IceCast Server takes too long2023-05-08T02:11:58ZschenklklopferLink-Preview on IceCast Server takes too longI own a really small webradio and running all the servers on my own.
So I also run an IceCast2 server that is used for the webradio stream itself.
Some of our listeners seems to use Pleroma to promote my webraddio.
So every now and ...I own a really small webradio and running all the servers on my own.
So I also run an IceCast2 server that is used for the webradio stream itself.
Some of our listeners seems to use Pleroma to promote my webraddio.
So every now and then a bunch of about 50, sometimes 100 sometimes up to 200 Pleroma bots floods my IceCast Server
that much that normal listeners cannot connect to it, due to a limitation of max. 200 listeners.
![image](/uploads/8334a2c6da76d266cb9975f326579d14/image.png)
Those bots stay for several seconds. Sometimes for about 2 minutes.
During this time no new real listeners can connect.
Even worse, I need to pay for this traffic to the german GEMA and GVL Rightholders.
For traffic that isn't even heared.
Mastodon had this problem as well a few months ago. But since one of the newer versions this is gone.
I do not know why... But suddenly Pleroma Bots appeared...
Maybe you can dedect if the link is a MP3 Stream and stop trying to preview it?https://git.pleroma.social/pleroma/pleroma/-/issues/1969Attachment cleanup worker query takes forever. Was: Database overloaded with ...2023-05-08T03:48:21ZkarolatAttachment cleanup worker query takes forever. Was: Database overloaded with selectsMy cpu utilization has been at 100 percent for over 12 hour now and the instance is unusable (it thinks I'm not logged in and wont show me anything since I'm not authorized). I've tried clearing the oban queue a few times with `DELETE F...My cpu utilization has been at 100 percent for over 12 hour now and the instance is unusable (it thinks I'm not logged in and wont show me anything since I'm not authorized). I've tried clearing the oban queue a few times with `DELETE FROM ONLY oban_jobs;`, just now it had 10904 jobs I deleted after being asleep. Have any ideas about how I can fix it?MaksimMaksimhttps://git.pleroma.social/pleroma/pleroma/-/issues/1857The 20200602125218_backfill_notification_types migration takes a really long ...2023-05-08T04:00:37ZYour New SJW WaifuThe 20200602125218_backfill_notification_types migration takes a really long time.So far it's been running for about an hour and a half over on Neckbeard.xyz
Assuming it's working it might be a good idea to add a warning with another ClippySo far it's been running for about an hour and a half over on Neckbeard.xyz
Assuming it's working it might be a good idea to add a warning with another Clippyhttps://git.pleroma.social/pleroma/pleroma/-/issues/1625Bad performance for accounts with many followers2023-05-08T04:12:42ZlainBad performance for accounts with many followersApparently, even with the recent changes related to query planning, postgres will still use a query plan that involves a sequential scan through the database for the profile pages of accounts that don't have many posts. This leads to rat...Apparently, even with the recent changes related to query planning, postgres will still use a query plan that involves a sequential scan through the database for the profile pages of accounts that don't have many posts. This leads to rather bad performance for those accounts.https://git.pleroma.social/pleroma/pleroma/-/issues/1480Figure out initializing Plugs at boot time2023-05-08T04:04:43Zrinpatchrin+pleroma@patch.cxFigure out initializing Plugs at boot timeCurrently both our rate limiter and upload limit Plug are initialized at compile-time, making it impossible to tweak on OTP releases. Initializing them at runtime is an option (see https://github.com/elixir-plug/plug/pull/889 ), but I im...Currently both our rate limiter and upload limit Plug are initialized at compile-time, making it impossible to tweak on OTP releases. Initializing them at runtime is an option (see https://github.com/elixir-plug/plug/pull/889 ), but I imagine this can get quite expensive. We should figure out a way to initialize them at boot time
cc @plataformatechttps://git.pleroma.social/pleroma/pleroma/-/issues/1052Unify activities and objects2021-04-21T08:01:01ZfeldUnify activities and objectsThis closes the activity/object divide that's causing us much performance painThis closes the activity/object divide that's causing us much performance pain2.0https://git.pleroma.social/pleroma/pleroma/-/issues/798Scaling Pleroma for a worldwide userbase2022-08-28T12:34:50ZfeldScaling Pleroma for a worldwide userbaseNormally you would expect users to join a community on the Fediverse that is both relevant to the users' interests and is also located nearby geographically for performance reasons. e.g., someone in Japan should join a Japanese server an...Normally you would expect users to join a community on the Fediverse that is both relevant to the users' interests and is also located nearby geographically for performance reasons. e.g., someone in Japan should join a Japanese server and use federation to communicate with users across the world. This eliminates performance bottlenecks by keeping the latency low.
However there are situations where this is not possible. I administer a customized Pleroma server that automatically enrolls an existing community/userbase with thousands of members who are worldwide. We observe performance issues as a result. e.g., users in Australia struggle to upload media to our server in Chicago.
We have a few options at our disposal to improve general performance such as using `config :Pleroma.Upload, base_url` to point to a CDN. This only solves the performance for consumption of content, not creation.
To achieve good performance we need to reduce latency of TCP connections by simply moving those activities to edge nodes which are geographically dispersed around the world. The problem is that this currently is not feasible in Pleroma. Simply moving Pleroma to be entirely behind a CDN is not wise because of `websockets`. CDNs do not like websockets.
CloudFlare supports websockets, but a limited number unless you pay for premium plans.
Akamai websocket support is unclear. It appears not permitted for production traffic based on my research.
This is unsurprising because websockets would be a serious strain on a CDN anyway. We can build our own CDN (and we are), but that doesn't change the reality that it's not a great solution to the problem.
This leaves us with the options of a major engineering effort and a lame hack:
1) OTP clustering, which will require some significant dev investment. But this is something we do want long term for building HA and load balanced Pleroma clusters
2) Some kind of API extension to support this, and bake it into the apps we influence (Roma, etc). e.g., hint that `/api/v1/statuses` should be accessed at a different `base_url` which can be proxied by a CDN, and fallback if that's unavailable.
Are there any other things we should consider? How else can this problem be approached?https://git.pleroma.social/pleroma/pleroma/-/issues/2338Remove object pruning2023-05-08T03:07:05ZlainRemove object pruning- object pruning bye bye
- what to do about rising space reqs- object pruning bye bye
- what to do about rising space reqshttps://git.pleroma.social/pleroma/pleroma/-/issues/1384Strip mentions and html from input used for the search index2023-05-08T03:09:02Zrinpatchrin+pleroma@patch.cxStrip mentions and html from input used for the search indexWe should introduce a new column for search tsvectors and ensure the content converted to tsvectors has it's html and mentions stripped beforehand. This will lead to more accurate matches and an ability to actually make remote profiles u...We should introduce a new column for search tsvectors and ensure the content converted to tsvectors has it's html and mentions stripped beforehand. This will lead to more accurate matches and an ability to actually make remote profiles unsearchable for unauthenticated users (right now we restrict the user search to not show remote users to unauthenticated users, but if a local user mentioned them in a post, the result will be displayed)