Poor performance in local development after importing a production database dump
This is a low-priority issue, but since it impacted other people I figured I would document it. I wanted to try fixing #2053 (closed) by experimenting with a copy of Spinster's database locally.
After importing a production database locally, the timelines don't load at all. I can log in and most things do work, but timelines (home, local, federated, user, notifications) don't.
Meanwhile, the production performance is very good.
My steps:
- Ran
pg_dump pleroma > pleroma.psql
on Spinster's production database. - Downloaded it to my laptop and ran
psql spinster < pleroma.psql
. - Ran
mix phx.server
and viewed the site atlocalhost:4000
. - Now I could log in, but couldn't view timelines.
Various actions I've tried:
- Logged in as a different user.
- Used both
develop
andspinster
(production commit) branches. - Copied production configuration for Nginx, Pleroma, and used
MIX_ENV=prod
. - Added spinster.xyz to
/etc/hosts
- Set my system clock to UTC.
- Ran
VACUUM ANALYZE;
- Used pg_tune.
I haven't done any analysis of the queries, but we do get this classic error:
23:19:26.048 [error] #PID<0.5203.0> running Pleroma.Web.Endpoint (connection #PID<0.5202.0>, stream id 1) terminated
Server: localhost:4000 (http)
Request: GET /api/v1/timelines/home
** (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.6) lib/ecto/repo/queryable.ex:192: Ecto.Repo.Queryable.execute/4
(ecto 3.4.6) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
(pleroma 2.2.50-595-g562ecf28-spinster) lib/pleroma/pagination.ex:40: Pleroma.Pagination.fetch_paginated/4
(pleroma 2.2.50-595-g562ecf28-spinster) lib/pleroma/web/activity_pub/activity_pub.ex:1171: Pleroma.Web.ActivityPub.ActivityPub.fetch_activities/3
(pleroma 2.2.50-595-g562ecf28-spinster) lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex:57: Pleroma.Web.MastodonAPI.TimelineController.home/2
(pleroma 2.2.50-595-g562ecf28-spinster) lib/pleroma/web/mastodon_api/controllers/timeline_controller.ex:5: Pleroma.Web.MastodonAPI.TimelineController.action/2
?? Very strange issue.
Edited by Alex Gleason