Neon•13mo ago

vicious-gold

Computes not going to idle

I have an Elixir app using Ecto (which runs it's own connection pool). My compute doesn't seem to ever go to idle while the app is running. Ecto's internal connection pool stays connected but shows as idle in the dashboard. I've tried both the pooled and unpooled connection options on the Neon side. From what I understand in the compute lifecycle and Elixir doc it seems that idle database connections should allow the compute to go to idle as well. Any ideas on what might be causing this or how to fix it?

12 Replies

helpful-purple•13mo ago

are you a paid user?

automatic-azure•13mo ago

Looking at postgrex docs:

:idle_interval - Ping connections after a period of inactivity in milliseconds. Defaults to 1000ms;

Looks like it's forcefully keeping the database active by pinging it

vicious-goldOP•13mo ago

Just free tier currently. Trying to figure out the auto-suspend to see if paid tier will be viable for us. Ooh thanks, let me see if I can disable that and see what it does.

helpful-purple•13mo ago

Thanks Conrad!

vicious-goldOP•13mo ago

Good call, setting :idle_interval to 24 hours allowed the compute to spin down.

config :app_name, AppName.Repo
  # normal connection options
  ...
  idle_interval: :timer.hours(24)

config :app_name, AppName.Repo
  # normal connection options
  ...
  idle_interval: :timer.hours(24)

Any idea what the risks or drawbacks of setting that so high might be? Seems like Postgrex might just be using it as a health check and/or to keep the connection from timing out?

helpful-purple•13mo ago

it could be an optimization on the side of postgrex because spinning up new postgres connections is "expensive" if you are using a pooler, this setting seemspretty pointless

vicious-goldOP•13mo ago

Hmm makes sense. Looks like Ecto/Postgrex isn't happy about the idle connection when I try to spin it back up (on the non-pooled Neon connection), let me see what the pooled connection does. Here's the error when I try reconnecting to the pool:

** (DBConnection.ConnectionError) ssl send: closed
    (ecto_sql 3.12.0) lib/ecto/adapters/sql.ex:1078: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.12.0) lib/ecto/adapters/sql.ex:976: Ecto.Adapters.SQL.execute/6
    (ecto 3.12.3) lib/ecto/repo/queryable.ex:232: Ecto.Repo.Queryable.execute/4
    (ecto 3.12.3) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (ecto 3.12.3) lib/ecto/repo/queryable.ex:154: Ecto.Repo.Queryable.one/3
    iex:2: (file)
[error] Postgrex.Protocol (#PID<0.800.0>) disconnected: ** (DBConnection.ConnectionError) ssl send: closed ()

** (DBConnection.ConnectionError) ssl send: closed
    (ecto_sql 3.12.0) lib/ecto/adapters/sql.ex:1078: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.12.0) lib/ecto/adapters/sql.ex:976: Ecto.Adapters.SQL.execute/6
    (ecto 3.12.3) lib/ecto/repo/queryable.ex:232: Ecto.Repo.Queryable.execute/4
    (ecto 3.12.3) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (ecto 3.12.3) lib/ecto/repo/queryable.ex:154: Ecto.Repo.Queryable.one/3
    iex:2: (file)
