From carlosreimer@yahoo.com.br Mon Aug  29 16:23:27 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 9BDB2D77ED
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 29 Aug 2005 13:23:27 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 25009-08
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 29 Aug 2005 16:23:23 +0000 (GMT)
Received: from web53211.mail.yahoo.com (web53211.mail.yahoo.com
	[206.190.49.81])
	by svr1.postgresql.org (Postfix) with SMTP id 437D4D6F5F
	for <pgsql-performance@postgresql.org>;
	Mon, 29 Aug 2005 13:23:21 -0300 (ADT)
Received: (qmail 9578 invoked by uid 60001); 29 Aug 2005 16:23:20 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.br;
	h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=YcxVTyik85IbDygyRR7Gk8lqBcJA+DY9kbbIgpkykgzdqVJnXq/k4JOPyhNFgyXWRIYggUTk8sOtWw/bTTlKA/rA0tG4E7ATBXfY/z+zByuRX5sO3KLBwXfTBLMnm1+EymDMQHN7kn0fyktNB+8ddgwpOHlxRbZM9Ig/JGARaRo=
	;
Message-ID: <20050829162320.9576.qmail@web53211.mail.yahoo.com>
Received: from [200.138.208.43] by web53211.mail.yahoo.com via HTTP;
	Mon, 29 Aug 2005 16:23:20 GMT
Date: Mon, 29 Aug 2005 16:23:20 +0000 (GMT)
From: Carlos Henrique Reimer <carlosreimer@yahoo.com.br>
Subject: shared buffers
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1569728133-1125332600=:9424"
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.47 required=5 tests=[DNS_FROM_RFC_ABUSE=0.374,
	HTML_50_60=0.095, HTML_MESSAGE=0.001]
X-Spam-Level: 
X-Archive-Number: 200508/493
X-Sequence-Number: 14240

--0-1569728133-1125332600=:9424
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hi,
 
I´ve configured postgresql to use 1GB of shared buffers but meminfo and "top" are indicanting 0 shared buffers page. Why?
 
It´s a Linux Redhat 9 box with 4GB RAM and postgresql 7.3.
 
Thanks in advance!
 
Reimer

		
---------------------------------
Yahoo! Acesso Grátis: Internet rápida e grátis. Instale o discador agora!
--0-1569728133-1125332600=:9424
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

<DIV>Hi,</DIV>
<DIV>&nbsp;</DIV>
<DIV>I´ve configured postgresql to use 1GB of shared buffers but meminfo and "top" are indicanting 0 shared buffers page. Why?</DIV>
<DIV>&nbsp;</DIV>
<DIV>It´s a Linux Redhat 9 box with 4GB RAM and postgresql 7.3.</DIV>
<DIV>&nbsp;</DIV>
<DIV>Thanks in advance!</DIV>
<DIV>&nbsp;</DIV>
<DIV>Reimer</DIV><p>
		<hr size=1><a href=" http://us.rd.yahoo.com/mail/br/taglines/*http://br.acesso.yahoo.com/">Yahoo! Acesso Grátis</a>: Internet rápida e grátis. <a href=" http://us.rd.yahoo.com/mail/br/taglines/*http://br.acesso.yahoo.com/">Instale o discador agora!</a>
--0-1569728133-1125332600=:9424--



From chriskl@familyhealth.com.au Tue Aug  30 01:26:32 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id D65C1D82F7
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 29 Aug 2005 22:26:32 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 60830-02
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 30 Aug 2005 01:26:30 +0000 (GMT)
Received: from houston.familyhealth.com.au (houston.au.fhnetwork.com
	[203.22.197.21])
	by svr1.postgresql.org (Postfix) with ESMTP id AEE72D82D1
	for <pgsql-performance@postgresql.org>;
	Mon, 29 Aug 2005 22:26:27 -0300 (ADT)
Received: from houston.familyhealth.com.au (localhost [127.0.0.1])
	by houston.familyhealth.com.au (Postfix) with ESMTP id 092452505A;
	Tue, 30 Aug 2005 09:26:27 +0800 (WST)
Received: from [127.0.0.1] (work-40.internal [192.168.0.40])
	by houston.familyhealth.com.au (Postfix) with ESMTP id 296B424FF1;
	Tue, 30 Aug 2005 09:26:26 +0800 (WST)
Message-ID: <4313B5F3.9000400@familyhealth.com.au>
Date: Tue, 30 Aug 2005 09:27:15 +0800
From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Carlos Henrique Reimer <carlosreimer@yahoo.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: shared buffers
References: <20050829162320.9576.qmail@web53211.mail.yahoo.com>
In-Reply-To: <20050829162320.9576.qmail@web53211.mail.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-familyhealth-MailScanner-Information: Please contact the ISP for more
	information
X-familyhealth-MailScanner: Found to be clean
X-familyhealth-MailScanner-From: chriskl@familyhealth.com.au
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.062 required=5 tests=[AWL=0.012,
	FORGED_RCVD_HELO=0.05]
X-Spam-Level: 
X-Archive-Number: 200508/504
X-Sequence-Number: 14251

> I´ve configured postgresql to use 1GB of shared buffers but meminfo and 
> "top" are indicanting 0 shared buffers page. Why?

1GB shared buffers is far too much.  Set it back to like 30000 buffers 
max...

Chris




From carlosreimer@yahoo.com.br Tue Aug  30 01:54:55 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 71E36D7A26
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 29 Aug 2005 22:54:55 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 75632-05
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 30 Aug 2005 01:54:51 +0000 (GMT)
Received: from web53204.mail.yahoo.com (web53204.mail.yahoo.com
	[206.190.49.74])
	by svr1.postgresql.org (Postfix) with SMTP id 26703D6E21
	for <pgsql-performance@postgresql.org>;
	Mon, 29 Aug 2005 22:54:50 -0300 (ADT)
Received: (qmail 54154 invoked by uid 60001); 30 Aug 2005 01:54:54 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.br;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=jyf4ihk8PLibD/muw8PHko9j+mgcq0o52j4fUI3GX7xI5iagO85uJ68T5X8tA85R+sgzH+WCTMUc1Qdr9FqPZApYmeDg/tG7lYT0MI09F8H/3UVbDVFp7+JIQvwZcfJ+g4xsJ2F0t0qTXtAETLAlZalMEYM8TUUsOVXd8vagETs=
	;
Message-ID: <20050830015454.54152.qmail@web53204.mail.yahoo.com>
Received: from [201.2.219.48] by web53204.mail.yahoo.com via HTTP;
	Mon, 29 Aug 2005 22:54:54 ART
Date: Mon, 29 Aug 2005 22:54:54 -0300 (ART)
From: Carlos Henrique Reimer <carlosreimer@yahoo.com.br>
Subject: Re: shared buffers
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	pgsql-performance@postgresql.org
In-Reply-To: <4313B5F3.9000400@familyhealth.com.au>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-841335462-1125366894=:50910"
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.321 required=5 tests=[AWL=-0.140,
	DNS_FROM_RFC_ABUSE=0.374, HTML_40_50=0.086, HTML_MESSAGE=0.001]
X-Spam-Level: 
X-Archive-Number: 200508/505
X-Sequence-Number: 14252

--0-841335462-1125366894=:50910
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

I forgot to say that it´s a 12GB database...
 
Ok, I´ll set shared buffers to 30.000 pages but even so "meminfo" and "top" shouldn´t show some shared pages? 
 
I heard something about that Redhat 9 can´t handle very well RAM higher than 2GB. Is it right?

Thanks in advance!
 
Reimer

Christopher Kings-Lynne <chriskl@familyhealth.com.au> escreveu:
> I´ve configured postgresql to use 1GB of shared buffers but meminfo and 
> "top" are indicanting 0 shared buffers page. Why?

1GB shared buffers is far too much. Set it back to like 30000 buffers 
max...

Chris


		
---------------------------------
Yahoo! Acesso Grátis: Internet rápida e grátis. Instale o discador agora!
--0-841335462-1125366894=:50910
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

<DIV>I forgot to say that it´s a 12GB database...</DIV>
<DIV>&nbsp;</DIV>
<DIV>Ok, I´ll set shared buffers to 30.000&nbsp;pages but even so "meminfo" and "top" shouldn´t show some shared pages? </DIV>
<DIV>&nbsp;</DIV>
<DIV>I heard something about that Redhat 9 can´t handle very well RAM higher than 2GB. Is it right?<BR></DIV>
<DIV>Thanks in advance!</DIV>
<DIV>&nbsp;</DIV>
<DIV>Reimer</DIV>
<DIV><BR><B><I>Christopher Kings-Lynne &lt;chriskl@familyhealth.com.au&gt;</I></B> escreveu:</DIV>
<BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">&gt; I´ve configured postgresql to use 1GB of shared buffers but meminfo and <BR>&gt; "top" are indicanting 0 shared buffers page. Why?<BR><BR>1GB shared buffers is far too much. Set it back to like 30000 buffers <BR>max...<BR><BR>Chris<BR><BR></BLOCKQUOTE><p>
		<hr size=1><a href=" http://us.rd.yahoo.com/mail/br/taglines/*http://br.acesso.yahoo.com/">Yahoo! Acesso Grátis</a>: Internet rápida e grátis. <a href=" http://us.rd.yahoo.com/mail/br/taglines/*http://br.acesso.yahoo.com/">Instale o discador agora!</a>
