Обсуждение: Request for inputs on #6208 - MaxQueuePool Issue

Hi Hackers,

For the #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 Please add if anything is missed.

Thanks,
Yogesh Mahajan
EnterpriseDB

Hi Yogesh

On Tue, Aug 8, 2023 at 1:48 PM Yogesh Mahajan <yogesh.mahajan@enterprisedb.com> wrote:

Hi Hackers,

For the #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. 

On Wed, 9 Aug 2023 at 08:25, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi Yogesh

On Tue, Aug 8, 2023 at 1:48 PM Yogesh Mahajan <yogesh.mahajan@enterprisedb.com> wrote:

Hi Hackers,

For the #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