Thread: To keep indexes in memory, is large enough effective_cache_sizeenough?

Hi

effective_cache_size is not cache. It is just approx value for query planner: how many data can be found in RAM (both
inshared_buffers and OS page cache)
 

> Q: Size of shared_buffers does not matter regarding keeping index in memory?
shared_buffers is cache for both tables and indexes pages. All data in tables and indexes are split to chunks 8 kb each
-pages (usually 8kb, it can be redefined during source compilation).
 
Shared buffers cache is fully automatic, active used pages keeps in memory, lower used pages may be evicted. You can
notpin any table or index to shared buffers.
 

regards, Sergei

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

On 19 September 2018 at 22:12, Kaixi Luo <kaixiluo@gmail.com> wrote:
> Does a large shared_buffers impact checkpoint performance negatively? I was
> under the impression that everything inside shared_buffers must be written
> during a checkpoint.

Only the dirty buffers get written.

Also having too small a shared buffers can mean that buffers must be
written more than they'd otherwise need to be. If a buffer must be
evicted from shared buffers to make way for a new buffer then the
chances of having to evict a dirty buffer increases with smaller
shared buffers. Obviously, this dirty buffer needs to be written out
before the new buffer can be loaded in. In a worst-case scenario, a
backend performing a query would have to do this.  pg_stat_bgwriter is
your friend.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 20 September 2018 at 15:19, Wei Shan <weishan.ang@gmail.com> wrote:
> I believe you can use pg_prewarm to pin index or table to cache.
>
> https://www.postgresql.org/docs/current/static/pgprewarm.html

I think the key sentence in the document you linked to is:

"Prewarmed data also enjoys no special protection from cache
evictions, so it is possible that other system activity may evict the
newly prewarmed blocks shortly after they are read"

So this is not pinning. It's merely loading buffers into shared
buffers in the hope that they might be around long enough for you to
make the most of that effort.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Tue, 25 Sep 2018 at 18:36, Sam R. <samruohola@yahoo.com> wrote:
> Regarding double buffering: I do not know how much double buffering would slow down operations.
> It could also be possible to turn off kernel page cache on our DB server, to avoid double buffering. Although, we may
stillkeep it in use.
 

I think you've misunderstood double buffering.  The double buffering
itself does not slow anything down. If the buffer is in shared buffers
already then it does not need to look any further for it. Double
buffering only becomes an issue when buffers existing 2 times in
memory causes other useful buffers to appear 0 times.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 28 September 2018 at 16:45, Sam R. <samruohola@yahoo.com> wrote:
> That was what I was suspecting a little. Double buffering may not matter in
> our case, because the whole server is meant for PostgreSQL only.
>
> In our case, we can e.g. reserve almost "all memory" for PostgreSQL (shared
> buffers etc.).
>
> Please correct me if I am wrong.

You mentioned above:

> RAM: 64 GB
> Data: 500 GB - 1.5 TB, for example.

If most of that data just sits on disk and is never read then you
might be right, but if the working set of the data is larger than RAM
then you might find you get better performance from smaller shared
buffers.

I think the best thing you can go and do is to go and test this. Write
some code that mocks up a realistic production workload and see where
you get the best performance.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services