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 1lSJ5J-0004Mx-FA for pgsql-admin@arkaria.postgresql.org; Fri, 02 Apr 2021 12:39:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lSJ5I-0004TB-CN for pgsql-admin@arkaria.postgresql.org; Fri, 02 Apr 2021 12:39:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lSJ5I-0004Q1-11 for pgsql-admin@lists.postgresql.org; Fri, 02 Apr 2021 12:39:32 +0000 Received: from mout.perfora.net ([74.208.4.197]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lSJ5F-0004Wb-4f for pgsql-admin@lists.postgresql.org; Fri, 02 Apr 2021 12:39:31 +0000 Received: from [192.168.1.165] ([98.169.214.254]) by mrelay.perfora.net (mreueus003 [74.208.5.2]) with ESMTPSA (Nemesis) id 0Lhgpj-1lwzBj1l8D-00mpXd; Fri, 02 Apr 2021 14:39:23 +0200 Subject: Re: work_mem To: SASIKUMAR Devaraj Cc: holger@jakobs.com, pgsql-admin@lists.postgresql.org References: <04D84259-1B75-4648-949F-78677916194E@jakobs.com> <66290583.2329958.1617367063840@mail.yahoo.com> From: MichaelDBA Message-ID: Date: Fri, 2 Apr 2021 08:39:18 -0400 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 PostboxApp/7.0.47 MIME-Version: 1.0 In-Reply-To: <66290583.2329958.1617367063840@mail.yahoo.com> Content-Type: multipart/alternative; boundary="------------DB3ABC610126A1BA4E6ADBA9" Content-Language: en-US X-Provags-ID: V03:K1:qPtE+4QQLlLD5drVnQmN0REHvCyjLR7c3V18TN34T9bPA5YDHye B05Sjdx/pXEOEIGrszAyXQluOVa8/4kzJPCM7Fa3ADLlECvlFKeCj8/nXAoxV1QyH7tc7vR lmNcRIySi3qESNlPPH5nUGYMssLvDbnEm/hrE5VWXLQtqBvzFhpKBbgX/md4v60Tb0qUtKi y/pV6XBRKGlQ9QufXyAaQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:+E2jt+WHZxg=:G5IAfTepJjYTtvb5ab2tP6 iHsP9c+KabroB5ZwpFYtMZIllW7dYXX4DRIjSDpPPHXrqrkeWlyKOXkyAbIhcpYvWSXG4B5T4 rSJxv7vQKR5xBmhAaRbPi52xcUpbhHazU58VyYKHKygwLwHpyQWZFCnlIbths2oH+muDwrgE6 YPeqXdkO3NmEmJwZxfAHUbNdvm/smNVo1TQpRUhuiVhUgq57daLS4esPGuBx23BO19Jxph7XS 2oWR7PJ1N2bm2Euhkw+A0Q0g2s4AnVJuZ74AjvMauGgowEuqzyP6oC1IPCadCOKY7jih0YUBl fflwcX8xEmGi7kgAHSJ4mhiPEjsftN/1dJ/5Nt+KhMyYbdr9s+i8+kyzzcRJrDc4Z7FczcCJp wTWD/H8yJ/ZTojol765OflvEWi8lXf7kQKbjmdQ4uHCDs96v0fU8hUMYo3EQTM4N4aSfQEdE8 EMqvQe1pUKIhgQ5ThTDxVgILxCemg7i0HvRauz0cqLTn68N6p9Zq List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------DB3ABC610126A1BA4E6ADBA9 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Memory is allocated dynamically per internal work_mem buffer requests. SASIKUMAR Devaraj wrote on 4/2/2021 8:37 AM: > Hi All > > As soon as client session is established work_mem will be allocated or > only when sort happens for that particular session? Please advise how > the internal behavior? This will help me to configure my database > memory with high connections > > Regards > > Sasi > > Sent from Yahoo Mail on Android > > > On Wed, Mar 31, 2021 at 8:52 PM, Holger Jakobs > wrote: > You can show any or all settings with the command SHOW. > > show work_mem; > show all; > > How many times the amount of work_mem has been allocated currently > cannot be shown this way. Any sort operation running currently > allocates it. So it is not identical with the number of current > queries, because queries can allocate it several times. > > > Am 31. März 2021 16:04:07 MESZ schrieb "Campbell, Lance" > : > > PostgreSQL 12 > > Is there a query that will tell us at any given time what > amount of PostgreSQL memory is being used for work_mem? > > Thanks, > > *LANCE CAMPBELL * > > Web Services > > Public Affairs > > /var/folders/wp/1f6l7hw95y718z976kgnl5f9kr5rtc/T/com.microsoft.Outlook/WebArchiveCopyPasteTempFiles/signature_logo.png > > > /Under the Illinois Freedom of Information Act any written > communication to or from university employees regarding > university business is a public record and may be subject to > public disclosure./ > > > -- > Holger Jakobs, Bergisch Gladbach > +49 178 9759012 > - sent from mobile, therefore short - > --------------DB3ABC610126A1BA4E6ADBA9 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Memory is allocated dynamically per internal work_mem buffer requests.

SASIKUMAR Devaraj wrote on 4/2/2021 8:37 AM:
Hi All

As soon as client session is established work_mem will be allocated or only when sort happens for that particular session? Please advise how the internal behavior? This will help me to configure my database memory with high connections

Regards

Sasi


On Wed, Mar 31, 2021 at 8:52 PM, Holger Jakobs
You can show any or all settings with the command SHOW.

show work_mem;
show all;

How many times the amount of work_mem has been allocated currently cannot be shown this way. Any sort operation running currently allocates it. So it is not identical with the number of current queries, because queries can allocate it several times.


Am 31. März 2021 16:04:07 MESZ schrieb "Campbell, Lance" <lance@illinois.edu>:

PostgreSQL 12

 

Is there a query that will tell us at any given time what amount of PostgreSQL memory is being used for work_mem?  

 

Thanks,

 

LANCE CAMPBELL

 

Web Services

Public Affairs

 

 

/var/folders/wp/1f6l7hw95y718z976kgnl5f9kr5rtc/T/com.microsoft.Outlook/WebArchiveCopyPasteTempFiles/signature_logo.png

 

Under the Illinois Freedom of Information Act any written communication to or from university employees regarding university business is a public record and may be subject to public disclosure.

 


--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -

--------------DB3ABC610126A1BA4E6ADBA9--