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

От: Andy Colson
Тема: Re: Database size growing over time and leads to performance impact
Дата: ,
Msg-id: 4BB20196.2050301@squeakycode.net
(см: обсуждение, исходный текст)
Ответ на: Re: Database size growing over time and leads to performance impact  ("Gnanakumar")
Список: pgsql-performance

Скрыть дерево обсуждения

Database size growing over time and leads to performance impact  ("Gnanakumar", )
 Re: Database size growing over time and leads to performance impact  (Andy Colson, )
  Re: Database size growing over time and leads to performance impact  ("Gnanakumar", )
   Re: Database size growing over time and leads to performance impact  (Andy Colson, )
  Re: Database size growing over time and leads to performance impact  (Scott Carey, )
   Re: Database size growing over time and leads to performance impact  (Robert Haas, )
    Re: Database size growing over time and leads to performance impact  (Scott Carey, )
     Re: Database size growing over time and leads to performance impact  (Tom Lane, )
      Re: Database size growing over time and leads to performance impact  (Scott Carey, )
   Re: Database size growing over time and leads to performance impact  (Alvaro Herrera, )
 Re: Database size growing over time and leads to performance impact  ("Pierre C", )
  Re: Database size growing over time and leads to performance impact  (Greg Smith, )
 Re: Database size growing over time and leads to performance impact  (Greg Smith, )

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



В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: temp table "on commit delete rows": transaction overhead
От: Robert Haas
Дата:
Сообщение: Re: experiments in query optimization