Re: Performance Tuning

> 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
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.

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

Merlin

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.

On Wed, 9 Feb 2005 17:30:41 -0500, Chris Kratz
<chris(dot)kratz(at)vistashare(dot)com> wrote:
> 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.

I'd beg to differ on that last part. The difference between a 3.6GHz
Xeon and a 2.8GHz Opteron is ~150% speed increase on the Opteron on my
CPU bound app. This is because the memory bandwidth on the Opteron is
ENORMOUS compared to on the Xeon. Add to that the fact that you
actually get to use more than about 2G of RAM directly and you've got
the perfect platform for a high speed database on a budget.

> 2. Moving to more materialized views and prepared statements where we can.

Definitely worth investigating. I wish I could, but I can't get my
customers to even consider slightly out of date stats.... :(

> 3. Continue to tweak the sql behind our app.

Short of an Opteron based system, this is by far your best bet.

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org

>> 2. Moving to more materialized views and prepared statements where we
>> can.
>
> Definitely worth investigating. I wish I could, but I can't get my
> customers to even consider slightly out of date stats.... :(

Put a button 'Stats updated every hour', which gives the results in 0.1
seconds, and a button 'stats in real time' which crunches 10 seconds
before displaying the page... if 90% of the people click on the first one
you save a lot of CPU.

Seems like people who hit Refresh every 10 seconds to see an earnings
graph creep up by half a pixel every time... but it seems it's moving !

More seriously, you can update your stats in near real time with a
materialized view, there are two ways :
- ON INSERT / ON UPDATE triggers which update the stats in real time
based on each modification
- Have statistics computed for everything until some point in time (like
an hour ago) and only compute and add stats on the records added or
modified since (but it does not work very well for deleted records...)