> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
