Re: Work_mem

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

>