effective_cache_size X shared_buffer

2017-04-03 13:23 GMT+12:00 Patrick B <patrickbakerbr@gmail.com>:

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?

Thanks
Patrick

Can I also increase shared_buffer on my slave only? Would that make any
difference if using selects on the slave? Or this parameter must be the
same across all servers (Master/slaves) ?

Thanks
P.

On Mon, Apr 3, 2017 at 11:23 AM, Patrick B <patrickbakerbr@gmail.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

On Mon, Apr 3, 2017 at 1:33 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

2017-04-03 13:23 GMT+12:00 Patrick B <patrickbakerbr@gmail.com>:

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?

Thanks
Patrick

Can I also increase shared_buffer on my slave only? Would that make any
difference if using selects on the slave? Or this parameter must be the
same across all servers (Master/slaves) ?

Yes, you can increase the shared_buffers in the slave database.
Configuration can be different across master and slave databases.

Regards,

Venkata B N
Database Consultant