langgenius/dify
1
0
Fork 0
mirror of https://github.com/langgenius/dify.git synced 2026-07-23 04:55:28 -04:00
Code Issues 775 Packages Projects Releases 10 Wiki Activity

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "db" (172.28.0.4), port 5432 failed: FATAL: sorry, too many clients already #4304

New Issue
Closed
opened 2026-02-21 18:05:40 -05:00 by yindo · 1 comment
yindo commented 2026-02-21 18:05:40 -05:00
Owner
Copy Link
Copy Source

Originally created by @majestichou on GitHub (Jun 27, 2024).

Self Checks

  • This is only for bug report, if you would like to ask a question, please head to Discussions.
  • I have searched for existing issues search for existing issues, including closed ones.
  • I confirm that I am using English to submit this report (我已阅读并同意 Language Policy).
  • 请务必使用英文提交 Issue，否则会被关闭。谢谢！:）
  • Please do not modify this template :) and fill in all the required fields.

Dify version

0.6.11

Cloud or Self Hosted

Self Hosted (Docker)

Steps to reproduce

  1. Local deployment using dify's 0.6.11 docker image
  2. cd dify-0.6.11/docker
  3. Add the following environment variables to the api service and worker service in the docker-compose.yaml file:
      UPLOAD_FILE_SIZE_LIMIT: 30
      UPLOAD_FILE_BATCH_LIMIT: 1000
      TENANT_DOCUMENT_COUNT: 100000
      SERVER_WORKER_AMOUNT: 48
      CELERY_WORKER_AMOUNT: 4
  1. Accounts were opened for 10 users for their daily use
  2. After using it for a few days, I found that dify's AI assistant often failed to reply to users' questions.
  3. Check the logs of the docker-api-1 container. The following error information is displayed:
