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(at)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