Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 5BBE0633A4F
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 30 Mar 2010 10:50:28 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 99833-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 30 Mar 2010 13:50:17 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from marajade.localdomain (vcs5.camavision.com [63.228.164.252])
	by mail.postgresql.org (Postfix) with ESMTP id 919C0632330
	for <pgsql-performance@postgresql.org>;
	Tue, 30 Mar 2010 10:50:17 -0300 (ADT)
Received: from [192.168.10.84] (unknown [192.168.10.84])
	(Authenticated sender: andy)
	by marajade.localdomain (Postfix) with ESMTPA id B6B9540B83;
	Tue, 30 Mar 2010 08:50:15 -0500 (CDT)
Message-ID: <4BB20196.2050301@squeakycode.net>
Date: Tue, 30 Mar 2010 08:50:14 -0500
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.1.8) Gecko/20100227 Lightning/1.0b1 Thunderbird/3.0.3
MIME-Version: 1.0
To: Gnanakumar <gnanam@zoniac.com>, pgsql-performance@postgresql.org
Subject: Re: Database size growing over time and leads to performance
 impact
References: <004701cacdad$7013e9f0$503bbdd0$@com>
	<4BAE09AE.9020305@squeakycode.net>
	<011e01cacffa$a780f320$f682d960$@com>
In-Reply-To: <011e01cacffa$a780f320$f682d960$@com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.185 tagged_above=-10 required=5
	tests=BAYES_40=-0.185
X-Spam-Level: 
X-Archive-Number: 201003/385
X-Sequence-Number: 38210

On 3/30/2010 6:17 AM, Gnanakumar wrote:
> We're using pgpool-II version 2.0.1 for PostgreSQL connection management.
>
> pgpool configurations are:
> num_init_children = 450
> child_life_time = 300
> connection_life_time = 120
> child_max_connections = 30
>
> As you recommended, I ran "ps -ax|grep postgres" at almost a busy
> transaction time and I can find "idle" entries:
> [root@newuser ~]# ps -ax|grep postgres
>   2664 ?        Ss     0:00 postgres: newuser mydb 192.168.0.200(43545) idle
>   2783 ?        Ss     0:00 postgres: newuser mydb 192.168.0.200(43585) idle
>   2806 ?        Ss     0:02 postgres: newuser mydb 192.168.0.200(43588) idle
>   2807 ?        Ss     0:01 postgres: newuser mydb 192.168.0.200(43589) idle
>   2818 ?        Ss     0:00 postgres: newuser mydb 192.168.0.200(43601) idle
>   2819 ?        Ss     0:00 postgres: newuser mydb 192.168.0.200(43602) idle
>   2833 ?        Ss     0:02 postgres: newuser mydb 192.168.0.200(43603) idle
>   2856 ?        Ss     0:03 postgres: newuser mydb 192.168.0.200(43614) idle
>
> Based on pgpool documentation, and also as far as I know, even though
> application layer returns/closes the application, pgpool will only handle
> actual closing of connections based on the connection_life_time parameter
> defined.  And if this timeout, it goes to "wait for connection request"
> state.
>
> Can you throw some light on this?  Is there any better way that we need to
> re-configure our pgpool parameters?
>

Connections are ok.  Connection is different than transaction.  The 
output above looks good, that's what you want to see.  (If it had said 
"idle in transaction" that would be a problem).  I dont think you need 
to change anything.

Hopefully just vacuuming more often will help.

-Andy