https://prefect.io logo
Join Slack
Powered by
<@ULVA73B9P> I have some errors causing my workpoo...
# marvin-ai
p
@Marvin I have some errors causing my workpool to crash it starts with a timeout error,
Copy code
c  1 19:50:20 dtftlp11 bash[2690]:  File "/data/conda/envs/datafetch/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 224, in __exit__
Dec  1 19:50:20 dtftlp11 bash[2690]:    raise exc_value.with_traceback(exc_tb)
Dec  1 19:50:20 dtftlp11 bash[2690]:  File "/data/conda/envs/datafetch/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 175, in _do_get
Dec  1 19:50:20 dtftlp11 bash[2690]:    return self._create_connection()
Dec  1 19:50:20 dtftlp11 bash[2690]:           ^^^^^^^^^^^^^^^^^^^^^^^^^
Dec  1 19:50:20 dtftlp11 bash[2690]:  File "/data/conda/envs/datafetch/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 388, in _create_connection
Dec  1 19:50:20 dtftlp11 bash[2690]:    return _ConnectionRecord(self)
Dec  1 19:50:20 dtftlp11 bash[2690]:           ^^^^^^^^^^^^^^^^^^^^^^^
Dec  1 19:50:20 dtftlp11 bash[2690]:  File "/data/conda/envs/datafetch/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 673, in __init__
Dec  1 19:50:20 dtftlp11 bash[2690]:    self.__connect()
Dec  1 19:50:20 dtftlp11 bash[2690]:  File "/data/conda/envs/datafetch/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 899, in __connect
Dec  1 19:50:20 dtftlp11 bash[2690]:    with util.safe_reraise():
Dec  1 19:50:20 dtftlp11 bash[2690]:         ^^^^^^^^^^^^^^^^^^^
Dec  1 19:50:20 dtftlp11 bash[2690]:  File "/data/conda/envs/datafetch/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 224, in __exit__
Dec  1 19:50:20 dtftlp11 bash[2690]:    raise exc_value.with_traceback(exc_tb)
Dec  1 19:50:20 dtftlp11 bash[2690]:  File "/data/conda/envs/datafetch/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 895, in __connect
Dec  1 19:50:20 dtftlp11 bash[2690]:    self.dbapi_connection = connection = pool._invoke_creator(self)
Dec  1 19:50:20 dtftlp11 bash[2690]:                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
Dec  1 19:50:20 dtftlp11 bash[2690]:  File "/data/conda/envs/datafetch/lib/python3.12/site-packages/sqlalchemy/engine/create.py", line 661, in connect
Dec  1 19:50:20 dtftlp11 bash[2690]:    return dialect.connect(*cargs, **cparams)
Dec  1 19:50:20 dtftlp11 bash[2690]:           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Dec  1 19:50:20 dtftlp11 bash[2690]:  File "/data/conda/envs/datafetch/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 629, in connect
Dec  1 19:50:20 dtftlp11 bash[2690]:    return self.loaded_dbapi.connect(*cargs, **cparams)  # type: ignore[no-any-return]  # NOQA: E501
Dec  1 19:50:20 dtftlp11 bash[2690]:           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Dec  1 19:50:20 dtftlp11 bash[2690]:  File "/data/conda/envs/datafetch/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 955, in connect
Dec  1 19:50:20 dtftlp11 bash[2690]:    await_only(creator_fn(*arg, **kw)),
Dec  1 19:50:20 dtftlp11 bash[2690]:    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Dec  1 19:50:20 dtftlp11 bash[2690]:  File "/data/conda/envs/datafetch/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 132, in await_only
Dec  1 19:50:20 dtftlp11 bash[2690]:    return current.parent.switch(awaitable)  # type: ignore[no-any-return,attr-defined] # noqa: E501
Dec  1 19:50:20 dtftlp11 bash[2690]:           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Dec  1 19:50:20 dtftlp11 bash[2690]:  File "/data/conda/envs/datafetch/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 196, in greenlet_spawn
Dec  1 19:50:20 dtftlp11 bash[2690]:    value = await result
Dec  1 19:50:20 dtftlp11 bash[2690]:            ^^^^^^^^^^^^
Dec  1 19:50:20 dtftlp11 bash[2690]:  File "/data/conda/envs/datafetch/lib/python3.12/site-packages/asyncpg/connection.py", line 2420, in connect
Dec  1 19:50:20 dtftlp11 bash[2690]:    async with compat.timeout(timeout):
Dec  1 19:50:20 dtftlp11 bash[2690]:               ^^^^^^^^^^^^^^^^^^^^^^^
Dec  1 19:50:20 dtftlp11 bash[2690]:  File "/data/conda/envs/datafetch/lib/python3.12/asyncio/timeouts.py", line 115, in __aexit__
Dec  1 19:50:20 dtftlp11 bash[2690]:    raise TimeoutError from exc_val
Dec  1 19:50:20 dtftlp11 bash[2690]: TimeoutError
then I have an internal error
Copy code
prefect.exceptions.PrefectHTTPStatusError: Server error '500 Internal Server Error' for url '<http://dtftlp11.mfi.inte:4200/api/deployments/f17ba367-fe08-4cf1-a6b6-af58e60eddcf>'
m
✅ thought for 131.1 seconds
Thanks for the traceback — this points to your Prefect API (self-hosted at http://dtftlp11.mfi.inte:4200) timing out when trying to open a PostgreSQL connection via asyncpg. When the API can’t reach its DB, it returns 500s to clients (your worker), which makes the work pool/worker look “crashy” even though the root cause is the API’s DB connectivity. What’s happening - TimeoutError originates in asyncpg during connect → the Prefect API cannot establish a DB connection in time. - Then your worker hits the API to fetch a deployment and receives a 500 Internal Server Error because the API request fails when it tries to query the DB. Quick triage checklist 1) Check API health/readiness - From the API host or a nearby machine:
Copy code
curl -sS <http://dtftlp11.mfi.inte:4200/api/health>
  curl -sS -i <http://dtftlp11.mfi.inte:4200/api/ready>
- /api/health should return 200 when the server process is up. - /api/ready checks DB connectivity. If it’s not 200, the API can’t reach the database. 2) Verify DB connectivity from the API host - Confirm you can connect to Postgres from the same host the API runs on:
Copy code
psql "host=<db-host> port=<port> dbname=<prefect_db> user=<user> password=<password>" -c "select 1"
If this fails, it’s networking/auth/SSL/firewall/DNS. 3) Confirm Prefect 3.x DB configuration (server side) Make sure your Prefect API is configured with an async Postgres URL and appropriate timeouts. In Prefect 3.x, set these environment variables for the API process: - Use an async driver (required):
Copy code
PREFECT_SERVER_DATABASE_CONNECTION_URL="postgresql+asyncpg://<user>:<password>@<host>:<port>/<database>"
If your password has special characters, URL-encode them. - If you prefer component settings instead of a full URL:
Copy code
PREFECT_SERVER_DATABASE_DRIVER="postgresql+asyncpg"
  PREFECT_SERVER_DATABASE_HOST="<host>"
  PREFECT_SERVER_DATABASE_PORT="5432"
  PREFECT_SERVER_DATABASE_USER="<user>"
  PREFECT_SERVER_DATABASE_PASSWORD="<password>"
  PREFECT_SERVER_DATABASE_NAME="<database>"
