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 ) 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 ) 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 ) 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 ) 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 User-Agent: Postbox 5.0.25 (Windows/20180328) MIME-Version: 1.0 To: Justin Pryzby CC: Michael Lewis , Laurenz Albe , Daulat Ram , "pgsql-performance@lists.postgresql.org" Subject: Re: Shared_buffers References: <56099ac31c7cc1a4e8a51963e2674e7fe592b86f.camel@cybertec.at> <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: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: 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 > 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 > 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 > Tuesday, March 12, 2019 3:23 PM > On Tue, Mar 12, 2019 at 2:29 AM Laurenz Albe > 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 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

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
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


Tuesday, March 12, 2019 3:23 PM
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)?

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