Re: Suggestion for memory parameters

On Mon, Sep 30, 2024 at 8:46 PM Philip Semanchuk <
philip(at)americanefficient(dot)com> wrote:

>
>
> > On Sep 26, 2024, at 7:03 AM, yudhi s <learnerdatabase99(at)gmail(dot)com>
> wrote:
> >
> > In a RDS postgres ...
>
> > Is it fine to let it use "FreeLocalStorage" unless it goes till zero?
>
> Hi Yudhi,
> FreeLocalStorage and some of the other things you ask about are specific
> to AWS RDS, so you might have better luck getting answers on an
> RDS-specific mailing list. We also use RDS-hosted Postgres and so I
> completely understand how Postgres and RDS are intertwined.
>
> We have had runaway queries exhaust FreeLocalStorage. It has been quite a
> while since that happened, so my memories are hazy, but I’m pretty sure
> that when we used all of FreeLocalStorage, the result was that Postgres
> restarted. It might be equivalent to using all memory and disk space on a
> standalone system. Once there’s no storage left, behavior is unpredictable
> but we can’t be surprised if things crash. Usually our runaway queries got
> killed before FreeLocalStorage filled up, but not always.
>
> I second Veem’s suggestion to set work_mem on a per-session basis. Also
> note that the doc for work_mem says, “the total memory used could be many
> times the value of work_mem; it is necessary to keep this fact in mind when
> choosing the value."
>
>
> https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM
>
>
Thank you.

When I execute the query with explain (analyze, buffers),I see the section
below in the plan having "sort method" information in three places
each showing ~75MB size, which if combined is coming <250MB. So , does that
mean it's enough to set the work_mem as ~250MB for these queries before
they start?

But yes somehow this query is finished in a few seconds when i execute
using explain(analyze,buffers) while if i run it without using explain it
runs for ~10minutes+. My expectation was that doing (explain analyze)
should actually execute the query fully. Is my understanding correct here
and if the disk spilling stats which I am seeing is accurate enough to go
with?

Limit (cost=557514.75..592517.20 rows=300000 width=1430) (actual
time=2269.939..2541.527 rows=300000 loops=1)
Buffers: shared hit=886206, temp read=38263 written=56947
I/O Timings: temp read=70.040 write=660.073
-> Gather Merge (cost=557514.75..643393.02 rows=736048 width=1430)
(actual time=2269.938..2513.748 rows=300000 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=886206, temp read=38263 written=56947
I/O Timings: temp read=70.040 write=660.073
-> Sort (cost=556514.73..557434.79 rows=368024 width=1430)
(actual time=2227.392..2279.389 rows=100135 loops=3)
Sort Key: column1, column2
Sort Method: external merge Disk: *77352kB*
Buffers: shared hit=886206, temp read=38263 written=56947
I/O Timings: temp read=70.040 write=660.073
Worker 0: Sort Method: external merge Disk: *75592kB*
Worker 1: Sort Method: external merge Disk: *74440kB*
-> Parallel Append (cost=0.00..64614.94 rows=368024
width=1430) (actual time=0.406..570.105 rows=299204 loops=3)