Re: Performance Tuning

On Wed, 2005-02-09 at 15:01 -0500, Chris Kratz wrote:
> Hello All,
>
> In contrast to what we hear from most others on this list, we find our
> database servers are mostly CPU bound. We are wondering if this is because
> we have postgres configured incorrectly in some way, or if we really need
> more powerfull processor(s) to gain more performance from postgres.

Not necessarily. I had a very disk bound system, bought a bunch of
higher end equipment (which focuses on IO) and now have a (faster) but
CPU bound system.

It's just the way the cookie crumbles.

Some things to watch for are large calculations which are easy to move
client side, such as queries that sort for display purposes. Or data
types which aren't really required (using numeric where an integer would
do).

> We continue to tune our individual queries where we can, but it seems we still
> are waiting on the db a lot in our app. When we run most queries, top shows
> the postmaster running at 90%+ constantly during the duration of the request.

Is this for the duration of a single request or 90% constantly?

If it's a single request, odds are you're going through much more
information than you need to. Lots of aggregate work (max / min) perhaps
or count(*)'s where an approximation would do?

> Our question is simply this, is it better to invest in a faster processor at
> this point, or are there configuration changes to make it faster? I've done

If it's for a single request, you cannot get single processors which are
much faster than what you describe as having.

Want to send us a few EXPLAIN ANALYZE's of your longer running queries?

--
Rod Taylor <rbt(at)sitesell(dot)com>