Обсуждение: effective_cache_size X shared_buffer
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
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_sizeor shared_buffer? What's the difference between them? ThanksPatrick
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:
Regards,
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_sizeor 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.
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_sizeor shared_buffer? What's the difference between them? ThanksPatrickCan 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