public inbox for [email protected]
help / color / mirror / Atom feedRe: Suggestion for memory parameters
2+ messages / 2 participants
[nested] [flat]
* Re: Suggestion for memory parameters
@ 2024-09-27 03:41 veem v <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: veem v @ 2024-09-27 03:41 UTC (permalink / raw)
To: yudhi s <[email protected]>; +Cc: pgsql-general <[email protected]>
On Thu, 26 Sept 2024 at 16:33, yudhi s <[email protected]> wrote:
> Hello All,
>
> In a RDS postgres we are seeing some select queries when running and doing
> sorting on 50 million rows(as its having order by clause in it) , the
> significant portion of wait event is showing as "IO:BufFileWrite" and it
> runs for ~20minutes+.
>
> Going through the document in the link below, it states we should monitor
> the "FreeLocalStorage" metric and when monitoring that, I see it showing up
> to ~535GB as the max limit and when these queries run this goes down till
> 100GB. Note- (it's a R7g8xl instance)
>
>
> https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.iobuffile.html
>
> We were thinking of bumping up the work_mem to a higher value in database
> level , which is currently having size 4MB default. But we will also have
> ~100 sessions running at same time and majority were from other
> applications which execute other single row "insert" queries and I hope
> that will not need high "work_mem" . And setting it at database level will
> consume 100 times that set work_mem value. So how to handle this situation?
> Or
> Is it fine to let it use "FreeLocalStorage" unless it goes till zero?
>
> Also I am confused between the local storage (which is showing as 535GB)
> vs the memory/RAM which is 256GB for this instance class with ~128TB max
> storage space restriction, how are these storage different, (mainly the
> 535GB space which it's showing vs the 128TB storage space restriction)?
> Appreciate your guidance.
>
> select query looks something as below with no Joins but just single table
> fetch:-
>
> Select....
> from <table_name>
> where
> order by column1, column2 LIMIT $b1 OFFSET $B2 ;
>
>
>
My 2 cents
I think you should set the work_mem on specific session level , if your
sorting queries are only from specific handful of sessions, as because
setting it up at database level will eat up your most of RAM(which you said
is 256GB) and you said 100+ sessions getting spawned at any point in time.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Suggestion for memory parameters
@ 2024-09-27 06:36 yudhi s <[email protected]>
parent: veem v <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: yudhi s @ 2024-09-27 06:36 UTC (permalink / raw)
To: veem v <[email protected]>; +Cc: pgsql-general <[email protected]>
On Fri, Sep 27, 2024 at 9:11 AM veem v <[email protected]> wrote:
>
> On Thu, 26 Sept 2024 at 16:33, yudhi s <[email protected]>
> wrote:
>
>> Hello All,
>>
>> In a RDS postgres we are seeing some select queries when running and
>> doing sorting on 50 million rows(as its having order by clause in it) , the
>> significant portion of wait event is showing as "IO:BufFileWrite" and it
>> runs for ~20minutes+.
>>
>> Going through the document in the link below, it states we should monitor
>> the "FreeLocalStorage" metric and when monitoring that, I see it showing up
>> to ~535GB as the max limit and when these queries run this goes down till
>> 100GB. Note- (it's a R7g8xl instance)
>>
>>
>> https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.iobuffile.html
>>
>> We were thinking of bumping up the work_mem to a higher value in database
>> level , which is currently having size 4MB default. But we will also have
>> ~100 sessions running at same time and majority were from other
>> applications which execute other single row "insert" queries and I hope
>> that will not need high "work_mem" . And setting it at database level will
>> consume 100 times that set work_mem value. So how to handle this situation?
>> Or
>> Is it fine to let it use "FreeLocalStorage" unless it goes till zero?
>>
>> Also I am confused between the local storage (which is showing as 535GB)
>> vs the memory/RAM which is 256GB for this instance class with ~128TB max
>> storage space restriction, how are these storage different, (mainly the
>> 535GB space which it's showing vs the 128TB storage space restriction)?
>> Appreciate your guidance.
>>
>> select query looks something as below with no Joins but just single table
>> fetch:-
>>
>> Select....
>> from <table_name>
>> where
>> order by column1, column2 LIMIT $b1 OFFSET $B2 ;
>>
>>
>>
> My 2 cents
> I think you should set the work_mem on specific session level , if your
> sorting queries are only from specific handful of sessions, as because
> setting it up at database level will eat up your most of RAM(which you said
> is 256GB) and you said 100+ sessions getting spawned at any point in time.
>
Thank you.
When I checked pg_stat_statements for this query , and divided the
temp_blk_read+temp_blk_written with the "calls", it came as ~1million which
means ~7GB. So does that mean ~7GB of work_mem should be allocated for this
query?
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-09-27 06:36 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-27 03:41 Re: Suggestion for memory parameters veem v <[email protected]>
2024-09-27 06:36 ` yudhi s <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox