> 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.
Every so often, I log all queries that are issued, and on a seperate
machine, I EXPLAIN them and store the results in a database, so I can do
analysis on them. Each time, we look at what's using the greatest amount
of resources, and attack that. Believe me, the "low-hanging fruit" like
using indexes instead of sequential scans were eliminated years ago. : )
Over the past four years, our traffic has increased, on average, about
90% per year. We've also incorporated far more sources of data into our
model, and come up with far more ways to use the data. When you're
talking about exponential traffic growth combined with exponential data
complexity, it doesn't take long before you start hitting limits!
Before I shell out the $15k on the 4-way Opteron, I'm going to spend
some long, hard time looking for ways to make the system more efficient.
However, after all that's already been done, I'm not optimistic that it's
going to preclude needing the new server. I'm just surprised that nobody
seems to have used PostgreSQL on a quad-Opteron before!
steve
