Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qUOAm-00Cim2-0F for pgadmin-hackers@arkaria.postgresql.org; Fri, 11 Aug 2023 09:11:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qUOAk-005yNv-6Z for pgadmin-hackers@arkaria.postgresql.org; Fri, 11 Aug 2023 09:11:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qUOAj-005yNm-Ru for pgadmin-hackers@lists.postgresql.org; Fri, 11 Aug 2023 09:11:06 +0000 Received: from mail-ej1-x636.google.com ([2a00:1450:4864:20::636]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qUOAc-001lpi-Ml for pgadmin-hackers@postgresql.org; Fri, 11 Aug 2023 09:11:04 +0000 Received: by mail-ej1-x636.google.com with SMTP id a640c23a62f3a-99c47ef365cso253749566b.0 for ; Fri, 11 Aug 2023 02:10:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgadmin.org; s=google; t=1691745056; x=1692349856; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=97fkrrfLKp6SMp7Re8j4nNhCl0QGIh7zD/MIS3EGAwA=; b=bP6i22hdgHYuh7EPjD3zLvJNQiCocuDo101lT4lekgUTRoDZtdj0V1OY5SJmop+ZsL TKVpFrLnvmJgxawnxv9iW+fkB0vMjwiqzxovC3K6awElynsHI9ADd7hpmxLl+xEkKeBs kccJi/XFV36Wekur0/TbDl3C95lf/SriDSzxDjk5R1bkR1+qlIbIE98WMirT1UlaCjfZ cpDM56F0OVUKi2GKmXFQyo2nP0T0z1AYa1SLDBAT3n7NktE8DFXcn5iDLd8nCPYKD9zW CfAjIuTpKv4fpvUjFNYYUxPCNny749xsgoVResBNhRaOlqyJaT0zcICe65u8o3qruKG7 E95Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1691745056; x=1692349856; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=97fkrrfLKp6SMp7Re8j4nNhCl0QGIh7zD/MIS3EGAwA=; b=fZvpec0eSq/QFrUVYFfxBOLZV+qDrXcTpWEDsADOpSux9NNAZhF1nbXJuyRWcrc69d K8B246Br/TsbD/+H9gy0nmZ5hWn51PUBxMUpZCtgOQLzuue2hv/nm2vL7JJGa3vOJmuC SoPNCtm1edTCX/QKyVTsFDuUqdnHupA1hXjlKdcwTPxbfaZz/2t3A+r79H/vZcAXZOza Lbz4hyhFf1S4b6q+i4a19tjctfkDHk+PaPWzayS8XCmwsGBCWoLgYSu1VLbl8Y3kIWPx gxM4xsJpWpCycbS8u+mAHvsqw37SaWDpKHPg06xv+F868xgSjoLdAEtWPJmQGy1GIZbA E7Gw== X-Gm-Message-State: AOJu0YyIVoNnvdcB7lt3A36Zneo01rp+dDvciKVgUcsNv0mo+nWaZDjK hdRNjDtp/W7poC27yu9JgIeqYB5W0FZ4bHwllbLL5g== X-Google-Smtp-Source: AGHT+IGuvlVvblfmW1p24IMe8oUYzSoozw+lglbhwjWvRusKp8hgjCPvonVgoTOZw9JtOqPcLgrKO9DDY+5bnF5be+U= X-Received: by 2002:a17:906:8a6d:b0:994:4095:3abf with SMTP id hy13-20020a1709068a6d00b0099440953abfmr1214709ejc.14.1691745055862; Fri, 11 Aug 2023 02:10:55 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Dave Page Date: Fri, 11 Aug 2023 10:10:44 +0100 Message-ID: Subject: Re: Request for inputs on #6208 - MaxQueuePool Issue To: Akshay Joshi Cc: Yogesh Mahajan , pgadmin-hackers , Nikhil Mohite Content-Type: multipart/alternative; boundary="0000000000001b0e840602a21777" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001b0e840602a21777 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, 9 Aug 2023 at 08:25, Akshay Joshi wrote: > Hi Yogesh > > On Tue, Aug 8, 2023 at 1:48=E2=80=AFPM Yogesh Mahajan < > yogesh.mahajan@enterprisedb.com> wrote: > >> Hi Hackers, >> >> For the #6208 , bel= ow >> 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 rese= t >> 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, SQLAlch= emy >> 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 incorr= ect >> 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 p= er >> 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... --=20 Dave Page Blog: https://pgsnake.blogspot.com Twitter: @pgsnake EDB: https://www.enterprisedb.com --0000000000001b0e840602a21777 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Wed, 9 Aug 2023 at 08:25, Akshay J= oshi <akshay.joshi@ente= rprisedb.com> wrote:
Hi=C2=A0Yogesh

On Tue, Aug 8, 2023 at 1:48=E2=80=AFPM Yogesh Ma= hajan <yogesh.mahajan@enterprisedb.com> wrote:
Hi Hackers,

For the=C2=A0#6208, bel= ow are the observations about this issue=C2=A0-=C2=A0

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 whi= ch is not handled and response is not sent, the application db connection r= emains orphaned. This connection is only reset on application restart.

Issue -=C2=A0I= n case of pgAdmin,=C2=A0if the user openes 15 query tool t= abs & all of them have long running transactions like pg_sleep(), then = opening new query tool/or any operation on pgAdmin which hits API request t= o backend will throw 'QueuePool Limit reached' error. (Because=C2=A0by default,=C2=A0SQLAlchemy allows 15 connections total: 5 connections in pool = & 10 in overflow and pgAdmin uses default setting.) OR =C2=A0if the user executes a que= ry in the query tool & while query execution is in progress, hits F5(ke= yboard shortcut to execute query) 16 times continuously, then pgAdmin throw= s 'QueuePool Limit reached' error. After that, the query tool gives= incorrect responses to the queries.
Solutions=C2=A0-=C2=A0
1.Provide configurable settings for 'po= ol_size' & 'max_overflow' parameters for SQLAlchemy.=
2.Disable pooling using NullPool.A Pool which does not poo= l connections. Instead it literally opens and closes the underlying DB-API = connection per each connection open/close. Using NullPool may impact the= performance.=C2=A0

What approach should = be followed to fix=C2=A0the issue?
=

=C2=A0 =C2=A0 If NullPool may impact the perform= ance then we should go with Solution 1.=C2=A0

Yes, with a much larger default value I would suggest.=

Plus, we should also stop leaving orphaned connec= tions behind...=C2=A0


--
Dave Page
Blog: https://pgsnake.blogspot.com
Twitter= : @pgsnake

EDB: https://www.enterprisedb.com

--0000000000001b0e840602a21777--