From: Laurenz Albe <laurenz.albe@cybertec.at>
To: James Pang (chaolpan) <chaolpan@cisco.com>
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: thousands of CachedPlan entry per backend
Date: Thu, 01 Jun 2023 08:53:41 +0200
Message-ID: <9513916420a6c0a00eb5ecacfba984e2e5a97e0d.camel@cybertec.at> (raw)
In-Reply-To: <PH0PR11MB5191F9F3C8CA35737E6F0A99D6499@PH0PR11MB5191.namprd11.prod.outlook.com>
References: <PH0PR11MB5191F9F3C8CA35737E6F0A99D6499@PH0PR11MB5191.namprd11.prod.outlook.com>
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 memory per
> backend, from Operating system and memorycontext dump “Grand total:”, both mached.
> But from details, we found almost of entry belong to “CacheMemoryContext”,
> from this line CacheMemoryContext: 8737352 total in 42 blocks; 1021944 free (215 chunks); 7715408 used,
> but there are thousands of lines of it’s child, the sum of blocks much more than “8737352” total in 42 blocks
>
> Our application use Postgresql JDBC driver with default parameters(maxprepared statement 256),
> there are many triggers, functions in this database, and a few functions run sql by an extension
> pg_background. We have thousands of connections and have big concern why have thousands of entrys
> of cached SQL ? that will consume huge memory , anyway to limit the cached plan entry to save memory
> consumption? 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 effective connection pooling.
Then 40MB per backend won't be a problem at all. Having thousands of connections will cause
other, worse, problems for you.
See for example
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
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: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, chaolpan@cisco.com, pgsql-performance@lists.postgresql.org
Subject: Re: thousands of CachedPlan entry per backend
In-Reply-To: <9513916420a6c0a00eb5ecacfba984e2e5a97e0d.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox