Increase timeout for long requests

[altendorfme]

I have 2 servers:
Server 1 (4cpu - 4gb ram): Plausible, Caddy, PostgreSQL
Server 2 (4cpu - 16gb ram): Clickhouse

When I make requests for long periods, from January 1st until now, I see a large CPU usage in clickhouse, but it lasts for a few seconds and stops
On the Plausible screen, the request does not end and I get "loading" on the screen

In the Plausible logs:

  16:41:58.480 [error] Ch.Connection (#PID<0.3376.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.14306.275> timed out because it queued and checked out the connection for longer than 15000ms

#PID<0.14306.275> was at location:

    :prim_inet.recv0/3
    (mint 1.5.1) lib/mint/core/transport/tcp.ex:63: Mint.Core.Transport.TCP.recv/3
    (mint 1.5.1) lib/mint/http1.ex:500: Mint.HTTP1.recv/3
    (ch 0.1.14) lib/ch/connection.ex:240: Ch.Connection.recv/4
    (ch 0.1.14) lib/ch/connection.ex:218: Ch.Connection.receive_response/3
    (ch 0.1.14) lib/ch/connection.ex:154: Ch.Connection.handle_execute/4
    (db_connection 2.5.0) lib/db_connection/holder.ex:354: DBConnection.Holder.holder_apply/4
    (db_connection 2.5.0) lib/db_connection.ex:1432: DBConnection.run_execute/5

15:29:56.599 [error] Ranch protocol #PID<0.21838.273> of listener PlausibleWeb.Endpoint.HTTP (connection #PID<0.20439.273>, stream id 101) terminated
an exception was raised:
    ** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 3426ms. 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 (although this increases resource consumption)
  4. Allowing requests to wait longer by increasing :queue_target and :queue_interval

See DBConnection.start_link/2 for more information

        (ecto_sql 3.9.2) lib/ecto/adapters/sql.ex:913: Ecto.Adapters.SQL.raise_sql_call_error/1
        (ecto_ch 0.1.9) lib/ecto/adapters/clickhouse.ex:269: Ecto.Adapters.ClickHouse.execute/5
        (ecto 3.9.5) lib/ecto/repo/queryable.ex:229: Ecto.Repo.Queryable.execute/4
        (ecto 3.9.5) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
        (ecto 3.9.5) lib/ecto/repo/queryable.ex:151: Ecto.Repo.Queryable.one/3
        (elixir 1.14.3) lib/task/supervised.ex:89: Task.Supervised.invoke_mfa/2
        (elixir 1.14.3) lib/task/supervised.ex:34: Task.Supervised.reply/4
        (stdlib 4.2) proc_lib.erl:240: :proc_lib.init_p_do_apply/3

There's a way to increase the timeout, I don't need fast responses, long queries are rarely used and it doesn't make sense to have a bigger server for that.

[ruslandoga]

👋 @altendorfme

You can set timeout in the query string in CLICKHOUSE_DATABASE_URL env var, for example:

CLICKHOUSE_DATABASE_URL=http:https://127.0.0.1:8123/plausible_events_db?timeout=20000

The default is 15000 (ms)

That would resolve the first error,

Ch.Connection (#PID<0.3376.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.14306.275> timed out because it queued and checked out the connection for longer than 15000ms

The second error is harder to solve with just the URL, but maybe it won't be necessary after the timeout config change.

[altendorfme]

Very thanks, exactly what i need.

[Tony-MF]

👋 @altendorfme

You can set timeout in the query string in CLICKHOUSE_DATABASE_URL env var, for example:

CLICKHOUSE_DATABASE_URL=http:https://127.0.0.1:8123/plausible_events_db?timeout=20000

The default is 15000 (ms)

That would resolve the first error,

Ch.Connection (#PID<0.3376.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.14306.275> timed out because it queued and checked out the connection for longer than 15000ms

@ruslandoga may I know in what file is this defined?

I tried adding it to plausible-conf.env , restarted all containers, but the timeout is still 15 secs.

[ruslandoga]

It can be set in https://github.com/plausible/community-edition/blob/v2.1.1/plausible-conf.env

I tried adding it to plausible-conf.env , restarted all containers, but the timeout is still 15 secs.

plausible container needs to be recreated since Docker Compose doesn't do that automatically on env_file changes.
And ENV stuff is baked in when container is created.

[Tony-MF]

It can be set in https://github.com/plausible/community-edition/blob/v2.1.1/plausible-conf.env

I tried adding it to plausible-conf.env , restarted all containers, but the timeout is still 15 secs.

plausible container needs to be recreated since Docker Compose doesn't do that automatically on env_file changes. And ENV stuff is baked in when container is created.

Thanks, that helped. I also had to use plausible_events_db instead of the local IP.

CLICKHOUSE_DATABASE_URL=http:https://plausible_events_db:8123/plausible_events_db?timeout=120000