[Geoserver-users] PostgreSQL connections

Lee_Wai_See · August 1, 2006, 10:00am

Hi Chris,

I came across this and thought I might just post to this email, as I am not
sure whether I am having the "too many connections" to PostgreSQL problem as
well. The situation now I have is that there are about 7-8 postgres.exe
running at a same time. Pardon me for the question, but is this supposed to
be normal? If not, what could be the reason? I am using GeoServer 1.3.0-RC2,
PostgreSQL 8.0 and PostGIS 1.0.4-1.

Another question: Can I understand how database connections are handled in
GeoServer with PostgreSQL? The reason why I ask this is that I need to do
database updates at a very high frequency (in intervals of seconds), and
thus I am concerned if GeoServer is creating, opening and closing
connections for every single request as this will certainly eats up a lot of
memory for my application. Could you please explain what the mechanism for
PostgreSQL connections is, or is there some documentation which I can refer
to? Or is there a need for me to be worried at all?

Thanks.

Regards,
Lee Wai See
ST Electronics (Info-Software Systems) Pte Ltd

-----Original Message-----
From: geoserver-users-admin@lists.sourceforge.net
[mailto:geoserver-users-admin@lists.sourceforge.net] On Behalf Of Chris
Holmes
Sent: Saturday, May 13, 2006 7:03 AM
To: Javier de la Torre
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] PostgreSQL connections

Could I convince you to maybe try 1.3.0? If it works fine then there's
definitely a bug in GeoTools 2.2.x that we should fix before 1.3.1 official.

thanks!

Chris

Javier de la Torre wrote:

Hi Chris,

I am using 1.3.1 Beta. I am doing just simple WMS getMap requests for a
Google Maps mashup. When viewing a map maybe 20 request are sent to
Geoserver one for each tile to be overlapped in Google maps.

I did not try with the stable 1.3.0 version sorry.

More info. I am running it on Red Hat 9 with PostgreSQL 8.1.3 and
Postgis 1.1.2

Javier.

On 12/05/2006, at 16:51, Chris Holmes wrote:

Yeah, it normally sets up a nice pool and shares all the connections.
If it's going up on every request then something's going wrong. What
operation are you doing? And what version of GeoServer? In the past
a few minor changes have lead to connection pool mess ups, though
generally in the less used operations.

Chris

Javier de la Torre wrote:

Hi,
I am suffering the typical "too many connections" to PostgreSQL
while using Geoserver. Seems that for every new request for a
featureType in a PostGIS data store a new connection to the
database is stablished... Therefore the list of postmasters running
in the system start growing until it reach the 300 limit I have in
my PostgreSQL configuration file...
I have the impression that this was not happening to me before,
strange...
Is Geoserver closing the connections after each request?
Thanks.
Javier.
---------------------------------------------------------------------
---
begin:vcard
fn:Javier de la Torre
n:de la Torre;Javier
org:Museo Nacional de Ciencias Naturales (CSIC)
adr:;;C/Jose Gutierrez Abascal, 2;Madrid;Madrid;28006;Spain
email;internet:jatorre@anonymised.com
title:Scientist
url:http://www.biogeografia.com/edit/
version:2.1
end:vcard
---------------------------------------------------------------------
---

--
Chris Holmes
The Open Planning Project
http://topp.openplans.org
<cholmes.vcf>

------------------------------------------------------------------------

begin:vcard
fn:Javier de la Torre
n:de la Torre;Javier
org:Museo Nacional de Ciencias Naturales (CSIC)
adr:;;C/Jose Gutierrez Abascal, 2;Madrid;Madrid;28006;Spain
email;internet:jatorre@anonymised.com
title:Scientist
url:http://www.biogeografia.com/edit/
version:2.1
end:vcard

------------------------------------------------------------------------

--
Chris Holmes
The Open Planning Project
http://topp.openplans.org

[This e-mail is confidential and may be priviledged. If you are not the
intended recipient, please kindly notify us immediately and delete the message
from your system; please do not copy or use it for any purpose, nor disclose
its contents to any other person. Thank you.]
---ST Electronics Group---

Chris_Holmes1 · August 1, 2006, 2:47pm

Lee Wai See wrote:

Hi Chris,

I came across this and thought I might just post to this email, as I am not
sure whether I am having the "too many connections" to PostgreSQL problem as
well. The situation now I have is that there are about 7-8 postgres.exe
running at a same time. Pardon me for the question, but is this supposed to
be normal? If not, what could be the reason? I am using GeoServer 1.3.0-RC2,
PostgreSQL 8.0 and PostGIS 1.0.4-1.

This is normal. GeoServer makes use of a decent number of connections, as it needs to get the bounds for all WFS responses. Some of this could probably be optimized further, but few have had a huge problem with it. Though there is a desire to do a maxConnections param, so that users can limit the number of connections geoserver opens, just waiting until a connection is released.

Another question: Can I understand how database connections are handled in
GeoServer with PostgreSQL? The reason why I ask this is that I need to do
database updates at a very high frequency (in intervals of seconds), and
thus I am concerned if GeoServer is creating, opening and closing
connections for every single request as this will certainly eats up a lot of
memory for my application. Could you please explain what the mechanism for
PostgreSQL connections is, or is there some documentation which I can refer
to? Or is there a need for me to be worried at all?

No, need to worry, we do connection pooling. The only time GeoServer opens new connections is if all the existing ones are being used. I can point you at the code if you want, but you shouldn't need to worry.

best regards,

Chris

Thanks.

Regards, Lee Wai See ST Electronics (Info-Software Systems) Pte Ltd

-----Original Message-----
From: geoserver-users-admin@lists.sourceforge.net
[mailto:geoserver-users-admin@lists.sourceforge.net] On Behalf Of Chris
Holmes
Sent: Saturday, May 13, 2006 7:03 AM
To: Javier de la Torre
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] PostgreSQL connections

Could I convince you to maybe try 1.3.0? If it works fine then there's definitely a bug in GeoTools 2.2.x that we should fix before 1.3.1 official.

thanks!

Chris

Javier de la Torre wrote:

Hi Chris,

I am using 1.3.1 Beta. I am doing just simple WMS getMap requests for a Google Maps mashup. When viewing a map maybe 20 request are sent to Geoserver one for each tile to be overlapped in Google maps.

I did not try with the stable 1.3.0 version sorry.

More info. I am running it on Red Hat 9 with PostgreSQL 8.1.3 and Postgis 1.1.2

Javier.

On 12/05/2006, at 16:51, Chris Holmes wrote:

Yeah, it normally sets up a nice pool and shares all the connections. If it's going up on every request then something's going wrong. What operation are you doing? And what version of GeoServer? In the past a few minor changes have lead to connection pool mess ups, though generally in the less used operations.

Chris

Javier de la Torre wrote:

Hi,
I am suffering the typical "too many connections" to PostgreSQL while using Geoserver. Seems that for every new request for a featureType in a PostGIS data store a new connection to the database is stablished... Therefore the list of postmasters running in the system start growing until it reach the 300 limit I have in my PostgreSQL configuration file...
I have the impression that this was not happening to me before, strange...
Is Geoserver closing the connections after each request?
Thanks.
Javier.
--------------------------------------------------------------------- ---
begin:vcard
fn:Javier de la Torre
n:de la Torre;Javier
org:Museo Nacional de Ciencias Naturales (CSIC)
adr:;;C/Jose Gutierrez Abascal, 2;Madrid;Madrid;28006;Spain
email;internet:jatorre@anonymised.com
title:Scientist
url:http://www.biogeografia.com/edit/
version:2.1
end:vcard
--------------------------------------------------------------------- ---

--
Chris Holmes
The Open Planning Project
http://topp.openplans.org
<cholmes.vcf>

------------------------------------------------------------------------

begin:vcard
fn:Javier de la Torre
n:de la Torre;Javier
org:Museo Nacional de Ciencias Naturales (CSIC)
adr:;;C/Jose Gutierrez Abascal, 2;Madrid;Madrid;28006;Spain
email;internet:jatorre@anonymised.com
title:Scientist
url:http://www.biogeografia.com/edit/
version:2.1
end:vcard

