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

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