Re: Hardware upgrade for a high-traffic database - Mailing list pgsql-performance

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: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Merlin Moncure
Sent: Wednesday, August 11, 2004 1:04 PM
To: skpobox@gawab.com
Cc: Postgresql Performance; jgardner@jonathangardner.net
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 majordomo@postgresql.org)