Re: PosgreSQL hogging resources?

Are you getting problems with crashing backends in postgresql and such
showing up? I'm wondering if you have bad memory or something like that.

In my experience, Linux/apache/php/postgresql never crashes, it just goes
unresponsive when you get into severe overload.

Is your database vacuum / analyzed often?

Do you have indexes that are being used?

On Fri, 20 Feb 2004, Jeremy Smith wrote:

> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>