Re: Postgresql JDBC process consumes more memory than psql client

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