Re: To keep indexes in memory, is large enough effective_cache_size enough?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi!

Thanks for all of the comments!

David wrote:
> if you mention
> how muchRAM the server has and how big the data is now

Let's say for example:
RAM: 64 GB
Data: 500 GB - 1.5 TB, for example.

( RAM: Less would of course be better, e.g. 32 GB, but we could maybe go for an even little bit bigger value than 64 GB, if needed to. )

BR Sam

On Wednesday, September 19, 2018 1:11 PM, David Rowley <david.rowley@xxxxxxxxxxxxxxx> wrote:


On 19 September 2018 at 21:18, Sam R. <samruohola@xxxxxxxxx> 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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux