Re: PostgreSQL Connections?

Ashish Karalkar wrote:
> Thanks Shane for your replay,
> It was by mistake , I have multiple clients,my server
> IP is 155, having a web server, what we are doing is
> using a java pool. and yes we are following the method
> to close the connection immediatly after its work and
> for next work pick up the new connection from pool, we
> are using jboss as web application server. Point of
> worry is that my server has many idle process on its
> own IP .

That is the whole point of the connection pools.
When a client connection is created there are things like allocating
memory and authentication etc that take a certain amount of time to do.
The connection pool opens a number of connections (or opens as needed
until limit is reached) and then gives the client one of these
connections when it asks for a new connection. The connection to
PostgerSQL is established and resources are already allocated so it is
quicker to start using the existing connection than if the connection
was established when asked for.
The connection pool keeps the connection active (in an idle state) when
the client disconnects and uses it to give to the next connection request.

When generating web pages the time overhead of creating new connections
can more than double the time to generate the page making for a very
slow site.

Unless you are hitting a performance limit (needing more concurrent
connections than your pool settings allow) then you will have some
client connections from the pool that are idle.

If the number of idle connections is excessive and you don't expect
traffic to increase/peak then feel free to reduce the number of
connections in your pool settings to reduce the number of idle
connections kept open.

The connections from the server IP would indicate that JBoss is running
on the same machine as Postgresql - that is the machine connecting to
Postgresql or there is some kind of tunnel (such as through ssh) that
means the connections are established locally.


> With regards
> Ashish Karalkar
>
>
> --- Shane Ambler <pgsql@007Marketing.com> wrote:
>
>> Ashish Karalkar wrote:
>>> Hi Shoaib
>>>
>>> Following is the output for ps auxwww | grep
>> ^postgres
>>> IP address of my server is 172.18.5.155
>>> postgres 12846  0.0  0.8 45328 4164 ?        Ss
>>> Jan12   0:00 postgres: qsweb qsweb06jan07
>>> 172.18.4.61(4272) idle
>>> postgres 23335  0.0  0.9 45336 4800 ?        Ss
>>> 11:38   0:00 postgres: qsweb postgres
>>> 172.18.4.16(1313) idle
>>> postgres 23665  0.0  0.8 45204 4260 ?        Ss
>>> 12:13   0:00 postgres: qsweb qsweb12jan2007
>>> 172.18.5.197(4799) idle
>>> postgres 23753  0.0  1.0 45336 5216 ?        Ss
>>> 12:18   0:00 postgres: qsweb postgres
>>> 172.18.4.58(1140) idle
>>> postgres 23761  0.0  1.0 45336 5216 ?        Ss
>>> 12:20   0:00 postgres: qsweb postgres
>>> 172.18.4.135(1214) idle
>>> postgres 23868  0.0  0.8 45204 4260 ?        Ss
>>> 12:30   0:00 postgres: qsweb qsweb12jan2007
>>> 172.18.5.155(37415) idle
>> Will probably need a bit more information here but
>> what I can figure is-
>>
>> You say you have a server and a single client -
>> there are at least 6
>> different ip addresses in the process list you sent.
>>
>> With the multiple client connections from other
>> machines - one is
>> obviously the ip address that you know you are
>> using, can you account
>> for the others?
>> If not then you should be fixing your security
>> settings - either in your
>> pg_hba.conf or a firewall on the server.
>>
>>  From the multiple connections from your server IP I
>> would guess that
>> you are running a web server and using persistent
>> connections of some
>> sort. This will keep each connection open so the
>> next page request
>> doesn't have the overhead of establishing a new
>> connection. The ones
>> that say idle in transaction would be linked to a
>> page being
>> constructed. The idle ones will be waiting for the
>> next page request.
>> This is not a bad thing, although some methods of
>> achieving this are
>> better than others.
>>
>> What client software are you using on your client
>> machine?
>>
>> Does this program exit normally or is it crashing
>> before it closes the
>> connection it has established?
>>
>> Does the one program open more than one connection
>> when it is running?
>>
>> Was this list taken while the client program was
>> running?
>>
>>
>>> --- Shoaib Mir <shoaibmir@gmail.com> wrote:
>>>
>>>> Can you show us the output for:
>>>>
>>>> ps auxwww | grep ^postgres
>>>>
>>>> that should explain a little more...
>>>>
>>>> -------------
>>>> Shoaib Mir
>>>> EnterpriseDB (www.enterprisedb.com)
>>>>
>>>> On 1/10/07, Ashish Karalkar
>>>> <ashish_karalkar@yahoo.com> wrote:
>>>>> Hello All,
>>>>> I am running PostgresSQL 8.2 on Redhat Linux 4.
>>>>> When I look for the processes that are using
>>>> postgres
>>>>> using ps aux|more
>>>>> I got lots of Idle processes with servers own IP
>>>>> address. Can anybody please tell me why this is
>>>>> happening.
>>>>>
>>>>> Also there are multiple processes for my single
>>>>> client(same IP) of which maximum are idle. are
>>>> these
>>>>> processes means connection or there is a single
>>>>> connection starting  many processes.
>>>>>
>> --
>>
>> Shane Ambler
>> pgSQL@007Marketing.com
>>
>> Get Sheeky @ http://Sheeky.Biz
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>>                http://archives.postgresql.org/
>>
>
>
>
>
> ____________________________________________________________________________________
> Don't get soaked.  Take a quick peak at the forecast
> with the Yahoo! Search weather shortcut.
> http://tools.search.yahoo.com/shortcuts/#loc_weather
>
>


--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz