pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPosgreSQL hogging resources?
9+ messages / 4 participants
[nested] [flat]
* PosgreSQL hogging resources?
@ 2004-02-20 18:39 Jeremy Smith <jer@highboard.com>
0 siblings, 3 replies; 9+ messages in thread
From: Jeremy Smith @ 2004-02-20 18:39 UTC (permalink / raw)
To: pgsql-admin
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?
Thanks alot,
Jeremy
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PosgreSQL hogging resources?
@ 2004-02-20 18:43 scott.marlowe <scott.marlowe@ihs.com>
parent: Jeremy Smith <jer@highboard.com>
2 siblings, 0 replies; 9+ messages in thread
From: scott.marlowe @ 2004-02-20 18:43 UTC (permalink / raw)
To: Jeremy Smith <jer@highboard.com>; +Cc: pgsql-admin
On Fri, 20 Feb 2004, 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?
Don't worry about sleeping processes, you should have a good hundred
sleeping on any unix box. My workstation has 152, my server has 173, and
the response time on both is way sub second.
Now, about postgresql, what is it doing when it's chewing up 100% cpu?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PosgreSQL hogging resources?
@ 2004-02-20 18:48 Lamar Owen <lowen@pari.edu>
parent: Jeremy Smith <jer@highboard.com>
2 siblings, 1 reply; 9+ messages in thread
From: Lamar Owen @ 2004-02-20 18:48 UTC (permalink / raw)
To: jer@highboard.com; pgsql-admin
On Friday 20 February 2004 01:39 pm, 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+.
Not too bad.
> 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.
That would be good.
> 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?
You don't. Sleeping processes is a normal thing with a multitasking OS.
How fast does the page load? That would be the big question. Run apache
bench (ab) against the page and see how many pages per second yu can get. A
load of 2.0, an average CPU of 60-100%, and 7 running processes is not bad at
all. It just means your server is working.
--
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC 28772
(828)862-5554
www.pari.edu
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PosgreSQL hogging resources?
@ 2004-02-20 18:51 scott.marlowe <scott.marlowe@ihs.com>
parent: Lamar Owen <lowen@pari.edu>
0 siblings, 0 replies; 9+ messages in thread
From: scott.marlowe @ 2004-02-20 18:51 UTC (permalink / raw)
To: Lamar Owen <lowen@pari.edu>; +Cc: jer@highboard.com; pgsql-admin
On Fri, 20 Feb 2004, Lamar Owen wrote:
> How fast does the page load? That would be the big question. Run apache
> bench (ab) against the page and see how many pages per second yu can get. A
> load of 2.0, an average CPU of 60-100%, and 7 running processes is not bad at
> all. It just means your server is working.
That really depends on the server. If it's a PII-266 then it's about
right, if it's a dual AMD Athlon 2800 with 2 gigs of ram something's
horribly wrong. So, what kind of hardware is this jer?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PosgreSQL hogging resources?
@ 2004-02-20 18:57 Mitch Pirtle <mitchy@spacemonkeylabs.com>
parent: Jeremy Smith <jer@highboard.com>
2 siblings, 1 reply; 9+ messages in thread
From: Mitch Pirtle @ 2004-02-20 18:57 UTC (permalink / raw)
To: jer@highboard.com; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PosgreSQL hogging resources?
@ 2004-02-20 19:12 Jeremy Smith <jer@highboard.com>
parent: Mitch Pirtle <mitchy@spacemonkeylabs.com>
0 siblings, 2 replies; 9+ messages in thread
From: Jeremy Smith @ 2004-02-20 19:12 UTC (permalink / raw)
To: pgsql-admin
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@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Mitch Pirtle
Sent: Friday, February 20, 2004 1:57 PM
To: jer@highboard.com
Cc: pgsql-admin@postgresql.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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PosgreSQL hogging resources?
@ 2004-02-20 20:24 Mitch Pirtle <mitchy@spacemonkeylabs.com>
parent: Jeremy Smith <jer@highboard.com>
1 sibling, 1 reply; 9+ messages in thread
From: Mitch Pirtle @ 2004-02-20 20:24 UTC (permalink / raw)
To: jer@highboard.com; +Cc: pgsql-admin
Jeremy Smith wrote:
>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.
>
When you switch to pg, you will be able to move some/lots? of your code
into the database as views, stored procedures, triggers etc... So keep
that in mind while working in MySQL, as you will definitely need a
different approach.
I inherited a site that had very database-hungry pages, and started
using stored procs and triggers to automate some of the jobs (like
updates and such) in the database, instead of making the webserver
manually send the instructions across the wire. If you plan on taking
this approach then you should get your app into pg sooner than later...
-- Mitch, wondering about 12 packs?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PosgreSQL hogging resources?
@ 2004-02-20 20:53 Jeremy Smith <jer@highboard.com>
parent: Mitch Pirtle <mitchy@spacemonkeylabs.com>
0 siblings, 0 replies; 9+ messages in thread
From: Jeremy Smith @ 2004-02-20 20:53 UTC (permalink / raw)
To: pgsql-admin
Mitch,
One of the issues with my site is that many of the pages use iframes, so I
am actually loading up to 7 pages of varying database hungriness into the
browser at the same time. I agree that pg with the triggers and procedures
should be able to adequately compete with mysql in terms of speed. The
problem I am having is the complexity of the existing code combined with the
short amount of time that I have to get it to work. Hopefully next
"offseason" I won't have as many new features do add and so will be able to
concentrate on optimization..
Anyway, I'm going to keep tinkering with pg offsite and I very much
appreciate all of the help and attention that I have been given from this
list.
Thanks,
Jeremy
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Mitch Pirtle
Sent: Friday, February 20, 2004 3:24 PM
To: jer@highboard.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PosgreSQL hogging resources?
Jeremy Smith wrote:
>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.
>
When you switch to pg, you will be able to move some/lots? of your code
into the database as views, stored procedures, triggers etc... So keep
that in mind while working in MySQL, as you will definitely need a
different approach.
I inherited a site that had very database-hungry pages, and started
using stored procs and triggers to automate some of the jobs (like
updates and such) in the database, instead of making the webserver
manually send the instructions across the wire. If you plan on taking
this approach then you should get your app into pg sooner than later...
-- Mitch, wondering about 12 packs?
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PosgreSQL hogging resources?
@ 2004-02-20 20:56 scott.marlowe <scott.marlowe@ihs.com>
parent: Jeremy Smith <jer@highboard.com>
1 sibling, 0 replies; 9+ messages in thread
From: scott.marlowe @ 2004-02-20 20:56 UTC (permalink / raw)
To: Jeremy Smith <jer@highboard.com>; +Cc: 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@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Mitch Pirtle
> Sent: Friday, February 20, 2004 1:57 PM
> To: jer@highboard.com
> Cc: pgsql-admin@postgresql.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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2004-02-20 20:56 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-20 18:39 PosgreSQL hogging resources? Jeremy Smith <jer@highboard.com>
2004-02-20 18:43 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-20 18:48 ` Lamar Owen <lowen@pari.edu>
2004-02-20 18:51 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-20 18:57 ` Mitch Pirtle <mitchy@spacemonkeylabs.com>
2004-02-20 19:12 ` Jeremy Smith <jer@highboard.com>
2004-02-20 20:24 ` Mitch Pirtle <mitchy@spacemonkeylabs.com>
2004-02-20 20:53 ` Jeremy Smith <jer@highboard.com>
2004-02-20 20:56 ` scott.marlowe <scott.marlowe@ihs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox