Error class: DBConnection.ConnectionError for small time case

I am getting connection dropped error messages even for small-time ~100ms as well.

Below is the stack trace

(DBConnection.ConnectionError) connection not available and request was dropped from queue after 175ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by: 1. By tracking down slow queries and making sure they are running fast enough 2. Increasing the pool_size (albeit it increases resource consumption) 3. Allow requests to wait longer by increasing :queue_target and :queue_interval See DBConnection.start_link/2 for more information
(ecto_sql) lib/ecto/adapters/sql.ex:612: Ecto.Adapters.SQL.raise_sql_call_error/1
(ecto_sql) lib/ecto/adapters/sql.ex:545: Ecto.Adapters.SQL.execute/5
(ecto) lib/ecto/repo/queryable.ex:192: Ecto.Repo.Queryable.execute/4
(ecto) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
(ecto) lib/ecto/repo/queryable.ex:112: Ecto.Repo.Queryable.one/3
(diamond) lib/diamond/models/query_functions/rule_set_version.ex:10: Diamond.QueryFunctions.RuleSetVersion.find_by_id/2
(diamond) lib/diamond/models/query_functions/rule_set_version.ex:63: Diamond.QueryFunctions.RuleSetVersion.find_by_id_with_dependencies/1
(diamond) lib/diamond/concepts/rule_set/evaluator.ex:372: Diamond.Concepts.RuleSet.Evaluator.fetch_version/2

As you can see it is just 175ms and it got a timeout.

My config

config :diamond, Diamond.Repo,
  username: System.get_env("RULES_ENGINE_DATABASE_USER"),
  password: System.get_env("RULES_ENGINE_DATABASE_PASSWORD"),
  database: System.get_env("RULES_ENGINE_DATABASE_NAME"),
  hostname: System.get_env("RULES_ENGINE_DATABASE_HOST") || "localhost",
  pool_size: 15,
  migration_source: "ecto_schema_migrations"

Please help.

As we see one of the options is to increase pool_size, but sometimes it’s not the most optimal solution… and assuming you already did your best optimizing queries, let’s take a looks at option #3.

Allow requests to wait longer by increasing :queue_target and :queue_interval
See DBConnection.start_link/2 for more information

From docs of DBConnection — db_connection v2.4.0

  • :queue_target in milliseconds, defaults to 50ms
  • :queue_interval in milliseconds, defaults to 1000ms

Our goal is to wait at most :queue_target for a connection. If all connections checked out during a :queue_interval takes more than :queue_target, then we double the :queue_target. If checking out connections take longer than the new target, then we start dropping messages.

For example, by default our target is 50ms. If all connections checkouts take longer than 50ms for a whole second, we double the target to 100ms and we start dropping messages if the time to checkout goes above the new limit.

We can tweak those options in the same config for our Repo. For example to increase that checkout waiting time 4 times (from default 50 to 200):

config :diamond, Diamond.Repo,
  username: ...
  password: ...
  ...
  ...
  pool_size: 15,
  queue_target: 200
1 Like