Re: effective_cache_size X shared_buffer

On Mon, Apr 3, 2017 at 11:23 AM, Patrick B <patrickbakerbr(at)gmail(dot)com> wrote:

> Hi guys.
>
> I'm thinking about increasing the query cache for my PG 9.2 server.
> I've got a project happening, which is doing lots and lots of writes and
> reads during the night, and in the morning I see PG cache warming up again,
> as all the cache "was used" by those write and read tasks.
>
> So my environment gets very slow for a few hours, until the queries used
> on a daily basis go to the cache.
>
> Question:
> Should I increase effective_cache_size or shared_buffer? What's the
> difference between them?
>

shared_buffers is the main memory component of PostgreSQL, where-in the
data accessed by queries is retrieved from the disk is stored so that
subsequent requests for the same data blocks will result in memory read. In
short, first read of a data block is the disk-read and most of the
subsequent reads will be from memory. Increasing this parameter will
increase the possibility of more memory reads.

effective_cache_size is important to improve performance of the select
queries. This parameter can be configured upto 75-80% of the RAM
(configuring to a much bigger size is not harmful). This parameter is the
scope of search for the data blocks required by SELECTs

It all depends on various factors like database size, size of the data
being accessed, database server hardware, RAM size etc. You have not
provided any such details.

Regards,

Venkata B N
Database Consultant