QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00 #6208
Comments
|
Hi @khteh, Thanks. |
|
2 |
|
Can you please share the value of |
What's this? |
Are you using PostgreSQL as your pgAdmin config DB? |
|
No. |
|
Hi @khteh, Please share some screenshot/recording on exactly when the error comes. Thanks. |
|
It's k8s Statefulset! |
|
It recurs from time to time although AWS RDS console shows less than the magic 5 shown in the stack strace. |
|
Hi! My configuration:
config_local.py: # 6432 - direct PG port
CONFIG_DATABASE_URI="postgresql://pgadmin:pwdpwd@pgadmin.my.domain:6432/pgadmin?sslmode=verify-full&sslrootcert=/cacert.pem&sslcert=/certs/server.cert&sslkey=/certs/server.key"
# Tried to add this, but didn't work:
SQLALCHEMY_POOL_SIZE = 20
SQLALCHEMY_MAX_OVERFLOW = 100pgAdmin works correctly a couple days, but then all pgadmin pages hang during loading and then give and error message To fix that I have to restart docker container of pgadmin. Also I can't connect pgadmin to pgbouncer because of sqlachemy pooler - it's lost connection to pgadmin DB and forces to relogin. I tried to get SELECT server.id AS server_id, server.user_id AS server_user_id, server.servergroup_id AS server_servergroup_id, server.name AS server_name, server.host AS server_host, server.port AS server_port, server.maintenance_db AS server_maintenance_db, server.username AS server_username, server.password AS server_password, server.save_password AS server_save_password, server.role AS server_role, server.comment AS server_comment, server.discovery_id AS server_discovery_id, server.db_res AS server_db_res, server.passexec_cmd AS server_passexec_cmd, server.passexec_expiration AS server_passexec_expiration, server.bgcolor AS server_bgcolor, server.fgcolor AS server_fgcolor, server.service AS server_service, server.use_ssh_tunnel AS server_use_ssh_tunnel, server.tunnel_host AS server_tunnel_host, server.tunnel_port AS server_tunnel_port, server.tunnel_username AS server_tunnel_username, server.tunnel_authentication AS server_tunnel_authentication, server.tunnel_identity_file AS server_tunnel_identity_file, server.tunnel_If you get
|
|
Hi! statement_timeout = 3600000 # in milliseconds, 0 is disabled
idle_in_transaction_session_timeout = 3600000 # in milliseconds, 0 is disabled
|
|
@ish1mura |
|
Hi! OS configuration:
Files in attached archive are from my configuration (some of data was hidden): 1 ) 2 ) 3 ) 4 ) Then run Above 50 users work on my pgAdmin server, they run a lot of queries. pgAdmin works correctly a couple days, but then all pgadmin pages hang during loading and then give and error message If you open pgadmin database at this time, you'll see 10+ queries in state SELECT server.id AS server_id, server.user_id AS server_user_id, server.servergroup_id AS server_servergroup_id, server.name AS server_name, server.host AS server_host, server.port AS server_port, server.maintenance_db AS server_maintenance_db, server.username AS server_username, server.password AS server_password, server.save_password AS server_save_password, server.role AS server_role, server.comment AS server_comment, server.discovery_id AS server_discovery_id, server.db_res AS server_db_res, server.passexec_cmd AS server_passexec_cmd, server.passexec_expiration AS server_passexec_expiration, server.bgcolor AS server_bgcolor, server.fgcolor AS server_fgcolor, server.service AS server_service, server.use_ssh_tunnel AS server_use_ssh_tunnel, server.tunnel_host AS server_tunnel_host, server.tunnel_port AS server_tunnel_port, server.tunnel_username AS server_tunnel_username, server.tunnel_authentication AS server_tunnel_authentication, server.tunnel_identity_file AS server_tunnel_identity_file, server.tunnel_For now I fixed that bug by adding 31 * * * * postgres psql -p 6432 -c "SELECT pg_terminate_backend(a.pid) FROM pg_stat_activity a WHERE a.state = 'idle in transaction' and current_timestamp - a.query_start > '30min';" |
|
@akshay-joshi @yogeshmahajan-1903 We are also facing the same issue by having the config database as default SQLLITE. Couldnt reproduce the issue so far. May I know whether it is possible to have like this as said here so that the connections it can be controlled by max_connections in target database. Correct me if I am wrong. Thanks. The pool can be configured to have unlimited overflow by setting create_engine.max_overflow to the value “-1”. With this setting, the pool will still maintain a fixed pool of connections, however it will never block upon a new connection being requested; it will instead unconditionally make a new connection if none are available. |
|
Just FYI, have established 471 DB sessions and made idle from PGadmin to a postgres DB cluster. However, I am not able to reproduce the issue. Could someone help me to understand how to utilize all pool_size=5 & max_overflow=10 DB connections from pool. In our pgadmin pod have found pgadmin process with 25 threads. In that case, am I good to have [(5+10) x 25] = 375 concurrent connections ? In our target database(postgres) parameter idle_in_transaction_session_timeout = 0 (disabled). |
|
Hi! SELECT anon_1.user_id AS anon_1_user_id, anon_1.user_email AS anon_1_user_email, anon_1.user_username AS anon_1_user_username, anon_1.user_password AS anon_1_user_password, anon_1.user_active AS anon_1_user_active, anon_1.user_confirmed_at AS anon_1_user_confirmed_at, anon_1.user_masterpass_check AS anon_1_user_masterpass_check, anon_1.user_auth_source AS anon_1_user_auth_source, anon_1.user_fs_uniquifier AS anon_1_user_fs_uniquifier, anon_1.user_login_attempts AS anon_1_user_login_attempts, anon_1.user_locked AS anon_1_user_locked, role_1.id AS role_1_id, role_1.name AS role_1_name, role_1.description AS role_1_description FROM (SELECT "user".id AS user_id, "user".email AS user_email, "user".username AS user_username, "user".password AS user_password, "user".active AS user_active, "user".confirmed_at AS user_confirmed_at, "user".masterpass_check AS user_masterpass_check, "user".auth_source AS user_auth_source, "user".fs_uniquifier AS user_fs_uniquifier, "user".login_attempts AS user_login_attempts, "user".At this time there are 8 hanging queries: I parsed pgadmin logs and saw that after those queries start time there are an errors: QUERY WITH START TIME 2023-07-31 09:22:47
2023-07-31 09:23:01,147: ERROR pgadmin: <asyncio.locks.Lock object at 0x7f1c0ee2bf50 [unlocked, waiters:1]> is bound to a different event loop
2023-07-31 09:23:03,613: ERROR pgadmin: <asyncio.locks.Lock object at 0x7f1c0ee2bf50 [unlocked, waiters:1]> is bound to a different event loop
-----------
QUERY WITH START TIME 2023-07-31 11:22:21
2023-07-31 11:22:25,131: ERROR pgadmin: <asyncio.locks.Lock object at 0x7f1c0f1472d0 [unlocked, waiters:1]> is bound to a different event loop
-----------
QUERY WITH START TIME 2023-07-31 12:03:10
2023-07-31 12:04:03,155: ERROR pgadmin: <asyncio.locks.Lock object at 0x7f1c265e2190 [unlocked, waiters:1]> is bound to a different event loop
2023-07-31 12:04:04,510: ERROR pgadmin: <asyncio.locks.Lock object at 0x7f1c265e2190 [unlocked, waiters:1]> is bound to a different event loop
2023-07-31 12:04:05,336: ERROR pgadmin: <asyncio.locks.Lock object at 0x7f1c265e2190 [unlocked, waiters:1]> is bound to a different event loop
-----------
QUERY WITH START TIME 2023-08-01 10:51:00
2023-08-01 10:51:11,634: ERROR pgadmin: <asyncio.locks.Lock object at 0x7f1c24265250 [unlocked, waiters:1]> is bound to a different event loop
2023-08-01 10:51:13,081: ERROR pgadmin: <asyncio.locks.Lock object at 0x7f1c24265250 [unlocked, waiters:1]> is bound to a different event loop
2023-08-01 10:51:13,716: ERROR pgadmin: <asyncio.locks.Lock object at 0x7f1c24265250 [unlocked, waiters:1]> is bound to a different event loop
2023-08-01 10:51:14,441: ERROR pgadmin: <asyncio.locks.Lock object at 0x7f1c24265250 [unlocked, waiters:1]> is bound to a different event loop
2023-08-01 10:51:14,885: ERROR pgadmin: <asyncio.locks.Lock object at 0x7f1c24265250 [unlocked, waiters:1]> is bound to a different event loop
-----------
QUERY WITH START TIME 2023-08-01 11:59:01
No errors after
-----------
QUERY WITH START TIME 2023-08-01 14:19:36
2023-08-01 14:19:39,964: ERROR pgadmin: <asyncio.locks.Lock object at 0x7f1c24267550 [unlocked, waiters:1]> is bound to a different event loop
2023-08-01 14:19:41,639: ERROR pgadmin: <asyncio.locks.Lock object at 0x7f1c24267550 [unlocked, waiters:1]> is bound to a different event loop
-----------
QUERY WITH START TIME 2023-08-01 15:26:31
No errors after
-----------
ALL ERRORS HAVE SAME TRACEBACK:
Traceback (most recent call last):
File "/pgadmin4/pgadmin/utils/driver/psycopg3/cursor.py", line 281, in execute
return asyncio.run(self._execute(query, params))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3.11/asyncio/runners.py", line 190, in run
return runner.run(main)
^^^^^^^^^^^^^^^^
File "/usr/lib/python3.11/asyncio/runners.py", line 118, in run
return self._loop.run_until_complete(task)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3.11/asyncio/base_events.py", line 653, in run_until_complete
return future.result()
^^^^^^^^^^^^^^^
File "/pgadmin4/pgadmin/utils/driver/psycopg3/cursor.py", line 292, in _execute
return await _async_cursor.execute(self, query, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/venv/lib/python3.11/site-packages/psycopg/cursor_async.py", line 89, in execute
async with self._conn.lock:
File "/usr/lib/python3.11/asyncio/locks.py", line 15, in __aenter__
await self.acquire()
File "/usr/lib/python3.11/asyncio/locks.py", line 106, in acquire
fut = self._get_loop().create_future()
^^^^^^^^^^^^^^^^
File "/usr/lib/python3.11/asyncio/mixins.py", line 20, in _get_loop
raise RuntimeError(f'{self!r} is bound to a different event loop')
RuntimeError: <asyncio.locks.Lock object at 0x7f1c265e2190 [unlocked, waiters:1]> is bound to a different event loopHope it helps! |
|
Hi! How to reproduce this bug
Repeat those steps 14 times and you'll see QueryPool error in pgAdmin (rare) or in logs (usually). pgAdmin will work very slow, new tabs will open and load more than 30 sec. WHAT TO DO: You must lock F5 hotkey button and macros hotkeys during query running on frontend. Now you lock only menu button.
|
|
@ish1mura Thanks for the details. I have followed the above said steps and received the error message |
|
@ish1mura |
Yes. But currently pgAdmin does not support configuration of pool_size & max_over_low. This solution is under discussion. |
…Admin config DB connection pool. #6208
|
I'm running pgadmin in k8s via this helm chart: https://artifacthub.io/packages/helm/runix/pgadmin4 |
|
Resolved, Verified on snapshot build: https://www.postgresql.org/ftp/pgadmin/pgadmin4/snapshots/2023-08-21/ |
…Admin config DB connection pool. pgadmin-org#6208
Please note that security bugs or issues should be reported to security@pgadmin.org.
I hit this error from pgadmin4 web connecting to an AWS RDS cluster of 2 nodes. What's the root cause? Is it due to some configurations that I need to adjust? https://docs.sqlalchemy.org/en/20/errors.html#error-3o7r
The RDS dashboard shows only 2 Connections. How does it hit the connection limit and the "overflow"?
Describe the bug
A clear and concise description of what the bug is.
To Reproduce
Steps to reproduce the behavior:
Expected behavior
A clear and concise description of what you expected to happen.
Error message
If possible, include the full, unedited text of any error messages displayed.
Screenshots
If applicable, add screenshots to help explain your problem.
Desktop (please complete the following information):
Additional context
Add any other context about the problem here.
The text was updated successfully, but these errors were encountered: