On Tue, Sep 06, 2022 at 04:15:03AM +0000, James Pang (chaolpan) wrote:
> We make 2 comparisions between partitioned(256 HASH) and no-partitioned(same data volume,same table attributes) , do
same "UPDATE,DELETE " .
> 1. with partitioned tables , the "RES" from top command memory increased quickly to 160MB and keep stable there.
> From auto_explain trace, we did saw partition pruning to specific partition when execution the prepared sql
statementby Postgresql JDBC .
> 2. with no-partitioned tables, the "RES" from top command memory only keep 24MB stable there.
> Same auto_explain , and only table and index scan there by prepared sql statement by Postgresql JDBC.
> 3. with psql client , run the UPDATE/DELETE sql locally, partition pruning works and the "RES" memory" is much less,
it'sabout 9MB .
>
> Yesterday, when workload test, a lot of Postgresql JDBC connections use 150-160MB memory , so we got ERROR: out of
memory
How many JDBC clients were there?
Did you use the same number of clients when you used psql ?
Otherwise it wasn't a fair test.
Also, did you try using psql with PREPARE+EXECUTE ? I imagine memory
use would match JDBC.
It's probably not important, but if you set the log level high enough,
you could log memory use more accurately using log_executor_stats
(maxrss).
> So, looks like something with Postgresql JDBC driver lead to the high memory consumption when table is partitioned ,
evenwhen table is no partitioned , compared with psql client, it consumes more memory. Any suggestions to tune that ?
PG V13 , OS RHEL8 , Virtua machine on VMWARE. We make shared_buffers=36% physical memory ,
effective_cache_size=70%physicalmemory , total physical memory is about 128GB.
I sent this before hoping to get answers to all the most common
questions earlier, rather than being spread out over the first handful
of emails.
https://wiki.postgresql.org/wiki/Slow_Query_Questions
version 13 point what ?
what are the other non-default gucs ?
what are the query plans ?
--
Justin
