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 <pry...@telsasoft.com> Sent: Wednesday, September 7, 2022 12:15 AM To: James Pang (chaolpan) <chaol...@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
