pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql JDBC process consumes more memory than psql client
19+ messages / 4 participants
[nested] [flat]
* Postgresql JDBC process consumes more memory than psql client
@ 2022-09-05 12:40 James Pang (chaolpan) <chaolpan@cisco.com>
0 siblings, 1 reply; 19+ messages in thread
From: James Pang (chaolpan) @ 2022-09-05 12:40 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Postgresql JDBC process consumes more memory than psql client
@ 2022-09-05 12:47 Justin Pryzby <pryzby@telsasoft.com>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
0 siblings, 1 reply; 19+ messages in thread
From: Justin Pryzby @ 2022-09-05 12:47 UTC (permalink / raw)
To: James Pang (chaolpan) <chaolpan@cisco.com>; +Cc: pgsql-performance@lists.postgresql.org
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....
--
Justin
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: Postgresql JDBC process consumes more memory than psql client
@ 2022-09-05 12:52 James Pang (chaolpan) <chaolpan@cisco.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 2 replies; 19+ messages in thread
From: James Pang (chaolpan) @ 2022-09-05 12:52 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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....
--
Justin
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Postgresql JDBC process consumes more memory than psql client
@ 2022-09-05 13:26 Justin Pryzby <pryzby@telsasoft.com>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
1 sibling, 0 replies; 19+ messages in thread
From: Justin Pryzby @ 2022-09-05 13:26 UTC (permalink / raw)
To: James Pang (chaolpan) <chaolpan@cisco.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: Postgresql JDBC process consumes more memory than psql client
@ 2022-09-06 04:15 James Pang (chaolpan) <chaolpan@cisco.com>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
1 sibling, 1 reply; 19+ messages in thread
From: James Pang (chaolpan) @ 2022-09-06 04:15 UTC (permalink / raw)
To: James Pang (chaolpan) <chaolpan@cisco.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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....
--
Justin
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Postgresql JDBC process consumes more memory than psql client
@ 2022-09-06 16:15 Justin Pryzby <pryzby@telsasoft.com>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
0 siblings, 1 reply; 19+ messages in thread
From: Justin Pryzby @ 2022-09-06 16:15 UTC (permalink / raw)
To: James Pang (chaolpan) <chaolpan@cisco.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: Postgresql JDBC process consumes more memory than psql client
@ 2022-09-07 00:05 James Pang (chaolpan) <chaolpan@cisco.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 19+ messages in thread
From: James Pang (chaolpan) @ 2022-09-07 00:05 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: Postgresql JDBC process consumes more memory with partition tables update delete
@ 2022-09-07 00:10 James Pang (chaolpan) <chaolpan@cisco.com>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
0 siblings, 1 reply; 19+ messages in thread
From: James Pang (chaolpan) @ 2022-09-07 00:10 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: Postgresql JDBC process consumes more memory with partition tables update delete
@ 2022-09-08 06:17 James Pang (chaolpan) <chaolpan@cisco.com>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
0 siblings, 1 reply; 19+ messages in thread
From: James Pang (chaolpan) @ 2022-09-08 06:17 UTC (permalink / raw)
To: pgsql-jdbc@lists.postgresql.org <pgsql-jdbc@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Postgresql JDBC process consumes more memory with partition tables update delete
@ 2022-09-08 07:05 Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
0 siblings, 4 replies; 19+ messages in thread
From: Vladimir Sitnikov @ 2022-09-08 07:05 UTC (permalink / raw)
To: James Pang (chaolpan) <chaolpan@cisco.com>; +Cc: pgsql-jdbc@lists.postgresql.org <pgsql-jdbc@lists.postgresql.org>
> > 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
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: Postgresql JDBC process consumes more memory with partition tables update delete
@ 2022-09-08 08:24 James Pang (chaolpan) <chaolpan@cisco.com>
parent: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
3 siblings, 0 replies; 19+ messages in thread
From: James Pang (chaolpan) @ 2022-09-08 08:24 UTC (permalink / raw)
To: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>; +Cc: pgsql-jdbc@lists.postgresql.org <pgsql-jdbc@lists.postgresql.org>
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
we have 18 tables copy from Oracle DB, but there is no much data, even empty table, we still got high memory.
most of tables have 256 HASH partitions
partition key is part of primary key ,
meetinginstanceid | character varying(128) | | not null |
and
the update and delete looks like this
update ... set ... where siteid=$1(bigint) and meetinginstanceid=$2(it's partition key and part of primary key) .
copy from part of context memory stats dumped to pglog when "out of memory" , this table has a update trigger that call a PL/PGSQL function so it's has that,
but enven other tables with 256 HASH parition ,no any trigger no any pl/pgsql, it's still fail into out of memory error and consume similar memory 160MB/per backend process.
TopMemoryContext: 1593168 total in 31 blocks; 127440 free (87 chunks); 1465728 used
TopTransactionContext: 8192 total in 1 blocks; 7160 free (1 chunks); 1032 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
pgstat TabStatusArray lookup hash table: 131072 total in 5 blocks; 58744 free (12 chunks); 72328 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 18874368 total in 14 blocks; 2544 free (6 chunks); 18871824 used
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 524288 total in 7 blocks; 247016 free (24 chunks); 277272 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used:
Relcache by OID: 131072 total in 5 blocks; 36024 free (10 chunks); 95048 used
CacheMemoryContext: 17586560 total in 78 blocks; 2655312 free (2 chunks); 14931248 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetingtranscriptmap_p31
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetingtranscriptmap_p31_siteid_tsobjectuuid_idx
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetingtranscriptmap_p31_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstanceinvitee_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceinvitee_p22_siteid_uid__meetinginstancei_idx4
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceinvitee_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticiprelation_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticiprelation_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticiprelation_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticipantaudio_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmeetinginstanceparticipant_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceparticipan_siteid_uid__meetinginstance_idx223
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceparticipant_p223_pkey
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p62
CachedPlan: 131072 total in 8 blocks; 38072 free (0 chunks); 93000 used: UPDATE WBXMEETINGINSTANCEREPORTINFO SET ITEMVALUE = $1 WHERE ITEMNAME = $2 AND MEE...
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p13_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p13_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p12_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p12_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p6_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p6_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p4_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p4_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p3_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p3_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p2_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p2_pkey
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 used: SELECT CLOCK_TIMESTAMP()
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 used: SELECT CLOCK_TIMESTAMP()
CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p117
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_pkey
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : StELrEiCgT gCeLOrC_Kf_TcItME_ScTAoMmPm(o)
n_ l mCachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedt: (S)E
LECT PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedCL: OtCrKi_gTgIeMrE_SfTcAtM_Pc(o)m
m o n _CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 usedl
m t (SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used)
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used : PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedwb: xtprositgmgeeert_ifncgtc_ocntoamimneornm_alpm_tp(4)2
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: : twrbxipgogsetrm_efectti_ncgocmomnotna_ilnemrtm(a)p
_p4 2_PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 useds: itteriid_gmgeeert_ifncgti_ncsotmamnocne_ildm_tk(e)y
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : wtbrxipgogsetrm_efectti_ncgocmonmtoani_nlemrtma(p)_
p4 2Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used_
pk ePLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 usedy
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 usedCachedPlan: 8192 total in 4 blocks; 1920 free (0 chunks); 6272 used
: IN SPL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 usedE: RtT rIiNgTgOe rW_BfXcMtE_EcToImNmGoInN_SlTmAtN(CE)R
EP ORendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedR
TIN FPLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 usedO
(S IPrepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 usedT
EI DTableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used,
ME EOperator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 usedT
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
E, smgr relation table: 1048576 total in 8 blocks; 518320 free (28 chunks); 530256 usedI
TEM VTransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 usedA
LU EPortal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used)
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used :
VA...
Relcache by OID: 262144 total in 6 blocks; 87104 free (12 chunks); 175040 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstancereportinfo_p5
index info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 used: wbxmeetinginstancereportinfo_p5_siteid_idx
CacheMemoryContext: 35269048 total in 1095 blocks; 5938376 free (93 chunks); 29330672 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used
: w b xindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedm: eewtbixnmgeientsitnagnicnesrteapnocretiinnfvoi_tpe5_ep_kpe2y5
_ s iindex info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 usedt: ewbixdm_euetiidn_g_imnesettaincnegrienpsotrtainncfeoi__pi1d_xs6i
te i dindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used_: iwdbxx
m e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedt: iwnbgximnesettainncgeiinnsvtiatnecee_rpe2po5r5t_ipnkfeoy_
p1 _ pindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedk: ewyb
x m epartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usede: twibnxgmienesttianngcienisntvaintceeree_ppo2r5ti_nsfiot_ep1i
d _ uCachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedi: dS_E_LmEeCeTt iCnLOgCiKn_sTtIaMnEcSeTiA_MiPd(x)5
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: S: EwLbExCmTe eCtLiOCnKg_iTnIsMtESaTnAcMePi(n)v
i t e eCachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
_ p 2SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used5
4 _ ppartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usedk: ewyb
xp o sindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedtm: ewebtximnegectoinntgaiinnsetramnacpe_pi2n1v7i
t e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used_: pw2b5x_psoisttemiede_tuiindg_c_omneteatiinnergmianps_tpa2n1c7e_is_iitdexid4_
m e etindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 usedi: nwgbixnmseteatnicnegiidn_skteayn
c e iindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: nwbvxiptoeset_mpe2e5t3i_npgkceoyn
ta i nindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usede: rmwabpx_mpe2e1t7i_npgkienys
Attachments:
[text/plain] postgresql-jdbc-partition-table-highmemroy.txt (15.4K, ../../PH0PR11MB51910C8097BFACC0C1137E28D6409@PH0PR11MB5191.namprd11.prod.outlook.com/3-postgresql-jdbc-partition-table-highmemroy.txt)
download | inline:
we have 18 tables copy from Oracle DB, but there is no much data, even empty table, we still got high memory.
most of tables have 256 HASH partitions
partition key is part of primary key ,
meetinginstanceid | character varying(128) | | not null |
and
the update and delete looks like this
update ... set ... where siteid=$1(bigint) and meetinginstanceid=$2(it's partition key and part of primary key) .
copy from part of context memory stats dumped to pglog when "out of memory" , this table has a update trigger that call a PL/PGSQL function so it's has that,
but enven other tables with 256 HASH parition ,no any trigger no any pl/pgsql, it's still fail into out of memory error and consume similar memory 160MB/per backend process.
TopMemoryContext: 1593168 total in 31 blocks; 127440 free (87 chunks); 1465728 used
TopTransactionContext: 8192 total in 1 blocks; 7160 free (1 chunks); 1032 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
pgstat TabStatusArray lookup hash table: 131072 total in 5 blocks; 58744 free (12 chunks); 72328 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 18874368 total in 14 blocks; 2544 free (6 chunks); 18871824 used
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 524288 total in 7 blocks; 247016 free (24 chunks); 277272 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used:
Relcache by OID: 131072 total in 5 blocks; 36024 free (10 chunks); 95048 used
CacheMemoryContext: 17586560 total in 78 blocks; 2655312 free (2 chunks); 14931248 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetingtranscriptmap_p31
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetingtranscriptmap_p31_siteid_tsobjectuuid_idx
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetingtranscriptmap_p31_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstanceinvitee_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceinvitee_p22_siteid_uid__meetinginstancei_idx4
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceinvitee_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticiprelation_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticiprelation_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticiprelation_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticipantaudio_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmeetinginstanceparticipant_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceparticipan_siteid_uid__meetinginstance_idx223
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceparticipant_p223_pkey
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p62
CachedPlan: 131072 total in 8 blocks; 38072 free (0 chunks); 93000 used: UPDATE WBXMEETINGINSTANCEREPORTINFO SET ITEMVALUE = $1 WHERE ITEMNAME = $2 AND MEE...
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p13_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p13_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p12_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p12_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p6_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p6_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p4_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p4_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p3_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p3_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p2_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p2_pkey
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 used: SELECT CLOCK_TIMESTAMP()
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 used: SELECT CLOCK_TIMESTAMP()
CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p117
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_pkey
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : StELrEiCgT gCeLOrC_Kf_TcItME_ScTAoMmPm(o)
n_ l mCachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedt: (S)E
LECT PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedCL: OtCrKi_gTgIeMrE_SfTcAtM_Pc(o)m
m o n _CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 usedl
m t (SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used)
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used : PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedwb: xtprositgmgeeert_ifncgtc_ocntoamimneornm_alpm_tp(4)2
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: : twrbxipgogsetrm_efectti_ncgocmomnotna_ilnemrtm(a)p
_p4 2_PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 useds: itteriid_gmgeeert_ifncgti_ncsotmamnocne_ildm_tk(e)y
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : wtbrxipgogsetrm_efectti_ncgocmonmtoani_nlemrtma(p)_
p4 2Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used_
pk ePLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 usedy
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 usedCachedPlan: 8192 total in 4 blocks; 1920 free (0 chunks); 6272 used
: IN SPL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 usedE: RtT rIiNgTgOe rW_BfXcMtE_EcToImNmGoInN_SlTmAtN(CE)R
EP ORendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedR
TIN FPLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 usedO
(S IPrepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 usedT
EI DTableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used,
ME EOperator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 usedT
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
E, smgr relation table: 1048576 total in 8 blocks; 518320 free (28 chunks); 530256 usedI
TEM VTransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 usedA
LU EPortal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used)
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used :
VA...
Relcache by OID: 262144 total in 6 blocks; 87104 free (12 chunks); 175040 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstancereportinfo_p5
index info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 used: wbxmeetinginstancereportinfo_p5_siteid_idx
CacheMemoryContext: 35269048 total in 1095 blocks; 5938376 free (93 chunks); 29330672 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used
: w b xindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedm: eewtbixnmgeientsitnagnicnesrteapnocretiinnfvoi_tpe5_ep_kpe2y5
_ s iindex info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 usedt: ewbixdm_euetiidn_g_imnesettaincnegrienpsotrtainncfeoi__pi1d_xs6i
te i dindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used_: iwdbxx
m e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedt: iwnbgximnesettainncgeiinnsvtiatnecee_rpe2po5r5t_ipnkfeoy_
p1 _ pindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedk: ewyb
x m epartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usede: twibnxgmienesttianngcienisntvaintceeree_ppo2r5ti_nsfiot_ep1i
d _ uCachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedi: dS_E_LmEeCeTt iCnLOgCiKn_sTtIaMnEcSeTiA_MiPd(x)5
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: S: EwLbExCmTe eCtLiOCnKg_iTnIsMtESaTnAcMePi(n)v
i t e eCachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
_ p 2SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used5
4 _ ppartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usedk: ewyb
xp o sindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedtm: ewebtximnegectoinntgaiinnsetramnacpe_pi2n1v7i
t e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used_: pw2b5x_psoisttemiede_tuiindg_c_omneteatiinnergmianps_tpa2n1c7e_is_iitdexid4_
m e etindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 usedi: nwgbixnmseteatnicnegiidn_skteayn
c e iindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: nwbvxiptoeset_mpe2e5t3i_npgkceoyn
ta i nindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usede: rmwabpx_mpe2e1t7i_npgkienys
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: Postgresql JDBC process consumes more memory with partition tables update delete
@ 2022-09-08 08:30 James Pang (chaolpan) <chaolpan@cisco.com>
parent: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
3 siblings, 0 replies; 19+ messages in thread
From: James Pang (chaolpan) @ 2022-09-08 08:30 UTC (permalink / raw)
To: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>; +Cc: pgsql-jdbc@lists.postgresql.org <pgsql-jdbc@lists.postgresql.org>
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
we have 18 tables copy from Oracle DB, but there is no much data, even empty table, we still got high memory.
most of tables have 256 HASH partitions
partition key is part of primary key ,
meetinginstanceid | character varying(128) | | not null |
and
the update and delete looks like this
update ... set ... where siteid=$1(bigint) and meetinginstanceid=$2(it's partition key and part of primary key) .
from pmap -x , any process run UPDATE/DELETE show similar memory 160-170MB/per backend server, because all JDBC SQL got run by shared connection pool. I see all process show similar
rw--- [anon] contribute most of memory
# pmap -x 16971
16971: postgres: mhs mhsdb 192.168.205.140(36594) idle
Address Kbytes RSS Dirty Mode Mapping
0000000000400000 7408 6152 0 r-x-- postgres
0000000000d3c000 116 116 8 r---- postgres
0000000000d59000 60 60 60 rw--- postgres
0000000000d68000 200 196 196 rw--- [ anon ]
0000000002a7f000 516 412 412 rw--- [ anon ]
0000000002b00000 157836 149484 149484 rw--- [ anon ]
00007f6794d85000 4100 4100 4100 rw--- [ anon ]
copy from part of context memory stats dumped to pglog when "out of memory" , this table has a update trigger that call a PL/PGSQL function so it's has that,
but enven other tables with 256 HASH parition ,no any trigger no any pl/pgsql, it's still fail into out of memory error and consume similar memory 160MB/per backend process.
TopMemoryContext: 1593168 total in 31 blocks; 127440 free (87 chunks); 1465728 used
TopTransactionContext: 8192 total in 1 blocks; 7160 free (1 chunks); 1032 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
pgstat TabStatusArray lookup hash table: 131072 total in 5 blocks; 58744 free (12 chunks); 72328 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 18874368 total in 14 blocks; 2544 free (6 chunks); 18871824 used
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 524288 total in 7 blocks; 247016 free (24 chunks); 277272 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used:
Relcache by OID: 131072 total in 5 blocks; 36024 free (10 chunks); 95048 used
CacheMemoryContext: 17586560 total in 78 blocks; 2655312 free (2 chunks); 14931248 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetingtranscriptmap_p31
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetingtranscriptmap_p31_siteid_tsobjectuuid_idx
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetingtranscriptmap_p31_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstanceinvitee_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceinvitee_p22_siteid_uid__meetinginstancei_idx4
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceinvitee_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticiprelation_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticiprelation_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticiprelation_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticipantaudio_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmeetinginstanceparticipant_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceparticipan_siteid_uid__meetinginstance_idx223
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceparticipant_p223_pkey
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p62
CachedPlan: 131072 total in 8 blocks; 38072 free (0 chunks); 93000 used: UPDATE WBXMEETINGINSTANCEREPORTINFO SET ITEMVALUE = $1 WHERE ITEMNAME = $2 AND MEE...
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p13_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p13_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p12_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p12_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p6_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p6_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p4_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p4_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p3_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p3_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p2_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p2_pkey
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 used: SELECT CLOCK_TIMESTAMP()
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 used: SELECT CLOCK_TIMESTAMP()
CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p117
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_pkey
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : StELrEiCgT gCeLOrC_Kf_TcItME_ScTAoMmPm(o)
n_ l mCachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedt: (S)E
LECT PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedCL: OtCrKi_gTgIeMrE_SfTcAtM_Pc(o)m
m o n _CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 usedl
m t (SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used)
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used : PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedwb: xtprositgmgeeert_ifncgtc_ocntoamimneornm_alpm_tp(4)2
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: : twrbxipgogsetrm_efectti_ncgocmomnotna_ilnemrtm(a)p
_p4 2_PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 useds: itteriid_gmgeeert_ifncgti_ncsotmamnocne_ildm_tk(e)y
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : wtbrxipgogsetrm_efectti_ncgocmonmtoani_nlemrtma(p)_
p4 2Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used_
pk ePLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 usedy
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 usedCachedPlan: 8192 total in 4 blocks; 1920 free (0 chunks); 6272 used
: IN SPL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 usedE: RtT rIiNgTgOe rW_BfXcMtE_EcToImNmGoInN_SlTmAtN(CE)R
EP ORendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedR
TIN FPLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 usedO
(S IPrepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 usedT
EI DTableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used,
ME EOperator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 usedT
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
E, smgr relation table: 1048576 total in 8 blocks; 518320 free (28 chunks); 530256 usedI
TEM VTransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 usedA
LU EPortal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used)
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used :
VA...
Relcache by OID: 262144 total in 6 blocks; 87104 free (12 chunks); 175040 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstancereportinfo_p5
index info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 used: wbxmeetinginstancereportinfo_p5_siteid_idx
CacheMemoryContext: 35269048 total in 1095 blocks; 5938376 free (93 chunks); 29330672 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used
: w b xindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedm: eewtbixnmgeientsitnagnicnesrteapnocretiinnfvoi_tpe5_ep_kpe2y5
_ s iindex info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 usedt: ewbixdm_euetiidn_g_imnesettaincnegrienpsotrtainncfeoi__pi1d_xs6i
te i dindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used_: iwdbxx
m e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedt: iwnbgximnesettainncgeiinnsvtiatnecee_rpe2po5r5t_ipnkfeoy_
p1 _ pindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedk: ewyb
x m epartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usede: twibnxgmienesttianngcienisntvaintceeree_ppo2r5ti_nsfiot_ep1i
d _ uCachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedi: dS_E_LmEeCeTt iCnLOgCiKn_sTtIaMnEcSeTiA_MiPd(x)5
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: S: EwLbExCmTe eCtLiOCnKg_iTnIsMtESaTnAcMePi(n)v
i t e eCachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
_ p 2SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used5
4 _ ppartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usedk: ewyb
xp o sindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedtm: ewebtximnegectoinntgaiinnsetramnacpe_pi2n1v7i
t e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used_: pw2b5x_psoisttemiede_tuiindg_c_omneteatiinnergmianps_tpa2n1c7e_is_iitdexid4_
m e etindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 usedi: nwgbixnmseteatnicnegiidn_skteayn
c e iindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: nwbvxiptoeset_mpe2e5t3i_npgkceoyn
ta i nindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usede: rmwabpx_mpe2e1t7i_npgkienys
...
TopMemoryContext: 1581096 total in 30 blocks; 26160 free (19 chunks); 1554936 used
pgstat TabStatusArray lookup hash table: 131072 total in 5 blocks; 61864 free (12 chunks); 69208 used
TopTransactionContext: 8192 total in 1 blocks; 7352 free (1 chunks); 840 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 29360128 total in 14 blocks; 896536 free (10 chunks); 28463592 used
CachedPlan: 534528 total in 10 blocks; 4328 free (5 chunks); 530200 used: DELETE FROM WBXMTGINSTANCEPARTICIPANTAUDIO WHERE SITEID=$1 AND MEETINGINSTANCEID =$2
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 524288 total in 7 blocks; 84552 free (24 chunks); 439736 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used:
Relcache by OID: 262144 total in 6 blocks; 116176 free (12 chunks); 145968 used
CacheMemoryContext: 30819864 total in 600 blocks; 6376040 free (128 chunks); 24443824 used
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p255_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p255_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p254_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p254_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p253_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p253_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p252_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p252_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p251_siteid_idx
...
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p210_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p209_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p209_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p208_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p208_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p207_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p207_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticTopMemoryContext: 1520736 total in 25 blocks; 148800 free (193 chunks); 1371936 usedi
pa nTopTransactionContext: 8192 total in 1 blocks; 6824 free (0 chunks); 1368 usedt
a udpgstat TabStatusArray lookup hash table: 65536 total in 4 blocks; 32704 free (11 chunks); 32832 usedi
o_ pPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used2: 0t6r_isgigteeri_df_citd_xc
o m mindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedo: nw_blxmmtt(g)i
n sPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedta: ntcreipgagretri_cficpta_nctoamumdoino__lpm2t0(6)_
pk ePL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedy:
trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 4194304 total in 10 blocks; 1380296 free (16 chunks); 2814008 used
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 262144 total in 6 blocks; 34272 free (20 chunks); 227872 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
...
Attachments:
[text/plain] postgresql-jdbc-partition-table-highmemroy.txt (22.8K, ../../PH0PR11MB519109C0DD5E9CCAE097952BD6409@PH0PR11MB5191.namprd11.prod.outlook.com/3-postgresql-jdbc-partition-table-highmemroy.txt)
download | inline:
we have 18 tables copy from Oracle DB, but there is no much data, even empty table, we still got high memory.
most of tables have 256 HASH partitions
partition key is part of primary key ,
meetinginstanceid | character varying(128) | | not null |
and
the update and delete looks like this
update ... set ... where siteid=$1(bigint) and meetinginstanceid=$2(it's partition key and part of primary key) .
from pmap -x , any process run UPDATE/DELETE show similar memory 160-170MB/per backend server, because all JDBC SQL got run by shared connection pool. I see all process show similar
rw--- [anon] contribute most of memory
# pmap -x 16971
16971: postgres: mhs mhsdb 192.168.205.140(36594) idle
Address Kbytes RSS Dirty Mode Mapping
0000000000400000 7408 6152 0 r-x-- postgres
0000000000d3c000 116 116 8 r---- postgres
0000000000d59000 60 60 60 rw--- postgres
0000000000d68000 200 196 196 rw--- [ anon ]
0000000002a7f000 516 412 412 rw--- [ anon ]
0000000002b00000 157836 149484 149484 rw--- [ anon ]
00007f6794d85000 4100 4100 4100 rw--- [ anon ]
copy from part of context memory stats dumped to pglog when "out of memory" , this table has a update trigger that call a PL/PGSQL function so it's has that,
but enven other tables with 256 HASH parition ,no any trigger no any pl/pgsql, it's still fail into out of memory error and consume similar memory 160MB/per backend process.
TopMemoryContext: 1593168 total in 31 blocks; 127440 free (87 chunks); 1465728 used
TopTransactionContext: 8192 total in 1 blocks; 7160 free (1 chunks); 1032 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
pgstat TabStatusArray lookup hash table: 131072 total in 5 blocks; 58744 free (12 chunks); 72328 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 18874368 total in 14 blocks; 2544 free (6 chunks); 18871824 used
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 524288 total in 7 blocks; 247016 free (24 chunks); 277272 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used:
Relcache by OID: 131072 total in 5 blocks; 36024 free (10 chunks); 95048 used
CacheMemoryContext: 17586560 total in 78 blocks; 2655312 free (2 chunks); 14931248 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetingtranscriptmap_p31
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetingtranscriptmap_p31_siteid_tsobjectuuid_idx
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetingtranscriptmap_p31_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstanceinvitee_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceinvitee_p22_siteid_uid__meetinginstancei_idx4
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceinvitee_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticiprelation_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticiprelation_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticiprelation_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticipantaudio_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmeetinginstanceparticipant_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceparticipan_siteid_uid__meetinginstance_idx223
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceparticipant_p223_pkey
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p62
CachedPlan: 131072 total in 8 blocks; 38072 free (0 chunks); 93000 used: UPDATE WBXMEETINGINSTANCEREPORTINFO SET ITEMVALUE = $1 WHERE ITEMNAME = $2 AND MEE...
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p13_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p13_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p12_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p12_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p6_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p6_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p4_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p4_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p3_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p3_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p2_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p2_pkey
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 used: SELECT CLOCK_TIMESTAMP()
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 used: SELECT CLOCK_TIMESTAMP()
CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p117
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_pkey
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : StELrEiCgT gCeLOrC_Kf_TcItME_ScTAoMmPm(o)
n_ l mCachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedt: (S)E
LECT PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedCL: OtCrKi_gTgIeMrE_SfTcAtM_Pc(o)m
m o n _CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 usedl
m t (SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used)
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used : PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedwb: xtprositgmgeeert_ifncgtc_ocntoamimneornm_alpm_tp(4)2
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: : twrbxipgogsetrm_efectti_ncgocmomnotna_ilnemrtm(a)p
_p4 2_PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 useds: itteriid_gmgeeert_ifncgti_ncsotmamnocne_ildm_tk(e)y
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : wtbrxipgogsetrm_efectti_ncgocmonmtoani_nlemrtma(p)_
p4 2Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used_
pk ePLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 usedy
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 usedCachedPlan: 8192 total in 4 blocks; 1920 free (0 chunks); 6272 used
: IN SPL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 usedE: RtT rIiNgTgOe rW_BfXcMtE_EcToImNmGoInN_SlTmAtN(CE)R
EP ORendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedR
TIN FPLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 usedO
(S IPrepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 usedT
EI DTableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used,
ME EOperator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 usedT
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
E, smgr relation table: 1048576 total in 8 blocks; 518320 free (28 chunks); 530256 usedI
TEM VTransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 usedA
LU EPortal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used)
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used :
VA...
Relcache by OID: 262144 total in 6 blocks; 87104 free (12 chunks); 175040 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstancereportinfo_p5
index info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 used: wbxmeetinginstancereportinfo_p5_siteid_idx
CacheMemoryContext: 35269048 total in 1095 blocks; 5938376 free (93 chunks); 29330672 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used
: w b xindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedm: eewtbixnmgeientsitnagnicnesrteapnocretiinnfvoi_tpe5_ep_kpe2y5
_ s iindex info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 usedt: ewbixdm_euetiidn_g_imnesettaincnegrienpsotrtainncfeoi__pi1d_xs6i
te i dindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used_: iwdbxx
m e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedt: iwnbgximnesettainncgeiinnsvtiatnecee_rpe2po5r5t_ipnkfeoy_
p1 _ pindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedk: ewyb
x m epartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usede: twibnxgmienesttianngcienisntvaintceeree_ppo2r5ti_nsfiot_ep1i
d _ uCachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedi: dS_E_LmEeCeTt iCnLOgCiKn_sTtIaMnEcSeTiA_MiPd(x)5
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: S: EwLbExCmTe eCtLiOCnKg_iTnIsMtESaTnAcMePi(n)v
i t e eCachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
_ p 2SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used5
4 _ ppartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usedk: ewyb
xp o sindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedtm: ewebtximnegectoinntgaiinnsetramnacpe_pi2n1v7i
t e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used_: pw2b5x_psoisttemiede_tuiindg_c_omneteatiinnergmianps_tpa2n1c7e_is_iitdexid4_
m e etindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 usedi: nwgbixnmseteatnicnegiidn_skteayn
c e iindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: nwbvxiptoeset_mpe2e5t3i_npgkceoyn
ta i nindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usede: rmwabpx_mpe2e1t7i_npgkienys
...
TopMemoryContext: 1581096 total in 30 blocks; 26160 free (19 chunks); 1554936 used
pgstat TabStatusArray lookup hash table: 131072 total in 5 blocks; 61864 free (12 chunks); 69208 used
TopTransactionContext: 8192 total in 1 blocks; 7352 free (1 chunks); 840 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 29360128 total in 14 blocks; 896536 free (10 chunks); 28463592 used
CachedPlan: 534528 total in 10 blocks; 4328 free (5 chunks); 530200 used: DELETE FROM WBXMTGINSTANCEPARTICIPANTAUDIO WHERE SITEID=$1 AND MEETINGINSTANCEID =$2
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 524288 total in 7 blocks; 84552 free (24 chunks); 439736 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used:
Relcache by OID: 262144 total in 6 blocks; 116176 free (12 chunks); 145968 used
CacheMemoryContext: 30819864 total in 600 blocks; 6376040 free (128 chunks); 24443824 used
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p255_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p255_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p254_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p254_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p253_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p253_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p252_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p252_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p251_siteid_idx
...
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p210_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p209_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p209_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p208_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p208_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p207_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p207_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticTopMemoryContext: 1520736 total in 25 blocks; 148800 free (193 chunks); 1371936 usedi
pa nTopTransactionContext: 8192 total in 1 blocks; 6824 free (0 chunks); 1368 usedt
a udpgstat TabStatusArray lookup hash table: 65536 total in 4 blocks; 32704 free (11 chunks); 32832 usedi
o_ pPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used2: 0t6r_isgigteeri_df_citd_xc
o m mindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedo: nw_blxmmtt(g)i
n sPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedta: ntcreipgagretri_cficpta_nctoamumdoino__lpm2t0(6)_
pk ePL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedy:
trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 4194304 total in 10 blocks; 1380296 free (16 chunks); 2814008 used
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 262144 total in 6 blocks; 34272 free (20 chunks); 227872 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
...
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: Postgresql JDBC process consumes more memory with partition tables update delete
@ 2022-09-08 08:32 James Pang (chaolpan) <chaolpan@cisco.com>
parent: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
3 siblings, 0 replies; 19+ messages in thread
From: James Pang (chaolpan) @ 2022-09-08 08:32 UTC (permalink / raw)
To: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>; +Cc: pgsql-jdbc@lists.postgresql.org <pgsql-jdbc@lists.postgresql.org>
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
we have 18 tables copy from Oracle DB, but there is no much data, even empty table, we still got high memory.
most of tables have 256 HASH partitions
partition key is part of primary key ,
meetinginstanceid | character varying(128) | | not null |
and
the update and delete looks like this
update ... set ... where siteid=$1(bigint) and meetinginstanceid=$2(it's partition key and part of primary key) .
from pmap -x , any process run UPDATE/DELETE show similar memory 160-170MB/per backend server, because all JDBC SQL got run by shared connection pool. I see all process show similar
rw--- [anon] contribute most of memory
# pmap -x 16971
16971: postgres: mhs mhsdb 192.168.205.140(36594) idle
Address Kbytes RSS Dirty Mode Mapping
0000000000400000 7408 6152 0 r-x-- postgres
0000000000d3c000 116 116 8 r---- postgres
0000000000d59000 60 60 60 rw--- postgres
0000000000d68000 200 196 196 rw--- [ anon ]
0000000002a7f000 516 412 412 rw--- [ anon ]
0000000002b00000 157836 149484 149484 rw--- [ anon ]
00007f6794d85000 4100 4100 4100 rw--- [ anon ]
copy from part of context memory stats dumped to pglog when "out of memory" , this table has a update trigger that call a PL/PGSQL function so it's has that,
but enven other tables with 256 HASH parition ,no any trigger no any pl/pgsql, it's still fail into out of memory error and consume similar memory 160MB/per backend process.
TopMemoryContext: 1593168 total in 31 blocks; 127440 free (87 chunks); 1465728 used
TopTransactionContext: 8192 total in 1 blocks; 7160 free (1 chunks); 1032 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
pgstat TabStatusArray lookup hash table: 131072 total in 5 blocks; 58744 free (12 chunks); 72328 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 18874368 total in 14 blocks; 2544 free (6 chunks); 18871824 used
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 524288 total in 7 blocks; 247016 free (24 chunks); 277272 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used:
Relcache by OID: 131072 total in 5 blocks; 36024 free (10 chunks); 95048 used
CacheMemoryContext: 17586560 total in 78 blocks; 2655312 free (2 chunks); 14931248 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetingtranscriptmap_p31
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetingtranscriptmap_p31_siteid_tsobjectuuid_idx
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetingtranscriptmap_p31_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstanceinvitee_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceinvitee_p22_siteid_uid__meetinginstancei_idx4
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceinvitee_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticiprelation_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticiprelation_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticiprelation_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticipantaudio_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmeetinginstanceparticipant_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceparticipan_siteid_uid__meetinginstance_idx223
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceparticipant_p223_pkey
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p62
CachedPlan: 131072 total in 8 blocks; 38072 free (0 chunks); 93000 used: UPDATE WBXMEETINGINSTANCEREPORTINFO SET ITEMVALUE = $1 WHERE ITEMNAME = $2 AND MEE...
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p13_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p13_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p12_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p12_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p6_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p6_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p4_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p4_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p3_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p3_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p2_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p2_pkey
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 used: SELECT CLOCK_TIMESTAMP()
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 used: SELECT CLOCK_TIMESTAMP()
CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p117
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_pkey
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : StELrEiCgT gCeLOrC_Kf_TcItME_ScTAoMmPm(o)
n_ l mCachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedt: (S)E
LECT PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedCL: OtCrKi_gTgIeMrE_SfTcAtM_Pc(o)m
m o n _CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 usedl
m t (SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used)
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used : PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedwb: xtprositgmgeeert_ifncgtc_ocntoamimneornm_alpm_tp(4)2
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: : twrbxipgogsetrm_efectti_ncgocmomnotna_ilnemrtm(a)p
_p4 2_PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 useds: itteriid_gmgeeert_ifncgti_ncsotmamnocne_ildm_tk(e)y
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : wtbrxipgogsetrm_efectti_ncgocmonmtoani_nlemrtma(p)_
p4 2Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used_
pk ePLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 usedy
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 usedCachedPlan: 8192 total in 4 blocks; 1920 free (0 chunks); 6272 used
: IN SPL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 usedE: RtT rIiNgTgOe rW_BfXcMtE_EcToImNmGoInN_SlTmAtN(CE)R
EP ORendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedR
TIN FPLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 usedO
(S IPrepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 usedT
EI DTableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used,
ME EOperator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 usedT
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
E, smgr relation table: 1048576 total in 8 blocks; 518320 free (28 chunks); 530256 usedI
TEM VTransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 usedA
LU EPortal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used)
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used :
VA...
Relcache by OID: 262144 total in 6 blocks; 87104 free (12 chunks); 175040 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstancereportinfo_p5
index info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 used: wbxmeetinginstancereportinfo_p5_siteid_idx
CacheMemoryContext: 35269048 total in 1095 blocks; 5938376 free (93 chunks); 29330672 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used
: w b xindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedm: eewtbixnmgeientsitnagnicnesrteapnocretiinnfvoi_tpe5_ep_kpe2y5
_ s iindex info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 usedt: ewbixdm_euetiidn_g_imnesettaincnegrienpsotrtainncfeoi__pi1d_xs6i
te i dindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used_: iwdbxx
m e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedt: iwnbgximnesettainncgeiinnsvtiatnecee_rpe2po5r5t_ipnkfeoy_
p1 _ pindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedk: ewyb
x m epartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usede: twibnxgmienesttianngcienisntvaintceeree_ppo2r5ti_nsfiot_ep1i
d _ uCachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedi: dS_E_LmEeCeTt iCnLOgCiKn_sTtIaMnEcSeTiA_MiPd(x)5
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: S: EwLbExCmTe eCtLiOCnKg_iTnIsMtESaTnAcMePi(n)v
i t e eCachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
_ p 2SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used5
4 _ ppartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usedk: ewyb
xp o sindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedtm: ewebtximnegectoinntgaiinnsetramnacpe_pi2n1v7i
t e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used_: pw2b5x_psoisttemiede_tuiindg_c_omneteatiinnergmianps_tpa2n1c7e_is_iitdexid4_
m e etindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 usedi: nwgbixnmseteatnicnegiidn_skteayn
c e iindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: nwbvxiptoeset_mpe2e5t3i_npgkceoyn
ta i nindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usede: rmwabpx_mpe2e1t7i_npgkienys
...
TopMemoryContext: 1581096 total in 30 blocks; 26160 free (19 chunks); 1554936 used
pgstat TabStatusArray lookup hash table: 131072 total in 5 blocks; 61864 free (12 chunks); 69208 used
TopTransactionContext: 8192 total in 1 blocks; 7352 free (1 chunks); 840 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 29360128 total in 14 blocks; 896536 free (10 chunks); 28463592 used
CachedPlan: 534528 total in 10 blocks; 4328 free (5 chunks); 530200 used: DELETE FROM xxxxxxxx WHERE SITEID=$1 AND MEETINGINSTANCEID =$2
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 524288 total in 7 blocks; 84552 free (24 chunks); 439736 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used:
Relcache by OID: 262144 total in 6 blocks; 116176 free (12 chunks); 145968 used
CacheMemoryContext: 30819864 total in 600 blocks; 6376040 free (128 chunks); 24443824 used
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p255_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p255_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p254_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p254_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p253_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p253_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p252_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p252_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p251_siteid_idx
...
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p210_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p209_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p209_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p208_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p208_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p207_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p207_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticTopMemoryContext: 1520736 total in 25 blocks; 148800 free (193 chunks); 1371936 usedi
pa nTopTransactionContext: 8192 total in 1 blocks; 6824 free (0 chunks); 1368 usedt
a udpgstat TabStatusArray lookup hash table: 65536 total in 4 blocks; 32704 free (11 chunks); 32832 usedi
o_ pPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used2: 0t6r_isgigteeri_df_citd_xc
o m mindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedo: nw_blxmmtt(g)i
n sPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedta: ntcreipgagretri_cficpta_nctoamumdoino__lpm2t0(6)_
pk ePL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedy:
trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 4194304 total in 10 blocks; 1380296 free (16 chunks); 2814008 used
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 262144 total in 6 blocks; 34272 free (20 chunks); 227872 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
...
Attachments:
[text/plain] postgresql-jdbc-partition-table-highmemroy.txt (22.8K, ../../PH0PR11MB519188F46BD5595EF6F225A3D6409@PH0PR11MB5191.namprd11.prod.outlook.com/3-postgresql-jdbc-partition-table-highmemroy.txt)
download | inline:
we have 18 tables copy from Oracle DB, but there is no much data, even empty table, we still got high memory.
most of tables have 256 HASH partitions
partition key is part of primary key ,
meetinginstanceid | character varying(128) | | not null |
and
the update and delete looks like this
update ... set ... where siteid=$1(bigint) and meetinginstanceid=$2(it's partition key and part of primary key) .
from pmap -x , any process run UPDATE/DELETE show similar memory 160-170MB/per backend server, because all JDBC SQL got run by shared connection pool. I see all process show similar
rw--- [anon] contribute most of memory
# pmap -x 16971
16971: postgres: mhs mhsdb 192.168.205.140(36594) idle
Address Kbytes RSS Dirty Mode Mapping
0000000000400000 7408 6152 0 r-x-- postgres
0000000000d3c000 116 116 8 r---- postgres
0000000000d59000 60 60 60 rw--- postgres
0000000000d68000 200 196 196 rw--- [ anon ]
0000000002a7f000 516 412 412 rw--- [ anon ]
0000000002b00000 157836 149484 149484 rw--- [ anon ]
00007f6794d85000 4100 4100 4100 rw--- [ anon ]
copy from part of context memory stats dumped to pglog when "out of memory" , this table has a update trigger that call a PL/PGSQL function so it's has that,
but enven other tables with 256 HASH parition ,no any trigger no any pl/pgsql, it's still fail into out of memory error and consume similar memory 160MB/per backend process.
TopMemoryContext: 1593168 total in 31 blocks; 127440 free (87 chunks); 1465728 used
TopTransactionContext: 8192 total in 1 blocks; 7160 free (1 chunks); 1032 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
pgstat TabStatusArray lookup hash table: 131072 total in 5 blocks; 58744 free (12 chunks); 72328 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 18874368 total in 14 blocks; 2544 free (6 chunks); 18871824 used
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 524288 total in 7 blocks; 247016 free (24 chunks); 277272 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used:
Relcache by OID: 131072 total in 5 blocks; 36024 free (10 chunks); 95048 used
CacheMemoryContext: 17586560 total in 78 blocks; 2655312 free (2 chunks); 14931248 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetingtranscriptmap_p31
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetingtranscriptmap_p31_siteid_tsobjectuuid_idx
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetingtranscriptmap_p31_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstanceinvitee_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceinvitee_p22_siteid_uid__meetinginstancei_idx4
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceinvitee_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticiprelation_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticiprelation_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticiprelation_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticipantaudio_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmeetinginstanceparticipant_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceparticipan_siteid_uid__meetinginstance_idx223
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceparticipant_p223_pkey
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p62
CachedPlan: 131072 total in 8 blocks; 38072 free (0 chunks); 93000 used: UPDATE WBXMEETINGINSTANCEREPORTINFO SET ITEMVALUE = $1 WHERE ITEMNAME = $2 AND MEE...
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p13_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p13_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p12_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p12_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p6_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p6_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p4_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p4_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p3_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p3_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p2_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p2_pkey
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 used: SELECT CLOCK_TIMESTAMP()
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 used: SELECT CLOCK_TIMESTAMP()
CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p117
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_pkey
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : StELrEiCgT gCeLOrC_Kf_TcItME_ScTAoMmPm(o)
n_ l mCachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedt: (S)E
LECT PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedCL: OtCrKi_gTgIeMrE_SfTcAtM_Pc(o)m
m o n _CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 usedl
m t (SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used)
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used : PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedwb: xtprositgmgeeert_ifncgtc_ocntoamimneornm_alpm_tp(4)2
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: : twrbxipgogsetrm_efectti_ncgocmomnotna_ilnemrtm(a)p
_p4 2_PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 useds: itteriid_gmgeeert_ifncgti_ncsotmamnocne_ildm_tk(e)y
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : wtbrxipgogsetrm_efectti_ncgocmonmtoani_nlemrtma(p)_
p4 2Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used_
pk ePLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 usedy
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 usedCachedPlan: 8192 total in 4 blocks; 1920 free (0 chunks); 6272 used
: IN SPL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 usedE: RtT rIiNgTgOe rW_BfXcMtE_EcToImNmGoInN_SlTmAtN(CE)R
EP ORendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedR
TIN FPLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 usedO
(S IPrepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 usedT
EI DTableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used,
ME EOperator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 usedT
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
E, smgr relation table: 1048576 total in 8 blocks; 518320 free (28 chunks); 530256 usedI
TEM VTransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 usedA
LU EPortal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used)
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used :
VA...
Relcache by OID: 262144 total in 6 blocks; 87104 free (12 chunks); 175040 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstancereportinfo_p5
index info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 used: wbxmeetinginstancereportinfo_p5_siteid_idx
CacheMemoryContext: 35269048 total in 1095 blocks; 5938376 free (93 chunks); 29330672 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used
: w b xindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedm: eewtbixnmgeientsitnagnicnesrteapnocretiinnfvoi_tpe5_ep_kpe2y5
_ s iindex info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 usedt: ewbixdm_euetiidn_g_imnesettaincnegrienpsotrtainncfeoi__pi1d_xs6i
te i dindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used_: iwdbxx
m e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedt: iwnbgximnesettainncgeiinnsvtiatnecee_rpe2po5r5t_ipnkfeoy_
p1 _ pindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedk: ewyb
x m epartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usede: twibnxgmienesttianngcienisntvaintceeree_ppo2r5ti_nsfiot_ep1i
d _ uCachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedi: dS_E_LmEeCeTt iCnLOgCiKn_sTtIaMnEcSeTiA_MiPd(x)5
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: S: EwLbExCmTe eCtLiOCnKg_iTnIsMtESaTnAcMePi(n)v
i t e eCachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
_ p 2SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used5
4 _ ppartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usedk: ewyb
xp o sindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedtm: ewebtximnegectoinntgaiinnsetramnacpe_pi2n1v7i
t e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used_: pw2b5x_psoisttemiede_tuiindg_c_omneteatiinnergmianps_tpa2n1c7e_is_iitdexid4_
m e etindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 usedi: nwgbixnmseteatnicnegiidn_skteayn
c e iindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: nwbvxiptoeset_mpe2e5t3i_npgkceoyn
ta i nindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usede: rmwabpx_mpe2e1t7i_npgkienys
...
TopMemoryContext: 1581096 total in 30 blocks; 26160 free (19 chunks); 1554936 used
pgstat TabStatusArray lookup hash table: 131072 total in 5 blocks; 61864 free (12 chunks); 69208 used
TopTransactionContext: 8192 total in 1 blocks; 7352 free (1 chunks); 840 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 29360128 total in 14 blocks; 896536 free (10 chunks); 28463592 used
CachedPlan: 534528 total in 10 blocks; 4328 free (5 chunks); 530200 used: DELETE FROM xxxxxxxx WHERE SITEID=$1 AND MEETINGINSTANCEID =$2
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 524288 total in 7 blocks; 84552 free (24 chunks); 439736 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used:
Relcache by OID: 262144 total in 6 blocks; 116176 free (12 chunks); 145968 used
CacheMemoryContext: 30819864 total in 600 blocks; 6376040 free (128 chunks); 24443824 used
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p255_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p255_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p254_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p254_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p253_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p253_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p252_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p252_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p251_siteid_idx
...
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p210_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p209_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p209_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p208_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p208_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p207_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p207_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticTopMemoryContext: 1520736 total in 25 blocks; 148800 free (193 chunks); 1371936 usedi
pa nTopTransactionContext: 8192 total in 1 blocks; 6824 free (0 chunks); 1368 usedt
a udpgstat TabStatusArray lookup hash table: 65536 total in 4 blocks; 32704 free (11 chunks); 32832 usedi
o_ pPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used2: 0t6r_isgigteeri_df_citd_xc
o m mindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedo: nw_blxmmtt(g)i
n sPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedta: ntcreipgagretri_cficpta_nctoamumdoino__lpm2t0(6)_
pk ePL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedy:
trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 4194304 total in 10 blocks; 1380296 free (16 chunks); 2814008 used
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 262144 total in 6 blocks; 34272 free (20 chunks); 227872 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
...
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: Postgresql JDBC process consumes more memory with partition tables update delete
@ 2022-09-08 08:36 James Pang (chaolpan) <chaolpan@cisco.com>
parent: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
3 siblings, 2 replies; 19+ messages in thread
From: James Pang (chaolpan) @ 2022-09-08 08:36 UTC (permalink / raw)
To: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>; +Cc: pgsql-jdbc@lists.postgresql.org <pgsql-jdbc@lists.postgresql.org>
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
we have eighteen tables copy from Oracle DB, but there is no much data, even empty table, we still got high memory.
most of tables have 256 HASH partitions
partition key is part of primary key ,
meetinginstanceid | character varying(128) | | not null |
and
the update and delete looks like this
update ... set ... where siteid=$1(bigint) and meetinginstanceid=$2(it's partition key and part of primary key) .
from pmap -x , any process run UPDATE/DELETE show similar memory 160-170MB/per backend server, because all JDBC SQL got run by shared connection pool. I see all process show similar
rw--- [anon] contribute most of memory
# pmap -x 16971
16971: postgres: xxxxxx(36594) idle
Address Kbytes RSS Dirty Mode Mapping
0000000000400000 7408 6152 0 r-x-- postgres
0000000000d3c000 116 116 8 r---- postgres
0000000000d59000 60 60 60 rw--- postgres
0000000000d68000 200 196 196 rw--- [ anon ]
0000000002a7f000 516 412 412 rw--- [ anon ]
0000000002b00000 157836 149484 149484 rw--- [ anon ]
00007f6794d85000 4100 4100 4100 rw--- [ anon ]
copy from part of context memory stats dumped to pglog when "out of memory" , this table has a update trigger that call a PL/PGSQL function so it's has that,
but enven other tables with 256 HASH parition ,no any trigger no any pl/pgsql, it's still fail into out of memory error and consume similar memory 160MB/per backend process.
TopMemoryContext: 1593168 total in 31 blocks; 127440 free (87 chunks); 1465728 used
TopTransactionContext: 8192 total in 1 blocks; 7160 free (1 chunks); 1032 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
pgstat TabStatusArray lookup hash table: 131072 total in 5 blocks; 58744 free (12 chunks); 72328 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 18874368 total in 14 blocks; 2544 free (6 chunks); 18871824 used
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 524288 total in 7 blocks; 247016 free (24 chunks); 277272 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used:
Relcache by OID: 131072 total in 5 blocks; 36024 free (10 chunks); 95048 used
CacheMemoryContext: 17586560 total in 78 blocks; 2655312 free (2 chunks); 14931248 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetingtranscriptmap_p31
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetingtranscriptmap_p31_siteid_tsobjectuuid_idx
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetingtranscriptmap_p31_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstanceinvitee_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceinvitee_p22_siteid_uid__meetinginstancei_idx4
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceinvitee_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticiprelation_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticiprelation_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticiprelation_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticipantaudio_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmeetinginstanceparticipant_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceparticipan_siteid_uid__meetinginstance_idx223
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceparticipant_p223_pkey
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p62
CachedPlan: 131072 total in 8 blocks; 38072 free (0 chunks); 93000 used: UPDATE WBXMEETINGINSTANCEREPORTINFO SET ITEMVALUE = $1 WHERE ITEMNAME = $2 AND MEE...
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p13_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p13_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p12_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p12_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p6_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p6_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p4_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p4_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p3_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p3_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p2_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p2_pkey
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 used: SELECT CLOCK_TIMESTAMP()
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 used: SELECT CLOCK_TIMESTAMP()
CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p117
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_pkey
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : StELrEiCgT gCeLOrC_Kf_TcItME_ScTAoMmPm(o)
n_ l mCachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedt: (S)E
LECT PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedCL: OtCrKi_gTgIeMrE_SfTcAtM_Pc(o)m
m o n _CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 usedl
m t (SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used)
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used : PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedwb: xtprositgmgeeert_ifncgtc_ocntoamimneornm_alpm_tp(4)2
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: : twrbxipgogsetrm_efectti_ncgocmomnotna_ilnemrtm(a)p
_p4 2_PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 useds: itteriid_gmgeeert_ifncgti_ncsotmamnocne_ildm_tk(e)y
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : wtbrxipgogsetrm_efectti_ncgocmonmtoani_nlemrtma(p)_
p4 2Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used_
pk ePLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 usedy
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 usedCachedPlan: 8192 total in 4 blocks; 1920 free (0 chunks); 6272 used
: IN SPL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 usedE: RtT rIiNgTgOe rW_BfXcMtE_EcToImNmGoInN_SlTmAtN(CE)R
EP ORendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedR
TIN FPLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 usedO
(S IPrepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 usedT
EI DTableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used,
ME EOperator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 usedT
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
E, smgr relation table: 1048576 total in 8 blocks; 518320 free (28 chunks); 530256 usedI
TEM VTransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 usedA
LU EPortal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used)
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used :
VA...
Relcache by OID: 262144 total in 6 blocks; 87104 free (12 chunks); 175040 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstancereportinfo_p5
index info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 used: wbxmeetinginstancereportinfo_p5_siteid_idx
CacheMemoryContext: 35269048 total in 1095 blocks; 5938376 free (93 chunks); 29330672 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used
: w b xindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedm: eewtbixnmgeientsitnagnicnesrteapnocretiinnfvoi_tpe5_ep_kpe2y5
_ s iindex info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 usedt: ewbixdm_euetiidn_g_imnesettaincnegrienpsotrtainncfeoi__pi1d_xs6i
te i dindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used_: iwdbxx
m e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedt: iwnbgximnesettainncgeiinnsvtiatnecee_rpe2po5r5t_ipnkfeoy_
p1 _ pindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedk: ewyb
x m epartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usede: twibnxgmienesttianngcienisntvaintceeree_ppo2r5ti_nsfiot_ep1i
d _ uCachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedi: dS_E_LmEeCeTt iCnLOgCiKn_sTtIaMnEcSeTiA_MiPd(x)5
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: S: EwLbExCmTe eCtLiOCnKg_iTnIsMtESaTnAcMePi(n)v
i t e eCachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
_ p 2SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used5
4 _ ppartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usedk: ewyb
xp o sindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedtm: ewebtximnegectoinntgaiinnsetramnacpe_pi2n1v7i
t e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used_: pw2b5x_psoisttemiede_tuiindg_c_omneteatiinnergmianps_tpa2n1c7e_is_iitdexid4_
m e etindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 usedi: nwgbixnmseteatnicnegiidn_skteayn
c e iindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: nwbvxiptoeset_mpe2e5t3i_npgkceoyn
ta i nindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usede: rmwabpx_mpe2e1t7i_npgkienys
...
TopMemoryContext: 1581096 total in 30 blocks; 26160 free (19 chunks); 1554936 used
pgstat TabStatusArray lookup hash table: 131072 total in 5 blocks; 61864 free (12 chunks); 69208 used
TopTransactionContext: 8192 total in 1 blocks; 7352 free (1 chunks); 840 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 29360128 total in 14 blocks; 896536 free (10 chunks); 28463592 used
CachedPlan: 534528 total in 10 blocks; 4328 free (5 chunks); 530200 used: DELETE FROM xxxxxxxx WHERE SITEID=$1 AND MEETINGINSTANCEID =$2
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 524288 total in 7 blocks; 84552 free (24 chunks); 439736 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used:
Relcache by OID: 262144 total in 6 blocks; 116176 free (12 chunks); 145968 used
CacheMemoryContext: 30819864 total in 600 blocks; 6376040 free (128 chunks); 24443824 used
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p255_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p255_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p254_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p254_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p253_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p253_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p252_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p252_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p251_siteid_idx
...
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p210_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p209_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p209_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p208_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p208_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p207_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p207_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticTopMemoryContext: 1520736 total in 25 blocks; 148800 free (193 chunks); 1371936 usedi
pa nTopTransactionContext: 8192 total in 1 blocks; 6824 free (0 chunks); 1368 usedt
a udpgstat TabStatusArray lookup hash table: 65536 total in 4 blocks; 32704 free (11 chunks); 32832 usedi
o_ pPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used2: 0t6r_isgigteeri_df_citd_xc
o m mindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedo: nw_blxmmtt(g)i
n sPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedta: ntcreipgagretri_cficpta_nctoamumdoino__lpm2t0(6)_
pk ePL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedy:
trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 4194304 total in 10 blocks; 1380296 free (16 chunks); 2814008 used
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 262144 total in 6 blocks; 34272 free (20 chunks); 227872 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
...
Attachments:
[text/plain] postgresql-jdbc-partition-table-highmemroy.txt (22.8K, ../../PH0PR11MB5191FC6F31970A43B81BC76CD6409@PH0PR11MB5191.namprd11.prod.outlook.com/3-postgresql-jdbc-partition-table-highmemroy.txt)
download | inline:
we have eighteen tables copy from Oracle DB, but there is no much data, even empty table, we still got high memory.
most of tables have 256 HASH partitions
partition key is part of primary key ,
meetinginstanceid | character varying(128) | | not null |
and
the update and delete looks like this
update ... set ... where siteid=$1(bigint) and meetinginstanceid=$2(it's partition key and part of primary key) .
from pmap -x , any process run UPDATE/DELETE show similar memory 160-170MB/per backend server, because all JDBC SQL got run by shared connection pool. I see all process show similar
rw--- [anon] contribute most of memory
# pmap -x 16971
16971: postgres: xxxxxx(36594) idle
Address Kbytes RSS Dirty Mode Mapping
0000000000400000 7408 6152 0 r-x-- postgres
0000000000d3c000 116 116 8 r---- postgres
0000000000d59000 60 60 60 rw--- postgres
0000000000d68000 200 196 196 rw--- [ anon ]
0000000002a7f000 516 412 412 rw--- [ anon ]
0000000002b00000 157836 149484 149484 rw--- [ anon ]
00007f6794d85000 4100 4100 4100 rw--- [ anon ]
copy from part of context memory stats dumped to pglog when "out of memory" , this table has a update trigger that call a PL/PGSQL function so it's has that,
but enven other tables with 256 HASH parition ,no any trigger no any pl/pgsql, it's still fail into out of memory error and consume similar memory 160MB/per backend process.
TopMemoryContext: 1593168 total in 31 blocks; 127440 free (87 chunks); 1465728 used
TopTransactionContext: 8192 total in 1 blocks; 7160 free (1 chunks); 1032 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
pgstat TabStatusArray lookup hash table: 131072 total in 5 blocks; 58744 free (12 chunks); 72328 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 18874368 total in 14 blocks; 2544 free (6 chunks); 18871824 used
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 524288 total in 7 blocks; 247016 free (24 chunks); 277272 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used:
Relcache by OID: 131072 total in 5 blocks; 36024 free (10 chunks); 95048 used
CacheMemoryContext: 17586560 total in 78 blocks; 2655312 free (2 chunks); 14931248 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetingtranscriptmap_p31
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetingtranscriptmap_p31_siteid_tsobjectuuid_idx
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetingtranscriptmap_p31_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstanceinvitee_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceinvitee_p22_siteid_uid__meetinginstancei_idx4
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceinvitee_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticiprelation_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticiprelation_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticiprelation_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticipantaudio_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmeetinginstanceparticipant_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceparticipan_siteid_uid__meetinginstance_idx223
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceparticipant_p223_pkey
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p62
CachedPlan: 131072 total in 8 blocks; 38072 free (0 chunks); 93000 used: UPDATE WBXMEETINGINSTANCEREPORTINFO SET ITEMVALUE = $1 WHERE ITEMNAME = $2 AND MEE...
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p13_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p13_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p12_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p12_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p6_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p6_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p4_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p4_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p3_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p3_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p2_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p2_pkey
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 used: SELECT CLOCK_TIMESTAMP()
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 used: SELECT CLOCK_TIMESTAMP()
CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p117
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_pkey
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : StELrEiCgT gCeLOrC_Kf_TcItME_ScTAoMmPm(o)
n_ l mCachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedt: (S)E
LECT PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedCL: OtCrKi_gTgIeMrE_SfTcAtM_Pc(o)m
m o n _CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 usedl
m t (SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used)
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used : PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedwb: xtprositgmgeeert_ifncgtc_ocntoamimneornm_alpm_tp(4)2
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: : twrbxipgogsetrm_efectti_ncgocmomnotna_ilnemrtm(a)p
_p4 2_PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 useds: itteriid_gmgeeert_ifncgti_ncsotmamnocne_ildm_tk(e)y
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : wtbrxipgogsetrm_efectti_ncgocmonmtoani_nlemrtma(p)_
p4 2Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used_
pk ePLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 usedy
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 usedCachedPlan: 8192 total in 4 blocks; 1920 free (0 chunks); 6272 used
: IN SPL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 usedE: RtT rIiNgTgOe rW_BfXcMtE_EcToImNmGoInN_SlTmAtN(CE)R
EP ORendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedR
TIN FPLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 usedO
(S IPrepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 usedT
EI DTableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used,
ME EOperator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 usedT
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
E, smgr relation table: 1048576 total in 8 blocks; 518320 free (28 chunks); 530256 usedI
TEM VTransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 usedA
LU EPortal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used)
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used :
VA...
Relcache by OID: 262144 total in 6 blocks; 87104 free (12 chunks); 175040 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstancereportinfo_p5
index info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 used: wbxmeetinginstancereportinfo_p5_siteid_idx
CacheMemoryContext: 35269048 total in 1095 blocks; 5938376 free (93 chunks); 29330672 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used
: w b xindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedm: eewtbixnmgeientsitnagnicnesrteapnocretiinnfvoi_tpe5_ep_kpe2y5
_ s iindex info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 usedt: ewbixdm_euetiidn_g_imnesettaincnegrienpsotrtainncfeoi__pi1d_xs6i
te i dindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used_: iwdbxx
m e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedt: iwnbgximnesettainncgeiinnsvtiatnecee_rpe2po5r5t_ipnkfeoy_
p1 _ pindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedk: ewyb
x m epartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usede: twibnxgmienesttianngcienisntvaintceeree_ppo2r5ti_nsfiot_ep1i
d _ uCachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedi: dS_E_LmEeCeTt iCnLOgCiKn_sTtIaMnEcSeTiA_MiPd(x)5
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: S: EwLbExCmTe eCtLiOCnKg_iTnIsMtESaTnAcMePi(n)v
i t e eCachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
_ p 2SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used5
4 _ ppartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usedk: ewyb
xp o sindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedtm: ewebtximnegectoinntgaiinnsetramnacpe_pi2n1v7i
t e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used_: pw2b5x_psoisttemiede_tuiindg_c_omneteatiinnergmianps_tpa2n1c7e_is_iitdexid4_
m e etindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 usedi: nwgbixnmseteatnicnegiidn_skteayn
c e iindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: nwbvxiptoeset_mpe2e5t3i_npgkceoyn
ta i nindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usede: rmwabpx_mpe2e1t7i_npgkienys
...
TopMemoryContext: 1581096 total in 30 blocks; 26160 free (19 chunks); 1554936 used
pgstat TabStatusArray lookup hash table: 131072 total in 5 blocks; 61864 free (12 chunks); 69208 used
TopTransactionContext: 8192 total in 1 blocks; 7352 free (1 chunks); 840 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 29360128 total in 14 blocks; 896536 free (10 chunks); 28463592 used
CachedPlan: 534528 total in 10 blocks; 4328 free (5 chunks); 530200 used: DELETE FROM xxxxxxxx WHERE SITEID=$1 AND MEETINGINSTANCEID =$2
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 524288 total in 7 blocks; 84552 free (24 chunks); 439736 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used:
Relcache by OID: 262144 total in 6 blocks; 116176 free (12 chunks); 145968 used
CacheMemoryContext: 30819864 total in 600 blocks; 6376040 free (128 chunks); 24443824 used
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p255_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p255_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p254_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p254_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p253_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p253_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p252_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p252_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p251_siteid_idx
...
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p210_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p209_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p209_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p208_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p208_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p207_siteid_idx
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmtginstanceparticipantaudio_p207_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticTopMemoryContext: 1520736 total in 25 blocks; 148800 free (193 chunks); 1371936 usedi
pa nTopTransactionContext: 8192 total in 1 blocks; 6824 free (0 chunks); 1368 usedt
a udpgstat TabStatusArray lookup hash table: 65536 total in 4 blocks; 32704 free (11 chunks); 32832 usedi
o_ pPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used2: 0t6r_isgigteeri_df_citd_xc
o m mindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedo: nw_blxmmtt(g)i
n sPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedta: ntcreipgagretri_cficpta_nctoamumdoino__lpm2t0(6)_
pk ePL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedy:
trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 4194304 total in 10 blocks; 1380296 free (16 chunks); 2814008 used
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 262144 total in 6 blocks; 34272 free (20 chunks); 227872 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
...
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Postgresql JDBC process consumes more memory with partition tables update delete
@ 2022-09-08 09:31 Dave Cramer <davecramer@postgres.rocks>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
1 sibling, 0 replies; 19+ messages in thread
From: Dave Cramer @ 2022-09-08 09:31 UTC (permalink / raw)
To: James Pang (chaolpan) <chaolpan@cisco.com>; +Cc: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>; pgsql-jdbc@lists.postgresql.org <pgsql-jdbc@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Postgresql JDBC process consumes more memory with partition tables update delete
@ 2022-09-08 09:55 Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
1 sibling, 1 reply; 19+ messages in thread
From: Vladimir Sitnikov @ 2022-09-08 09:55 UTC (permalink / raw)
To: James Pang (chaolpan) <chaolpan@cisco.com>; +Cc: pgsql-jdbc@lists.postgresql.org <pgsql-jdbc@lists.postgresql.org>
> 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...
)
Vladimir
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: Postgresql JDBC process consumes more memory with partition tables update delete
@ 2022-09-08 11:38 James Pang (chaolpan) <chaolpan@cisco.com>
parent: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: James Pang (chaolpan) @ 2022-09-08 11:38 UTC (permalink / raw)
To: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>; +Cc: pgsql-jdbc@lists.postgresql.org <pgsql-jdbc@lists.postgresql.org>
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...
)
Vladimir
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: Postgresql JDBC process consumes more memory with partition tables update delete
@ 2022-09-08 12:04 James Pang (chaolpan) <chaolpan@cisco.com>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
0 siblings, 1 reply; 19+ messages in thread
From: James Pang (chaolpan) @ 2022-09-08 12:04 UTC (permalink / raw)
To: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>; +Cc: pgsql-jdbc@lists.postgresql.org <pgsql-jdbc@lists.postgresql.org>
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...
)
Vladimir
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Postgresql JDBC process consumes more memory with partition tables update delete
@ 2022-09-08 12:12 Dave Cramer <davecramer@postgres.rocks>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
0 siblings, 0 replies; 19+ messages in thread
From: Dave Cramer @ 2022-09-08 12:12 UTC (permalink / raw)
To: James Pang (chaolpan) <chaolpan@cisco.com>; +Cc: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>; pgsql-jdbc@lists.postgresql.org <pgsql-jdbc@lists.postgresql.org>
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...
> )
>
>
> Vladimir
>
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2022-09-08 12:12 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-09-05 12:40 Postgresql JDBC process consumes more memory than psql client James Pang (chaolpan) <chaolpan@cisco.com>
2022-09-05 12:47 ` Justin Pryzby <pryzby@telsasoft.com>
2022-09-05 12:52 ` James Pang (chaolpan) <chaolpan@cisco.com>
2022-09-05 13:26 ` Justin Pryzby <pryzby@telsasoft.com>
2022-09-06 04:15 ` James Pang (chaolpan) <chaolpan@cisco.com>
2022-09-06 16:15 ` Justin Pryzby <pryzby@telsasoft.com>
2022-09-07 00:05 ` James Pang (chaolpan) <chaolpan@cisco.com>
2022-09-07 00:10 ` RE: Postgresql JDBC process consumes more memory with partition tables update delete James Pang (chaolpan) <chaolpan@cisco.com>
2022-09-08 06:17 ` RE: Postgresql JDBC process consumes more memory with partition tables update delete James Pang (chaolpan) <chaolpan@cisco.com>
2022-09-08 07:05 ` Re: Postgresql JDBC process consumes more memory with partition tables update delete Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
2022-09-08 08:24 ` RE: Postgresql JDBC process consumes more memory with partition tables update delete James Pang (chaolpan) <chaolpan@cisco.com>
2022-09-08 08:30 ` RE: Postgresql JDBC process consumes more memory with partition tables update delete James Pang (chaolpan) <chaolpan@cisco.com>
2022-09-08 08:32 ` RE: Postgresql JDBC process consumes more memory with partition tables update delete James Pang (chaolpan) <chaolpan@cisco.com>
2022-09-08 08:36 ` RE: Postgresql JDBC process consumes more memory with partition tables update delete James Pang (chaolpan) <chaolpan@cisco.com>
2022-09-08 09:31 ` Re: Postgresql JDBC process consumes more memory with partition tables update delete Dave Cramer <davecramer@postgres.rocks>
2022-09-08 09:55 ` Re: Postgresql JDBC process consumes more memory with partition tables update delete Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
2022-09-08 11:38 ` RE: Postgresql JDBC process consumes more memory with partition tables update delete James Pang (chaolpan) <chaolpan@cisco.com>
2022-09-08 12:04 ` RE: Postgresql JDBC process consumes more memory with partition tables update delete James Pang (chaolpan) <chaolpan@cisco.com>
2022-09-08 12:12 ` Re: Postgresql JDBC process consumes more memory with partition tables update delete Dave Cramer <davecramer@postgres.rocks>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox