Re: Performance Tuning

On Wednesday 09 February 2005 05:08 pm, Merlin Moncure 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.
>
> Yes, many apps are not I/O bound (mine isn't). Here are factors that
> are likely to make your app CPU bound:
>
> 1. Your cache hit ratio is very high
> 2. You have a lot of concurrency.
> 3. Your queries are complex, for example, doing sorting or statistics
> analysis

For now, it's number 3. Relatively low usage, but very complex sql.

> 4. Your queries are simple, but the server has to process a lot of them
> (transaction overhead becomes significant) sequentially.
> 5. You have context switching problems, etc.
>
> On the query side, you can tune things down considerably...try and keep
> sorting down to a minimum (order on keys, avoid distinct where possible,
> use 'union all', not 'union'). Basically, reduce individual query time.
>
> Other stuff:
> For complex queries, use views to cut out plan generation.
> For simple but frequently run queries (select a,b,c from t where k), use
> parameterized prepared statements for a 50% cpu savings, this may not be
> an option in some client interfaces.

Prepared statements are not something we've tried yet. Perhaps we should look
into that in cases where it makes sense.

>
> On the hardware side, you will get improvements by moving to Opteron,
> etc.
>
> Merlin

Well, that's what we were looking for.

---

It sounds like our configuration as it stands is probably about as good as we
are going to get with the hardware we have at this point.

We are cpu bound reflecting the fact that we tend to have complex statements
doing aggregates, sorts and group bys.

The solutions appear to primarily be:
1. Going to faster hardware of which probably Opterons would be about the only
choice. And even that probably won't be a huge difference.
2. Moving to more materialized views and prepared statements where we can.
3. Continue to tweak the sql behind our app.