DB Connection Error - connection not available and request was dropped

Phoenix Forum Questions / Help
, , , , ,
1

I get the following intermittent test failure errors for some queries.

 ** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 1102ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:
     
       1. Ensuring your database is available and that you can connect to it
       2. Tracking down slow queries and making sure they are running fast enough
       3. Increasing the pool_size (albeit it increases resource consumption)
       4. Allowing requests to wait longer by increasing :queue_target and :queue_interval
     
     See DBConnection.start_link/2 for more information

How should I resolve this? I have three config files, test, dev and pre_prod. I don’t think increasing the pool size in pre_prod contributes to test execution.

dev.exs Config

config :my_app, MyApp.Repo,
   username: "postgres",
   password: "postgres",
   database: "my_app_dev",
   hostname: "localhost",
   show_sensitive_data_on_connection_error: true,
   pool_size: 10

pre_prod.exs Config

config :my_app, MyApp.Repo,
  username: "postgres",
  password: "postgres",
  database: "my_app_preprod",
  hostname: "db",
  show_sensitive_data_on_connection_error: true,
  pool_size: 10

test.exs Config

config :my_app, MyApp.Repo,
  username: "postgres",
  password: "postgres",
  database: "my_app_test#{System.get_env("MIX_TEST_PARTITION")}",
  hostname: "localhost",
  pool: Ecto.Adapters.SQL.Sandbox
2

Can you post the queries?

3

This is one of the query tests

  test "query comments with sortBy inserted_at and sortOrder DESC", context do
       current_user = tennat_with_current_user(context.tenant)
 
       request_and_parse_gql(
         """
         query {
             comments(sort: { sortBy: INSERTED_AT, sortOrder: DESC}) {
                 entries{
                     id
                 }
             }
         }
         """

,
         %{current_user: current_user}
       )
       |> graphql_response(
         expected_response(
           [
             context.comment4,
             context.comment3,
             context.comment2,
             context.comment1
           ],
           [:id]
         ),
         200
       )
 
       parse_graphql(
         """
           query {
             comments(sort: { sortBy: INSERTED_AT, sortOrder: DESC}, limit: 2) {
               paginationMetadata {
                 after
                 before
                 limit
                 totalCount
                 totalCountCapExceeded
               }
             }
           }
         """,
         %{current_user: current_user}
       )
       |> graphql_response(
         %{
           "comments" => %{
             "paginationMetadata" => %{
               "after" =>
                 Paginator.cursor_for_record(
                   context.comment3,
                   id: :asc,
                   inserted_at: :desc
                 ),
               "before" => nil,
               "limit" => 2,
               "totalCount" => 4,
               "totalCountCapExceeded" => false
             }
           }
         },
         200
       )
     end
4

Not GraphQL queries. Post the SQL queries.

5

can I know where I can find this

6

One cause of this in tests: parallel tests that all get blocked waiting for each other’s DB transaction to commit/rollback.

For instance, let’s say you have a Company schema with a name field covered by a unique index.

If multiple tests try to insert a record with the same name, all but the first one will block until the first one completes - and since ex_unit starts System.schedulers_online * 2 cases in parallel, this can cause connection starvation like you’re seeing if the pool is too small.

Boosting the pool size can help, but the better fix is to make sure your code either:

  • inserts the shared records before switching the sandbox on
  • inserts unique values into the unique-indexed column (with an ExMachina sequence, for instance)
1 Like
7

Hi! Is it possible to get an explanation how one can: