public inbox for [email protected]
help / color / mirror / Atom feedRequest for inputs on #6208 - MaxQueuePool Issue
4+ messages / 3 participants
[nested] [flat]
* Request for inputs on #6208 - MaxQueuePool Issue
@ 2023-08-08 08:17 Yogesh Mahajan <[email protected]>
2023-08-09 07:24 ` Re: Request for inputs on #6208 - MaxQueuePool Issue Akshay Joshi <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Yogesh Mahajan @ 2023-08-08 08:17 UTC (permalink / raw)
To: pgadmin-hackers; +Cc: Nikhil Mohite <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Request for inputs on #6208 - MaxQueuePool Issue
2023-08-08 08:17 Request for inputs on #6208 - MaxQueuePool Issue Yogesh Mahajan <[email protected]>
@ 2023-08-09 07:24 ` Akshay Joshi <[email protected]>
2023-08-11 09:10 ` Re: Request for inputs on #6208 - MaxQueuePool Issue Dave Page <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Akshay Joshi @ 2023-08-09 07:24 UTC (permalink / raw)
To: Yogesh Mahajan <[email protected]>; +Cc: pgadmin-hackers; Nikhil Mohite <[email protected]>
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.
>
>
> @Nikhil Mohite <[email protected]> Please add if anything is
> missed.
>
> Thanks,
> Yogesh Mahajan
> EnterpriseDB
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Request for inputs on #6208 - MaxQueuePool Issue
2023-08-08 08:17 Request for inputs on #6208 - MaxQueuePool Issue Yogesh Mahajan <[email protected]>
2023-08-09 07:24 ` Re: Request for inputs on #6208 - MaxQueuePool Issue Akshay Joshi <[email protected]>
@ 2023-08-11 09:10 ` Dave Page <[email protected]>
2023-08-11 09:14 ` Re: Request for inputs on #6208 - MaxQueuePool Issue Yogesh Mahajan <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Dave Page @ 2023-08-11 09:10 UTC (permalink / raw)
To: Akshay Joshi <[email protected]>; +Cc: Yogesh Mahajan <[email protected]>; pgadmin-hackers; Nikhil Mohite <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Request for inputs on #6208 - MaxQueuePool Issue
2023-08-08 08:17 Request for inputs on #6208 - MaxQueuePool Issue Yogesh Mahajan <[email protected]>
2023-08-09 07:24 ` Re: Request for inputs on #6208 - MaxQueuePool Issue Akshay Joshi <[email protected]>
2023-08-11 09:10 ` Re: Request for inputs on #6208 - MaxQueuePool Issue Dave Page <[email protected]>
@ 2023-08-11 09:14 ` Yogesh Mahajan <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Yogesh Mahajan @ 2023-08-11 09:14 UTC (permalink / raw)
To: Dave Page <[email protected]>; +Cc: Akshay Joshi <[email protected]>; pgadmin-hackers; Nikhil Mohite <[email protected]>
Hi Dave
On Fri, Aug 11, 2023 at 2:40 PM Dave Page <[email protected]> wrote:
>
>
> 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.
>
Okay.Will proceed with solution 1.
>
> Plus, we should also stop leaving orphaned connections behind...
>
Fix for this is already commited.
>
>
> --
> Dave Page
> Blog: https://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EDB: https://www.enterprisedb.com
>
>
Thanks,
Yogesh Mahajan
EnterpriseDB
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-08-11 09:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-08 08:17 Request for inputs on #6208 - MaxQueuePool Issue Yogesh Mahajan <[email protected]>
2023-08-09 07:24 ` Akshay Joshi <[email protected]>
2023-08-11 09:10 ` Dave Page <[email protected]>
2023-08-11 09:14 ` Yogesh Mahajan <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox