From: Justin Pryzby <pryzby@telsasoft.com>
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql JDBC process consumes more memory than psql client
Date: Tue, 6 Sep 2022 11:15:12 -0500
Message-ID: <20220906161512.GQ31833@telsasoft.com> (raw)
In-Reply-To: <PH0PR11MB51919678C0C5449A95EE0452D67E9@PH0PR11MB5191.namprd11.prod.outlook.com>
References: <PH0PR11MB5191A047B707B2FE6527C549D67F9@PH0PR11MB5191.namprd11.prod.outlook.com>
<20220905124722.GI31833@telsasoft.com>
<PH0PR11MB5191C2C75F91CE1237267479D67F9@PH0PR11MB5191.namprd11.prod.outlook.com>
<PH0PR11MB51919678C0C5449A95EE0452D67E9@PH0PR11MB5191.namprd11.prod.outlook.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, chaolpan@cisco.com, pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql JDBC process consumes more memory than psql client
In-Reply-To: <20220906161512.GQ31833@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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