Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix recovery_prefetch with low maintenance_io_concurrency.

  1. Postgresql JDBC process consumes more memory than psql client

    James Pang (chaolpan) <chaolpan@cisco.com> — 2022-09-05T12:40:46Z

      We run same update or delete SQL statement " DELETE FROM ... WHERE ... "  the table is a hash partition table (256 hash partitions). When run the sql from Postgresql JDBC driver, it soon increased to 150MB memory (RES filed from top command),       but when run the same SQL from psql , it only consumes about 10MB memory.  UPDATE statements is similar , need 100MB memory, even it delete or update 0 rows.  Any specific control about Postgresql JDBC driver ?

Thanks,

James

  2. Re: Postgresql JDBC process consumes more memory than psql client

    Justin Pryzby <pryzby@telsasoft.com> — 2022-09-05T12:47:22Z

    On Mon, Sep 05, 2022 at 12:40:46PM +0000, James Pang (chaolpan) wrote:
>   We run same update or delete SQL statement " DELETE FROM ... WHERE ... "  the table is a hash partition table (256 hash partitions). When run the sql from Postgresql JDBC driver, it soon increased to 150MB memory (RES filed from top command),       but when run the same SQL from psql , it only consumes about 10MB memory.  UPDATE statements is similar , need 100MB memory, even it delete or update 0 rows.  Any specific control about Postgresql JDBC driver ?

It sounds like JDBC is using prepared statements, and partitions maybe
weren't pruned by the server.  What is the query plan from psql vs from
jdbc ?

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

What version is the postgres server ?
That affects pruning as well as memory use.

https://www.postgresql.org/docs/14/release-14.html
Improve the performance of updates and deletes on partitioned tables
with many partitions (Amit Langote, Tom Lane)

This change greatly reduces the planner's overhead for such cases, and
also allows updates/deletes on partitioned tables to use execution-time
partition pruning.

Actually, this is about the same response as when you asked in June,
except that was about UPDATE.
https://www.postgresql.org/message-id/PH0PR11MB519134D4171A126776E3E063D6B89@PH0PR11MB5191.namprd11.prod.outlook.com

-- 
Justin

  3. RE: Postgresql JDBC process consumes more memory than psql client

    James Pang (chaolpan) <chaolpan@cisco.com> — 2022-09-05T12:52:14Z

    PG V13, yes JDBC use prepared statements ,  from psql use pruned ,but even all partitions it NOT consumes too much memory.  Any idea how to print SQL plan from JDBC driver ? 

-----Original Message-----
From: Justin Pryzby <pryzby@telsasoft.com> 
Sent: Monday, September 5, 2022 8:47 PM
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 Mon, Sep 05, 2022 at 12:40:46PM +0000, James Pang (chaolpan) wrote:
>   We run same update or delete SQL statement " DELETE FROM ... WHERE ... "  the table is a hash partition table (256 hash partitions). When run the sql from Postgresql JDBC driver, it soon increased to 150MB memory (RES filed from top command),       but when run the same SQL from psql , it only consumes about 10MB memory.  UPDATE statements is similar , need 100MB memory, even it delete or update 0 rows.  Any specific control about Postgresql JDBC driver ?

It sounds like JDBC is using prepared statements, and partitions maybe weren't pruned by the server.  What is the query plan from psql vs from jdbc ?

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

What version is the postgres server ?
That affects pruning as well as memory use.

https://www.postgresql.org/docs/14/release-14.html
Improve the performance of updates and deletes on partitioned tables with many partitions (Amit Langote, Tom Lane)

This change greatly reduces the planner's overhead for such cases, and also allows updates/deletes on partitioned tables to use execution-time partition pruning.

Actually, this is about the same response as when you asked in June, except that was about UPDATE.
https://www.postgresql.org/message-id/PH0PR11MB519134D4171A126776E3E063D6B89@PH0PR11MB5191.namprd11.prod.outlook.com

--
Justin

  4. Re: Postgresql JDBC process consumes more memory than psql client

    Justin Pryzby <pryzby@telsasoft.com> — 2022-09-05T13:26:58Z

    On Mon, Sep 05, 2022 at 12:52:14PM +0000, James Pang (chaolpan) wrote:
> Any idea how to print SQL plan from JDBC driver ? 

You could use "explain execute" on the client, or autoexplain on the
server-side.

-- 
Justin

  5. RE: Postgresql JDBC process consumes more memory than psql client

    James Pang (chaolpan) <chaolpan@cisco.com> — 2022-09-06T04:15:03Z

    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
  Detail: Failed on request of size 240 in memory context "MessageContext".  And other non-postgresql process like top command even failed into no-memory error. 

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.

Thanks,

James


-----Original Message-----
From: James Pang (chaolpan) <chaolpan@cisco.com> 
Sent: Monday, September 5, 2022 8:52 PM
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: RE: Postgresql JDBC process consumes more memory than psql client

PG V13, yes JDBC use prepared statements ,  from psql use pruned ,but even all partitions it NOT consumes too much memory.  Any idea how to print SQL plan from JDBC driver ? 

-----Original Message-----
From: Justin Pryzby <pryzby@telsasoft.com> 
Sent: Monday, September 5, 2022 8:47 PM
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 Mon, Sep 05, 2022 at 12:40:46PM +0000, James Pang (chaolpan) wrote:
>   We run same update or delete SQL statement " DELETE FROM ... WHERE ... "  the table is a hash partition table (256 hash partitions). When run the sql from Postgresql JDBC driver, it soon increased to 150MB memory (RES filed from top command),       but when run the same SQL from psql , it only consumes about 10MB memory.  UPDATE statements is similar , need 100MB memory, even it delete or update 0 rows.  Any specific control about Postgresql JDBC driver ?

It sounds like JDBC is using prepared statements, and partitions maybe weren't pruned by the server.  What is the query plan from psql vs from jdbc ?

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

What version is the postgres server ?
That affects pruning as well as memory use.

https://www.postgresql.org/docs/14/release-14.html
Improve the performance of updates and deletes on partitioned tables with many partitions (Amit Langote, Tom Lane)

This change greatly reduces the planner's overhead for such cases, and also allows updates/deletes on partitioned tables to use execution-time partition pruning.

Actually, this is about the same response as when you asked in June, except that was about UPDATE.
https://www.postgresql.org/message-id/PH0PR11MB519134D4171A126776E3E063D6B89@PH0PR11MB5191.namprd11.prod.outlook.com

--
Justin

  6. Re: Postgresql JDBC process consumes more memory than psql client

    Justin Pryzby <pryzby@telsasoft.com> — 2022-09-06T16:15:12Z

    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

  7. RE: Postgresql JDBC process consumes more memory than psql client

    James Pang (chaolpan) <chaolpan@cisco.com> — 2022-09-07T00:05:13Z

    Yes, same prepared statement from both psql and JDBC.   We started to compare with one by one,  and see big difference as explained.  Psql and JDBC show big difference. Let's focuse on JDBC driver client ,why it consumes 160MB memory even table size is very small.  

-----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 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

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

    James Pang (chaolpan) <chaolpan@cisco.com> — 2022-09-07T00:10:09Z

        Yes, same prepared statement from both psql and JDBC.   We started to compare with one by one,  and see big difference as explained.  Psql and JDBC show big difference. Let's focuse on JDBC driver client ,why it consumes 160MB memory even table size is very small. But only consumes 25MB for non-partitioned tables with same table attributes and data volume 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 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

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

    James Pang (chaolpan) <chaolpan@cisco.com> — 2022-09-08T06:17:39Z

    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 difference as explained.  Psql and JDBC show big difference. Let's focuse on JDBC driver client ,why it consumes 160MB memory even table size is very small. But only consumes 25MB for non-partitioned tables with same table attributes and data volume 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 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

  10. Re: Postgresql JDBC process consumes more memory with partition tables update delete

    Vladimir Sitnikov <sitnikov.vladimir@gmail.com> — 2022-09-08T07:05:14Z

    > > Yesterday, when workload test, a lot of Postgresql JDBC connections
> > use 150-160MB memory , so we got ERROR: out of memory

Would you please share a reproducer? (e.g. DDL for the table, test code)

Have you tried capturing memory context information for the backend that
consumes memory?
https://wiki.postgresql.org/wiki/Developer_FAQ#Examining_backend_memory_use

Vladimir

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

    James Pang (chaolpan) <chaolpan@cisco.com> — 2022-09-08T08:24:03Z

    Please check attached.

Thanks,
James
From: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
Sent: Thursday, September 8, 2022 3:05 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

> > Yesterday, when workload test, a lot of Postgresql JDBC connections
> > use 150-160MB memory , so we got ERROR: out of memory

Would you please share a reproducer? (e.g. DDL for the table, test code)

Have you tried capturing memory context information for the backend that consumes memory?
https://wiki.postgresql.org/wiki/Developer_FAQ#Examining_backend_memory_use

Vladimir

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

    James Pang (chaolpan) <chaolpan@cisco.com> — 2022-09-08T08:30:32Z

    Please check attached.

Thanks,
James
From: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
Sent: Thursday, September 8, 2022 3:05 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

> > Yesterday, when workload test, a lot of Postgresql JDBC connections
> > use 150-160MB memory , so we got ERROR: out of memory

Would you please share a reproducer? (e.g. DDL for the table, test code)

Have you tried capturing memory context information for the backend that consumes memory?
https://wiki.postgresql.org/wiki/Developer_FAQ#Examining_backend_memory_use

Vladimir

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

    James Pang (chaolpan) <chaolpan@cisco.com> — 2022-09-08T08:32:14Z

    Please check attached.

Thanks,
James
From: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
Sent: Thursday, September 8, 2022 3:05 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

> > Yesterday, when workload test, a lot of Postgresql JDBC connections
> > use 150-160MB memory , so we got ERROR: out of memory

Would you please share a reproducer? (e.g. DDL for the table, test code)

Have you tried capturing memory context information for the backend that consumes memory?
https://wiki.postgresql.org/wiki/Developer_FAQ#Examining_backend_memory_use

Vladimir

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

    James Pang (chaolpan) <chaolpan@cisco.com> — 2022-09-08T08:36:30Z

    Please check attached.  Removed some dbname and tablename , the interesting thing is we only see this issue by JDBC driver client, for psql client , only 25MB memory and similar SQL plan used.

Thanks,
James
From: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
Sent: Thursday, September 8, 2022 3:05 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

> > Yesterday, when workload test, a lot of Postgresql JDBC connections
> > use 150-160MB memory , so we got ERROR: out of memory

Would you please share a reproducer? (e.g. DDL for the table, test code)

Have you tried capturing memory context information for the backend that consumes memory?
https://wiki.postgresql.org/wiki/Developer_FAQ#Examining_backend_memory_use

Vladimir

  15. Re: Postgresql JDBC process consumes more memory with partition tables update delete

    Dave Cramer <davecramer@postgres.rocks> — 2022-09-08T09:31:15Z

    On Thu, 8 Sept 2022 at 04:36, James Pang (chaolpan) <chaolpan@cisco.com>
wrote:

>
>
> Please check attached.  Removed some dbname and tablename , the
> interesting thing is we only see this issue by JDBC driver client, for psql
> client , only 25MB memory and similar SQL plan used.
>
>
>
> Thanks,
>
> James
>
> *From:* Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
> *Sent:* Thursday, September 8, 2022 3:05 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
>
>
>
> > > Yesterday, when workload test, a lot of Postgresql JDBC connections
> > > use 150-160MB memory , so we got ERROR: out of memory
>
> Would you please share a reproducer? (e.g. DDL for the table, test code)
>
>
>
> Have you tried capturing memory context information for the backend that
> consumes memory?
>
> https://wiki.postgresql.org/wiki/Developer_FAQ#Examining_backend_memory_use
>
> Vladimir
>


I'd like to see the actual statement for each. Can you turn on log all
statements in the back end and capture the actual statement for psql and
jdbc ?


Dave Cramer
www.postgres.rocks

  16. Re: Postgresql JDBC process consumes more memory with partition tables update delete

    Vladimir Sitnikov <sitnikov.vladimir@gmail.com> — 2022-09-08T09:55:38Z

    > 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

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

    James Pang (chaolpan) <chaolpan@cisco.com> — 2022-09-08T11:38:04Z

    Hi,
   When I convert the partitioned table to non-partitioned and copy all data to non-partitioned tables, then restart the load 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 memory consumption changes. The dumped stats is backend(session) level cached plans ,right?   The test servers use shared connection 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 not same 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 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

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

    James Pang (chaolpan) <chaolpan@cisco.com> — 2022-09-08T12:04:45Z

    Hi,
   When I convert the partitioned table to non-partitioned and copy all data to non-partitioned tables, then restart the load 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 memory consumption changes. The dumped stats is backend(session) level cached plans ,right?   The test servers use shared connection 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 keep the 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 not same 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 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

  19. Re: Postgresql JDBC process consumes more memory with partition tables update delete

    Dave Cramer <davecramer@postgres.rocks> — 2022-09-08T12:12:19Z

    On Thu, 8 Sept 2022 at 08:05, James Pang (chaolpan) <chaolpan@cisco.com>
wrote:

> Hi,
>    When I convert the partitioned table to non-partitioned and copy all
> data to non-partitioned tables, then restart the load 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 memory consumption changes.
> The dumped stats is backend(session) level cached plans ,right?   The test
> servers use shared connection 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
> keep the same memory without release back to OS.
>

If you are using a connection pool, then the connections aren't closed so I
don't see this an issue.

Dave

>   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 not same 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 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
>