public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dave Page <[email protected]>
To: Akshay Joshi <[email protected]>
Cc: Yogesh Mahajan <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Cc: Nikhil Mohite <[email protected]>
Subject: Re: Request for inputs on #6208 - MaxQueuePool Issue
Date: Fri, 11 Aug 2023 10:10:44 +0100
Message-ID: <CA+OCxozty7ZTZnmND0_+iHoV7yvmce2Dt7vV+eBrkHAD8L5UDQ@mail.gmail.com> (raw)
In-Reply-To: <CANxoLDfHoprxmZ0ej8e2D1EWjswt3mv4ewCsBUtwsdELFQ8qhw@mail.gmail.com>
References: <CAMa=N=NVxKMyofyEL-53i8DV1=6PmgEjmomrXs1hya7BB8kcyw@mail.gmail.com>
<CANxoLDfHoprxmZ0ej8e2D1EWjswt3mv4ewCsBUtwsdELFQ8qhw@mail.gmail.com>
On Wed, 9 Aug 2023 at 08:25, Akshay Joshi <[email protected]>
wrote:
> Hi Yogesh
>
> On Tue, Aug 8, 2023 at 1:48 PM Yogesh Mahajan <
> [email protected]> wrote:
>
>> 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?
>>
>
> If NullPool *may* impact the performance then we should go with
> Solution 1.
>
Yes, with a much larger default value I would suggest.
Plus, we should also stop leaving orphaned connections behind...
--
Dave Page
Blog: https://pgsnake.blogspot.com
Twitter: @pgsnake
EDB: https://www.enterprisedb.com
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], [email protected], [email protected]
Subject: Re: Request for inputs on #6208 - MaxQueuePool Issue
In-Reply-To: <CA+OCxozty7ZTZnmND0_+iHoV7yvmce2Dt7vV+eBrkHAD8L5UDQ@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