--0-841335462-1125366894=:50910--



From chriskl@familyhealth.com.au Tue Aug  30 02:07:32 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 561D9D8208
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 29 Aug 2005 23:07:32 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 88236-07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 30 Aug 2005 02:07:31 +0000 (GMT)
Received: from houston.familyhealth.com.au (houston.au.fhnetwork.com
	[203.22.197.21])
	by svr1.postgresql.org (Postfix) with ESMTP id 06185D818E
	for <pgsql-performance@postgresql.org>;
	Mon, 29 Aug 2005 23:07:29 -0300 (ADT)
Received: from houston.familyhealth.com.au (localhost [127.0.0.1])
	by houston.familyhealth.com.au (Postfix) with ESMTP id EDEEE24FE6;
	Tue, 30 Aug 2005 10:07:32 +0800 (WST)
Received: from [127.0.0.1] (work-40.internal [192.168.0.40])
	by houston.familyhealth.com.au (Postfix) with ESMTP id 9EBEA2505D;
	Tue, 30 Aug 2005 10:07:30 +0800 (WST)
Message-ID: <4313BF95.3030105@familyhealth.com.au>
Date: Tue, 30 Aug 2005 10:08:21 +0800
From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Carlos Henrique Reimer <carlosreimer@yahoo.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: shared buffers
References: <20050830015454.54152.qmail@web53204.mail.yahoo.com>
In-Reply-To: <20050830015454.54152.qmail@web53204.mail.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-familyhealth-MailScanner-Information: Please contact the ISP for more
	information
X-familyhealth-MailScanner: Found to be clean
X-familyhealth-MailScanner-From: chriskl@familyhealth.com.au
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.062 required=5 tests=[AWL=0.012,
	FORGED_RCVD_HELO=0.05]
X-Spam-Level: 
X-Archive-Number: 200508/506
X-Sequence-Number: 14253

> I forgot to say that it´s a 12GB database...

That's actually not that large.

> Ok, I´ll set shared buffers to 30.000 pages but even so "meminfo" and 
> "top" shouldn´t show some shared pages?

Yeah. The reason for not setting buffers so high is because PostgreSQL 
cannot efficiently manage huge shared buffers, so you're better off 
giving the RAM to Linux's disk cache.

Chris




From tgl@sss.pgh.pa.us Tue Aug  30 02:16:28 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 3D83DD8077
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 29 Aug 2005 23:16:28 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 22707-07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 30 Aug 2005 02:16:27 +0000 (GMT)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 33381D7A26
	for <pgsql-performance@postgresql.org>;
	Mon, 29 Aug 2005 23:16:25 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j7U2GKt7016377;
	Mon, 29 Aug 2005 22:16:20 -0400 (EDT)
To: Carlos Henrique Reimer <carlosreimer@yahoo.com.br>
Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	pgsql-performance@postgresql.org
Subject: Re: shared buffers 
In-reply-to: <20050830015454.54152.qmail@web53204.mail.yahoo.com> 
References: <20050830015454.54152.qmail@web53204.mail.yahoo.com>
Comments: In-reply-to Carlos Henrique Reimer <carlosreimer@yahoo.com.br>
	message dated "Mon, 29 Aug 2005 22:54:54 -0300"
Date: Mon, 29 Aug 2005 22:16:20 -0400
Message-ID: <16376.1125368180@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006]
X-Spam-Level: 
X-Archive-Number: 200508/507
X-Sequence-Number: 14254

Carlos Henrique Reimer <carlosreimer@yahoo.com.br> writes:
> I heard something about that Redhat 9 can´t handle very well RAM higher than 2GB. Is it right?

RHL 9 is certainly pretty long in the tooth.  Why aren't you using a
more recent distro?

			regards, tom lane



From wyu@talisys.com Tue Aug  30 07:05:22 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 100C2D77EE
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 30 Aug 2005 04:05:22 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 48069-01
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 30 Aug 2005 07:05:19 +0000 (GMT)
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2])
	by svr1.postgresql.org (Postfix) with ESMTP id A4F14D7270
	for <pgsql-performance@postgresql.org>;
	Tue, 30 Aug 2005 04:05:15 -0300 (ADT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id B6C2F30952; Tue, 30 Aug 2005 09:05:16 +0200 (MET DST)
From: William Yu <wyu@talisys.com>
X-Newsgroups: pgsql.performance
Subject: Re: shared buffers
Date: Tue, 30 Aug 2005 00:05:15 -0700
Organization: Hub.Org Networking Services
Lines: 13
Message-ID: <df10f9$1en9$1@news.hub.org>
References: <4313B5F3.9000400@familyhealth.com.au>
	<20050830015454.54152.qmail@web53204.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Complaints-To: usenet@news.hub.org
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
X-Accept-Language: en-us, en
In-Reply-To: <20050830015454.54152.qmail@web53204.mail.yahoo.com>
To: pgsql-performance@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.024 required=5 tests=[AWL=0.024]
X-Spam-Level: 
X-Archive-Number: 200508/510
X-Sequence-Number: 14257

Carlos Henrique Reimer wrote:
> I forgot to say that it´s a 12GB database...
>  
> Ok, I´ll set shared buffers to 30.000 pages but even so "meminfo" and 
> "top" shouldn´t show some shared pages?
>  
> I heard something about that Redhat 9 can´t handle very well RAM higher 
> than 2GB. Is it right?
> Thanks in advance!

RH9, like any 32-bit OS, is limited to 2GB address space w/o special 
tricks. However, it can access > 2GB for the OS disk cache using PAE if 
you are running the bigmem kernel.



From martin@portant.com Mon Sep  5 02:51:39 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 87346D79E7
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sun,  4 Sep 2005 23:51:39 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 11958-03
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon,  5 Sep 2005 02:51:35 +0000 (GMT)
Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 086D4D6E06
	for <pgsql-performance@postgresql.org>;
	Sun,  4 Sep 2005 23:51:33 -0300 (ADT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id 9179331F59; Mon,  5 Sep 2005 04:51:38 +0200 (MET DST)
From: Martin Nickel <martin@portant.com>
X-Newsgroups: pgsql.performance
Subject: Re: shared buffers
Date: Sun, 04 Sep 2005 21:56:01 -0500
Organization: Hub.Org Networking Services
Lines: 32
Message-ID: <pan.2005.09.05.02.55.50.340618@portant.com>
References: <20050830015454.54152.qmail@web53204.mail.yahoo.com>
	<4313BF95.3030105@familyhealth.com.au>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Complaints-To: usenet@news.hub.org
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing
	moment of clarity.)
To: pgsql-performance@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 required=5 tests=[none]
X-Spam-Level: 
X-Archive-Number: 200509/75
X-Sequence-Number: 14383

Chris,
Would you say that 30000 pages is a good maximum for a Postgres install?
We're running 8.0.3 on 64-bit SUSE on a dual Opteron box with 4G and have
shared_buffers set at 120000.  I've moved it up and down (it was 160000
when I got here) without any measurable performance difference.

The reason I ask is because I occasionally see large-ish queries take
forever (like cancel-after-12-hours forever) and wondered if this could
result from shared_buffers being too large.

