From: Andy Colson <andy@squeakycode.net>
To: Gnanakumar <gnanam@zoniac.com>
To: pgsql-performance@postgresql.org
Subject: Re: Database size growing over time and leads to performance impact
Date: Tue, 30 Mar 2010 08:50:14 -0500
Message-ID: <4BB20196.2050301@squeakycode.net> (raw)
In-Reply-To: <011e01cacffa$a780f320$f682d960$@com>
References: <004701cacdad$7013e9f0$503bbdd0$@com>
<4BAE09AE.9020305@squeakycode.net>
<011e01cacffa$a780f320$f682d960$@com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andy@squeakycode.net, gnanam@zoniac.com
Subject: Re: Database size growing over time and leads to performance impact
In-Reply-To: <4BB20196.2050301@squeakycode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox