Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lSYlY-00035h-Vt for pgsql-admin@arkaria.postgresql.org; Sat, 03 Apr 2021 05:24:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lSYlX-0008K8-Jz for pgsql-admin@arkaria.postgresql.org; Sat, 03 Apr 2021 05:24:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lSYlW-0008K1-Vl for pgsql-admin@lists.postgresql.org; Sat, 03 Apr 2021 05:24:11 +0000 Received: from sonic304-19.consmr.mail.sg3.yahoo.com ([106.10.242.209]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lSYlP-0000fX-Af for pgsql-admin@lists.postgresql.org; Sat, 03 Apr 2021 05:24:10 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1617427438; bh=Solj16cKiLTeuz+JcHH2peb6UAgaSvf6dkcT/+V2VlI=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=YskNxfXiBspEJdILFSWtxmxS90hIoOhBrQWmf4mNS1a5zJlQuPvtCU0xj5EoVt8arCk6UntBPKAwCVhdSpf0vqfQacXoczVrS55JScLNmpNxMyjwXV0EFom8B3+IsTX/IWGGAENBIeRAChuCV5J10YP8Nc82QEAjGWS1/pEhBsXF9H7NIlNfmhRFgdgN13aXehami8kHYA50FlX0t7qo9kU2FiVipsXRoezGnvZzHa2OGf1ASVniFP1smSq06ZgUcbsRcp541vl4Y1gTlu/RnkowNZ50Bst+Xl3Aa75nYy1txl8sZoCw61+863EdlpJpBn+CEh6tyHAjEAWnxWew6g== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1617427438; bh=Si2D6Qk6L73/hU614h0sCz7uIzsGZJsPJ91Tdn8JfTe=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=rl3Orxc6BjK2jHISORtUjhe2PINI8zWZfWHnwSy9Trv9nLoAAC6ZQ+X9NOpW1EpVxMhoPHlLPFrHxkDhjNz9NghlmWgSJRnMPSEjtlTgwLpgPTgHUtecoLnwCO3KPcjIZuGamQmKspkKXdSFagAw56vKrJXGjjW1yejo9aovUEyYoTP9sik4HtbXl9iO0pZI0v//zpqKL4kM95FTKbyv5lJEJqR0QjTYLkkNLWYK+H8L8ThQu87Y6UIgtkXqlrv5eGYk9SceaoYswVUIMydtSBD0yLxNI1/2ncDLOsyo2Ms9Gt7/xXZwhZh6QDvTYAz1adsZKSi77Oidv+W38NAjuA== X-YMail-OSG: 02PGRYgVM1lmYndp3ynojpB6y_LRoMOExneFepDZJS3HHMcXY5Bn7HzorA2TIPw WGgC8EOEC4c7OGnNFtGW3YT.k3kOUqixs4PvRG_2DDYjr2emBrpDfNG0AmS9Og6KHuElCwxbkMIm HeWX50WcBwukCQVOAIQLCemvmC0XNHWDjwCk27hHkdHROO54uLvhE4PLPdzFPm0o.knL_3FwVXwf f5fR1ky9WzvBoC2jRvG8mvp457B6GrV1n1YQ.l2UPtKtf31Oukf8O9p1Z_kUCoDVRgOVGCjcBQSH H7y3Pqrn6VL4YBuGak.r.MjMbq9B.QvQabscn7EJLgzUSbm.7vioNl82TTRK1iJNlXByED7FJGGx i2I4b.WZijO2IAeoXaekahrLcaQdrGkyK2O4QBGafonCGd01mcLdivvHOQE6DOrsr9BIgBHlshWJ uG9gy8jNBMlkD2XewPJAgJqyYr3mL1Qgez7DQlrrTiqgWx2NqvI6SqOaeXkj7piNOmCjULKUlDSN tVzAmtFLLf5vG77rhi1h5l3OcEHI.qGvonqYN9uKqUv.8UIb1L3kgnI45_9eq0lQVphoypm5tbPh YllbCCKIr76f_LdXDmennHZ3QLg1GBpeJCDbZxrWw2_jgvbn80vOtAwsxOKN_uj_8WHd7fDYlcKR B0usmw8nZLnthXzKS3ez.mNdYE6rXe._6oKoXMT9q_1KkDdgpPaa7YOtyY39zeRszF.Aq4c65bKx QtYH2OlUWd.7aeRPsCSQj5OQVO08tLjK44uCl2uLBu6SWvQpw_EuoUL4l05F.ybr.TS_I93kzFe4 Cagngz7LrYO4CXs531TUX0Ps8B6hQtJ7Pdu2HFhZHooF9CYTLIBlvdr6RNdCr2DAdWGIjkt1Kab7 reKZXE9d4T5DnOCQAGW8tR9qZW7o89wf7ZwUEQABKM3HKwrPz7jR7iR7bm.yd6dIDYJT8YwegS5P kHjOPeicGXYno2cwv2cT_zLsmHE5QJole6kjEjSCjHH2l1zgY6Ip.lTU99PlFrsoBx.8X5RPe5pG eOtTPxrxZJzjMw2yJkWMCB.1HlwsPFxDyiFz5nuFtUaK6Zmo0Bf3NDSZLiWiYWNIxaOX7Uaf1.NZ WuLYm5VtlYYYvVtA_XwkmnVYfzHBXbYfkXfEb1Eb50PU9ACy2a_kLgrGijv08Yd_Or3CxuMN1s6y RhFFrhtPd4Tt1s6xLx54oBfcRdVpPmj9ensd5Ol16c87VH26cHd1hI1u0mAFsOpHCoF_tFZkG2xl cKicHZ3OrmhFJ9Bb8NOL3yvgfnr_LFgVNDNm5JDo7BtUqNtYhOic1uoDxRi.sF9xHepn9X2pWDZN jJaNTRxAD88rSWbdpeZqkNE6zLyrmKk58eBHnEP_p3HRR6W_yL7c.Q6TCZnkCHdCzeunex3MX9P9 uc48fuA0DCHLHGDzqqV1Dec89dBu1O8ipZf03dF27bdO3WG8KlfNIws4hPZfko_kwjq.SxWOeno0 AGoN53iOiMFJhpYYxa8vgXxWwti6j9xNRHFWj1SXVpGZ0_njP17oy6xOaeEdU1HER_XtCiOmG7tn oFIkoQ1NpDyS8G5JNobQRl6CdSIgvyMgTs3JA2I9mWXZTMQvwp1_Wc1JY.PeW41fEH047IoE8mhs kmRFnZwpKRsKIFkG3n_5tlOAG3DLcPz3WOkg1o2g9l.Putlm4Gj3TFP36Os4RdSQGN0lgQ.NsTw5 KUcL7cbJoAp.Bp.Cng1eyfxvQxL_kfAevrIjltgZzq7CCGEbO6y22NCgA8__QqqVwxtmFWeFFNxX uowoA78npheptFuc_yNUnDZ0IkMFGSwSH1H9oHl09t0N1.tNQcYGlLwlnimtMt9JdtwXwzhcZlJV JI7GJn0xTaBlupjtZ3gnkzcV_H37Q3e62awLLfzpfeecBS1vcT4mi6zVM17tQv1afLTMFF9QG5uH dtskwWm_4XliFpqaPN1XHtheRWpvvWgGnzSZ18lj9YACGr2oTOakrCxQ1BGLahlwkSHhaSuTOsmH jW30F5vjQaaOqLElXHUErm33c9EIsNhcFQQEgU8pcoihSODjV54D8an1XQaV25rMc0CWoKW3AYw4 HsBhnU_1atMsFFSO5Nb._c_jyu599C7PREbM1ddo76Insa61eyV0QkaTIN62dfiZG30dmaWBceLH x8e4w6bXwePDRapY24ar3u_hwnMtukw2qaZeMAejVd0NNsgacihe2aExY2tSiWtZGExP_kBvA3v. H8Bd7X3RgkLMCgteiAz_kekChOoEnkJALooyilWuwOijy6yJspxVHzN0hsjgQsYN_ZZt6k6QUThF NAY.7nRabvPH4dVSUSjU_pCB0Btf6VUlhr3yAPIMmcVxVsdERcpKdYingIvLTqS_DiQTYUTsgGK4 0bMEdbOoDEBUJm53b1vmOFMFUkQbQLlDxRr0gxBKu17qiwWa77oN5tmQKCqU9gSMHvb2R3Y30UF4 3DV8ZbuOn62Fxqobb0gIvci_VET4lcFLrD8t7gMXKBnKMwU0XjNiNbjp.ypCLC.YDjC8ZpA3pk.s sqXc3L6aRNDf0t8g06Qcwjxh5iMhSavh_UQ7Kpz1sUhp9vKwQrbZODS7jgPI9eGgHpvIRXcqcHmR 8UnKDcJgktT_LwXCHV9BZgik- X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic304.consmr.mail.sg3.yahoo.com with HTTP; Sat, 3 Apr 2021 05:23:58 +0000 Date: Sat, 3 Apr 2021 05:23:56 +0000 (UTC) From: SASIKUMAR Devaraj To: "Campbell, Lance" , Bruce Momjian Cc: Laurenz Albe , MichaelDBA , "holger@jakobs.com" , "pgsql-admin@lists.postgresql.org" Message-ID: <230367927.2421710.1617427436762@mail.yahoo.com> In-Reply-To: <20210402153436.GE9270@momjian.us> References: <04D84259-1B75-4648-949F-78677916194E@jakobs.com> <66290583.2329958.1617367063840@mail.yahoo.com> <1654783699.2333997.1617367552583@mail.yahoo.com> <592e5895-8dbd-1a4f-a87c-af72ac8eea02@sqlexec.com> <339e1ee870881a3efaffc7ea4cddac6bc4d009cf.camel@cybertec.at> <20210402150736.GD9270@momjian.us> <20210402153436.GE9270@momjian.us> Subject: Re: work_mem MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2421709_1562311596.1617427436760" X-Mailer: WebService/1.1.17936 YMailNorrin Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.75 Safari/537.36 Content-Length: 7208 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_2421709_1562311596.1617427436760 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable =20 Hi All One basic doubt.=C2=A0 Whether work_mem is allocated for each clients connected to Database or for= against the Sort Operations(Queries) in database? Please clarify Case 1:=C2=A0work_mem=3D4mTotal Sessions connected=3D100Total=3D400M=C2=A0 Or Case 2work_mem=3D4mTotal Sort operations at DB now=3D50Total work_mem=3D= 200M Please clarify at high level whether Case 1 is true or Case 2 is true? Regards Sasi On Friday, 2 April, 2021, 09:04:56 pm IST, Bruce Momjian wrote: =20 =20 On Fri, Apr=C2=A0 2, 2021 at 03:25:04PM +0000, Campbell, Lance wrote: > Thanks for sharing this thread.=C2=A0 My suggestion of having a work_mem_= stack_size > is the same concept mentioned in this thread regarding having a work_mem_= pool.=20 > I prefer this later term rather than the one I was using.=C2=A0 When the = work mem > pool is exhausted PostgreSQL just uses temp files for work_mem.=C2=A0 Wit= h current > statics for temp files and with a new stats on a work mem pool usage a us= er > could fine tune memory much more precisely.=C2=A0 It would leave the =E2= =80=9Cart of memory > tuning=E2=80=9D behind.=C2=A0 The other added benefit is that people woul= d have a better > understanding of how work_mem is used by naturally having to explain what= a > work_mem_pool is and when it is drawn on.=C2=A0 There are probably a lot = of > PostgreSQL instance that would run faster just by having the confidence t= o > increase the size of work_mem.=C2=A0 I am sure many instances have this v= alue set to > low. Uh, did you read the blog before it, referenced in that blog entry: =C2=A0=C2=A0=C2=A0 https://momjian.us/main/blogs/pgblog/2018.html#December_= 7_2018 Even if we have a pool, it is still complex to configure memory, but it might help. --=20 =C2=A0 Bruce Momjian=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ht= tps://momjian.us =C2=A0 EDB=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 https= ://enterprisedb.com =C2=A0 If only the physical world exists, free will is an illusion. =20 ------=_Part_2421709_1562311596.1617427436760 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Hi All
<= div dir=3D"ltr" data-setdir=3D"false">
One basic doubt. 

Whether work_mem is al= located for each clients connected to Database or for against the Sort Oper= ations(Queries) in database? Please clarify

Case 1: =
work_mem=3D4m
Total Sessions connected=3D100
Total=3D400M 

Or Case 2<= /div>
work_mem=3D4m
Total Sort operations at DB now=3D50
Total work_mem=3D200M

Pl= ease clarify at high level whether Case 1 is true or Case 2 is true?
<= div dir=3D"ltr" data-setdir=3D"false">

Regards
<= div dir=3D"ltr" data-setdir=3D"false">
Sasi
=20
=20
On Friday, 2 April, 2021, 09:04:56 pm IST, Bruce Momjia= n <bruce@momjian.us> wrote:


On Fri, Apr  2, 2021 at 03:25:04PM +0000, Campbel= l, Lance wrote:
> Thanks for sharing this thread. = ; My suggestion of having a work_mem_stack_size
> is t= he same concept mentioned in this thread regarding having a work_mem_pool. =
> I prefer this later term rather than the one I was = using.  When the work mem
> pool is exhausted Pos= tgreSQL just uses temp files for work_mem.  With current
> statics for temp files and with a new stats on a work mem pool us= age a user
> could fine tune memory much more precisel= y.  It would leave the =E2=80=9Cart of memory
> t= uning=E2=80=9D behind.  The other added benefit is that people would h= ave a better
> understanding of how work_mem is used b= y naturally having to explain what a
> work_mem_pool i= s and when it is drawn on.  There are probably a lot of
> PostgreSQL instance that would run faster just by having the confi= dence to
> increase the size of work_mem.  I am s= ure many instances have this value set to
> low.

Uh, did you read the blog before it, refere= nced in that blog entry:

  &= nbsp; https://momjian.us/main/blogs/pgblog= /2018.html#December_7_2018

Even if= we have a pool, it is still complex to configure memory, but it
might help.


--
  Bruce Momjian&n= bsp; <bruce@momjian.us>        https://momji= an.us
  EDB          &n= bsp;                     =       https://enterprisedb.com

  If only the physical world exists, free will is an illusion.<= br clear=3D"none">


=
------=_Part_2421709_1562311596.1617427436760--