Thanks for your (and anyone else's) help!
Martin Nickel

On Tue, 30 Aug 2005 10:08:21 +0800, Christopher Kings-Lynne wrote:

>> I forgot to say that it´s a 12GB database...
> 
> That's actually not that large.
> 
>> Ok, I´ll set shared buffers to 30.000 pages but even so "meminfo" and 
>> "top" shouldn´t show some shared pages?
> 
> Yeah. The reason for not setting buffers so high is because PostgreSQL 
> cannot efficiently manage huge shared buffers, so you're better off 
> giving the RAM to Linux's disk cache.
> 
> Chris
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster




From john@arbash-meinel.com Sun Sep  11 12:36:30 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 653B1D6E46
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sun, 11 Sep 2005 09:36:30 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 29059-07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sun, 11 Sep 2005 12:36:25 +0000 (GMT)
Received: from sccmmhc92.asp.att.net (sccmmhc92.asp.att.net [204.127.203.212])
	by svr1.postgresql.org (Postfix) with ESMTP id 0BF5BD6E0B
	for <pgsql-performance@postgresql.org>;
	Sun, 11 Sep 2005 09:36:23 -0300 (ADT)
Received: from juju.arbash-meinel.com ([12.214.18.81])
	by sccmmhc92.asp.att.net (sccmmhc92) with ESMTP
	id <20050911123623m9200lajhve>; Sun, 11 Sep 2005 12:36:23 +0000
Received: by juju.arbash-meinel.com (Postfix, from userid 505)
	id 1D2CA55FCC; Sun, 11 Sep 2005 07:36:22 -0500 (CDT)
Received: from [209.101.126.19] (unknown [209.101.126.19])
	by juju.arbash-meinel.com (Postfix) with ESMTP id C312E55FA7;
	Sun, 11 Sep 2005 07:36:08 -0500 (CDT)
Message-ID: <432424A9.4060403@arbash-meinel.com>
Date: Sun, 11 Sep 2005 08:35:53 -0400
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Macintosh/20050716)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Martin Nickel <martin@portant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: shared buffers
References: <20050830015454.54152.qmail@web53204.mail.yahoo.com>
	<4313BF95.3030105@familyhealth.com.au>
	<pan.2005.09.05.02.55.50.340618@portant.com>
In-Reply-To: <pan.2005.09.05.02.55.50.340618@portant.com>
X-Enigmail-Version: 0.92.0.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig7527593C6661C605725824D1"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.05 required=5 tests=[FORGED_RCVD_HELO=0.05]
X-Spam-Level: 
X-Archive-Number: 200509/129
X-Sequence-Number: 14437

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig7527593C6661C605725824D1
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Martin Nickel wrote:
> Chris,
> Would you say that 30000 pages is a good maximum for a Postgres install?
> We're running 8.0.3 on 64-bit SUSE on a dual Opteron box with 4G and have
> shared_buffers set at 120000.  I've moved it up and down (it was 160000
> when I got here) without any measurable performance difference.

What I've read on the mailing list, is that usually the sweet spot is
actually around 10k pages. 120k seems far too high.

I believe that the major fixes to the buffer manager are more in 8.1
rather than 8.0, so you probably are hitting some problems. (The biggest
problem was that there were places that require doing a complete scan
through shared memory looking for dirty pages, or some such).

>
> The reason I ask is because I occasionally see large-ish queries take
> forever (like cancel-after-12-hours forever) and wondered if this could
> result from shared_buffers being too large.

There are lots of possibilities for why these take so long, perhaps you
would want to post them, and we can try to help.
For instance, if you have a foreign key reference from one table to
another, and don't have indexes on both sides, then deleting from the
referenced table, will cause a sequential scan on the referring table
for *each* deleted row. (IIRC).

John
=:->

>
> Thanks for your (and anyone else's) help!
> Martin Nickel

--------------enig7527593C6661C605725824D1
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDJCStJdeBCYSNAAMRAhSqAJ4rj5xXBOPOXHaVFoe+AqT9Cr/TIgCdFWIZ
InLHDRTxNp/zVWS5NybgoV0=
=I6fd
-----END PGP SIGNATURE-----

--------------enig7527593C6661C605725824D1--



From georges@mhsoftware.com Mon Feb  22 15:05:13 2010
Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id B2C6B63270D
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 22 Feb 2010 11:05:13 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 52987-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 22 Feb 2010 15:04:54 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.mhsoftware.com (mail.mhsoftware.com [209.169.14.178])
	by mail.postgresql.org (Postfix) with ESMTP id 13D79632713
	for <pgsql-performance@postgresql.org>;
	Mon, 22 Feb 2010 11:05:02 -0400 (AST)
Received: from emp00 (c-76-120-104-193.hsd1.co.comcast.net [76.120.104.193])
	(using TLSv1 with cipher RC4-MD5 (128/128 bits))
	(No client certificate requested)
	by mail.mhsoftware.com (Postfix) with ESMTPSA id 9484E2DD3D
	for <pgsql-performance@postgresql.org>;
	Mon, 22 Feb 2010 08:05:00 -0700 (MST)
From: "George Sexton" <georges@mhsoftware.com>
To: <pgsql-performance@postgresql.org>
Subject: shared_buffers
Date: Mon, 22 Feb 2010 08:04:59 -0700
Message-ID: <05ee01cab3d0$67abeb10$3703c130$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: Acqz0GaRAd+BGE6pQHSj5dIxAIGIoA==
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201002/297
X-Sequence-Number: 37718

I'm reading the docs for 8.4.2, section 18.4.1 Memory. I'm trying to =
figure
out what reasonable values for my usage would be.

The doc says:

shared_buffers (integer)

    Sets the amount of memory the database server uses for shared memory
buffers.

While circular definitions are always right, I'm kind of looking for =
some
information like:

"PostgreSQL uses shared memory buffers for ...."

Could someone please explain what the role of shared buffers is?

George Sexton
MH Software, Inc. - Home of Connect Daily Web Calendar
http://www.mhsoftware.com/
Voice: 303 438 9585
=A0=20






From Kevin.Grittner@wicourts.gov Mon Feb  22 15:23:19 2010
Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 7C7DF63343B
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 22 Feb 2010 11:23:19 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 56251-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 22 Feb 2010 15:22:58 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id 65FCD63261E
	for <pgsql-performance@postgresql.org>;
	Mon, 22 Feb 2010 11:23:07 -0400 (AST)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Mon, 22 Feb 2010 09:23:05 -0600
Message-Id: <4B824CF4020000250002F45E@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Mon, 22 Feb 2010 09:23:00 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "George Sexton" <georges@mhsoftware.com>,
	<pgsql-performance@postgresql.org>
Subject: Re: shared_buffers
References: <05ee01cab3d0$67abeb10$3703c130$@com>
In-Reply-To: <05ee01cab3d0$67abeb10$3703c130$@com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.717 tagged_above=-10 required=5 tests=AWL=-0.607,
	BAYES_05=-1.11
X-Spam-Level: 
X-Archive-Number: 201002/298
X-Sequence-Number: 37719

"George Sexton" <georges@mhsoftware.com> wrote:
 
> Could someone please explain what the role of shared buffers is?
 
This Wiki page might be useful to you:
 
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
 
The short answer (from that page) is:
 
"The shared_buffers configuration parameter determines how much
memory is dedicated to PostgreSQL use for caching data."
 
-Kevin



From Daulat.Ram@exponential.com Mon Mar  11 08:12:36 2019
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h3OkR-0001jW-5j
	for pgsql-performance@arkaria.postgresql.org; Mon, 11 Mar 2019 17:29:59 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h3OkP-0000Ra-FA
	for pgsql-performance@arkaria.postgresql.org; Mon, 11 Mar 2019 17:29:57 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <Daulat.Ram@exponential.com>)
	id 1h3G3B-0005S6-Ak
	for pgsql-performance@lists.postgresql.org; Mon, 11 Mar 2019 08:12:45 +0000
Received: from mail-eopbgr730076.outbound.protection.outlook.com
 ([40.107.73.76] helo=NAM05-DM3-obe.outbound.protection.outlook.com)
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <Daulat.Ram@exponential.com>)
	id 1h3G37-0005qf-QB
	for pgsql-performance@lists.postgresql.org; Mon, 11 Mar 2019 08:12:44 +0000
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=Exponentialinc.onmicrosoft.com; s=selector1-exponential-com;
 h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck;
 bh=MljCwXKOQ9aYB5/rd85/4FgmmJfmv22imB9woTwmzaI=;
 b=TAjRHQTcOoJhVM/9PRwCHNZLX/4u8CjP6kFBWAEHH4HO2IO50pWceewuvZke+Npe/QWok7xNNKMWYjfQAn16+G0CxkwC3/fRfm4tpj7mLoTcrYWtzNpDiZp5ogzkcO7amKhM0JlCHVLkpB+WsTjqi2++o/27SUYN0VsVK2z9h6k=
Received: from BL0PR01MB5187.prod.exchangelabs.com (10.167.235.95) by
 BL0PR01MB4915.prod.exchangelabs.com (10.167.182.225) with Microsoft SMTP
 Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id
 15.20.1686.18; Mon, 11 Mar 2019 08:12:36 +0000
Received: from BL0PR01MB5187.prod.exchangelabs.com ([fe80::432:4e0d:4df6:b49])
 by BL0PR01MB5187.prod.exchangelabs.com ([fe80::432:4e0d:4df6:b49%3]) with
 mapi id 15.20.1686.021; Mon, 11 Mar 2019 08:12:36 +0000
From: Daulat Ram <Daulat.Ram@exponential.com>
To: "pgsql-performance@lists.postgresql.org"
	<pgsql-performance@lists.postgresql.org>
