Date: Tue, 6 Sep 2022 11:15:12 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: "James Pang (chaolpan)" <chaolpan@cisco.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql JDBC process consumes more memory than psql client
Message-ID: <20220906161512.GQ31833@telsasoft.com>
References: 
 <PH0PR11MB5191A047B707B2FE6527C549D67F9@PH0PR11MB5191.namprd11.prod.outlook.com>
 <20220905124722.GI31833@telsasoft.com>
 <PH0PR11MB5191C2C75F91CE1237267479D67F9@PH0PR11MB5191.namprd11.prod.outlook.com>
 <PH0PR11MB51919678C0C5449A95EE0452D67E9@PH0PR11MB5191.namprd11.prod.outlook.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <PH0PR11MB51919678C0C5449A95EE0452D67E9@PH0PR11MB5191.namprd11.prod.outlook.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20220906161512.GQ31833%40telsasoft.com>
Precedence: bulk

On Tue, Sep 06, 2022 at 04:15:03AM +0000, James Pang (chaolpan) wrote:
> We make 2 comparisions between partitioned(256 HASH) and no-partitioned(same data volume,same table attributes) , do same  "UPDATE,DELETE " .
>  1. with partitioned tables , the "RES" from top command memory increased quickly to 160MB and keep stable there. 
>       From auto_explain trace, we did saw  partition pruning to specific partition when execution the prepared sql statement by Postgresql JDBC .
> 2. with no-partitioned tables, the "RES" from top command memory only keep 24MB stable there. 
>        Same auto_explain , and only table and index scan there by prepared sql statement by Postgresql JDBC. 
> 3. with psql client , run the UPDATE/DELETE sql locally,  partition pruning works and the "RES" memory" is much less, it's about 9MB . 
> 
> Yesterday, when workload test, a lot of Postgresql JDBC connections use 150-160MB memory , so we got ERROR: out of memory

How many JDBC clients were there?

Did you use the same number of clients when you used psql ?
Otherwise it wasn't a fair test.

Also, did you try using psql with PREPARE+EXECUTE ?  I imagine memory
use would match JDBC.

It's probably not important, but if you set the log level high enough,
you could log memory use more accurately using log_executor_stats
(maxrss).

> So, looks like something with Postgresql JDBC driver lead to the high memory consumption when table is partitioned , even when table is no partitioned , compared with psql client, it consumes more memory.   Any suggestions to tune that ?  PG V13 , OS RHEL8 , Virtua machine on VMWARE. We make shared_buffers=36% physical memory ,  effective_cache_size=70%physical memory , total physical memory is about 128GB.

I sent this before hoping to get answers to all the most common
questions earlier, rather than being spread out over the first handful
of emails.

https://wiki.postgresql.org/wiki/Slow_Query_Questions

version 13 point what ?
what are the other non-default gucs ?
what are the query plans ?

-- 
Justin