Hello, I followed this advice for picking a good effective_cache_size value (below) from Scott Marlowe, and run into a bit of trouble: I looked at the `top' output and saw "721380k cached". So I calculated the effective cache size using Scott's formula: 721380/8 = 90172 Then I changed my effective_cache size from the previous 10000 to 90172: effective_cache_size = 90172 # typically 8KB each I restarted PG, and thought I'd see some performance improvements, but I actually saw degradation in performance. All of a sudden a query that took a second started taking a few seconds, and consumed more CPU than before. Can anyone explain this drop in performance and increase in CPU usage and tell me what I did wrong? I also noticed that the "721380k cached" number in top dropped to about 300000k (about a half). Maybe that was simply due to PG restart? If so, does that indicate the kernel had about 400,000K worth of PG data cached? Thanks, Otis --- Scott Marlowe <smarlowe@xxxxxxxxxxxxxxxxx> wrote: > Effective cache size just tells the query planner about how much > memory > the OS is using to cache your dataset. > > Bring the machine up, run lots of queries, and check the cache and > buffers with top, and there's your amount. divide by 8k to get the > setting for effective cache size.
