Skip site navigation (1) Skip section navigation (2)

Re: New server to improve performance on our large and busy DB - advice?

From: "Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: New server to improve performance on our large and busy DB - advice?
Date: 2010-01-22 17:37:56
Message-ID: hjcnp4$m8f$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-performance
Hi Greg,

As a follow up to this suggestion:

>> I don't see effective_cache_size listed there.  If that's at the default, 
>> I wouldn't be surprised that you're seeing sequential scans instead of 
>> indexed ones far too often.

I found an article written by you 
http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm and thought 
this was pretty useful, and especially this comment:

<<effective_cache_size should be set to how much memory is leftover for disk 
caching after taking into account what's used by the operating system, 
dedicated PostgreSQL memory, and other applications. If it's set too low, 
indexes may not be used for executing queries the way you'd expect. Setting 
effective_cache_size to 1/2 of total memory would be a normal conservative 
setting. You might find a better estimate by looking at your operating 
system's statistics. On UNIX-like systems, add the free+cached numbers from 
free or top. On Windows see the "System Cache" in the Windows Task Manager's 
Performance tab.
>>

Are these values to look at BEFORE starting PG? If so, how do I relate the 
values returned to setting the effective_cache_size values?

Carlo

PS Loved your 1995 era pages. Being a musician, it was great to read your 
recommendations on how to buy these things called "CD's". I Googled the 
term, and they appear to be some ancient precursor to MP3s which people 
actually PAID for. What kind of stone were they engraved on? ;-D



In response to

Responses

pgsql-performance by date

Next:From: Tory M BlueDate: 2010-01-22 17:59:34
Subject: Re: Data Set Growth causing 26+hour runtime, on what we believe to be very simple SQL
Previous:From: DMDate: 2010-01-22 17:10:48
Subject: Re: Fragmentation/Vacuum, Analyze, Re-Index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group