Message-ID: <9513916420a6c0a00eb5ecacfba984e2e5a97e0d.camel@cybertec.at>
Subject: Re: thousands of CachedPlan entry per backend
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "James Pang (chaolpan)" <chaolpan@cisco.com>,
	"pgsql-performance@lists.postgresql.org"
	 <pgsql-performance@lists.postgresql.org>
Date: Thu, 01 Jun 2023 08:53:41 +0200
In-Reply-To: 
 <PH0PR11MB5191F9F3C8CA35737E6F0A99D6499@PH0PR11MB5191.namprd11.prod.outlook.com>
References: 
	<PH0PR11MB5191F9F3C8CA35737E6F0A99D6499@PH0PR11MB5191.namprd11.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/9513916420a6c0a00eb5ecacfba984e2e5a97e0d.camel%40cybertec.at>
Precedence: bulk

On Thu, 2023-06-01 at 03:36 +0000, James Pang (chaolpan) wrote:
> PG V14.8-1 , client using Postgresql JDBC driver we found 40MB process me=
mory per
> backend, from Operating system and memorycontext dump =E2=80=9CGrand tota=
l:=E2=80=9D, both mached.
> But from details, we found almost of entry belong to =C2=A0=E2=80=9CCache=
MemoryContext=E2=80=9D,
> from this line =C2=A0CacheMemoryContext: 8737352 total in 42 blocks; 1021=
944 free (215 chunks); 7715408 used,
> but there are thousands of lines of it=E2=80=99s child, the sum of blocks=
 much more than =E2=80=9C8737352=E2=80=9D total in 42 blocks
>=20
> Our application use Postgresql JDBC driver with default parameters(maxpre=
pared statement 256),
> there are many triggers, functions in this database, and a few functions =
run sql by an extension
> pg_background. =C2=A0We have thousands of connections and have big concer=
n why have thousands of entrys
> of cached SQL ? =C2=A0that will consume huge memory ,=C2=A0 anyway to lim=
it the cached plan entry to save memory
> consumption?=C2=A0 Or it looks like an abnormal behavior or bug to see so=
 many cached plan lines.

If you have thousands of connections, that's your problem.  You need effect=
ive connection pooling.
Then 40MB per backend won't be a problem at all.  Having thousands of conne=
ctions will cause
other, worse, problems for you.

See for example=20
https://www.cybertec-postgresql.com/en/tuning-max_connections-in-postgresql=
/

If you want to use functions, but don't want to benefit from plan caching, =
you can set
the configuration parameter "plan_cache_mode" to "force_custom_plan".

Yours,
Laurenz Albe