Subject: Shared_buffers
Thread-Topic: Shared_buffers
Thread-Index: AdTX4cJtdw/o16I1StW+ztem3tAXvA==
Date: Mon, 11 Mar 2019 08:12:36 +0000
Message-ID: 
 <BL0PR01MB51871A89E9255FFCD13CC3839B480@BL0PR01MB5187.prod.exchangelabs.com>
Accept-Language: en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
authentication-results: spf=none (sender IP is )
 smtp.mailfrom=Daulat.Ram@exponential.com;
x-originating-ip: [182.156.222.194]
x-ms-publictraffictype: Email
x-ms-office365-filtering-correlation-id: f5b6d725-f765-4de5-d102-08d6a5f9523f
x-microsoft-antispam: 
 BCL:0;PCL:0;RULEID:(2390118)(7020095)(4652040)(8989299)(4534185)(4627221)(201703031133081)(201702281549075)(8990200)(5600127)(711020)(4605104)(2017052603328)(7153060)(7193020);SRVR:BL0PR01MB4915;
x-ms-traffictypediagnostic: BL0PR01MB4915:
x-microsoft-exchange-diagnostics: 
 =?us-ascii?Q?1;BL0PR01MB4915;23:FvpO3vvz4MT2mCe0hbryqoxONE+1SWP3ACgowBT9/?=
 =?us-ascii?Q?7xz+ANKa+lLK3ErPZcLE1LCa76vpkS5DlCN3or0sDCAtkV2Z60/3BJ2S8HQ3?=
 =?us-ascii?Q?E7xphkEoIjwxdyS8inzi0LB+ysGl5PrB9+iqF5l8F7n4Rq78BaseCCCBfndD?=
 =?us-ascii?Q?OFNY+H9rbSsdTHl9Vqd2t2DY0SdJvk532NHK8OpcUYNO61jt5UrQs6oUVsv8?=
 =?us-ascii?Q?/hpuMXjYQ3kUOR7G14xAMRLUGzNjxcb0JQEUalRnFf+Edfql/uMUczOjZH2A?=
 =?us-ascii?Q?7N7w+FDwBjMEUFH8JMlwcnZdk1J6YidfkHNULYCCOSeBKROUxDAiYoK2rYQu?=
 =?us-ascii?Q?Igas1VSmpNDdRSKGm0agFUM3Vc/NZ2rl+aJdk09d6+6uUFP3l4kSEt4ix+au?=
 =?us-ascii?Q?RJo0mYQVR7h98ielxdsxqzhgdtGDgfCjPOfWprNa2gTTXhBA8Vo6QbysCnpC?=
 =?us-ascii?Q?kncM3uMAa08tdw0EIYcJLxE9EoupVlDVYNpy4zGI4/TgJkh5ntw+AKy/2kIi?=
 =?us-ascii?Q?RBUPNBLs9nBhPpAmhC3zY/d3cIHjZWFVsnP7tr7tAdkurO1UGAzUiY6Aum5t?=
 =?us-ascii?Q?B+YHrh/EBuEXYQYVpdgUDdH57fJNgb+JhW9hFTusxzEI9qt2T/RzrO2Ei+qk?=
 =?us-ascii?Q?yHdiWdRGXHCRHUYDvcLk6HZfiJf9GLz32O1dzh1Ye8v88BZNxKEyDQY3XVOo?=
 =?us-ascii?Q?b2SPhj1k1aW02uupNjhrhIo9UlTsDKVs6sE+Tv04iQ7I7ilbcaSaNevUX1Hl?=
 =?us-ascii?Q?ixIJUiLGYj7xYyGIS/0PIhKf/Rnsrbpik5l6bACwdNItT39X2ZufkNa6bbe8?=
 =?us-ascii?Q?5assWCm1ky5Xy2DS2XNJYG71Yk48LqpVXfdE4uiH7LFJ0LHBu/sFNUChM9ec?=
 =?us-ascii?Q?XYjdqQD05aNi82Fl+U1ri3RHRg89ul9mGe2ch8YKc1w/E+d+XbkdndxJoMSg?=
 =?us-ascii?Q?3WAqzUqBrUo548d25NGsM6PbStmDcwqbOXbpOV+KCtXLpt+0gz8pFLYLCEg/?=
 =?us-ascii?Q?s4iyfV5DtJSOpmzSfBZwkHAhK7aXHqgSnNX/1CCSoCw+8qvn+4aCdd2sUkr2?=
 =?us-ascii?Q?wIVF7UDHHuSZZqpKKVrR5xhTe6y2NGBHkKu3PgpE3BUKuAIRTEpXvJN+F/WC?=
 =?us-ascii?Q?mYEkV9Lr1kVnCQXyLrQ8f278FQac15JDqxSUbTD6fpEUm5yJBeA6ivq7D7Qo?=
 =?us-ascii?Q?dOR8Nma0ixN8YQjF/GMmuDf7gYI4hn9lxhCptf0saJK6Z/XPWYpE00KYVRWB?=
 =?us-ascii?Q?tpeEW1Qy1eulTuu/5QmoJZLySjDVDVF3qVVtWbu?=
x-microsoft-antispam-prvs: 
 <BL0PR01MB49158FCD312271C9C7AE33C89B480@BL0PR01MB4915.prod.exchangelabs.com>
x-forefront-prvs: 09730BD177
x-forefront-antispam-report: 
 SFV:NSPM;SFS:(10009020)(396003)(366004)(346002)(136003)(376002)(39840400004)(189003)(199004)(66066001)(486006)(102836004)(52536013)(5660300002)(6506007)(99286004)(7696005)(68736007)(105586002)(7116003)(186003)(14454004)(6916009)(106356001)(7736002)(316002)(26005)(78486014)(2351001)(8676002)(8936002)(478600001)(25786009)(74316002)(33656002)(71200400001)(71190400001)(5640700003)(2501003)(9686003)(2906002)(6436002)(54896002)(86362001)(6306002)(55016002)(81156014)(476003)(53936002)(81166006)(558084003)(256004)(72206003)(3846002)(6116002)(97736004)(790700001);DIR:OUT;SFP:1101;SCL:1;SRVR:BL0PR01MB4915;H:BL0PR01MB5187.prod.exchangelabs.com;FPR:;SPF:None;LANG:en;PTR:InfoNoRecords;MX:1;A:1;
received-spf: None (protection.outlook.com: exponential.com does not designate
 permitted sender hosts)
x-ms-exchange-senderadcheck: 1
x-microsoft-antispam-message-info: 
 2tIGzeGoR+UYidwaCSWRQlVvga+brUAF6jy9nJNkga4YsNJEtWGrs03KSun4F7RRx38ShnVvGMuxFHg/W7ciSDpdXSaC22Sv+YcIerfZZM1MZHCUW54NBmttwBCcscFPTySm4I5HOrvR0efMHbSFU/KNjJzCyIbj+psfIuDfGe0Qzn9o0wNMOpzjnxwW0ESyNQ5b5pUM5xH/CxsRyGRxrwt1TPdv035iGNHNCCNm31PHzCTirKl3ZLHMcOF+pcAyTisFHBdtRYBHldccSQfG32YLuLSWoc7RF5fknSfpX7vt+sitc0hwkt6+yo+DLWmvfSUjbd4RC3nSq2kXyTAzsPKAW0ii1jOSMDOCP6m3pP8MGc9iuMwNvTSjrFTMYRB9sCIu76Z7QXO1oqwWg8EgYFrMQ1ChKJ0URWu51OviGTQ=
Content-Type: multipart/alternative;
	boundary="_000_BL0PR01MB51871A89E9255FFCD13CC3839B480BL0PR01MB5187prod_"
MIME-Version: 1.0
X-OriginatorOrg: exponential.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 f5b6d725-f765-4de5-d102-08d6a5f9523f
X-MS-Exchange-CrossTenant-originalarrivaltime: 11 Mar 2019 08:12:36.3686
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: c1877106-092b-492d-8db3-c9fec130d4fd
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BL0PR01MB4915
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

--_000_BL0PR01MB51871A89E9255FFCD13CC3839B480BL0PR01MB5187prod_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi team,

I want to know about the working and importance of shared_buffers  in Postg=
resql? is it similar to the oracle database buffer cache?

Regards,
Daulat


--_000_BL0PR01MB51871A89E9255FFCD13CC3839B480BL0PR01MB5187prod_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:#0563C1;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:#954F72;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri",sans-serif;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#0563C1" vlink=3D"#954F72">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">Hi team,<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I want to know about the working and importance of s=
hared_buffers &nbsp;in Postgresql? is it similar to the oracle database buf=
fer cache?
<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Regards,<o:p></o:p></p>
<p class=3D"MsoNormal">Daulat<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
</div>
</body>
</html>

--_000_BL0PR01MB51871A89E9255FFCD13CC3839B480BL0PR01MB5187prod_--




