Skip site navigation (1) Skip section navigation (2)

Re: PosgreSQL hogging resources?

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Jeremy Smith <jer(at)highboard(dot)com>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: PosgreSQL hogging resources?
Date: 2004-02-20 20:56:39
Message-ID: Pine.LNX.4.33.0402201355210.11556-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-admin
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
> 


In response to

pgsql-admin by date

Next:From: kaolin fireDate: 2004-02-20 22:05:49
Subject: Re: WAL logs and segment files
Previous:From: Jeremy SmithDate: 2004-02-20 20:53:37
Subject: Re: PosgreSQL hogging resources?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group