Ecto - Troubleshooting
28 Dec 2018- (DBConnection.ConnectionError) connection not available and request was dropped from queue after 171ms
- (DBConnection.ConnectionError) client timed out because it queued and checked out the connection for longer than 15000ms
- (Postgrex.Error) FATAL 53300 (too_many_connections) sorry, too many clients already
- (Postgrex.QueryError) postgresql protocol can not handle 158816 parameters, the maximum is 65535
(DBConnection.ConnectionError) connection not available and request was dropped from queue after 171ms
error occurs when importing lots of entries in batches (batch size is 1000) with
Ecto.Repo.insert_all/3
:
[error] Postgrex.Protocol (#PID<0.463.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.553.0> exited
...
** (EXIT) an exception was raised:
** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 171ms.
You can configure how long requests wait in the queue using :queue_target and :queue_interval.
See DBConnection.start_link/2 for more information
(ecto_sql) lib/ecto/adapters/sql.ex:604: Ecto.Adapters.SQL.raise_sql_call_error/1
(ecto_sql) lib/ecto/adapters/sql.ex:513: Ecto.Adapters.SQL.insert_all/8
(ecto) lib/ecto/repo/schema.ex:56: Ecto.Repo.Schema.do_insert_all/6
solution
https://github.com/elixir-ecto/ecto/issues/2833#issuecomment-440400022
pool_timeout has no effect anymore in favor of a much improved queue system.
https://hexdocs.pm/db_connection/DBConnection.html#start_link/2-queue-config
Handling requests is done through a queue.
:queue_target in milliseconds, defaults to 50 :queue_interval in milliseconds, defaults to 1000
Our goal is to stay under :queue_target for :queue_interval. In case we can’t reach that, then we double the :queue_target. If we go above that, then we start dropping messages.
For example, by default our queue time is 50ms. If we stay above 50ms for a whole second, we double the target to 100ms and we start dropping messages once it goes above the new limit.
# config/prod.secret.exs
config :my_app, MyApp.Repo,
username: "my_app_dev",
password: "my_app_dev",
database: "my_app_dev",
pool_size: 30,
- timeout: 120_000
+ timeout: 120_000,
+ queue_target: 5000,
+ queue_interval: 100_000
(DBConnection.ConnectionError) client timed out because it queued and checked out the connection for longer than 15000ms
iex> MyApp.User.Mutator.delete_all()
[error] Postgrex.Protocol (#PID<0.439.0>) disconnected: ** (DBConnection.ConnectionError)
client #PID<0.478.0> timed out because it queued and checked out the connection
for longer than 15000ms
** (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) lib/ecto/adapters/sql.ex:604: Ecto.Adapters.SQL.raise_sql_call_error/1
(ecto_sql) lib/ecto/adapters/sql.ex:537: Ecto.Adapters.SQL.execute/5
solution
increase the value of timeout
option (time to wait for query call to finish)
which defaults to 15_000 ms:
https://github.com/elixir-ecto/ecto/issues/1838#issuecomment-265692336
You can either Ecto.Adapters.SQL.query(Repo, query, params, timeout: 30_000) or by setting the timeout: 30_000 in your Repo configuration in your config/* files.
-
on per a repository operation basis
https://hexdocs.pm/ecto/Ecto.Repo.html#module-shared-options
Almost all of the repository operations below accept the following options:
:timeout - The time in milliseconds to wait for the query call to finish, :infinity will wait indefinitely (default: 15000);
MyApp.Repo.delete_all(MyApp.User, timeout: 120_000)
-
for all repository operations via repository configuration
https://hexdocs.pm/ecto/2.0.0/Ecto.Adapters.Postgres.html
Compile time options
:timeout - The default timeout to use on queries, defaults to 15000
# config/prod.secret.exs config :my_app, MyApp.Repo, username: "my_app_prod", password: "my_app_prod", database: "my_app_prod", pool_size: 30, timeout: 120_000
NOTE: setting
timeout
option inMyApp.Repo
module has no effect:# lib/my_app/repo.ex defmodule MyApp.Repo do use Ecto.Repo, otp_app: :my_app, adapter: Ecto.Adapters.Postgres, timeout: 120_000 end
set breakpoint in Ecto.Adapters.SQL.execute/5
to see actual options being used
when performing repository operation:
# deps/ecto_sql/lib/ecto/adapters/sql.ex
def execute(adapter_meta, query_meta, prepared, params, opts) do
require IEx; IEx.pry
# print `adapter_meta`
%{num_rows: num, rows: rows} =
execute!(adapter_meta, prepared, params, put_source(opts, query_meta))
{num, rows}
end
(Postgrex.Error) FATAL 53300 (too_many_connections) sorry, too many clients already
I got this error when running lots of tests asynchronously:
$ mix test
...
module=DBConnection.Connection [error] Postgrex.Protocol (#PID<0.1208.0>)
failed to connect: ** (Postgrex.Error) FATAL 53300 (too_many_connections)
sorry, too many clients already
solution
there are 2 ways to fix this error:
- increase
max_connections
in postgresql.conf - or decrease
pool_size
in config/test.secret.exs
(Postgrex.QueryError) postgresql protocol can not handle 158816 parameters, the maximum is 65535
error occurs when importing too many entries:
[error] Postgrex.Protocol (#PID<0.2502.0>) disconnected: ** (Postgrex.QueryError) postgresql protocol can not handle 158816 parameters, the maximum is 65535
...
** (Postgrex.QueryError) postgresql protocol can not handle 158816 parameters, the maximum is 65535
(ecto_sql 3.4.2) lib/ecto/adapters/sql.ex:612: Ecto.Adapters.SQL.raise_sql_call_error/1
(ecto_sql 3.4.2) lib/ecto/adapters/sql.ex:521: Ecto.Adapters.SQL.insert_all/8
(ecto 3.4.0) lib/ecto/repo/schema.ex:54: Ecto.Repo.Schema.do_insert_all/6
(yancy 0.1.0) lib/yancy/yd/agency_client/operations/collect_daily_campaign_reports.ex:56: anonymous fn/3 in Yancy.YD.AgencyClient.Operations.CollectDailyCampaignReports.import_campaign_reports/3
solution
insert entries in batches: if row has 20 columns, max batch size must be
65536 / 20 = floor(3276)
.