Re: Question on Opteron performance

nw(at)codon(dot)com writes:

> > What sort of load is "heavy load" to you?
>
> If I recall from today's loads, we were getting about 50 queries per
> second from the pool of front-end servers. Obviously, whether 50 queries
> per second is "heavy" depends on the type of queries, these were enough
> to push the 5-minute system loads up into the 0.8 range. In our application,
> once we exceed a system load of about 0.9, we start seeing enough slowdown
> that it does become noticeable. Not always very significant, but noticeable.

The only time I've seen high cpu and memory bandwidth load with near-zero i/o
load like you describe was on Oracle and it turned out to be an sql
optimization problem.

What caused it was a moderate but not very large table on which a very
frequent query was doing a full table scan (= sequential scan). The entire
table was easily kept in cache, but it was large enough that merely scanning
every block of it in the cache consumed a lot of cpu and memory bandwidth. I
don't remember how large, but something on the order of a few thousand records.

The query still ran reasonably fast, but much slower than it ought to have
been. I don't remember numbers, it was probably something like 200ms instead
of 20ms. Plenty of other queries were in the 200ms range but due to normal i/o
delays. 200ms is a lot more cpu/memory usage than it is i/o usage, enough to
hog those resources and slow down the entire system but not show up on our
lists of top slow queries.

I don't know if your problem is anything similar, and I'm not even sure where
I would start to find a problem like this in postgres. In Oracle I could sort
the query cache by "total logical buffer gets" which basically translated into
memory bandwidth consumed for all executions of the query. That produces very
different results than looking at the queries sorted by the time they take to
execute.

--
greg