public inbox for [email protected]  

help / color / mirror / Atom feed

From: Yogesh Mahajan <[email protected]>
To: pgadmin-hackers <[email protected]>
Cc: Nikhil Mohite <[email protected]>
Subject: Request for inputs on #6208 - MaxQueuePool Issue
Date: Tue, 8 Aug 2023 13:47:16 +0530
Message-ID: <CAMa=N=NVxKMyofyEL-53i8DV1=6PmgEjmomrXs1hya7BB8kcyw@mail.gmail.com> (raw)

Hi Hackers,

For the #6208 <https://github.com/pgadmin-org/pgadmin4/issues/6208;, below
are the observations about this issue -

On each API request, an application db connection is created with state
'idle in transaction'. Connection state is changed to idle only after a
successful response from the web server. If an exception occurs while
processing a request which is not handled and response is not sent, the
application db connection remains orphaned. This connection is only reset
on application restart.

Issue - In case of pgAdmin, if the user openes 15 query tool tabs & all of
them have long running transactions like pg_sleep(), then opening new query
tool/or any operation on pgAdmin which hits API request to backend will
throw 'QueuePool Limit reached' error. (Because by default, SQLAlchemy
allows 15 connections total: 5 connections in pool & 10 in overflow and
pgAdmin uses default setting.) OR  if the user executes a query in the
query tool & while query execution is in progress, hits F5(keyboard
shortcut to execute query) 16 times continuously, then pgAdmin throws
'QueuePool Limit reached' error. After that, the query tool gives incorrect
responses to the queries.

Solutions -
1.Provide configurable settings for 'pool_size' & 'max_overflow' parameters
for SQLAlchemy.
2.Disable pooling using NullPool.A Pool which does not pool connections.
Instead it literally opens and closes the underlying DB-API connection per
each connection open/close. Using NullPool may impact the performance.

What approach should be followed to fix the issue?

@Nikhil Mohite <[email protected]> Please add if anything is
missed.

Thanks,
Yogesh Mahajan
EnterpriseDB

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Request for inputs on #6208 - MaxQueuePool Issue
  In-Reply-To: <CAMa=N=NVxKMyofyEL-53i8DV1=6PmgEjmomrXs1hya7BB8kcyw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox