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.