https://prefect.io logo
Join Slack
Powered by
hi, if i run self hosted prefect, the ui seems ver...
# prefect-ui
a
hi, if i run self hosted prefect, the ui seems very unresponsive when running lots of jobs concurrently, how would i make the ui/server be more responsive?
database is postgres gcp
redis, the prefect server and the services are running in a VM
n
how's the db if you check table sizes?
a
its currently at 30GB
been 3-4 days
n
have you considered a cleanup flow to delete old stuff? (docs example) or tweaking event retention?
if you're seeing specific perf bottlenecks, please do report them! but some level of cleanup will be required to keep things moving smoothly
a
yes i have it setup to retain flows and artifacts logs for 14 days
n
there are likely some ways in which the OSS UI is being a bad client that you might be running into here we are working on replatforming the UI so that we can iterate / more easily accept contributions on this front in the meantime, it'd be helpful if you could share what pages are bad/unresponsive so we can fix what we can at the API layer
👍 1
a
all the pages seem to freeze and when i check the logs it has the SQLAlchemy Pooling issue, but but, when i reset the db, the ui and the api becomes responsive again
n
interesting, have you customized the connection pooling settings at all? we may need to document better values for larger installations
a
the connection is th ethe same VPC and goes like this: 
PREFECT_API_DATABASE_CONNECTION_URL: <postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
with the private ip
nothing special
can it have something to do with the events table? it had about 3 million rows i think
70k flows in total
n
sorry, i mean there are sqlalchemy connection pool knobs you can turn https://github.com/PrefectHQ/prefect/issues/16299#issuecomment-2698732783
can it have something to do with the events table?
yea it might, which is why i was wondering if you had altered the event retention default
a
also if i shift to the prefect cloud, would i expect the same thing? or would these issues be resolved
n
there's a possible future where we allow you to plug in an OLAP situation to OSS that better deals with scale
but yes we handle rather extreme scale in cloud
✅ 1
a
altered the event retention
not really, i altered it to 1d to see if that helps with the responsiveness
Copy code
# Redis for messaging
      PREFECT_MESSAGING_BROKER: prefect_redis.messaging
      PREFECT_MESSAGING_CACHE: prefect_redis.messaging
      PREFECT_REDIS_MESSAGING_HOST: redis
      PREFECT_REDIS_MESSAGING_PORT: 6379
      PREFECT_REDIS_MESSAGING_DB: 0
      PREFECT_SERVER_EVENTS_CAUSAL_ORDERING: prefect_redis.ordering
      PREFECT_SERVER_CONCURRENCY_LEASE_STORAGE: prefect_redis.lease_storage

      # Logging optimizations
      PREFECT_LOGGING_TO_API_BATCH_INTERVAL: "10.0"
      PREFECT_LOGGING_TO_API_BATCH_SIZE: "8000000"

      # Event retention
      PREFECT_EVENTS_RETENTION_PERIOD: "1d"

      # Database connection pool tuning
      PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE: 20
      PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW: 40
      PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT: 60
      PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_RECYCLE: 3600
      PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_STATEMENT_CACHE_SIZE: 0

      # Worker heartbeat reduction
      PREFECT_WORKER_HEARTBEAT_SECONDS: 60
      PREFECT_WORKER_QUERY_SECONDS: 15
      PREFECT_WORKER_PREFETCH_SECONDS: 15

      # API keepalive
      PREFECT_SERVER_API_KEEPALIVE_TIMEOUT: 65
these are what i have set in the new deployment to reduce load from logging and events
n
the pool size and overflow values seem... potentially excessive on first glance (I could be wrong)
we should totally add some connection pool guidance in the docs here, will put up a PR
a
i have noticed it to help, its 5 and 10 by default
n
how many replicas of the server are you running though?
a
and the sql database i have setup can have about 200 concurrent requests
no replicas as of now
n
interesting ok, i suspect the improvement there is a bit of a bandaid and that the root issue is queries over the events table
you may be interested in using logfire to see what's going on at the db / api level (you can use their SDK as a pure OTEL exporter regardless of whether you want to use their SaaS UI)
a
will have a read
thanks for the help @Nate, like always, really appreciate it
n
catjam lmk if you check out logfire, they let you write SQL against their spans to make dashboards, so i have a couple setup i could share
15 Views
Open in Slack
PreviousNext
Powered by