[error] Postgrex.Protocol (#PID<0.800.0>) disconnected: ** (DBConnection.ConnectionError) ssl send: closed ()

And my repo config block:

config :app, App.Repo,
  username: "user",
  password: "pass",
  hostname: "-pooler hostname",
  database: "app",
  ssl: [cacerts: :public_key.cacerts_get()],
  stacktrace: true,
  show_sensitive_data_on_connection_error: true,
  pool_size: 10,
  idle_interval: :timer.hours(24),
  prepare: :unnamed

config :app, App.Repo,
  username: "user",
  password: "pass",
  hostname: "-pooler hostname",
  database: "app",
  ssl: [cacerts: :public_key.cacerts_get()],
  stacktrace: true,
  show_sensitive_data_on_connection_error: true,
  pool_size: 10,
  idle_interval: :timer.hours(24),
  prepare: :unnamed

Based on that error it seems like the ping might be used to help keep the TCP connection open? Digging into Postgrex a bit, it looks like it sends a Sync message on that interval: - the timeout handler that runs on :idle_interval (summary: SimpleConnection is a state machine that manages the Postgres connection and sets a timeout event using :idle_interval after ~every event it handles) - Protocol.ping/1 definition
- msg_sync definition

helpful-purple•13mo ago

I think Ecto needs to handle the connection being closed from underneath it and reconnect This is what psql does for instance open a psql connection, and let it idle for 5ish minutes, then run SELECT 1; you'll see what I'm talking about

automatic-azure•13mo ago

An unfortunate theme in postgres clients is not handling shutdowns very gracefully :ferrisPensive:

vicious-goldOP•13mo ago

As far as I can tell, Ecto just checks out connections from the pool, the actual connection pool management is handled between Postgrex, the ecto_sql Postgres adapater and the DBConnection library. So potentially I could catch the error in my app code and restart the whole Ecto Repo, or try to create a patch in Ecto that can gracefully restart the adapter. To @Conrad Ludgate's point, it might be a nice Postgrex/Ecto.Adapters.Postgres enhancement to handle timed out connections more gracefully. Could also probably use dynamic repos and just spin up the database connections as needed, but then you'd basically be managing your own connection pool again. --- From a Neon perspective, we could probably just let our prod instance scale down to 0.25 and call it a day. We might deploy using Fly, so for non-prod environments the Elixir app might scale to zero which would also solve the database connection side.

GitHub

ecto_sql/lib/ecto/adapters/postgres.ex at v3.12.1 · elixir-ecto/ect...

SQL-based adapters for Ecto and database migrations - elixir-ecto/ecto_sql

vicious-goldOP•13mo ago

Last note for now, hopefully helpful as a starting point in the future: - Postgrex allows passing a custom pool implementation: https://github.com/elixir-ecto/postgrex/blob/a4b85e83c074f1c49fbf9b9d38a3875dc5580fc1/lib/postgrex.ex#L151 - Defaults to DBConnection.ConnectionPool: https://github.com/elixir-ecto/db_connection/blob/master/lib/db_connection.ex So I think you could conceivably provide just your own Pool implementation without having to fork any of the main libraries.

deep-jade•5mo ago

After following the instructions on Connect from Elixir with Ecto to Neon (https://neon.com/docs/guides/elixir-ecto) and reaching 4. Configure Ecto which then points to Postgrex: DBConnection ConnectionError ssl send: closed (https://neon.com/docs/connect/connection-errors#postgrex-dbconnection-connectionerror-ssl-send-closed) it recommends setting idle_interval: :timer.hours(24) The rationale for this setting is described as the default idle_interval being too low which prevents autosuspend, which results in the error.

The default setting is 1000ms. If you rely on Neon's autosuspend feature to scale your compute to zero when your database is not active, this setting will prevent that and you may encounter a (DBConnection.ConnectionError) ssl send: closed (ecto_sql 3.12.0) error as a result.

The default setting is 1000ms. If you rely on Neon's autosuspend feature to scale your compute to zero when your database is not active, this setting will prevent that and you may encounter a (DBConnection.ConnectionError) ssl send: closed (ecto_sql 3.12.0) error as a result.

This gives the clear impression that setting idle_interval: :timer.hours(24) both enables autosuspend and prevents the error listed from occuring. This is not true. Setting idle_interval: :timer.hours(24) does allow for autosuspend but as a result actually causes the error to occur when the compute gets suspended. I think the docs should be updated to make it clear that autosuspend is not currently possible with a default Ecto configuration.

Neon

Connect from Elixir with Ecto to Neon - Neon Docs

This guide describes how to connect from an Elixir application with Ecto, which is a database wrapper and query generator for Elixir. Ecto provides an API and abstractions for interacting databases, e...

Neon

Connection errors - Neon Docs

This topic describes how to resolve connection errors you may encounter when using Neon. The errors covered include The endpoint ID is not specified Password authentication failed for user Couldn't co...

Gaming

Programming

Computes not going to idle

Did you find this page helpful?