From laurenz.albe@cybertec.at Tue Mar  12 08:29:13 2019
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h3cms-00041y-LU
	for pgsql-performance@arkaria.postgresql.org; Tue, 12 Mar 2019 08:29:26 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h3cmr-00059C-Bh
	for pgsql-performance@arkaria.postgresql.org; Tue, 12 Mar 2019 08:29:25 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <laurenz.albe@cybertec.at>)
	id 1h3cmr-000594-45
	for pgsql-performance@lists.postgresql.org; Tue, 12 Mar 2019 08:29:25 +0000
Received: from mx2a.mailbox.org ([2001:67c:2050:104:0:2:25:2]
 helo=mx2.mailbox.org)
	by magus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <laurenz.albe@cybertec.at>)
	id 1h3cmp-00022L-FJ
	for pgsql-performance@lists.postgresql.org; Tue, 12 Mar 2019 08:29:24 +0000
Received: from smtp1.mailbox.org (smtp1.mailbox.org [80.241.60.240])
	(using TLSv1.2 with cipher ECDHE-RSA-CHACHA20-POLY1305 (256/256 bits))
	(No client certificate requested)
	by mx2.mailbox.org (Postfix) with ESMTPS id D9A7CA1195;
	Tue, 12 Mar 2019 09:29:20 +0100 (CET)
X-Virus-Scanned: amavisd-new at heinlein-support.de
Received: from smtp1.mailbox.org ([80.241.60.240])
	by spamfilter04.heinlein-hosting.de (spamfilter04.heinlein-hosting.de
 [80.241.56.122]) (amavisd-new, port 10030)
	with ESMTP id aILJ9DihPbnF; Tue, 12 Mar 2019 09:29:14 +0100 (CET)
Message-ID: <56099ac31c7cc1a4e8a51963e2674e7fe592b86f.camel@cybertec.at>
Subject: Re: Shared_buffers
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Daulat Ram <Daulat.Ram@exponential.com>,
	"pgsql-performance@lists.postgresql.org"
	 <pgsql-performance@lists.postgresql.org>
Date: Tue, 12 Mar 2019 09:29:13 +0100
In-Reply-To: 
 <BL0PR01MB51871A89E9255FFCD13CC3839B480@BL0PR01MB5187.prod.exchangelabs.com>
References: 
	<BL0PR01MB51871A89E9255FFCD13CC3839B480@BL0PR01MB5187.prod.exchangelabs.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

Daulat Ram wrote:
> I want to know about the working and importance of shared_buffers  in Postgresql?
> is it similar to the oracle database buffer cache?

Yes, exactly.

The main difference is that PostgreSQL uses buffered I/O, while Oracle usually
uses direct I/O.

Usually you start with shared_buffers being the minimum of a quarter of the
available RAM and 8 GB.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com





From mlewis@entrata.com Tue Mar  12 19:23:52 2019
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h3n0i-0006xZ-ER
	for pgsql-performance@arkaria.postgresql.org; Tue, 12 Mar 2019 19:24:24 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h3n0h-0001TZ-4K
	for pgsql-performance@arkaria.postgresql.org; Tue, 12 Mar 2019 19:24:23 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <mlewis@entrata.com>)
	id 1h3n0g-0001Rs-Jb
	for pgsql-performance@lists.postgresql.org; Tue, 12 Mar 2019 19:24:22 +0000
Received: from mail-yw1-xc44.google.com ([2607:f8b0:4864:20::c44])
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <mlewis@entrata.com>)
	id 1h3n0d-00017b-FZ
	for pgsql-performance@lists.postgresql.org; Tue, 12 Mar 2019 19:24:21 +0000
Received: by mail-yw1-xc44.google.com with SMTP id x129so3110214ywc.1
        for <pgsql-performance@lists.postgresql.org>;
 Tue, 12 Mar 2019 12:24:19 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=entrata-com.20150623.gappssmtp.com; s=20150623;
        h=mime-version:references:in-reply-to:from:date:message-id:subject:to
         :cc;
        bh=mCPEGovWBeslduUuPUb5eWzCT44TEDKRhTw/ICzVgSs=;
        b=gyOpW0ckMZp8C7cQnUJuFyMiTCb4tgTqqMugu20wGpAyv0nEJr42Lvslnxjg9AkmV+
         JJpDdo6YHKleMdYYJRDTSHUeUYfPmhymhNsnIGvKb9NQkN01H4q/i106K7f5QNLh4q/k
         8GDtVvPe+pXK8t5hMrpNS+aYQ+z9gSSkUVg7NcKMmF0Cy9qT7yowAYk/DJBHtRDG/wZ6
         yqf8UOdfzaBeKp4t8KQAwKC1+dSeHZi3yFGB+I+bEu85LQuSD7QlbGjRnsW4600AchRa
         Sep/Pb3m90hPeCfCyjNQaowkCNyIBKV6/hg+SjWG5sbVZ+pQHOIzOhCCazY7++MEffV5
         NPDg==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=1e100.net; s=20161025;
        h=x-gm-message-state:mime-version:references:in-reply-to:from:date
         :message-id:subject:to:cc;
        bh=mCPEGovWBeslduUuPUb5eWzCT44TEDKRhTw/ICzVgSs=;
        b=WuqFLP22E4QLDyjSOFjA0/vfIKT4DFg71pXSfK1yJYAWaflQBsryf2FNyb9ELBHin0
         4I2zuVwPwDYsa4G+UNRTUNk6mE3t6IruTosHtiasDhGsajU/tOBj64LesGwLe5jgfQzu
         DzBwDSQTucwzTtlHh5a+EnZjWY4DCN5NlWTM1lXXyWm1qG+vCNiXY79MyGvagg7vB+Yj
         rNzny9H9wT46iqCma1QUOHjg6vj0WStSA8VDGmGmjbaJN9bSBUl6mmZuTBYSVmcUO4es
         MkczSLODF3e5tUjxumP4YzxdX3/kmYw7q8W6DTD9/Q0HiRCPB/UR1SXIP+ph13O04LbE
         V8cg==
X-Gm-Message-State: APjAAAVVlv6PFBQpWAo3dBFhhzF0Td+roQphnFE++cmzWjllErti60n/
	x6P+gtHEGZ/bDYckoR6FQAO+W86YT5SmZJn6rJ3JL3ghBfE=
X-Google-Smtp-Source: 
 APXvYqyicQC49GmAUXF3OCB+/13N7qylZLVpDkVlfdiMJqvNQWSO18HtWwi6EMsRwo1UcJT9fyiPW2Quov3hRLewblY=
X-Received: by 2002:a25:6c06:: with SMTP id h6mr33144275ybc.130.1552418658432;
 Tue, 12 Mar 2019 12:24:18 -0700 (PDT)
MIME-Version: 1.0
References: 
 <BL0PR01MB51871A89E9255FFCD13CC3839B480@BL0PR01MB5187.prod.exchangelabs.com>
 <56099ac31c7cc1a4e8a51963e2674e7fe592b86f.camel@cybertec.at>
In-Reply-To: <56099ac31c7cc1a4e8a51963e2674e7fe592b86f.camel@cybertec.at>
From: Michael Lewis <mlewis@entrata.com>
Date: Tue, 12 Mar 2019 13:23:52 -0600
Message-ID: 
 <CAHOFxGqT24bb_G9Ej3jAwBDLizfK1dwb+rXo62mP4zjB6iDRpA@mail.gmail.com>
Subject: Re: Shared_buffers
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Daulat Ram <Daulat.Ram@exponential.com>,
	"pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000adbe360583eaa258"
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

--000000000000adbe360583eaa258
Content-Type: text/plain; charset="UTF-8"

On Tue, Mar 12, 2019 at 2:29 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

> Daulat Ram wrote:
> > I want to know about the working and importance of shared_buffers  in
> Postgresql?
> > is it similar to the oracle database buffer cache?
>
> Yes, exactly.
>
> The main difference is that PostgreSQL uses buffered I/O, while Oracle
> usually
> uses direct I/O.
>
> Usually you start with shared_buffers being the minimum of a quarter of the
> available RAM and 8 GB.
>

Any good rule of thumb or write up about when shared buffers in excess of
8GBs makes sense (assuming system ram 64+ GBs perhaps)?

--000000000000adbe360583eaa258
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Mar 12, 2019 at 2:29 AM Laurenz A=
lbe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cybertec.a=
t</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"g=
mail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204=
,204,204);padding-left:1ex">Daulat Ram wrote:<br>
&gt; I want to know about the working and importance of shared_buffers=C2=
=A0 in Postgresql?<br>
&gt; is it similar to the oracle database buffer cache?<br>
<br>
Yes, exactly.<br>
<br>
The main difference is that PostgreSQL uses buffered I/O, while Oracle usua=
lly<br>
uses direct I/O.<br>
<br>
Usually you start with shared_buffers being the minimum of a quarter of the=
<br>
available RAM and 8 GB.<br></blockquote><div><br></div><div>Any good rule o=
f thumb or write up about when shared buffers in excess of 8GBs makes sense=
 (assuming system ram 64+ GBs perhaps)?</div></div></div>

--000000000000adbe360583eaa258--




From MichaelDBA@sqlexec.com Tue Mar  12 20:03:11 2019
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h3ncz-0000Nl-TC
	for pgsql-performance@arkaria.postgresql.org; Tue, 12 Mar 2019 20:03:58 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h3ncy-0002yH-MO
	for pgsql-performance@arkaria.postgresql.org; Tue, 12 Mar 2019 20:03:56 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <MichaelDBA@sqlexec.com>)
	id 1h3ncy-0002yA-Dr
	for pgsql-performance@lists.postgresql.org; Tue, 12 Mar 2019 20:03:56 +0000
Received: from mout.perfora.net ([74.208.4.197])
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <MichaelDBA@sqlexec.com>)
	id 1h3ncw-0000A6-3Y
	for pgsql-performance@lists.postgresql.org; Tue, 12 Mar 2019 20:03:56 +0000
Received: from [192.168.1.101] ([98.169.208.73]) by mrelay.perfora.net
 (mreueus001 [74.208.5.2]) with ESMTPSA (Nemesis) id 0MacbH-1hJYHl45t3-00K4k8;
 Tue, 12 Mar 2019 21:03:17 +0100
Message-ID: <5C88107F.7040902@sqlexec.com>
Date: Tue, 12 Mar 2019 16:03:11 -0400
From: MichaelDBA <MichaelDBA@sqlexec.com>
User-Agent: Postbox 5.0.25 (Windows/20180328)
MIME-Version: 1.0
To: Michael Lewis <mlewis@entrata.com>
CC: Laurenz Albe <laurenz.albe@cybertec.at>,
 Daulat Ram <Daulat.Ram@exponential.com>,
 "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: Shared_buffers
References: 
 <BL0PR01MB51871A89E9255FFCD13CC3839B480@BL0PR01MB5187.prod.exchangelabs.com>
 <56099ac31c7cc1a4e8a51963e2674e7fe592b86f.camel@cybertec.at>
 <CAHOFxGqT24bb_G9Ej3jAwBDLizfK1dwb+rXo62mP4zjB6iDRpA@mail.gmail.com>
In-Reply-To: 
 <CAHOFxGqT24bb_G9Ej3jAwBDLizfK1dwb+rXo62mP4zjB6iDRpA@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------060304090108060408040102"
X-Provags-ID: V03:K1:EDfiiSPxloK1u5NDYByAeR03s5pFxMa+HMYQXvJ4SjFau9KMPcP
 MfvlUkeGS1Ai1eqsN2vhLutKEx2P9mEFJpUW1sdFWod2XnTPtrVBwBDwdKrEBhD7pi8ER5o
 ArFqUZlVxAdnYvzdetOruKpkFqQ4AHde37btmbxYDrnY/PKfINw1osIJ5uxSthbrzwlpuAP
 N9r1UTIBl2f+NsuXtXZdA==
X-Spam-Flag: NO
X-UI-Out-Filterresults: notjunk:1;V03:K0:zE/9GGHkJNE=:9HuEx4LKck5DuEPNy5DNHJ
 SvvlJgUf93tWKJv2TfniMAPaQhVg0thB8vljHb6h9UO8pcur/Dj8Up4e4T0R8aW2OP+l791PK
 nKn4b3iAHwP6mLtvrD+pqpW+tC1ca5Irh8VaFq+mA4JFr+XayzdthFtepWlzu7UAX3iNnGRgp
 M1+c9/N7524vxF1IA9xClyf8J+2IBFSdfu0OMEfLesJ6+d7zYDtKystcU3E6OmUi5yc1mzhBP
 tj2UsDS+YgossSpyfGSiuNnvJY5VklZedig3fToo2t1gHMiYF8Ia50mIb7mNVC4Y8X5DCHop5
 Hx5oztMS2/za48kN8wllU/apSIPquVDdyPw9p9QepKvVmi1iWcyHX9nbvBeJXoauNIMsNNnXG
 Kz70RtnJCYWLZx36a0w/Wul4TtahLKXJTZ69Rq40pppINdI5hgpkMk6qhlVysqL7hEiod7hLO
 vDPNtfUDBKFhcEPNqBgCq2UL4rR8GElYOmjjqfVX+uLOp+nHOng8SiQvfIsWsQeVL4YPmfScX
 Daonyk59BU17K5upRK2Ct+dKy+6FD+161oiHLT3bCTrmkgYor4ukp0Y3o4DegWU6zQXeVjUj8
 t+wgbEAoYc4xf9l+Pc1JezOWYJgeSo9dF05cpa+APnTNMzLehn4pN7A/bZR1BTAb1P1oK+aXj
 jeVKTIVm663Ii1cjjEvqIHUYmMv90jxD3ucRRFKs6bEsxlNomb+O59mflJC06raTIvT04qnao
 jZEtpFfQbhsPaGWP5djB9JHL2l3vXwgMLE8tHGmyC17y+3diDDUHpspwSDM=
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

This is a multi-part message in MIME format.
--------------060304090108060408040102
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Set shared_buffers more accurately by using pg_buffercache extension and 
the related queries during high load times.

Regards,
Michael Vitale

> Michael Lewis <mailto:mlewis@entrata.com>
> Tuesday, March 12, 2019 3:23 PM
> On Tue, Mar 12, 2019 at 2:29 AM Laurenz Albe <laurenz.albe@cybertec.at 
> <mailto:laurenz.albe@cybertec.at>> wrote:
>
>     Daulat Ram wrote:
>     > I want to know about the working and importance of
>     shared_buffers  in Postgresql?
>     > is it similar to the oracle database buffer cache?
>
>     Yes, exactly.
>
>     The main difference is that PostgreSQL uses buffered I/O, while
>     Oracle usually
>     uses direct I/O.
>
>     Usually you start with shared_buffers being the minimum of a
>     quarter of the
>     available RAM and 8 GB.
>
>
> Any good rule of thumb or write up about when shared buffers in excess 
> of 8GBs makes sense (assuming system ram 64+ GBs perhaps)?


--------------060304090108060408040102
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html><head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head><body bgcolor="#FFFFFF" text="#000000">Set shared_buffers more 
accurately by using pg_buffercache extension and the related queries 
during high load times.<br>
<br>
Regards,<br>
Michael Vitale<br>
<span>

</span><br>
<blockquote style="border: 0px none;" 
cite="mid:CAHOFxGqT24bb_G9Ej3jAwBDLizfK1dwb+rXo62mP4zjB6iDRpA@mail.gmail.com"
 type="cite">
  <div style="margin:30px 25px 10px 25px;" class="__pbConvHr"><div 
style="width:100%;border-top:2px solid #EDF1F4;padding-top:10px;">   <div
 
style="display:inline-block;white-space:nowrap;vertical-align:middle;width:49%;">
   	<a moz-do-not-send="true" href="mailto:mlewis@entrata.com" 
style="color:#485664 
!important;padding-right:6px;font-weight:500;text-decoration:none 
!important;">Michael Lewis</a></div>   <div 
style="display:inline-block;white-space:nowrap;vertical-align:middle;width:48%;text-align:
 right;">     <font color="#909AA4"><span style="padding-left:6px">Tuesday,
 March 12, 2019 3:23 PM</span></font></div>    </div></div>
  <div style="color:#909AA4;margin-left:24px;margin-right:24px;" 
__pbrmquotes="true" class="__pbConvBody"><div dir="ltr"><div dir="ltr">On
 Tue, Mar 12, 2019 at 2:29 AM Laurenz Albe &lt;<a moz-do-not-send="true"
 href="mailto:laurenz.albe@cybertec.at">laurenz.albe@cybertec.at</a>&gt;
 wrote:<br></div><div class="gmail_quote"><blockquote style="margin:0px 
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" 
class="gmail_quote">Daulat Ram wrote:<br>
&gt; I want to know about the working and importance of shared_buffers  
in Postgresql?<br>
&gt; is it similar to the oracle database buffer cache?<br>
<br>
Yes, exactly.<br>
<br>
The main difference is that PostgreSQL uses buffered I/O, while Oracle 
usually<br>
uses direct I/O.<br>
<br>
Usually you start with shared_buffers being the minimum of a quarter of 
the<br>
available RAM and 8 GB.<br></blockquote><div><br></div><div>Any good 
rule of thumb or write up about when shared buffers in excess of 8GBs 
makes sense (assuming system ram 64+ GBs perhaps)?</div></div></div>

  </div>
</blockquote>
<br>
</body></html>

--------------060304090108060408040102--




