Thanks for all the feedback. To clear it up, we are definitely not CPU bound at the moment. Any slowdown seems to be disk dependant, or from to serialization due to a long query (due to disk).
We do have a lot of INSERT/UPDATE calls, specifically on tables that track user sessions, then of course things like comments, etc (where we'll see 10-30 INSERT's per second, with TEXT field, and hundreds of reads per second). Additionally, our system does use a lot of aggregate functions. I'll look into materialized views, it sounds like it may be worth implementing. One question I do have though - you specifically mentioned NOW() as something to watch out for, in that it's mutable. We typically use COUNT() as a subselect to retrieve the number of associated rows to the current query. Additionally, we use NOW a lot, primarily to detect the status of a date, i.e.: SELECT id FROM subscriptions WHERE userid = 11111 AND timeend > NOW(); Is there a better way to do this? I was under the impression that NOW() was pretty harmless, just to return a current timestamp. Based on feedback, I'm looking at a minor upgrade of our RAID controller to a 3ware 9000 series (SATA with cache, battery backup optional), and re-configuring it for RAID 10. It's a damn cheap upgrade at around $350 and an hour of downtime, so I figure that it's worth it for us to give it a shot. Thanks, Jason -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Merlin Moncure Sent: Wednesday, August 11, 2004 1:04 PM To: [EMAIL PROTECTED] Cc: Postgresql Performance; [EMAIL PROTECTED] Subject: Re: [PERFORM] Hardware upgrade for a high-traffic database > On Wed, 2004-08-11 at 17:51, Merlin Moncure wrote: > > > 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. > > I thought materialized views support in pgsql was experimental as yet. > Are the pg mat-view code upto production servers? Also, do you have to > delete mat-views before you dump the db or does dump automatically not > dump the mat-views data? I think you are thinking about 100% 'true' materialized views. In that case the answer is no, the server does not have them. The GeneralBits article describes how to emulate them through pl/sql triggers. I just bumped into the article yesterday and was very impressed by it...I have to admin though Note: I have never tried the method, but it should work. I cc'd the author who perhaps might chime in and tell you more about them. Materialized views can give performance savings so good that the tpc people had to ban them from benchmarks because they skewed results...:) In postgres, they can help a lot with aggregates...there are many gotchas tho, for example keeping a count() up to date can get kind of tricky. If you can get them to work, the filesystem cache efficiency will rocket upwards...YMMV. Getting back on topic, I missed the original post where the author stated his problems were i/o related, not cpu (contrary to my speculation). I wonder what his insert/update load is? Merlin ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html