- Timeouts and pool (bump these if you see timeouts under load or slow networks):
Copy code
PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT="15.0"      # connect timeout (seconds)
  PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT="60.0" # wait time for a pooled connection
  PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE="10"
  PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW="20"
- If you’re behind PgBouncer (transaction mode), avoid prepared statement issues:
Copy code
PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_STATEMENT_CACHE_SIZE="0"
  PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_PREPARED_STATEMENT_CACHE_SIZE="0"
- TLS (if your Postgres requires it):
Copy code
PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_TLS_ENABLED="true"
  PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_TLS_CA_FILE="/path/to/ca.crt"
  PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_TLS_CERT_FILE="/path/to/client.crt"
  PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_TLS_KEY_FILE="/path/to/client.key"
  PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_TLS_CHECK_HOSTNAME="true"
4) Restart the API after config changes - After adjusting env vars for the API, restart the Prefect server process, then re-check:
Copy code
curl -i <http://dtftlp11.mfi.inte:4200/api/ready>
5) Look for clues in logs - Prefect API logs around the failure should show DB connection attempts and errors. - Postgres server logs (or PgBouncer logs) will show if connections are refused, auth failures, or pool exhaustion. Why this affects your worker/work pool - Workers just call the API. If the API can’t query the DB, workers receive 5xx errors on endpoints like 
/api/deployments/<id>
. Fixing the API’s DB connectivity should stop the work pool “crashes”. A couple of questions to help us pinpoint it: - Are you running self-hosted Prefect Server 3.x with Postgres (vs. SQLite)? Any PgBouncer in front?
- What is your current 
PREFECT_SERVER_DATABASE_CONNECTION_URL
(feel free to redact secrets)? - Do 
/api/health
and 
/api/ready
return 200? If not, what’s the status/body? - Any recent changes to your DB, network rules, or TLS configuration? If you share your server env settings (mask secrets) and a snippet of API logs around the failure, I can suggest exact values to try next.
p
@Marvin it happen just sometimes and I thinks it comes from a DNS error (we have some issues with DNS sometimes)
Copy code
"database": {
    "sqlalchemy": {
      "connect_args": {
        "application_name": null,
        "statement_cache_size": null,
        "prepared_statement_cache_size": null,
        "tls": {
          "enabled": false,
          "ca_file": null,
          "cert_file": null,
          "key_file": null,
          "check_hostname": true
        }
      },
      "pool_size": 5,
      "pool_recycle": 3600,
      "pool_timeout": 180,
      "max_overflow": -1
    },
    "connection_url": "**********",
    "driver": null,
    "host": null,
    "port": null,
    "user": null,
    "name": null,
    "password": null,
    "echo": false,
    "migrate_on_start": true,
    "timeout": 10,
    "connection_timeout": 5
  },