------------------------------------------------------------------------

--
Chris Holmes
The Open Planning Project
http://topp.openplans.org

Chris_Holmes1 · August 1, 2006, 2:51pm

Note that there was a bug with Javier's stuff, but we've fixed it. The connections weren't getting properly returned to their pool.

Lee Wai See wrote:

Hi Chris,

I came across this and thought I might just post to this email, as I am not
sure whether I am having the "too many connections" to PostgreSQL problem as
well. The situation now I have is that there are about 7-8 postgres.exe
running at a same time. Pardon me for the question, but is this supposed to
be normal? If not, what could be the reason? I am using GeoServer 1.3.0-RC2,
PostgreSQL 8.0 and PostGIS 1.0.4-1.

Another question: Can I understand how database connections are handled in
GeoServer with PostgreSQL? The reason why I ask this is that I need to do
database updates at a very high frequency (in intervals of seconds), and
thus I am concerned if GeoServer is creating, opening and closing
connections for every single request as this will certainly eats up a lot of
memory for my application. Could you please explain what the mechanism for
PostgreSQL connections is, or is there some documentation which I can refer
to? Or is there a need for me to be worried at all?

Thanks.

Regards, Lee Wai See ST Electronics (Info-Software Systems) Pte Ltd

-----Original Message-----
From: geoserver-users-admin@lists.sourceforge.net
[mailto:geoserver-users-admin@lists.sourceforge.net] On Behalf Of Chris
Holmes
Sent: Saturday, May 13, 2006 7:03 AM
To: Javier de la Torre
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] PostgreSQL connections

Could I convince you to maybe try 1.3.0? If it works fine then there's definitely a bug in GeoTools 2.2.x that we should fix before 1.3.1 official.

thanks!

Chris

Javier de la Torre wrote:

Hi Chris,

I am using 1.3.1 Beta. I am doing just simple WMS getMap requests for a Google Maps mashup. When viewing a map maybe 20 request are sent to Geoserver one for each tile to be overlapped in Google maps.

I did not try with the stable 1.3.0 version sorry.

More info. I am running it on Red Hat 9 with PostgreSQL 8.1.3 and Postgis 1.1.2

Javier.

On 12/05/2006, at 16:51, Chris Holmes wrote:

Yeah, it normally sets up a nice pool and shares all the connections. If it's going up on every request then something's going wrong. What operation are you doing? And what version of GeoServer? In the past a few minor changes have lead to connection pool mess ups, though generally in the less used operations.

Chris

Javier de la Torre wrote:

Hi,
I am suffering the typical "too many connections" to PostgreSQL while using Geoserver. Seems that for every new request for a featureType in a PostGIS data store a new connection to the database is stablished... Therefore the list of postmasters running in the system start growing until it reach the 300 limit I have in my PostgreSQL configuration file...
I have the impression that this was not happening to me before, strange...
Is Geoserver closing the connections after each request?
Thanks.
Javier.
--------------------------------------------------------------------- ---
begin:vcard
fn:Javier de la Torre
n:de la Torre;Javier
org:Museo Nacional de Ciencias Naturales (CSIC)
adr:;;C/Jose Gutierrez Abascal, 2;Madrid;Madrid;28006;Spain
email;internet:jatorre@anonymised.com
title:Scientist
url:http://www.biogeografia.com/edit/
version:2.1
end:vcard
--------------------------------------------------------------------- ---

--
Chris Holmes
The Open Planning Project
http://topp.openplans.org
<cholmes.vcf>

------------------------------------------------------------------------

begin:vcard
fn:Javier de la Torre
n:de la Torre;Javier
org:Museo Nacional de Ciencias Naturales (CSIC)
adr:;;C/Jose Gutierrez Abascal, 2;Madrid;Madrid;28006;Spain
email;internet:jatorre@anonymised.com
title:Scientist
url:http://www.biogeografia.com/edit/
version:2.1
end:vcard

------------------------------------------------------------------------

--
Chris Holmes
The Open Planning Project
http://topp.openplans.org

Lee_Wai_See · August 2, 2006, 2:17am

Hi Chris,

Thanks for the reply.

Another question: Can I understand how database connections are handled

in

GeoServer with PostgreSQL? The reason why I ask this is that I need to do
database updates at a very high frequency (in intervals of seconds), and
thus I am concerned if GeoServer is creating, opening and closing
connections for every single request as this will certainly eats up a lot

of

memory for my application. Could you please explain what the mechanism

for

PostgreSQL connections is, or is there some documentation which I can

refer

to? Or is there a need for me to be worried at all?

No, need to worry, we do connection pooling. The only time GeoServer
opens new connections is if all the existing ones are being used. I can
point you at the code if you want, but you shouldn't need to worry.

That is great. It would be good if you could point me to the code as I might
just want to take a look. Is there some documentation to go along with it?
If not, do you mind just giving a brief explanation of what is done? Greatly
appreciate that.

Regards,
Lee Wai See
ST Electronics (Info-Software Systems) Pte Ltd

-----Original Message-----
From: geoserver-users-bounces@lists.sourceforge.net
[mailto:geoserver-users-bounces@lists.sourceforge.net] On Behalf Of Chris
Holmes
Sent: Tuesday, August 01, 2006 10:48 PM
To: Lee Wai See
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] PostgreSQL connections

Lee Wai See wrote:

Hi Chris,

I came across this and thought I might just post to this email, as I am

not

sure whether I am having the "too many connections" to PostgreSQL problem

as

well. The situation now I have is that there are about 7-8 postgres.exe
running at a same time. Pardon me for the question, but is this supposed

to

be normal? If not, what could be the reason? I am using GeoServer

1.3.0-RC2,

PostgreSQL 8.0 and PostGIS 1.0.4-1.

This is normal. GeoServer makes use of a decent number of connections,
as it needs to get the bounds for all WFS responses. Some of this could
probably be optimized further, but few have had a huge problem with it.
Though there is a desire to do a maxConnections param, so that users
can limit the number of connections geoserver opens, just waiting until
a connection is released.

Another question: Can I understand how database connections are handled in
GeoServer with PostgreSQL? The reason why I ask this is that I need to do
database updates at a very high frequency (in intervals of seconds), and
thus I am concerned if GeoServer is creating, opening and closing
connections for every single request as this will certainly eats up a lot

of

memory for my application. Could you please explain what the mechanism for
PostgreSQL connections is, or is there some documentation which I can

refer

to? Or is there a need for me to be worried at all?

No, need to worry, we do connection pooling. The only time GeoServer
opens new connections is if all the existing ones are being used. I can
point you at the code if you want, but you shouldn't need to worry.

best regards,

Chris

Thanks.

Regards,
Lee Wai See
ST Electronics (Info-Software Systems) Pte Ltd

-----Original Message-----
From: geoserver-users-admin@lists.sourceforge.net
[mailto:geoserver-users-admin@lists.sourceforge.net] On Behalf Of Chris
Holmes
Sent: Saturday, May 13, 2006 7:03 AM
To: Javier de la Torre
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] PostgreSQL connections

Could I convince you to maybe try 1.3.0? If it works fine then there's
definitely a bug in GeoTools 2.2.x that we should fix before 1.3.1

official.

thanks!

Chris

Javier de la Torre wrote:

Hi Chris,

I am using 1.3.1 Beta. I am doing just simple WMS getMap requests for a
Google Maps mashup. When viewing a map maybe 20 request are sent to
Geoserver one for each tile to be overlapped in Google maps.

I did not try with the stable 1.3.0 version sorry.

More info. I am running it on Red Hat 9 with PostgreSQL 8.1.3 and
Postgis 1.1.2

Javier.

On 12/05/2006, at 16:51, Chris Holmes wrote:

Yeah, it normally sets up a nice pool and shares all the connections.
If it's going up on every request then something's going wrong. What
operation are you doing? And what version of GeoServer? In the past
a few minor changes have lead to connection pool mess ups, though
generally in the less used operations.

Chris

Javier de la Torre wrote:

