Thread: Postgresql JDBC process consumes more memory than psql client
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
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 hashpartitions). 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
RE: Postgresql JDBC process consumes more memory than psql client
From
"James Pang (chaolpan)"
Date:
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 hashpartitions). 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 planfrom 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 tablesto 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
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
RE: Postgresql JDBC process consumes more memory than psql client
From
"James Pang (chaolpan)"
Date:
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
Detail: Failed on request of size 240 in memory context "MessageContext". And other non-postgresql process like top
commandeven failed into no-memory error.
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.
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
hashpartitions). 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
planfrom 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
tablesto 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
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 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
RE: Postgresql JDBC process consumes more memory than psql client
From
"James Pang (chaolpan)"
Date:
Yes, same prepared statement from both psql and JDBC. We started to compare with one by one, and see big difference asexplained. Psql and JDBC show big difference. Let's focuse on JDBC driver client ,why it consumes 160MB memory even tablesize 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 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
RE: Postgresql JDBC process consumes more memory with partition tables update delete
From
"James Pang (chaolpan)"
Date:
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