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

Carlo Stonebanks wrote:
>
> <<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?
>
After starting the database. You can set effective_cache_size to a size
in megabytes, so basically you'd look at the amount of free cache, maybe
round down a bit, and set effective_cache_size to exactly that. It's
not super important to get the number right. The point is that the
default is going to be a tiny number way smaller than the RAM in your
system, and even getting it within a factor of 2 or 3 of reality will
radically change some types of query plans.

> 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

They're plastic, just like the iPod, iPhone, iToilet, or whatever other
white plastic Apple products people listen to music during this new
era. Since both my CD collection and the stereo I listen to them on are
each individually worth more than my car, it's really tough to sell me
on all the terrible sounding MP3s I hear nowadays. I'm the guy who can
tell you how the LP, regular CD, gold CD, and SACD/DVD-A for albums I
like all compare, so dropping below CD quality is right out. If you
ever find yourself going "hey, I wish I had six different versions of
'Dark Side of the Moon' around so I could compare the subtle differences
in the mastering and mix on each of them", I'm your guy.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com