Hi,
I am suffering the typical "too many connections" to PostgreSQL
while using Geoserver. Seems that for every new request for a
featureType in a PostGIS data store a new connection to the
database is stablished... Therefore the list of postmasters running
in the system start growing until it reach the 300 limit I have in
my PostgreSQL configuration file...
I have the impression that this was not happening to me before,
strange...
Is Geoserver closing the connections after each request?
Thanks.
Javier.
---------------------------------------------------------------------
---
begin:vcard
fn:Javier de la Torre
n:de la Torre;Javier
org:Museo Nacional de Ciencias Naturales (CSIC)
adr:;;C/Jose Gutierrez Abascal, 2;Madrid;Madrid;28006;Spain
email;internet:jatorre@anonymised.com
title:Scientist
url:http://www.biogeografia.com/edit/
version:2.1
end:vcard
---------------------------------------------------------------------
---

--
Chris Holmes
The Open Planning Project
http://topp.openplans.org
<cholmes.vcf>

------------------------------------------------------------------------

begin:vcard
fn:Javier de la Torre
n:de la Torre;Javier
org:Museo Nacional de Ciencias Naturales (CSIC)
adr:;;C/Jose Gutierrez Abascal, 2;Madrid;Madrid;28006;Spain
email;internet:jatorre@anonymised.com
title:Scientist
url:http://www.biogeografia.com/edit/
version:2.1
end:vcard

------------------------------------------------------------------------

--
Chris Holmes
The Open Planning Project
http://topp.openplans.org

[This e-mail is confidential and may be priviledged. If you are not the
intended recipient, please kindly notify us immediately and delete the message
from your system; please do not copy or use it for any purpose, nor disclose
its contents to any other person. Thank you.]
---ST Electronics Group---

Chris_Holmes1 · August 2, 2006, 2:58pm

Lee Wai See wrote:

Hi Chris,

Thanks for the reply.

Another question: Can I understand how database connections are handled

in

GeoServer with PostgreSQL? The reason why I ask this is that I need to do
database updates at a very high frequency (in intervals of seconds), and
thus I am concerned if GeoServer is creating, opening and closing
connections for every single request as this will certainly eats up a lot

of

memory for my application. Could you please explain what the mechanism

for

PostgreSQL connections is, or is there some documentation which I can

refer

to? Or is there a need for me to be worried at all?

No, need to worry, we do connection pooling. The only time GeoServer opens new connections is if all the existing ones are being used. I can point you at the code if you want, but you shouldn't need to worry.

That is great. It would be good if you could point me to the code as I might
just want to take a look. Is there some documentation to go along with it?
If not, do you mind just giving a brief explanation of what is done? Greatly
appreciate that.

No problem. The code is found at: http://svn.geotools.org/geotools/branches/2.2.x/module/main/src/org/geotools/data/jdbc/ConnectionPool.java and
http://svn.geotools.org/geotools/branches/2.2.x/module/main/src/org/geotools/data/jdbc/ConnectionPoolManager.java

This is the postgis code that calls it: http://svn.geotools.org/geotools/branches/2.2.x/plugin/postgis/src/org/geotools/data/postgis/PostgisConnectionFactory.java

The connection factory makes a ConnectionPool, and whenever a call to getConnection is made, it gets it from the Pool. This is all pretty standard JDBC2+ stuff, we use the pooling implementation from the jdbc driver. Note that we did write our own library for doing the actual pool, I think there wasn't a solid open source one at the time. We'd like to replace it with a library to do it, which would give us the ability to limit the max connections, but at this point it's not broken so we don't really want to rip out a bunch of code to fix it.

best regards,

Chris

Regards, Lee Wai See ST Electronics (Info-Software Systems) Pte Ltd

-----Original Message-----
From: geoserver-users-bounces@lists.sourceforge.net
[mailto:geoserver-users-bounces@lists.sourceforge.net] On Behalf Of Chris
Holmes
Sent: Tuesday, August 01, 2006 10:48 PM
To: Lee Wai See
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] PostgreSQL connections

Lee Wai See wrote:

Hi Chris,

I came across this and thought I might just post to this email, as I am

not

