> The issue that I think we're seeing is that the performance on the
3Ware
> RAID is quite bad, watching FreeBSD systat will show it at "100% busy"
at
> around "3.5 MB/s". When it needs to seek across a table (for, say, an
> aggregate function - typically a COUNT()), it slows the entire server
down
> while working on the disk. Additionally, VACUUM's make the server
> practically useless. We have indexes on everything that's used in
> queries,
> and the planner is using them.
It sounds to me like your application is CPU bound, except when
vacuuming...then your server is just overloaded. A higher performance
i/o system will help when vacuuming and checkpointing but will not solve
the overall problem.
With a (good & well supported) battery backed raid controller you can
turn fsync back on which will help you with your i/o storm issues (plus
the safety issue). This will be particularly important if you follow
my next suggestion.
One thing you might consider is materialized views. Your aggregate
functions are killing you...try to avoid using them (except min/max on
an index). Just watch out for mutable functions like now().
http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html
An application specific approach is to use triggers to keep the data you
need in as close to query form as possible...you can reap enormous
savings particularly if your queries involve 3 or more tables or have
large aggregate scans.
Merlin