The Effectiveness of effective_cache_size

The Effectiveness of effective_cache_size

bruce.momjian's picture
Author: Bruce Momjian

Friday, May 4, 2012

Having reported the methods for finding the size of the kernel cache on Linux, I wish to highlight the importance of the postgresql.conf setting effective_cache_size.

Unlike other memory settings that control how memory is allocated, effective_cache_size tells the optimizer how much cache is present in the kernel. This is important for determining how expensive large index scans will be. The optimizer knows the size of shared_buffers, but not the kernel cache size, which affects the probability of expensive disk access.

The kernel cache size changes frequently, so run free during a period of normal system load and use that value to set effective_cache_size. The value doesn't have to be perfect, but just a rough estimate of how much kernel memory is acting as secondary cache for the shared buffers.