sure whether I am having the "too many connections" to PostgreSQL problem

as

well. The situation now I have is that there are about 7-8 postgres.exe
running at a same time. Pardon me for the question, but is this supposed

to

be normal? If not, what could be the reason? I am using GeoServer

1.3.0-RC2,

PostgreSQL 8.0 and PostGIS 1.0.4-1.

This is normal. GeoServer makes use of a decent number of connections, as it needs to get the bounds for all WFS responses. Some of this could probably be optimized further, but few have had a huge problem with it. Though there is a desire to do a maxConnections param, so that users can limit the number of connections geoserver opens, just waiting until a connection is released.

Another question: Can I understand how database connections are handled in
GeoServer with PostgreSQL? The reason why I ask this is that I need to do
database updates at a very high frequency (in intervals of seconds), and
thus I am concerned if GeoServer is creating, opening and closing
connections for every single request as this will certainly eats up a lot

of

memory for my application. Could you please explain what the mechanism for
PostgreSQL connections is, or is there some documentation which I can

refer

to? Or is there a need for me to be worried at all?

No, need to worry, we do connection pooling. The only time GeoServer opens new connections is if all the existing ones are being used. I can point you at the code if you want, but you shouldn't need to worry.

best regards,

Chris

Thanks.

Regards, Lee Wai See ST Electronics (Info-Software Systems) Pte Ltd

-----Original Message-----
From: geoserver-users-admin@lists.sourceforge.net
[mailto:geoserver-users-admin@lists.sourceforge.net] On Behalf Of Chris
Holmes
Sent: Saturday, May 13, 2006 7:03 AM
To: Javier de la Torre
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] PostgreSQL connections

Could I convince you to maybe try 1.3.0? If it works fine then there's definitely a bug in GeoTools 2.2.x that we should fix before 1.3.1

official.

thanks!

Chris

Javier de la Torre wrote:

Hi Chris,

I am using 1.3.1 Beta. I am doing just simple WMS getMap requests for a Google Maps mashup. When viewing a map maybe 20 request are sent to Geoserver one for each tile to be overlapped in Google maps.

I did not try with the stable 1.3.0 version sorry.

More info. I am running it on Red Hat 9 with PostgreSQL 8.1.3 and Postgis 1.1.2

Javier.

On 12/05/2006, at 16:51, Chris Holmes wrote:

Yeah, it normally sets up a nice pool and shares all the connections. If it's going up on every request then something's going wrong. What operation are you doing? And what version of GeoServer? In the past a few minor changes have lead to connection pool mess ups, though generally in the less used operations.

Chris

Javier de la Torre wrote:

Hi,
I am suffering the typical "too many connections" to PostgreSQL while using Geoserver. Seems that for every new request for a featureType in a PostGIS data store a new connection to the database is stablished... Therefore the list of postmasters running in the system start growing until it reach the 300 limit I have in my PostgreSQL configuration file...
I have the impression that this was not happening to me before, strange...
Is Geoserver closing the connections after each request?
Thanks.
Javier.
--------------------------------------------------------------------- ---
begin:vcard
fn:Javier de la Torre
n:de la Torre;Javier
org:Museo Nacional de Ciencias Naturales (CSIC)
adr:;;C/Jose Gutierrez Abascal, 2;Madrid;Madrid;28006;Spain
email;internet:jatorre@anonymised.com
title:Scientist
url:http://www.biogeografia.com/edit/
version:2.1
end:vcard
--------------------------------------------------------------------- ---

--
Chris Holmes
The Open Planning Project
http://topp.openplans.org
<cholmes.vcf>

------------------------------------------------------------------------

begin:vcard
fn:Javier de la Torre
n:de la Torre;Javier
org:Museo Nacional de Ciencias Naturales (CSIC)
adr:;;C/Jose Gutierrez Abascal, 2;Madrid;Madrid;28006;Spain
email;internet:jatorre@anonymised.com
title:Scientist
url:http://www.biogeografia.com/edit/
version:2.1
end:vcard

------------------------------------------------------------------------

--
Chris Holmes
The Open Planning Project
http://topp.openplans.org