Re: Database size growing over time and leads to performance impact

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