Re: PosgreSQL hogging resources?

I agree that my site is a bit bloated, it has more than 2500 total queries,
but it is a bit more complex of an application that might be readily
apparent. For the curious, this is my site: http://www.xpertleagues.com.
But the issue is that with mysql, at my peak levels last year I had a server
load of 30+ (I know this is horrendous, I am looking into either upgrading
my P4 2.4gig 1gig ram server this year, or distributing across more than one
server) but the site itself never performed as slowly as it is now. And
amazingly considering the server load last year, the server never crashed.
But now I am actually getting complaints on the lagtime, and I only have one
league actively drafting, last year I had 70+ at peak.

I will look into some of the suggestions you have made, the problem is that
I can't do large scale optimization at the moment because I am still adding
features to the site. I just wonder if the best mode of attack would be
switching back to mysql until I have added all of the necessary features,
optimizing the queries and code there, and then switching back to pg at a
later date.

Jeremy

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of Mitch Pirtle
Sent: Friday, February 20, 2004 1:57 PM
To: jer(at)highboard(dot)com
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] PosgreSQL hogging resources?

Jeremy Smith wrote:

>I have newly installed PostgreSQL onto my server, the server's main
function
>is to serve up a fantasy football site that has a tremendous number of
>queries per page. Right now with very low traffic I am seeing a server
load
>of 2.0+. That got me a little concerned, so I looked at "top" and noticed
>that postgres is taking anywhere from 60 - 100 percent of my CPU at any
>given time. There are also 116 sleeping processes out of 123. This all
>seems very bad, do you guys have any idea what might be causing it or how
it
>can be addressed? How do I go about cleaning out the sleeping processes?
>
I agree with Lamar's comments, as well as wondering if it is really
needed to run a 'tremendous number of queries' for each page view...
Some quick solutions could be to determine if you could:

1) make changes to your design to require fewer hits to the database per
page,
2) make a view that provided the information without running so many
separate queries, and/or
3) consider using a caching library like ADOdb to limit the number of
trips to your database

Any combination of these three could significantly reduce the load on
your DB box, as well as provide some huge performance gains. How hard
is your webserver working? Are they running on the same box?

-- Mitch

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html