Traceback (most recent call last):
  File "/app/api/core/app/apps/chat/app_generator.py", line 177, in _generate_worker
    conversation = self._get_conversation(conversation_id)
  File "/app/api/core/app/apps/message_based_app_generator.py", line 269, in _get_conversation
    .first()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2728, in first
    return self.limit(1)._iter().first()  # type: ignore
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2827, in _iter
    result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2351, in execute
    return self._execute_internal(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2226, in _execute_internal
    conn = self._connection_for_bind(bind)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2095, in _connection_for_bind
    return trans._connection_for_bind(engine, execution_options)
  File "<string>", line 2, in _connection_for_bind
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1189, in _connection_for_bind
    conn = bind.connect()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3276, in connect
    return self._connection_cls(self)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 148, in __init__
    Connection._handle_dbapi_exception_noconnection(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2440, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 146, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3300, in raw_connection
    return self.pool.connect()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 179, in _do_get
    with util.safe_reraise():
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
    self.__connect()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 900, in __connect
    with util.safe_reraise():
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 643, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 620, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.10/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "db" (172.28.0.4), port 5432 failed: FATAL:  sorry, too many clients already

How can the number of database connections be saturated when the number of users does not exceed 10?
View the official document of dify, https://docs.dify.ai/v/zh-hans/getting-started/install-self-hosted/environments#:~:text=SQLALCHEMY_POOL_SIZE%EF%BC%9A%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0%E5%A4%A7%E5%B0%8F%EF%BC%8C%E9%BB%98%E8%AE%A4%2030%20%E4%B8%AA%E8%BF%9E%E6%8E%A5%E6%95%B0%EF%BC%8C%E5%8F%AF%E9%80%82%E5%BD%93%E5%A2%9E%E5%8A%A0.
The default value of SQLALCHEMY_POOL_SIZE is 30 and can be increased. I think 30 connections should be enough for 10 people.

✔️ Expected Behavior

dify works well

❌ Actual Behavior

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "db" (172.28.0.4), port 5432 failed: FATAL: sorry, too many clients already

Originally created by @majestichou on GitHub (Jun 27, 2024). ### Self Checks - [X] This is only for bug report, if you would like to ask a question, please head to [Discussions](https://github.com/langgenius/dify/discussions/categories/general). - [X] I have searched for existing issues [search for existing issues](https://github.com/langgenius/dify/issues), including closed ones. - [X] I confirm that I am using English to submit this report (我已阅读并同意 [Language Policy](https://github.com/langgenius/dify/issues/1542)). - [X] 请务必使用英文提交 Issue，否则会被关闭。谢谢！:） - [X] Please do not modify this template :) and fill in all the required fields. ### Dify version 0.6.11 ### Cloud or Self Hosted Self Hosted (Docker) ### Steps to reproduce 1. Local deployment using dify's 0.6.11 docker image 2. cd dify-0.6.11/docker 3. Add the following environment variables to the api service and worker service in the docker-compose.yaml file: ``` UPLOAD_FILE_SIZE_LIMIT: 30 UPLOAD_FILE_BATCH_LIMIT: 1000 TENANT_DOCUMENT_COUNT: 100000 SERVER_WORKER_AMOUNT: 48 CELERY_WORKER_AMOUNT: 4 ``` 4. Accounts were opened for 10 users for their daily use 5. After using it for a few days, I found that dify's AI assistant often failed to reply to users' questions. 6. Check the logs of the docker-api-1 container. The following error information is displayed: ``` Traceback (most recent call last): File "/app/api/core/app/apps/chat/app_generator.py", line 177, in _generate_worker conversation = self._get_conversation(conversation_id) File "/app/api/core/app/apps/message_based_app_generator.py", line 269, in _get_conversation .first() File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2728, in first return self.limit(1)._iter().first() # type: ignore File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2827, in _iter result: Union[ScalarResult[_T], Result[_T]] = self.session.execute( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2351, in execute return self._execute_internal( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2226, in _execute_internal conn = self._connection_for_bind(bind) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2095, in _connection_for_bind return trans._connection_for_bind(engine, execution_options) File "<string>", line 2, in _connection_for_bind File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go ret_value = fn(self, *arg, **kw) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1189, in _connection_for_bind conn = bind.connect() File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3276, in connect return self._connection_cls(self) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 148, in __init__ Connection._handle_dbapi_exception_noconnection( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2440, in _handle_dbapi_exception_noconnection raise sqlalchemy_exception.with_traceback(exc_info[2]) from e File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 146, in __init__ self._dbapi_connection = engine.raw_connection() File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3300, in raw_connection return self.pool.connect() File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 449, in connect return _ConnectionFairy._checkout(self) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout fairy = _ConnectionRecord.checkout(pool) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 712, in checkout rec = pool._do_get() File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 179, in _do_get with util.safe_reraise(): File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__ raise exc_value.with_traceback(exc_tb) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get return self._create_connection() File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection return _ConnectionRecord(self) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 674, in __init__ self.__connect() File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 900, in __connect with util.safe_reraise(): File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__ raise exc_value.with_traceback(exc_tb) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 896, in __connect self.dbapi_connection = connection = pool._invoke_creator(self) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 643, in connect return dialect.connect(*cargs, **cparams) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 620, in connect return self.loaded_dbapi.connect(*cargs, **cparams) File "/usr/local/lib/python3.10/site-packages/psycopg2/__init__.py", line 122, in connect conn = _connect(dsn, connection_factory=connection_factory, **kwasync) sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "db" (172.28.0.4), port 5432 failed: FATAL: sorry, too many clients already ``` How can the number of database connections be saturated when the number of users does not exceed 10? View the official document of dify, https://docs.dify.ai/v/zh-hans/getting-started/install-self-hosted/environments#:~:text=SQLALCHEMY_POOL_SIZE%EF%BC%9A%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0%E5%A4%A7%E5%B0%8F%EF%BC%8C%E9%BB%98%E8%AE%A4%2030%20%E4%B8%AA%E8%BF%9E%E6%8E%A5%E6%95%B0%EF%BC%8C%E5%8F%AF%E9%80%82%E5%BD%93%E5%A2%9E%E5%8A%A0. The default value of SQLALCHEMY_POOL_SIZE is 30 and can be increased. I think 30 connections should be enough for 10 people. ### ✔️ Expected Behavior dify works well ### ❌ Actual Behavior sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "db" (172.28.0.4), port 5432 failed: FATAL: sorry, too many clients already
yindo added the 🐞 bug label 2026-02-21 18:05:40 -05:00
yindo closed this issue 2026-02-21 18:05:40 -05:00
yindo commented 2026-02-21 18:05:40 -05:00
Author
Owner
Copy Link
Copy Source

@dosubot[bot] commented on GitHub (Jun 27, 2024):

The number of database connections can be saturated even with a small number of users due to the configuration of the SQLAlchemy connection pool. The SQLALCHEMY_POOL_SIZE and SQLALCHEMY_MAX_OVERFLOW settings determine the maximum number of connections that can be created.

Here are the relevant configurations:

SQLALCHEMY_POOL_SIZE: NonNegativeInt = Field(
    description='pool size of SqlAlchemy',
    default=30,
)

SQLALCHEMY_MAX_OVERFLOW: NonNegativeInt = Field(
    description='max overflows for SqlAlchemy',
    default=10,
)
  • SQLALCHEMY_POOL_SIZE is set to 30, which means the pool can hold up to 30 connections.
  • SQLALCHEMY_MAX_OVERFLOW is set to 10, which means up to 10 additional connections can be created if all pool connections are in use.

This means a total of 40 connections (30 + 10) can be created. If each user opens multiple connections or if there are background tasks (e.g., Celery tasks) that also use database connections, the pool can be quickly exhausted, leading to saturation [1].

To mitigate this issue, you can increase the SQLALCHEMY_POOL_SIZE and SQLALCHEMY_MAX_OVERFLOW values in your docker-compose.yaml file:

services:
  api:
    environment:
      - SQLALCHEMY_POOL_SIZE=50
      - SQLALCHEMY_MAX_OVERFLOW=20
  worker:
    environment:
      - SQLALCHEMY_POOL_SIZE=50
      - SQLALCHEMY_MAX_OVERFLOW=20

Additionally, ensure that your database server is configured to handle the increased number of connections.

To continue talking to Dosu, mention @dosu.

@dosubot[bot] commented on GitHub (Jun 27, 2024): <!-- Answer --> The number of database connections can be saturated even with a small number of users due to the configuration of the SQLAlchemy connection pool. The `SQLALCHEMY_POOL_SIZE` and `SQLALCHEMY_MAX_OVERFLOW` settings determine the maximum number of connections that can be created. Here are the relevant configurations: ```py SQLALCHEMY_POOL_SIZE: NonNegativeInt = Field( description='pool size of SqlAlchemy', default=30, ) SQLALCHEMY_MAX_OVERFLOW: NonNegativeInt = Field( description='max overflows for SqlAlchemy', default=10, ) ``` - `SQLALCHEMY_POOL_SIZE` is set to 30, which means the pool can hold up to 30 connections. - `SQLALCHEMY_MAX_OVERFLOW` is set to 10, which means up to 10 additional connections can be created if all pool connections are in use. This means a total of 40 connections (30 + 10) can be created. If each user opens multiple connections or if there are background tasks (e.g., Celery tasks) that also use database connections, the pool can be quickly exhausted, leading to saturation <sup>[[1]](https://github.com/langgenius/dify/blob/main/api/configs/middleware/__init__.py)</sup>. To mitigate this issue, you can increase the `SQLALCHEMY_POOL_SIZE` and `SQLALCHEMY_MAX_OVERFLOW` values in your `docker-compose.yaml` file: ```yaml services: api: environment: - SQLALCHEMY_POOL_SIZE=50 - SQLALCHEMY_MAX_OVERFLOW=20 worker: environment: - SQLALCHEMY_POOL_SIZE=50 - SQLALCHEMY_MAX_OVERFLOW=20 ``` Additionally, ensure that your database server is configured to handle the increased number of connections. <!-- Dosu Comment Footer --> *To continue talking to [Dosu](https://dosu.dev), mention @dosu.*
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
Knowledge Pipeline
Proposal
WIP
cloud
dependencies
feat:webapp
good first issue
outdated
performance
pull-request
Mirrored from GitHub Pull Request
 python:uv
refactor
size:L
stale
status: accepting prs
status: blocked
status: in discussion
stt & tts
vulnerablity
web
wont-fix
🌊 feat:workflow
🌚 invalid
🐞 bug
👻 feat:rag
💪 enhancement
📚 documentation
🔨 feat:tools
🙋‍♂️ question
🙏 help wanted
🤔 cant-reproduce
🤖 feat:agent
No Label 🐞 bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
yindo
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: langgenius/dify#4304
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?