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.janes@xxxxxxxxx> wrote:
On Mon, Apr 8, 2024 at 2:50 AM Rajesh Kumar <rajeshkumar.dba09@xxxxxxxxx> 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
