Обсуждение: [MASSMAIL]Work_mem
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.
Can I resolve this by increasing overall work_mem (set to 25MB now)?
Or by setting work_mem to users consuming those 3 dbs?
If I may want to increase how to decide on how much to increase?
On Mon, 2024-04-08 at 12:20 +0530, Rajesh Kumar wrote: > 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. > > Can I resolve this by increasing overall work_mem (set to 25MB now)? > > Or by setting work_mem to users consuming those 3 dbs? Both would help. > If I may want to increase how to decide on how much to increase? You increase it ontil the performance is good, and frequently running queries no longer need to create temporary files. But you don't increase too much, otherwise you will run out of memory. This is a question of trial and error, and it impossible to give you exact numbers (even if we knew something about your system, which we don't). Yours, Laurenz Albe
On Mon, Apr 8, 2024 at 2:50 AM Rajesh Kumar <rajeshkumar.dba09@gmail.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
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@gmail.com> wrote:
On Mon, Apr 8, 2024 at 2:50 AM Rajesh Kumar <rajeshkumar.dba09@gmail.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
On Mon, Apr 8, 2024 at 1:57 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
I dont see any long running queries under pg_stat_activity or pg_stat_statements.
With pg_stat_activity, you would need to catch them "in the act", but there are no columns there which describe temp file usage anyway.
pg_stat_statements has the columns "temp_blks_read" and "temp_blks_written", in all supported versions, so you should be able to spot the queries using temp files there. Unless maybe your pg_stat_statements.max setting is too low and those queries are forced out.
Or you could set log_temp_files = 0. That would log all statements using temp files into the PostgreSQL server log.
Cheers,
Jeff
Thanks, I'll look into it.
On Thu, 11 Apr 2024, 03:51 Jeff Janes, <jeff.janes@gmail.com> wrote:
On Mon, Apr 8, 2024 at 1:57 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:I dont see any long running queries under pg_stat_activity or pg_stat_statements.With pg_stat_activity, you would need to catch them "in the act", but there are no columns there which describe temp file usage anyway.pg_stat_statements has the columns "temp_blks_read" and "temp_blks_written", in all supported versions, so you should be able to spot the queries using temp files there. Unless maybe your pg_stat_statements.max setting is too low and those queries are forced out.Or you could set log_temp_files = 0. That would log all statements using temp files into the PostgreSQL server log.Cheers,Jeff