Pleroma 2.0.x increases database load with SELECT queries
Using Pleroma from git tag v2.0.5 currently, problem also occured in v2.0.3. I discussed via IRC with @rinpatch about this briefly but did not reach a conclusion yet.
Issue potentially related to #1652 (closed). Unlike #1740 (closed) and #1611 (closed) it does not seem like database is suffering from INSERT/UPDATE load but instead many SELECT queries per second.
Increased load has happened on both my instances: pl.wowana.me and anime.website. On pl.wowana.me I upgraded to v2.0.0 and experienced CPU usage issues (both with Pleroma beam.smp process and with the PostgreSQL database) but I did not attempt to debug it. I let it run for about a week and load returned to normal. On anime.website I am still suffering from issues such as HTTP 500 errors when requesting e.g. timelines, increased CPU with postgres (Pleroma itself seems to be okay), and increased disk I/O.
On anime.website I have also experienced two potentially-unrelated crashes; I may open a new issue with crash dump attached, if I know whether I need to redact secret credentials in it first.
PostgreSQL query log over 1 minute 45 second time span: pg-query.log
config/prod.secret.exs:
import Config
config :cors_plug, :max_age, 86400
config :cors_plug, :methods, ["POST", "PUT", "DELETE", "GET", "PATCH", "OPTIONS"]
config :cors_plug, :credentials, true
config :cors_plug, :headers, ["Authorization", "Content-Type", "Idempotency-Key"]
config :cors_plug, :expose, [
"Link",
"X-RateLimit-Reset",
"X-RateLimit-Limit",
"X-RateLimit-Remaining",
"X-Request-Id",
"Idempotency-Key"
]
config :pleroma, :activitypub,
accept_blocks: false,
unfollow_blocked: true,
outgoing_blocks: false
config :pleroma, Pleroma.Plugs.RemoteIp,
reserved: ["0::1/128", "127.0.0.0/8"],
proxies: ["172.22.3.103/32"],
headers: ["x-forwarded-for"]
config :pleroma, :chat, enabled: false
config :pleroma, :rich_media, enabled: false
config :pleroma, :http_security, enabled: false
config :pleroma, :user, deny_follow_blocked: false
config :pleroma, Pleroma.Emails.Mailer, no_mx_lookups: true
config :pleroma, Pleroma.Captcha, enabled: true
config :logger, :backends, [{ExSyslogger, :ex_syslogger}]
config :pleroma, :http, adapter: [ssl_options: [versions: [:"tlsv1.2"]]]
config :pleroma, :instance,
name: "anime website",
email: "abuse@anime.website",
limit: 2_147_483_647,
account_activation_required: false,
dedupe_media: true,
allowed_post_formats: ["text/plain"],
quarantined_instances: ["social.librem.one"],
managed_config: false,
rewrite_policy: Pleroma.Web.ActivityPub.MRF.SimplePolicy,
registrations_open: true,
user_name_length: 64,
healthcheck: true,
notify_email: "noreply@anime.website",
external_user_synchronization: false,
cleanup_attachments: false
config :pleroma, Pleroma.Repo,
adapter: Ecto.Adapters.Postgres,
username: "pleroma",
password: "[redacted]",
database: "pleroma",
socket_dir: "/mnt/sockets/postgresql",
pool_size: 10
config :pleroma, Pleroma.Web.Endpoint,
url: [host: "anime.website", scheme: "https", port: 443],
#http: [port: 4000, transport_options: [socket_opts: [:inet6]]],
http: [port: 4000, ip: {0, 0, 0, 0}],
secret_key_base: "[redacted]"
config :pleroma, :markup,
allow_inline_images: false,
allow_headings: false,
allow_tables: false,
allow_fonts: false,
scrub_policy: [
Pleroma.HTML.Transform.MediaProxy,
Pleroma.HTML.Scrubber.TwitterText
]