Re: Work_mem

I dont see any long running queries under pg_stat_activity or
pg_stat_statements.

On Mon, 8 Apr 2024 at 22:57, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Mon, Apr 8, 2024 at 2:50 AM Rajesh Kumar <rajeshkumar(dot)dba09(at)gmail(dot)com>
> wrote:
>
>> Hi team,
>>
>>
>> In a cluster, I have around 15dbs..out of which in 3 dbs creates of
>> temp_files which means I belive work_mem allocation is not sufficient for
>> those dbs.
>>
>
> That is a questionable conclusion. Using temp files is not a disaster.
> It is not using them which is more likely to be a disaster, when too large
> a setting of work_mem leads to swapping/paging. When the system switches to
> using temp files, it also switches to using algorithms which are well
> suited to them.
>
> Indeed on modern systems and modern versions of PostgreSQL, switching to
> temp files can often be faster than using very large work_mem even when the
> large work_mem doesn't lead to swapping/paging. I think this is
> because main RAM is slow compared to the various forms of CPU cache, and
> the temp-file algorithms are often far friendlier to CPU cache than the
> random-access algorithms are. And on modern systems, the temp files likely
> never even reach disk; they are just transferred to and from main RAM, and
> in cache friendly ways if your cache supports some kind of read-ahead.
> Although these improvements are not easy to predict and so not easy to tune
> for.
>
> You should figure out which queries cause those temp files, and if those
> queries are timing sensitive then test those queries with various settings
> of work_mem. Keep in mind that you don't want to set it so high that you
> exhaust memory, so you must keep in mind how many instances of work_mem
> might be in use, system wide, at the same time.
>
> Can I resolve this by increasing overall work_mem (set to 25MB now)?
>>
>
> That is like asking "how long is a piece of string?". Identify the
> queries that cause the "problem".
>
> Cheers,
>
> Jeff
>
>>