Home timeline database timeout after migrating 13K users
Note: I know this is not normal usage of Pleroma, I'm just documenting what I found.
I migrated 13K users (in a test, locally) with the Mastodon->Pleroma migrator. Everything works fine on the public-facing side. After logging in, the Home timeline will never load:
[error] Postgrex.Protocol (#PID<0.4060.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.4451.0> timed out because it queued and checked out the connection for longer than 15000ms
#PID<0.4451.0> was at location:
:prim_inet.recv0/3
(postgrex 0.15.5) lib/postgrex/protocol.ex:2838: Postgrex.Protocol.msg_recv/4
(postgrex 0.15.5) lib/postgrex/protocol.ex:1880: Postgrex.Protocol.recv_bind/3
(postgrex 0.15.5) lib/postgrex/protocol.ex:1735: Postgrex.Protocol.bind_execute_close/4
(db_connection 2.2.2) lib/db_connection/holder.ex:316: DBConnection.Holder.holder_apply/4
(db_connection 2.2.2) lib/db_connection.ex:1255: DBConnection.run_execute/5
(db_connection 2.2.2) lib/db_connection.ex:1342: DBConnection.run/6
(db_connection 2.2.2) lib/db_connection.ex:539: DBConnection.parsed_prepare_execute/5
(db_connection 2.2.2) lib/db_connection.ex:532: DBConnection.prepare_execute/4
(ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:534: Ecto.Adapters.SQL.execute!/4
(ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:526: Ecto.Adapters.SQL.execute/5
(ecto 3.4.5) lib/ecto/repo/queryable.ex:192: Ecto.Repo.Queryable.execute/4
(ecto 3.4.5) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
(pleroma 2.0.50-2284-g1abf453a-bleroma+dev) lib/pleroma/pagination.ex:40: Pleroma.Pagination.fetch_paginated/4
(pleroma 2.0.50-2284-g1abf453a-bleroma+dev) lib/pleroma/web/activity_pub/activity_pub.ex:1107: Pleroma.Web.ActivityPub.ActivityPub.fetch_activities/3
(pleroma 2.0.50-2284-g1abf453a-bleroma+dev) lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex:56: Pleroma.Web.MastodonAPI.TimelineController.home/2
(pleroma 2.0.50-2284-g1abf453a-bleroma+dev) lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex:5: Pleroma.Web.MastodonAPI.TimelineController.action/2
(pleroma 2.0.50-2284-g1abf453a-bleroma+dev) lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex:5: Pleroma.Web.MastodonAPI.TimelineController.phoenix_controller_pipeline/2
(pleroma 2.0.50-2284-g1abf453a-bleroma+dev) lib/pleroma/web/endpoint.ex:5: Pleroma.Web.Endpoint.instrument/4
(phoenix 1.4.17) lib/phoenix/router.ex:288: Phoenix.Router.__call__/2
[debug] QUERY ERROR source="activities" db=15001.3ms queue=1.4ms idle=589.8ms
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)) AND (recipients_contain_blocked_domains(a0."recipients", $8) = false) AND (not (a0."data"->>'type' = 'Announce' and a0."data"->'to' ?| $9)) AND ((not (split_part(a0."actor", '/', 3) = ANY($10))) or a0."actor" = ANY($11)) AND ((not (split_part(o1."data"->>'actor', '/', 3) = ANY($12))) or (o1."data"->>'actor') = ANY($13)) AND (not ( a0."data"->>'type' = 'Announce' and a0."actor" = ANY($14))) AND (a0."data"->>'type' != 'Announce' or o1."data"->>'actor' != $15) AND (NOT (a0."actor" = ANY($16))) AND (not(o1."data"->>'type' = 'Answer')) AND (not(o1."data"->>'type' = 'ChatMessage')) AND (NOT (a0."actor" = ANY($17))) ORDER BY a0."id" desc nulls last LIMIT $18 [<<0, 0, 1, 115, 157, 181, 146, 150, 13, 65, 94, 75, 212, 152, 0, 0>>, <<0, 0, 1, 115, 157, 181, 146, 150, 13, 65, 94, 75, 212, 152, 0, 0>>, ["https://spinster.xyz/users/alex", "https://spinster.xyz/users/alex/followers", "https://mastodon.gamedev.place/users/voadi/followers", "https://bots.tinysubversions.com/u/NinaPaleysBlog/followers", "https://bots.tinysubversions.com/u/ninapaley/followers", "https://bots.tinysubversions.com/u/feministcurrent/followers", "https://bots.tinysubversions.com/u/gendercritical/followers", "https://bots.tinysubversions.com/u/feminism/followers", "https://bots.tinysubversions.com/u/terfisaslur/followers", "https://mastodon.sdf.org/users/salixlucida/followers", "https://spinster.xyz/users/mk/followers", "https://assortedflotsam.com/users/NewsBot/followers", "https://pawoo.net/users/catpostingbot/followers", "https://mastodon.org.uk/users/bigben/followers", "https://spinster.xyz/users/WHRC/followers", "https://ligma.pro/users/r000t/followers", "https://gab.com/users/TranimeGirl/followers", "https://bitcoinhackers.org/users/mastodonusercount/followers", "https://gab.com/users/SarahCorriher/followers", "https://gab.com/users/SuzSez/followers", "https://gab.com/users/miss_anthropy/followers", "https://gab.com/users/MissGendering/followers", "https://gab.com/users/mirandayardley/followers", "https://gab.com/users/ReclaimTERF/followers", "https://mirrored.social/users/nasa/followers", "https://gab.com/users/therealDiscoSB/followers", "https://gab.com/users/MoonWillow/followers", "https://gab.com/users/RhapsodyTheBlue/followers", "https://spinster.xyz/users/Csilla/followers", "https://rcsocial.net/users/Kymberly/followers", "https://gab.com/users/SharylAttkisson/followers", "https://gab.com/users/reclaimthenet/followers", "https://gab.com/users/gab/followers", "", "", "https://pleroma.site/users/hcs/followers", "https://mamot.fr/users/doctorow/followers", "https://spinster.xyz/users/Womanimal/followers", "https://spinster.xyz/users/ChaiRadFem/followers", "https://donteatanimals.org/users/rgggn/followers", "https://en.osm.town/users/osmvegan/followers", "https://gab.com/users/MagicalAfrica/followers", "https://mastodon.internot.no/users/RT/followers", "https://social.librem.one/users/vegangargoyle/followers", "https://donteatanimals.org/users/mk_altra/followers", "https://mstdn.io/users/EccentricElephant/followers", "https://mstdn.io/users/codewiz/followers", ...], "https://spinster.xyz/users/alex", ["Create", "Announce"], ["https://spinster.xyz/users/bitter_badfem_harpy"], ["https://spinster.xyz/users/bitter_badfem_harpy"], [], ["https://spinster.xyz/users/bitter_badfem_harpy"], [], ["https://mastodon.gamedev.place/users/voadi", "https://bots.tinysubversions.com/u/NinaPaleysBlog", "https://bots.tinysubversions.com/u/ninapaley", "https://bots.tinysubversions.com/u/feministcurrent", "https://bots.tinysubversions.com/u/gendercritical", "https://bots.tinysubversions.com/u/feminism", "https://bots.tinysubversions.com/u/terfisaslur", "https://mastodon.sdf.org/users/salixlucida", "https://spinster.xyz/users/mk", "https://assortedflotsam.com/users/NewsBot", "https://pawoo.net/users/catpostingbot", "https://mastodon.org.uk/users/bigben", "https://spinster.xyz/users/WHRC", "https://ligma.pro/users/r000t", "https://gab.com/users/TranimeGirl", "https://bitcoinhackers.org/users/mastodonusercount", "https://gab.com/users/SarahCorriher", "https://gab.com/users/SuzSez", "https://gab.com/users/miss_anthropy", "https://gab.com/users/MissGendering", "https://gab.com/users/mirandayardley", "https://gab.com/users/ReclaimTERF", "https://mirrored.social/users/nasa", "https://gab.com/users/therealDiscoSB", "https://gab.com/users/MoonWillow", "https://gab.com/users/RhapsodyTheBlue", "https://spinster.xyz/users/Csilla", "https://rcsocial.net/users/Kymberly", "https://gab.com/users/SharylAttkisson", "https://gab.com/users/reclaimthenet", "https://gab.com/users/gab", "https://gnusocial.no/user/44162", "https://gnusocial.no/user/3197", "https://pleroma.site/users/hcs", "https://mamot.fr/users/doctorow", "https://spinster.xyz/users/Womanimal", "https://spinster.xyz/users/ChaiRadFem", "https://donteatanimals.org/users/rgggn", "https://en.osm.town/users/osmvegan", ...], [], ["https://mastodon.gamedev.place/users/voadi", "https://bots.tinysubversions.com/u/NinaPaleysBlog", "https://bots.tinysubversions.com/u/ninapaley", "https://bots.tinysubversions.com/u/feministcurrent", "https://bots.tinysubversions.com/u/gendercritical", "https://bots.tinysubversions.com/u/feminism", "https://bots.tinysubversions.com/u/terfisaslur", "https://mastodon.sdf.org/users/salixlucida", "https://spinster.xyz/users/mk", "https://assortedflotsam.com/users/NewsBot", "https://pawoo.net/users/catpostingbot", "https://mastodon.org.uk/users/bigben", "https://spinster.xyz/users/WHRC", "https://ligma.pro/users/r000t", "https://gab.com/users/TranimeGirl", "https://bitcoinhackers.org/users/mastodonusercount", "https://gab.com/users/SarahCorriher", "https://gab.com/users/SuzSez", "https://gab.com/users/miss_anthropy", "https://gab.com/users/MissGendering", "https://gab.com/users/mirandayardley", "https://gab.com/users/ReclaimTERF", "https://mirrored.social/users/nasa", "https://gab.com/users/therealDiscoSB", "https://gab.com/users/MoonWillow", "https://gab.com/users/RhapsodyTheBlue", "https://spinster.xyz/users/Csilla", "https://rcsocial.net/users/Kymberly", "https://gab.com/users/SharylAttkisson", "https://gab.com/users/reclaimthenet", "https://gab.com/users/gab", "https://gnusocial.no/user/44162", "https://gnusocial.no/user/3197", "https://pleroma.site/users/hcs", "https://mamot.fr/users/doctorow", "https://spinster.xyz/users/Womanimal", "https://spinster.xyz/users/ChaiRadFem", ...], [], "https://spinster.xyz/users/alex", ["https://spinster.xyz/users/Spack1818", "https://spinster.xyz/users/Squiddy", "https://spinster.xyz/users/metermaid", "https://spinster.xyz/users/DopeSmoker", "https://spinster.xyz/users/SallyLewis", "https://spinster.xyz/users/blackilocks", "https://spinster.xyz/users/Clara67", "https://spinster.xyz/users/Lune", "https://spinster.xyz/users/jane89", "https://spinster.xyz/users/spin99", "https://spinster.xyz/users/terfhunter", "https://spinster.xyz/users/barb", "https://spinster.xyz/users/bluesky", "https://spinster.xyz/users/owlfree", "https://spinster.xyz/users/owlfree1", "https://spinster.xyz/users/owlfree4", "https://spinster.xyz/users/owlfree3", "https://spinster.xyz/users/owlfree5", "https://spinster.xyz/users/newbie", "https://spinster.xyz/users/tali", "https://spinster.xyz/users/juharofram", "https://spinster.xyz/user (truncated)
[debug] Sent 500 in 15338ms
[error] #PID<0.4451.0> running Pleroma.Web.Endpoint (connection #PID<0.4350.0>, stream id 14) terminated
Server: localhost:4000 (http)
Request: GET /api/v1/timelines/home?with_muted=true&limit=20
** (exit) an exception was raised:
** (DBConnection.ConnectionError) tcp recv: closed (the connection was closed by the pool, possibly due to a timeout or because the pool has been terminated)
(ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:593: Ecto.Adapters.SQL.raise_sql_call_error/1
(ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:526: Ecto.Adapters.SQL.execute/5
(ecto 3.4.5) lib/ecto/repo/queryable.ex:192: Ecto.Repo.Queryable.execute/4
(ecto 3.4.5) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
(pleroma 2.0.50-2284-g1abf453a-bleroma+dev) lib/pleroma/pagination.ex:40: Pleroma.Pagination.fetch_paginated/4
(pleroma 2.0.50-2284-g1abf453a-bleroma+dev) lib/pleroma/web/activity_pub/activity_pub.ex:1107: Pleroma.Web.ActivityPub.ActivityPub.fetch_activities/3
(pleroma 2.0.50-2284-g1abf453a-bleroma+dev) lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex:56: Pleroma.Web.MastodonAPI.TimelineController.home/2
(pleroma 2.0.50-2284-g1abf453a-bleroma+dev) lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex:5: Pleroma.Web.MastodonAPI.TimelineController.action/2
(pleroma 2.0.50-2284-g1abf453a-bleroma+dev) lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex:5: Pleroma.Web.MastodonAPI.TimelineController.phoenix_controller_pipeline/2
(pleroma 2.0.50-2284-g1abf453a-bleroma+dev) lib/pleroma/web/endpoint.ex:5: Pleroma.Web.Endpoint.instrument/4
(phoenix 1.4.17) lib/phoenix/router.ex:288: Phoenix.Router.__call__/2
(pleroma 2.0.50-2284-g1abf453a-bleroma+dev) lib/pleroma/web/endpoint.ex:5: Pleroma.Web.Endpoint.plug_builder_call/2
(pleroma 2.0.50-2284-g1abf453a-bleroma+dev) lib/plug/debugger.ex:132: Pleroma.Web.Endpoint."call (overridable 3)"/2
(pleroma 2.0.50-2284-g1abf453a-bleroma+dev) lib/pleroma/web/endpoint.ex:5: Pleroma.Web.Endpoint.call/2
(phoenix 1.4.17) lib/phoenix/endpoint/cowboy2_handler.ex:42: Phoenix.Endpoint.Cowboy2Handler.init/4
(cowboy 2.8.0) /home/alex/Projects/pleroma/deps/cowboy/src/cowboy_handler.erl:37: :cowboy_handler.execute/2
(cowboy 2.8.0) /home/alex/Projects/pleroma/deps/cowboy/src/cowboy_stream_h.erl:300: :cowboy_stream_h.execute/3
(cowboy 2.8.0) /home/alex/Projects/pleroma/deps/cowboy/src/cowboy_stream_h.erl:291: :cowboy_stream_h.request_process/3
(stdlib 3.13) proc_lib.erl:226: :proc_lib.init_p_do_apply/3
I was able to resolve this (see below) by increasing the database timeout limit to a very high amount (600 seconds):
config :pleroma, Pleroma.Repo,
adapter: Ecto.Adapters.Postgres,
username: "pleroma",
password: "redacted",
database: "pleroma",
hostname: "localhost",
pool_size: 20,
timeout: 600_000,
ownership_timeout: 600_000,
pool_timeout: 600_000
Then after logging in, the home timeline eventually loaded.
I'm not sure exactly how the Home timeline works under the hood.
Edited by Alex Gleason