Maybe it could be a good option to increase some timeouts no?
m
✅ thought for 47.6 seconds
Intermittent DNS issues would absolutely present like this: asyncpg can’t resolve/connect within the connect timeout, the API returns 5xx, and the worker/pool looks unhealthy. Given your current settings, two pragmatic paths: A) Bypass DNS for the DB - Easiest/most reliable: use the DB’s IP in the connection URL (or add a static entry in /etc/hosts on the API host). - This removes DNS from the critical path entirely. B) Make the API more tolerant of slow/flaky DNS - Your 
connection_timeout
is 5s — that’s the time allowed to establish a new connection (includes DNS). Bump it to 20–30s. - Your 
pool_timeout
is already generous at 180s (time waiting for a pooled connection), that’s fine. - Consider slightly larger, bounded pooling so new connection attempts don’t explode under bursts: - `pool_size`: 10 (from 5) - `max_overflow`: 10–20 (instead of -1 which is unlimited) - Optionally lower 
pool_recycle
(e.g., 600–1800) to avoid occasionally stale connections lingering. - If you’re behind PgBouncer (transaction mode), disable prepared statement caching. Concrete env vars to try on the API (Prefect 3.x):
Copy code
PREFECT_SERVER_DATABASE_CONNECTION_URL="postgresql+asyncpg://<user>:<password>@<IP-or-host>:5432/<database>"

# Make connection establishment more tolerant of DNS slowness
PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT="30.0"   # was 5

# Optional: if DB queries are sometimes slow, increase statement timeout
PREFECT_SERVER_DATABASE_TIMEOUT="30.0"              # was 10

# Pooling: increase capacity and bound overflow
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE="10"   # was 5
PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW="15" # was -1 (unbounded)
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT="180"
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_RECYCLE="1200"

# If using PgBouncer (transaction mode), avoid prepared statements
PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_STATEMENT_CACHE_SIZE="0"
PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_PREPARED_STATEMENT_CACHE_SIZE="0"
After applying, restart the API and check:
Copy code
curl -i <http://dtftlp11.mfi.inte:4200/api/ready>
- 200 OK means the API can reach the DB; if it flips to non-200 during DNS hiccups, increasing 
PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT
should reduce false “down” windows. A couple of clarifying questions so I can tailor the recommendations: - Can you switch the 
connection_url
to use the DB’s IP (even temporarily) to confirm DNS is the root cause? - Are you using PgBouncer in front of Postgres? - Roughly how many concurrent API requests do you see during busy periods (to size the pool appropriately)? If you share your redacted 
connection_url
(hostname vs IP) and whether PgBouncer is in play, I can suggest exact values to settle on.
p
@Marvin I'm using a postgres database, does these "PREFECT_SERVER_DATABASE_SQLALCHEMY" config apply for this ?
m
✅ thought for 152.4 seconds
Yes. In Prefect 3.x, those 
PREFECT_SERVER_DATABASE_SQLALCHEMY_*
environment variables apply to the Prefect Server API’s SQLAlchemy engine regardless of whether you’re using PostgreSQL (asyncpg) or SQLite. For PostgreSQL specifically, they’re the right knobs to tune pool behavior and connect args. Key ones you can safely adjust with Postgres: - Pool sizing and timeouts - 
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE
- 
PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW
- 
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT
- 
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_RECYCLE
- Connect-timeout and statement-timeout - 
PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT
(connect establishment; includes DNS) - 
PREFECT_SERVER_DATABASE_TIMEOUT
(statement execution) - Postgres-specific connect args (asyncpg) - 
PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_APPLICATION_NAME
- 
PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_STATEMENT_CACHE_SIZE
- 
PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_PREPARED_STATEMENT_CACHE_SIZE
- TLS fields under 
...CONNECT_ARGS_TLS_*
if you need SSL/mTLS Note on names/nesting: - Use single underscores for nesting, e.g. 
PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_STATEMENT_CACHE_SIZE
(not double underscores). - These settings apply when your API is using an async Postgres URL like 
postgresql+asyncpg://...
. Given intermittent DNS issues, the most impactful changes are: - Increase connect timeout:
Copy code
PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT="30.0"
- Optionally increase statement timeout if queries are slow:
Copy code
PREFECT_SERVER_DATABASE_TIMEOUT="30.0"
- Right-size and bound the pool:
Copy code
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE="10"
  PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW="15"
  PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT="180"
  PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_RECYCLE="1200"
And if DNS flakiness persists, consider using the DB’s IP (or a hosts file entry) in your 
PREFECT_SERVER_DATABASE_CONNECTION_URL
to remove DNS from the critical path.
3 Views
Open in Slack
PreviousNext
Powered by