Hi Gerhard
Thanks for the mail
On Thu, Sep 24, 2009 at 7:19 PM, Gerhard Wiesinger
<gerhard@wiesinger.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