Thread: RE: Postgresql JDBC process consumes more memory with partition tables update delete

Hi ,
  Looks like it's Postgresql JDBC driver related client.

-----Original Message-----
From: James Pang (chaolpan)
Sent: Wednesday, September 7, 2022 8:10 AM
To: 'Justin Pryzby' <pryzby@telsasoft.com>
Cc: 'pgsql-performance@lists.postgresql.org' <pgsql-performance@lists.postgresql.org>
Subject: RE: Postgresql JDBC process consumes more memory with partition tables update delete

    Yes, same prepared statement from both psql and JDBC.   We started to compare with one by one,  and see big
differenceas explained.  Psql and JDBC show big difference. Let's focuse on JDBC driver client ,why it consumes 160MB
memoryeven table size is very small. But only consumes 25MB for non-partitioned tables with same table attributes and
datavolume size. 

-----Original Message-----
From: Justin Pryzby <pryzby@telsasoft.com>
Sent: Wednesday, September 7, 2022 12:15 AM
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: 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
statementby 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'sabout 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 ,
evenwhen 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%physicalmemory , 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
thefirst 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

> interesting thing is we only see this issue by JDBC driver client

First of all, it turns out that a single UPDATE statement consumes 4M

Then, it looks like you have **multiple** UPDATE statements in the
server-side cache.
It does sound strange that a single backend contains multiple entries
for the same SQL text.

1) Would you please double-check that SQL text is the same. Do you use
bind variables?
2) Would you please double-check that you close statements after use
(e.g. try-with-resources).


CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks);
3715144 used: UPDATE WBXMEETINGINS

Frankly speaking, I am not sure the JDBC driver is in a position to
predict that a single-line statement would consume that much
server-side memory.

It would be nice if backend devs could optimize the memory consumption
of the cached plan.
If optimization is not possible, then it would be nice if the backend
could provide clients with memory consumption of the cached plan.
In other words, it would be nice if there was a status message or
something that says "ok, by the way, the prepared statement S_01
consumes 2M".

James, the captured dump includes only the first 100 entries.
Would you please try capturing more details via the following command?

MemoryContextStatsDetail(TopMemoryContext, 1000, true)

(see
https://github.com/postgres/postgres/blob/adb466150b44d1eaf43a2d22f58ff4c545a0ed3f/src/backend/utils/mmgr/mcxt.c#L574-L591
)


Vladimir

Hi,
   When I convert the partitioned table to non-partitioned and copy all data to non-partitioned tables, then restart
theload test , one backend server only consumes 25mb there.  With partitioned tables , 
 
PGV13 , 160-170mb /per backend server,   PGV14, 130-138mb/per backend server. So , it's partitioned tables make the
memoryconsumption changes. The dumped stats is backend(session) level cached plans ,right?   The test servers use
sharedconnection pooling to run same insert/update/delete transaction by multiple connections(we simulate 300
connections), so each session see similar cached SQL plans, and part of table has trigger before UPDATE, so when UPDATE
it trigger to call pl/pgsql function. 
 
  I only use psql to make same prepared SQL and run that in a loop, I see stable memory usage, maybe my psql test is
notsame as the JAVA test code.  I will check the test code details and try to check if possible to dump more context
details.
 

Thanks,

James 
  

-----Original Message-----
From: Vladimir Sitnikov <sitnikov.vladimir@gmail.com> 
Sent: Thursday, September 8, 2022 5:56 PM
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: pgsql-jdbc@lists.postgresql.org
Subject: Re: Postgresql JDBC process consumes more memory with partition tables update delete

> interesting thing is we only see this issue by JDBC driver client

First of all, it turns out that a single UPDATE statement consumes 4M

Then, it looks like you have **multiple** UPDATE statements in the server-side cache.
It does sound strange that a single backend contains multiple entries for the same SQL text.

1) Would you please double-check that SQL text is the same. Do you use bind variables?
2) Would you please double-check that you close statements after use (e.g. try-with-resources).


CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks);
3715144 used: UPDATE WBXMEETINGINS

Frankly speaking, I am not sure the JDBC driver is in a position to predict that a single-line statement would consume
thatmuch server-side memory.
 

It would be nice if backend devs could optimize the memory consumption of the cached plan.
If optimization is not possible, then it would be nice if the backend could provide clients with memory consumption of
thecached plan.
 
In other words, it would be nice if there was a status message or something that says "ok, by the way, the prepared
statementS_01 consumes 2M".
 

James, the captured dump includes only the first 100 entries.
Would you please try capturing more details via the following command?

MemoryContextStatsDetail(TopMemoryContext, 1000, true)

(see
https://github.com/postgres/postgres/blob/adb466150b44d1eaf43a2d22f58ff4c545a0ed3f/src/backend/utils/mmgr/mcxt.c#L574-L591
)


Vladimir

Hi,
   When I convert the partitioned table to non-partitioned and copy all data to non-partitioned tables, then restart
theload test , one backend server only consumes 25mb there.  With partitioned tables , 
 
PGV13 , 160-170mb /per backend server,   PGV14, 130-138mb/per backend server. So , it's partitioned tables make the
memoryconsumption changes. The dumped stats is backend(session) level cached plans ,right?   The test servers use
sharedconnection pooling to run same insert/update/delete transaction by multiple connections(we simulate 300
connections), so each session see similar cached SQL plans, and part of table has trigger before UPDATE, so when UPDATE
it trigger to call pl/pgsql function.  Another thing is even after the backend server idle there long time, it's still
keepthe same memory without release back to OS.
 
  I only use psql to make same prepared SQL and run that in a loop, I see stable memory usage, maybe my psql test is
notsame as the JAVA test code.  I will check the test code details and try to check if possible to dump more context
details.
 

Thanks,

James 
  

-----Original Message-----
From: Vladimir Sitnikov <sitnikov.vladimir@gmail.com> 
Sent: Thursday, September 8, 2022 5:56 PM
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: pgsql-jdbc@lists.postgresql.org
Subject: Re: Postgresql JDBC process consumes more memory with partition tables update delete

> interesting thing is we only see this issue by JDBC driver client

First of all, it turns out that a single UPDATE statement consumes 4M

Then, it looks like you have **multiple** UPDATE statements in the server-side cache.
It does sound strange that a single backend contains multiple entries for the same SQL text.

1) Would you please double-check that SQL text is the same. Do you use bind variables?
2) Would you please double-check that you close statements after use (e.g. try-with-resources).


CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks);
3715144 used: UPDATE WBXMEETINGINS

Frankly speaking, I am not sure the JDBC driver is in a position to predict that a single-line statement would consume
thatmuch server-side memory.
 

It would be nice if backend devs could optimize the memory consumption of the cached plan.
If optimization is not possible, then it would be nice if the backend could provide clients with memory consumption of
thecached plan.
 
In other words, it would be nice if there was a status message or something that says "ok, by the way, the prepared
statementS_01 consumes 2M".
 

James, the captured dump includes only the first 100 entries.
Would you please try capturing more details via the following command?

MemoryContextStatsDetail(TopMemoryContext, 1000, true)

(see
https://github.com/postgres/postgres/blob/adb466150b44d1eaf43a2d22f58ff4c545a0ed3f/src/backend/utils/mmgr/mcxt.c#L574-L591
)


Vladimir