From: David Rowley <david.rowley@2ndquadrant.com>
To: Sam R. <samruohola@yahoo.com>
Cc: Sergei Kornilov <sk@zsrv.org>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: To keep indexes in memory, is large enough effective_cache_size enough?
Date: Wed, 19 Sep 2018 22:11:08 +1200
Message-ID: <CAKJS1f_rMSNu2atjj09f6i-GGOavqxKy8F_J5pO2qHDa8+4Mgw@mail.gmail.com> (raw)
In-Reply-To: <1476619436.6435500.1537348704720@mail.yahoo.com>
References: <1321418371.5650982.1537346138600.ref@mail.yahoo.com>
<1321418371.5650982.1537346138600@mail.yahoo.com>
<5456011537348252@sas1-fed4e4c8a570.qloud-c.yandex.net>
<1476619436.6435500.1537348704720@mail.yahoo.com>
On 19 September 2018 at 21:18, Sam R. <samruohola@yahoo.com> wrote:
> Ok. So, we should set also shared_buffers big.
It might not be quite as beneficial as you might think. If your
database is larger than RAM often having a smaller shared_buffers
setting yields better performance. The reason is that if you have a
very large shared_buffers that the same buffers can end up cached in
the kernel page cache and shared buffers. If you have a smaller shared
buffers setting then the chances of that double buffering are reduced
and the chances of finding a page cached somewhere increases.
However, if your database is quite small and you can afford to fit all
your data in shared buffers, with enough free RAM for everything else,
then you might benefit from a large shared buffers, but it's important
to also consider that some operations, such as DROP TABLE can become
slow of shared buffers is very large.
You might get more specific recommendations if you mention how much
RAM the server has and how big the data is now and will be in the
future.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: david.rowley@2ndquadrant.com, samruohola@yahoo.com, sk@zsrv.org, pgsql-performance@lists.postgresql.org
Subject: Re: To keep indexes in memory, is large enough effective_cache_size enough?
In-Reply-To: <CAKJS1f_rMSNu2atjj09f6i-GGOavqxKy8F_J5pO2qHDa8+4Mgw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox