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

Hi Gerhard

Thanks for the mail

On Thu, Sep 24, 2009 at 7:19 PM, Gerhard Wiesinger <gerhard(at)wiesinger(dot)com>wrote:

> Hello Shiva,
>
> What I see from top (0.0%wa) you don't have any I/O problem but a major CPU
> problem. But this is contrast to iostat where up to 50% of iowait is there
> (sometimes).
>
> I think you have 2 problems:
> 1.) Client applications which don't close the connection. If the
> applications wants persistent connections (for performance reasons), then
> idle postgresql processes are ok. A better approach would be some kind of
> connection pool. What programming language do you use on the web tier?
>

I am using connection pooling on Tomcat Web Server . Total of 500
connections are configured to be handled in the connection pool.

> 2.) Find out queries which produce the high CPU load. (e.g. pg_top). I
> guess there are some very suboptimal queries there. (I guess some indexes
> are missing).
>
You could e.g. set
> log_min_duration_statement = 50 # 50ms, all slower queries are logged
>
> I enabled the min duration statement and i found that allmost ninety
percent of queries are logged which has duration more thatn 50. Most of the
queries ranges between 50 and 500.
Certain Select queuries duration are between 1000 and 2500. And for report
queries with more than 3 lakh and 1 lakh rows , the queries takes more than
6000 ms.

And: Idle connection don't take any I/O and CPU, just memory resources (and
> very small network resources).
>
> And IHMO killing database processes isn't a solution to your problem.
> Database server should nearly never be restarted.
>
> Ciao,
> Gerhard
>

Regards

Shiva Raman

>
>
>