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
