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

Re: High CPU load on Postgres Server during Peak times!!!!

From: Andy Colson <andy(at)squeakycode(dot)net>
To: Shiva Raman <raman(dot)shivag(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: High CPU load on Postgres Server during Peak times!!!!
Date: 2009-09-23 18:53:14
Message-ID: 4ABA6E9A.6090607@squeakycode.net (view raw or flat)
Thread:
Lists: pgsql-performance
Shiva Raman wrote:
> /If you run a 'ps ax|grep post' do you see anything that says 'idle in 
> transaction'? (I hope that old of version will show it. my processes 
> show up as postgres not postmaster)/
> 
> 
> Lots of requests shows as 'idle in transaction'.
> 

Eww.  I think that's bad.  A connection that has a transaction open will 
cause lots of row versions, which use up ram, and make it slower to step 
through the table (even with an index).  You really need to fix up your 
code and make sure you commit transactions.  (any statement (select, 
insert, update) will start a new transaction that you need to explicitly 
commit).


> 
> Currently i am restarting the database using a cron job every 30 minutes 
> during offpeak time
> 
> and every 15 minutes during the peak time.

do you get lots of update/deletes?  Or are there mostly selects?  If its 
mostly update/delete then the 'idle in transactions' is killing you.  If 
you have mostly selects then its probably something else.


> work_mem = 3000 ( 3000 * max connections * 1024 = 3000 * 1800 * 1024 = 
> 5529 MB ( this is the working memory for postgres) )

work_mem is per connection.  If you changed this to get a better query 
plan then ok, but dont change it just for the sake of changing it. 
Ick... I just went back and checked, you have 16G of ram... this 
probably isn't a problem.  Nevermind.


-Andy

In response to

pgsql-performance by date

Next:From: Fernando HeviaDate: 2009-09-23 20:50:08
Subject: Re: High CPU load on Postgres Server during Peak times!!!!
Previous:From: Shiva RamanDate: 2009-09-23 18:28:39
Subject: Re: High CPU load on Postgres Server during Peak times!!!!

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