From pryzby@telsasoft.com Tue Mar  12 20:11:02 2019
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h3nk4-0000j0-0H
	for pgsql-performance@arkaria.postgresql.org; Tue, 12 Mar 2019 20:11:16 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h3nk2-0004B0-Qj
	for pgsql-performance@arkaria.postgresql.org; Tue, 12 Mar 2019 20:11:14 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <pryzby@telsasoft.com>)
	id 1h3nk2-0004Al-Eq
	for pgsql-performance@lists.postgresql.org; Tue, 12 Mar 2019 20:11:14 +0000
Received: from mail-it1-x141.google.com ([2607:f8b0:4864:20::141])
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <pryzby@telsasoft.com>)
	id 1h3njv-0000Nm-IW
	for pgsql-performance@lists.postgresql.org; Tue, 12 Mar 2019 20:11:14 +0000
Received: by mail-it1-x141.google.com with SMTP id k193so6652209ita.3
        for <pgsql-performance@lists.postgresql.org>;
 Tue, 12 Mar 2019 13:11:07 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=telsasoft-com.20150623.gappssmtp.com; s=20150623;
        h=date:from:to:cc:subject:message-id:references:mime-version
         :content-disposition:in-reply-to:user-agent;
        bh=xiVzV9ROuKAuLa0iwKNPsZrsw7n5c0l2CR65X1mMK90=;
        b=GHogNjTpFSmVfjcS6pvdwp4Fa4svhfVPPYvFLteaXU/Ra62J5VgFhYvJ8rmoqH6StK
         S3jzMgZgp8m0D6GAEoyds3F6q4738UUj4A7XCQOQKotOqC5GYZFbhd/seLYmkFLJJ9yy
         XF99xF9xyyCD4fuT4vScmsCNa4EsF3dYogTqEemUpkt4WR7HySZShnM8+JWyA1wy3I3t
         rbxQ+hnzakJItK4Kt5/pYfXA2QiTn269Szb1Mp1hwtgHyDYkKNBP/4cYYbOhj4SKpknL
         gJJ5/YhW575Qr74SWVVB36XfVpCm3LzFzd3ybe33tcn6CgVa5uk24mJSztymvBIRd0eP
         xHWA==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=1e100.net; s=20161025;
        h=x-gm-message-state:date:from:to:cc:subject:message-id:references
         :mime-version:content-disposition:in-reply-to:user-agent;
        bh=xiVzV9ROuKAuLa0iwKNPsZrsw7n5c0l2CR65X1mMK90=;
        b=n/gXkDx+ANQhGgBmyhWjUTWkFAequ0kXnTqvCCAvP4AFF30XRtEXfd0+gSpdV68JEV
         nkyndnMSqSlX+Hb4RQN9pf3EYdkUj0LjS63pO7/aUhWz8/AnprUzY0ydEvmktpIrvuYR
         1sWAMhJheYf+Mkr6ZT5zgYzbo7ja2a47R6Y7NX7v375djKzFTH8bFHDIWQLNfABmU9Ls
         73NiV4zVPkRPz45bjJiVMGs044U/I/PlGkog7bvpWai5N4vHDfb1/u+pJi5qJSRdxAsl
         BAZuZwF6A4eZrDel4sRnv4sYs5WAMFLKgHgQgX/SmdzSnTs1l++80mT4agMXO5JvwFxw
         eKwQ==
X-Gm-Message-State: APjAAAXxerok4HlCh1rpA28uFwCmMq5WvzG1JDfiQ2xQP43pBu7veBAI
	gGPpffO9Y91fw0scojzA6nNX1w==
X-Google-Smtp-Source: 
 APXvYqw+zGtMH9hIIkx1Z6xDgIkS+SLEWiY0PAS78usjMdfc6yMrOi2dJOP3JjyV4CeMXIefX/nFYw==
X-Received: by 2002:a24:6ec1:: with SMTP id
 w184mr3235895itc.113.1552421464712;
        Tue, 12 Mar 2019 13:11:04 -0700 (PDT)
Received: from pryzbyj (charmander.telsasoft.com. [50.244.222.1])
        by smtp.gmail.com with ESMTPSA id
 m79sm799035itm.25.2019.03.12.13.11.03
        (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128);
        Tue, 12 Mar 2019 13:11:03 -0700 (PDT)
Received: by pryzbyj (Postfix, from userid 1000)
	id 9E78E800A28; Tue, 12 Mar 2019 15:11:02 -0500 (CDT)
Date: Tue, 12 Mar 2019 15:11:02 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: MichaelDBA <MichaelDBA@sqlexec.com>
Cc: Michael Lewis <mlewis@entrata.com>,
	Laurenz Albe <laurenz.albe@cybertec.at>,
	Daulat Ram <Daulat.Ram@exponential.com>,
	"pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: Shared_buffers
Message-ID: <20190312201102.GI23597@telsasoft.com>
References: 
 <BL0PR01MB51871A89E9255FFCD13CC3839B480@BL0PR01MB5187.prod.exchangelabs.com>
 <56099ac31c7cc1a4e8a51963e2674e7fe592b86f.camel@cybertec.at>
 <CAHOFxGqT24bb_G9Ej3jAwBDLizfK1dwb+rXo62mP4zjB6iDRpA@mail.gmail.com>
 <5C88107F.7040902@sqlexec.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <5C88107F.7040902@sqlexec.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

On Tue, Mar 12, 2019 at 04:03:11PM -0400, MichaelDBA wrote:
> Set shared_buffers more accurately by using pg_buffercache extension and the
> related queries during high load times.

I've tuned ~40 postgres instances, primarily using log_checkpoints and
pg_stat_bgwriter, with custom RRD graphs.  pg_buffercache does provide some
valuable insights, and I know it's commonly suggested to check histogram of
usagecounts, but I've never had any idea how to apply that to tune
shared_buffers.

Could you elaborate on what procedure you suggest ?

Justin




From MichaelDBA@sqlexec.com Tue Mar  12 20:11:49 2019
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h3nkx-0000kv-Ir
	for pgsql-performance@arkaria.postgresql.org; Tue, 12 Mar 2019 20:12:11 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h3nkw-0006oo-2O
	for pgsql-performance@arkaria.postgresql.org; Tue, 12 Mar 2019 20:12:10 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <MichaelDBA@sqlexec.com>)
	id 1h3nkv-0006jf-M3
	for pgsql-performance@lists.postgresql.org; Tue, 12 Mar 2019 20:12:09 +0000
Received: from mout.perfora.net ([74.208.4.194])
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <MichaelDBA@sqlexec.com>)
	id 1h3nks-0002En-RY
	for pgsql-performance@lists.postgresql.org; Tue, 12 Mar 2019 20:12:08 +0000
Received: from [192.168.1.101] ([98.169.208.73]) by mrelay.perfora.net
 (mreueus001 [74.208.5.2]) with ESMTPSA (Nemesis) id 0Mdtql-1hP2bE3XC9-00PbHu;
 Tue, 12 Mar 2019 21:11:53 +0100
Message-ID: <5C881285.8030905@sqlexec.com>
Date: Tue, 12 Mar 2019 16:11:49 -0400
From: MichaelDBA <MichaelDBA@sqlexec.com>
User-Agent: Postbox 5.0.25 (Windows/20180328)
MIME-Version: 1.0
To: Justin Pryzby <pryzby@telsasoft.com>
CC: Michael Lewis <mlewis@entrata.com>,
 Laurenz Albe <laurenz.albe@cybertec.at>,
 Daulat Ram <Daulat.Ram@exponential.com>,
 "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: Shared_buffers
References: 
 <BL0PR01MB51871A89E9255FFCD13CC3839B480@BL0PR01MB5187.prod.exchangelabs.com>
 <56099ac31c7cc1a4e8a51963e2674e7fe592b86f.camel@cybertec.at>
 <CAHOFxGqT24bb_G9Ej3jAwBDLizfK1dwb+rXo62mP4zjB6iDRpA@mail.gmail.com>
 <5C88107F.7040902@sqlexec.com> <20190312201102.GI23597@telsasoft.com>
In-Reply-To: <20190312201102.GI23597@telsasoft.com>
Content-Type: multipart/alternative;
 boundary="------------080501070200030603080708"
X-Provags-ID: V03:K1:I1/pazpLSiU9X84NPu7xq4gfnLYpp2kkxaYLpo+5C/c6Ih4XuFi
 mE9XKMXHI2MIASqPZdmBiVxoqvB1EaZO0LqhcCOoD1d8XSeGIo0lztYN9UrkzFyEW/rQs7h
 hWlwUawotrgQek7JLwdPRKGpHkoX041ldXMlD8Hq2FY1dn4xwXRtVM731mioo2fK5J9uvNP
 nuEir11DzihN0HeZ5eBDA==
X-Spam-Flag: NO
X-UI-Out-Filterresults: notjunk:1;V03:K0:XlKsAaU793A=:OyB+N/pgt9yX/gt+x7F9y0
 ds9WaJqOTIFgX+ZRGsJZfvipRs1nyNYOED2vB7ek63txn9XHaW/tdyUTcoGm8XXzXd1igqGCu
 To3N7MGZ9eGQFkmWup3wQaDAqMhM7HNHe6jX1HqCqgabKeYcXw0bDxnZJc/Y/YHH3gY1a3XYn
 ehy2n7rJ0RGCwPJZYN4rhRfltx7G/RmLaHFq2hkeXvyCovU0EOnACObVqjOl86ttClkHZms93
 1KUnDFh3DHL7vfAlLp/EoJwuXIYDdwxlLhM1cl2vjv1nJpBdYJ1653tM5mNHILeePIWFzGw1j
 Yv8pxnq4kKLbvX4y/cHhoUvApxK2Tq21bC1rzbqUxaa6wDzy4TkcDpFY8W1+0SxiXSoXHvjuj
 hc52aB2C8lbqUw4cilwOgt+WIHFQOYE2OwOlV+2UZfO6IxQNXwRl2GkmHFZVPopT8yHrqCMEU
 7UEDrW+XqB1QtS4BBGalxJN6n+oqmkVw8o20us00VQwywIYFhUgm/J2tdZSjqC8DvNsteVJJN
 v+pdrZl54vkvfH0pqoedwtUMr5BvsHb/m0Yitv3ZiYIGnfasdivjDjJ4dfxcjD+Xm8bB0GAxC
 vVpoI+FNpX+WjAzd4DLY+uclnl5nAthlUVv3RhCeEVe1CJyhuRrBqGobd2I42x1NgHFR4fZ90
 8BJu0Wj4HJamZ7ZxGawW5fBkSChGzjrb2EBLuYtEVybsjH3uXz2BHEZUXoPYEvJqmbKGlgBRT
 dtXfGV1DzlIyYxTYuYQOH38moY6srPlhn1XhlRInX1KvqTv7T+sxozqpKmA=
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

This is a multi-part message in MIME format.
--------------080501070200030603080708
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Here's one cook article on using pg_buffercache...

https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/

Regards,
Michael Vitale

> Justin Pryzby <mailto:pryzby@telsasoft.com>
> Tuesday, March 12, 2019 4:11 PM
>
> I've tuned ~40 postgres instances, primarily using log_checkpoints and
> pg_stat_bgwriter, with custom RRD graphs. pg_buffercache does provide some
> valuable insights, and I know it's commonly suggested to check 
> histogram of
> usagecounts, but I've never had any idea how to apply that to tune
> shared_buffers.
>
> Could you elaborate on what procedure you suggest ?
>
> Justin
> MichaelDBA <mailto:MichaelDBA@sqlexec.com>
> Tuesday, March 12, 2019 4:03 PM
> Set shared_buffers more accurately by using pg_buffercache extension 
> and the related queries during high load times.
>
> Regards,
> Michael Vitale
>
>
> Michael Lewis <mailto:mlewis@entrata.com>
> Tuesday, March 12, 2019 3:23 PM
> On Tue, Mar 12, 2019 at 2:29 AM Laurenz Albe <laurenz.albe@cybertec.at 
> <mailto:laurenz.albe@cybertec.at>> wrote:
>
>     Daulat Ram wrote:
>     > I want to know about the working and importance of
>     shared_buffers  in Postgresql?
>     > is it similar to the oracle database buffer cache?
>
>     Yes, exactly.
>
>     The main difference is that PostgreSQL uses buffered I/O, while
>     Oracle usually
>     uses direct I/O.
>
>     Usually you start with shared_buffers being the minimum of a
>     quarter of the
>     available RAM and 8 GB.
>
>
> Any good rule of thumb or write up about when shared buffers in excess 
> of 8GBs makes sense (assuming system ram 64+ GBs perhaps)?


--------------080501070200030603080708
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

<html><head>
<meta content="text/html; charset=ISO-8859-1" http-equiv="Content-Type">
</head><body bgcolor="#FFFFFF" text="#000000">Here's one cook article on
 using pg_buffercache...<br>
<br>
<a class="moz-txt-link-freetext" href="https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/">https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/</a><br>
<br>
Regards,<br>
Michael Vitale<br>
<span>

</span><br>
<blockquote style="border: 0px none;" 
cite="mid:20190312201102.GI23597@telsasoft.com" type="cite">
  <div style="margin:30px 25px 10px 25px;" class="__pbConvHr"><div 
style="width:100%;border-top:2px solid #EDF1F4;padding-top:10px;">   <div
 
style="display:inline-block;white-space:nowrap;vertical-align:middle;width:49%;">
   	<a moz-do-not-send="true" href="mailto:pryzby@telsasoft.com" 
style="color:#485664 
!important;padding-right:6px;font-weight:500;text-decoration:none 
!important;">Justin Pryzby</a></div>   <div 
style="display:inline-block;white-space:nowrap;vertical-align:middle;width:48%;text-align:
 right;">     <font color="#909AA4"><span style="padding-left:6px">Tuesday,
 March 12, 2019 4:11 PM</span></font></div>    </div></div>
  <div style="color:#909AA4;margin-left:24px;margin-right:24px;" 
__pbrmquotes="true" class="__pbConvBody"><div><!----><br>I've tuned ~40 
postgres instances, primarily using log_checkpoints and<br>pg_stat_bgwriter,
 with custom RRD graphs.  pg_buffercache does provide some<br>valuable 
insights, and I know it's commonly suggested to check histogram of<br>usagecounts,
 but I've never had any idea how to apply that to tune<br>shared_buffers.<br><br>Could
 you elaborate on what procedure you suggest ?<br><br>Justin<br></div></div>
  <div style="margin:30px 25px 10px 25px;" class="__pbConvHr"><div 
style="width:100%;border-top:2px solid #EDF1F4;padding-top:10px;">   <div
 
style="display:inline-block;white-space:nowrap;vertical-align:middle;width:49%;">
   	<a moz-do-not-send="true" href="mailto:MichaelDBA@sqlexec.com" 
style="color:#485664 
!important;padding-right:6px;font-weight:500;text-decoration:none 
!important;">MichaelDBA</a></div>   <div 
style="display:inline-block;white-space:nowrap;vertical-align:middle;width:48%;text-align:
 right;">     <font color="#909AA4"><span style="padding-left:6px">Tuesday,
 March 12, 2019 4:03 PM</span></font></div>    </div></div>
  <div style="color:#909AA4;margin-left:24px;margin-right:24px;" 
__pbrmquotes="true" class="__pbConvBody">
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
Set shared_buffers more 
accurately by using pg_buffercache extension and the related queries 
during high load times.<br>
<br>
Regards,<br>
Michael Vitale<br>
<span>

</span><br>

<br>
  </div>
  <div style="margin:30px 25px 10px 25px;" class="__pbConvHr"><div 
style="width:100%;border-top:2px solid #EDF1F4;padding-top:10px;">   <div
 
style="display:inline-block;white-space:nowrap;vertical-align:middle;width:49%;">
   	<a moz-do-not-send="true" href="mailto:mlewis@entrata.com" 
style="color:#485664 
!important;padding-right:6px;font-weight:500;text-decoration:none 
!important;">Michael Lewis</a></div>   <div 
style="display:inline-block;white-space:nowrap;vertical-align:middle;width:48%;text-align:
 right;">     <font color="#909AA4"><span style="padding-left:6px">Tuesday,
 March 12, 2019 3:23 PM</span></font></div>    </div></div>
  <div style="color:#909AA4;margin-left:24px;margin-right:24px;" 
__pbrmquotes="true" class="__pbConvBody"><div dir="ltr"><div dir="ltr">On
 Tue, Mar 12, 2019 at 2:29 AM Laurenz Albe &lt;<a moz-do-not-send="true"
 href="mailto:laurenz.albe@cybertec.at">laurenz.albe@cybertec.at</a>&gt;
 wrote:<br></div><div class="gmail_quote"><blockquote style="margin:0px 
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" 
class="gmail_quote">Daulat Ram wrote:<br>
&gt; I want to know about the working and importance of shared_buffers  
in Postgresql?<br>
&gt; is it similar to the oracle database buffer cache?<br>
<br>
Yes, exactly.<br>
<br>
The main difference is that PostgreSQL uses buffered I/O, while Oracle 
usually<br>
uses direct I/O.<br>
<br>
Usually you start with shared_buffers being the minimum of a quarter of 
the<br>
available RAM and 8 GB.<br></blockquote><div><br></div><div>Any good 
rule of thumb or write up about when shared buffers in excess of 8GBs 
makes sense (assuming system ram 64+ GBs perhaps)?</div></div></div>

  </div>
</blockquote>
<br>
</body></html>

--------------080501070200030603080708--