Home Timeline taking longer than 15 seconds to load
I ran into an issue today where my home timeline was bumping up into the configured statement timeout in Pleroma. After poking around some closed bugs I increased it via:
# Database
config :pleroma, Pleroma.Repo,
adapter: Ecto.Adapters.Postgres,
...
pool_size: 30,
timeout: 60_000
# DB performance is sucky
config :pleroma, :dangerzone, override_repo_pool_size: true
This helped in that the timeline isn't causing a 500 but is still oddly slow for my Pleroma instance size. It has 3 users, 1 of which is my main account, 1 is a sort of announcement account, and my band mate who never uses it :P So it's basically just me. I'm running an 8GB Pi4 and have tuned the DB as follows:
max_connections = 100
shared_buffers = 512MB
effective_cache_size = 1536MB
maintenance_work_mem = 128MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 2621kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2
I'm also logging slow queries and the home timeline causes this one to get logged:
2022-12-07 22:37:28.833 UTC [2107] pleroma@victimcache_pleorma STATEMENT: 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) AND (not (a0."data"->>'type' = 'Announce' and a0."data"->'to' ?| $10)) AND ((not (split_part(a0."actor", '/', 3) = ANY($11))) or a0."actor" = ANY($12)) AND ((not (split_part(o1."data"->>'actor', '/', 3) = ANY($13))) or (o1."data"->>'actor') = ANY($14)) AND (not ( a0."data"->>'type' = 'Announce' and a0."actor" = ANY($15))) AND (a0."data"->>'type' != 'Announce' or o1."data"->>'actor' != $16) AND (NOT (a0."actor" = ANY($17))) AND (not(o1."data"->>'type' = 'Answer')) AND (not(o1."data"->>'type' = 'ChatMessage')) AND (NOT (a0."actor" = ANY($18))) ORDER BY a0."id" desc nulls last LIMIT $19
The interesting bit is that I don't see any I/O wait on my Pi which leads me to believe it's not because of disk I/O (I'm running a SATA SSD via USB3). It is burning CPU cycles though, seems to be partly single threaded as I'll see one CPU core hit 100% user when looking at top, at least when initially refreshing the timeline. Then it starts to even out.
It feels like this just popped up almost overnight and my other Pleroma instance doesn't have this issue (same Pi). I post on that one less and it's similar (just a few users, mostly me). That one I have not upgraded to the latest release. I was having performance issues on the one I'm referring to here before I upgraded and it doesn't seem to have helped (it may even be worse). Opting to keep that instance at the previous version just to try and better compare the two.
I tried to run explains and things on the above query but I'm a MySQL and Clickhouse DBA and don't have a lot of PGSQL experience - or at least all the info I have is really dated. Since the above has a ton of variables/parameters I wasn't able to pursue it much further yet. I would assume I can get the literal query being sent to PGSQL but haven't found them in looking at the execution list (processlist in MySQL speak).
The slow load is in both the web and Whalebird (hitting the same API endpoint I'm sure, I believe it's /api/v1/timelines/home )