[Geoserver-users] Connection Pooling...

Corey_Puffalt · June 14, 2006, 3:12pm

Resending as this doesn’t appear to have made it to the list for some reason…

Corey

---------- Forwarded message ----------
From: Corey Puffalt <cplists@anonymised.com>
Date: Jun 13, 2006 11:26 AM
Subject: Connection Pooling…
To: GeoServer Users <geoserver-users@lists.sourceforge.net>

All…

I’ve been doing a little bit of load testing and I’m finding that GeoServer doesn’t seem to be limiting the number of connections it’s opening to Postgres. I can’t find anyway to limit the maximum number of connections in the connection pool? Is this true or am I missing something? It seems strange that GeoServer doesn’t seem to use any of the existing open source libraries to handle its connection pooling…

Thanks for your help…

Corey

Chris_Holmes1 · June 14, 2006, 9:36pm

Is this with 1.3.1beta? If so then it's been fixed for 1.3.1

We'd like to use a more standard connection pooling library, but generally the pooling works great, so it doesn't seem worth the time investment. Also note that this problem isn't due to anything wrong with the actual pooling, instead it has to do with the fact that transactions were left open in the code.

Chris

Corey Puffalt wrote:

Resending as this doesn't appear to have made it to the list for some reason...

Corey

---------- Forwarded message ----------
From: *Corey Puffalt* <cplists@anonymised.com <mailto:cplists@anonymised.com>>
Date: Jun 13, 2006 11:26 AM
Subject: Connection Pooling...
To: GeoServer Users <geoserver-users@lists.sourceforge.net <mailto:geoserver-users@lists.sourceforge.net>>

All...

I've been doing a little bit of load testing and I'm finding that GeoServer doesn't seem to be limiting the number of connections it's opening to Postgres. I can't find anyway to limit the maximum number of connections in the connection pool? Is this true or am I missing something? It seems strange that GeoServer doesn't seem to use any of the existing open source libraries to handle its connection pooling...

Thanks for your help...
Corey

!DSPAM:1003,4490275d216581527717022!

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

!DSPAM:1003,4490275d216581527717022!

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

_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

!DSPAM:1003,4490275d216581527717022!

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

Corey_Puffalt · June 14, 2006, 10:41pm

Chris,

Thanks for your response.

I’m running v1.3.0… did it have a similar issue? What I’m observing doesn’t look like connection leaking though that’s possible. What I’m seeing is that if there are 64 simultaneous hits requesting a map then I have 64 open connections to the database (which makes sense). I’m looking for a way of limiting the number of open connections to the database. Most connection pools allow the user to configure a maximum number of connections. This would allow me to throttle things down to something reasonable. This would mean some of the requests would have to block waiting for a database connection to be freed up…but I’d rather have that happen than kill the database server.

Thanks,
Corey

On 6/14/06, Chris Holmes <cholmes@anonymised.com> wrote:

Is this with 1.3.1beta? If so then it’s been fixed for 1.3.1

We’d like to use a more standard connection pooling library, but
generally the pooling works great, so it doesn’t seem worth the time
investment. Also note that this problem isn’t due to anything wrong
with the actual pooling, instead it has to do with the fact that
transactions were left open in the code.

Chris

Corey Puffalt wrote:

Resending as this doesn’t appear to have made it to the list for some
reason…

Corey

---------- Forwarded message ----------
From: Corey Puffalt <cplists@anonymised.com <mailto: cplists@anonymised.com>>
Date: Jun 13, 2006 11:26 AM
Subject: Connection Pooling…
To: GeoServer Users <geoserver-users@lists.sourceforge.net
mailto:[geoserver-users@lists.sourceforge.net](mailto:geoserver-users@lists.sourceforge.net)>

All…

I’ve been doing a little bit of load testing and I’m finding that
GeoServer doesn’t seem to be limiting the number of connections it’s
opening to Postgres. I can’t find anyway to limit the maximum number of
connections in the connection pool? Is this true or am I missing
something? It seems strange that GeoServer doesn’t seem to use any of
the existing open source libraries to handle its connection pooling…

Thanks for your help…
Corey

!DSPAM:1003,4490275d216581527717022!

!DSPAM:1003,4490275d216581527717022!

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

!DSPAM:1003,4490275d216581527717022!

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

Corey_Puffalt · June 15, 2006, 3:02pm

Chris...

Thanks for your response. My first reply got rejected by the sourceforge
servers claiming that the gmail SMTP server is in the SpamCop RBL list.

I'm running v1.3.0... did it have a similar issue? What I'm observing
doesn't look like connection leaking though that's possible. What I'm
seeing is that if there are 64 simultaneous hits requesting a map then I
have 64 open connections to the database (which makes sense). I'm looking
for a way of limiting the number of open connections to the database. Most
connection pools allow the user to configure a maximum number of
connections. This would allow me to throttle things down to something
reasonable. This would mean some of the requests would have to block
waiting for a database connection to be freed up...but I'd rather have that
happen than kill the database server.

Thanks,
Corey

Chris Holmes wrote:

Is this with 1.3.1beta? If so then it's been fixed for 1.3.1

We'd like to use a more standard connection pooling library, but
generally the pooling works great, so it doesn't seem worth the time
investment. Also note that this problem isn't due to anything wrong
with the actual pooling, instead it has to do with the fact that
transactions were left open in the code.

Chris

--
View this message in context: http://www.nabble.com/Fwd%3A-Connection-Pooling…-t1786794.html#a4884523
Sent from the GeoServer - User forum at Nabble.com.

Chris_Holmes1 · June 20, 2006, 7:06pm

Yeroc wrote:

Chris...

Thanks for your response. My first reply got rejected by the sourceforge
servers claiming that the gmail SMTP server is in the SpamCop RBL list.

I'm running v1.3.0... did it have a similar issue? What I'm observing
doesn't look like connection leaking though that's possible. What I'm
seeing is that if there are 64 simultaneous hits requesting a map then I
have 64 open connections to the database (which makes sense). I'm looking
for a way of limiting the number of open connections to the database. Most
connection pools allow the user to configure a maximum number of
connections. This would allow me to throttle things down to something
reasonable. This would mean some of the requests would have to block
waiting for a database connection to be freed up...but I'd rather have that
happen than kill the database server.

Ah, I get you. No, that's a different issue, not in 1.3.0. Yeah, that would be an advantage of going with a better known connection pooling library. Though configuring it would add a bunch of options that would be confusing for most people. You can submit the request to our JIRA task tracker.

Is it possible to throttle it on the database side? Can't postgres limit the number of connections on its end? Or does that cause nasty errors?

Chris

Thanks,
Corey

Chris Holmes wrote:

Is this with 1.3.1beta? If so then it's been fixed for 1.3.1

We'd like to use a more standard connection pooling library, but generally the pooling works great, so it doesn't seem worth the time investment. Also note that this problem isn't due to anything wrong with the actual pooling, instead it has to do with the fact that transactions were left open in the code.

Chris

--
View this message in context: http://www.nabble.com/Fwd%3A-Connection-Pooling…-t1786794.html#a4884523
Sent from the GeoServer - User forum at Nabble.com.

_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

!DSPAM:1003,449176d344701116498154!

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

Corey_Puffalt · June 20, 2006, 10:33pm

Chris,

On 6/20/06, Chris Holmes <cholmes@anonymised.com> wrote:

Yeroc wrote:

I’m running v1.3.0… did it have a similar issue? What I’m observing
doesn’t look like connection leaking though that’s possible. What I’m
seeing is that if there are 64 simultaneous hits requesting a map then I
have 64 open connections to the database (which makes sense). I’m looking
for a way of limiting the number of open connections to the database. Most
connection pools allow the user to configure a maximum number of
connections. This would allow me to throttle things down to something
reasonable. This would mean some of the requests would have to block
waiting for a database connection to be freed up…but I’d rather have that
happen than kill the database server.
Ah, I get you. No, that’s a different issue, not in 1.3.0. Yeah, that
would be an advantage of going with a better known connection pooling
library. Though configuring it would add a bunch of options that would
be confusing for most people. You can submit the request to our JIRA
task tracker.

With reasonable defaults people that are setting up an installation to play around with wouldn’t have to worry about the extra parameters. I would argue that this is going to be an issue for nearly anyone setting up GeoServer in a production environment though. The problem is particularly evident when using a WMS client like openlayers that tiles requests and I think we will see more and more clients moving in this direction.

Is it possible to throttle it on the database side? Can’t postgres
limit the number of connections on its end? Or does that cause nasty
errors?

I’m not a Postgres guru but my understanding is that there is only a global max connections setting. This is not a solution to the problem though. As you suggest you’ll just end up getting SQLExceptions resulting in broken images. I think it’s far better to have a solution that results in a slowdown in the rendering process (block waiting for an available connection) than having errors thrown back to the client. Additionally, what about database servers that are shared with other users? I don’t think Postgres has a maximum connections per user setting (I could be wrong.) For instance, in my case, there’s another process running periodically to update the postgres database with new data from another production (Oracle) database. I can’t have these updates failing because GeoServer is using up all the connections…

I will submit a JIRA ticket…

Regards,
Corey

Chris_Holmes1 · June 20, 2006, 10:56pm

Corey Puffalt wrote:

Chris,

On 6/20/06, *Chris Holmes* <cholmes@anonymised.com <mailto:cholmes@anonymised.com>> wrote:

    Yeroc wrote:
     > I'm running v1.3.0... did it have a similar issue? What I'm
    observing
     > doesn't look like connection leaking though that's
    possible. What I'm
     > seeing is that if there are 64 simultaneous hits requesting a map
    then I
     > have 64 open connections to the database (which makes
    sense). I'm looking
     > for a way of limiting the number of open connections to the
    database. Most
     > connection pools allow the user to configure a maximum number of
     > connections. This would allow me to throttle things down to
    something
     > reasonable. This would mean some of the requests would have to block
     > waiting for a database connection to be freed up...but I'd rather
    have that
     > happen than kill the database server.
    Ah, I get you. No, that's a different issue, not in 1.3.0. Yeah, that
    would be an advantage of going with a better known connection pooling
    library. Though configuring it would add a bunch of options that would
    be confusing for most people. You can submit the request to our JIRA
    task tracker.

With reasonable defaults people that are setting up an installation to play around with wouldn't have to worry about the extra parameters.

Yeah, ideally we could have 'basic' and 'advanced' config sections

I would argue that this is going to be an issue for nearly anyone setting up GeoServer in a production environment though. The problem is particularly evident when using a WMS client like openlayers that tiles requests and I think we will see more and more clients moving in this direction.

Yeah, I agree.

    Is it possible to throttle it on the database side? Can't postgres
    limit the number of connections on its end? Or does that cause nasty
    errors?

I'm not a Postgres guru but my understanding is that there is only a global max connections setting. This is not a solution to the problem though. As you suggest you'll just end up getting SQLExceptions resulting in broken images. I think it's far better to have a solution that results in a slowdown in the rendering process (block waiting for an available connection) than having errors thrown back to the client. Additionally, what about database servers that are shared with other users? I don't think Postgres has a maximum connections per user setting (I could be wrong.) For instance, in my case, there's another process running periodically to update the postgres database with new data from another production (Oracle) database. I can't have these updates failing because GeoServer is using up all the connections...

I will submit a JIRA ticket...

Cool. I'll try to get to it relatively soon. It's just that it involves touching a lot of classes that are known to work, fixing something which for the most part isn't broke. And that is generally a pain to fix. If others who want this issue could vote on it that will increase its chances of it getting fixed sooner (or if someone actually wants to fund it or donate their time to working on it).

If there are interim solutions like setting postgres max connections that would be nice. Do you have a recommendation of a good open source connection pooling implementation to use? Though at this point it may just be easier to just have our implementation borrow some code to do maxConnections from one that does it.

Chris

Regards,
Corey

!DSPAM:1003,4498779f280722223018498!

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

Farber_Saul_EEA · June 21, 2006, 2:45pm

It's worth noting that the SDE datastore has already implemented exactly what you guys are talking about. There's are "initial connections", "pool increment" and "max connections" parameters on the datastore, and as the demand for connections grows, connections are added to the pool in "pool increment"-sized blocks. Connections are persistent and return to the pool after being used. So only "maxconnections" are ever open to SDE at any one time, and the system will block for a configurable amount of time waiting for a new connection before it dies and says, "couldn't get a connection".

It all works quite well, but I'd definitely agree that using someone else's library to handle this would be a better way of doing it.

I didn't think it through enough to realize that the SDE connection-pooling code living in the SDE adapter means that other DB adapters might now have the same functionality.

So, the recap:

* SDE Adapter has connection pooling functionality.
* Feel free to copy it from SDE/refactor it into a shared library
* Best idea seems to be to use a generic connection-pooling library (from apache commons?)

--saul

Corey Puffalt wrote:

I'm not a Postgres guru but my understanding is that there is only a global max connections setting. This is not a solution to the problem though. As you suggest you'll just end up getting SQLExceptions resulting in broken images. I think it's far better to have a solution that results in a slowdown in the rendering process (block waiting for an available connection) than having errors thrown back to the client. Additionally, what about database servers that are shared with other users? I don't think Postgres has a maximum connections per user setting (I could be wrong.) For instance, in my case, there's another process running periodically to update the postgres database with new data from another production (Oracle) database. I can't have these updates failing because GeoServer is using up all the connections...

Corey_Puffalt · June 21, 2006, 3:35pm

On 6/20/06, Chris Holmes <cholmes@anonymised.com> wrote:

Cool. I’ll try to get to it relatively soon. It’s just that it
involves touching a lot of classes that are known to work, fixing
something which for the most part isn’t broke. And that is generally a
pain to fix. If others who want this issue could vote on it that will
increase its chances of it getting fixed sooner (or if someone actually
wants to fund it or donate their time to working on it).

If there are interim solutions like setting postgres max connections
that would be nice. Do you have a recommendation of a good open source
connection pooling implementation to use? Though at this point it may
just be easier to just have our implementation borrow some code to do
maxConnections from one that does it.

I’ve created a ticket for this: http://jira.codehaus.org/browse/GEOS-636

Looking at the Postgres plugin code it looks like the Postgres connection pool being used supports a maxConnections setting. It’s just not being used. Even if that were implemented it would be nice if all the JDBC-based feature sources could benefit from this feature without implementing it in each one.

I’ve used C3P0 in a production environment with some success. As of v0.9.0 it has all the knobs you need to twiddle with to get jdbc connection pooling working reliably. (I’ve added a note on this to the ticket above.)

Corey

Corey_Puffalt · June 21, 2006, 3:40pm

Is the ArcSDE datastore JDBC-based as well? One key bit of functionality that the JDBC-specific connection pools (the good ones at least) support is connection testing which usually means executing a SQL query on the connection prior to handing out a connection (in case the connection is no longer valid for some reason).

Corey

On 6/21/06, Saul Farber <Saul.Farber@anonymised.com> wrote:

It’s worth noting that the SDE datastore has already implemented exactly
what you guys are talking about. There’s are “initial connections”,
“pool increment” and “max connections” parameters on the datastore, and
as the demand for connections grows, connections are added to the pool
in “pool increment”-sized blocks. Connections are persistent and return
to the pool after being used. So only “maxconnections” are ever open to
SDE at any one time, and the system will block for a configurable amount
of time waiting for a new connection before it dies and says, “couldn’t
get a connection”.

It all works quite well, but I’d definitely agree that using someone
else’s library to handle this would be a better way of doing it.

I didn’t think it through enough to realize that the SDE
connection-pooling code living in the SDE adapter means that other DB
adapters might now have the same functionality.

So, the recap:

SDE Adapter has connection pooling functionality.

Feel free to copy it from SDE/refactor it into a shared library

Best idea seems to be to use a generic connection-pooling library
(from apache commons?)

Farber_Saul_EEA · June 21, 2006, 3:50pm

No, the ArcSDE datastore does not use JDBC connections. ArcSDE has its own proprietary API and the ArcSDE datastore brokers com.esri.sde.SeConnection objects which are not JDBC-related at all.

--saul

Corey Puffalt wrote:

Is the ArcSDE datastore JDBC-based as well? One key bit of functionality that the JDBC-specific connection pools (the good ones at least) support is connection testing which usually means executing a SQL query on the connection prior to handing out a connection (in case the connection is no longer valid for some reason).

Chris_Holmes1 · June 21, 2006, 6:04pm

Saul Farber wrote:

It's worth noting that the SDE datastore has already implemented exactly what you guys are talking about. There's are "initial connections", "pool increment" and "max connections" parameters on the datastore, and as the demand for connections grows, connections are added to the pool in "pool increment"-sized blocks. Connections are persistent and return to the pool after being used. So only "maxconnections" are ever open to SDE at any one time, and the system will block for a configurable amount of time waiting for a new connection before it dies and says, "couldn't get a connection".

It all works quite well, but I'd definitely agree that using someone else's library to handle this would be a better way of doing it.

I didn't think it through enough to realize that the SDE connection-pooling code living in the SDE adapter means that other DB adapters might now have the same functionality.

So, the recap:

* SDE Adapter has connection pooling functionality.
* Feel free to copy it from SDE/refactor it into a shared library

Actually looks like Gabriel's code was originally based on Sean's stuff, so theoretically it shouldn't be too hard to refactor.

* Best idea seems to be to use a generic connection-pooling library (from apache commons?)

Yes, Corey suggested c3p0, which I'd like to investigate in the future.

Chris

--saul

Corey Puffalt wrote:

I'm not a Postgres guru but my understanding is that there is only a global max connections setting. This is not a solution to the problem though. As you suggest you'll just end up getting SQLExceptions resulting in broken images. I think it's far better to have a solution that results in a slowdown in the rendering process (block waiting for an available connection) than having errors thrown back to the client. Additionally, what about database servers that are shared with other users? I don't think Postgres has a maximum connections per user setting (I could be wrong.) For instance, in my case, there's another process running periodically to update the postgres database with new data from another production (Oracle) database. I can't have these updates failing because GeoServer is using up all the connections...

_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

!DSPAM:1003,44995b9919991429667743!

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

Jukka_Rahkonen · June 22, 2006, 8:27am

Hi,

What might be the situation with Oracle connection pooling?

I have (very) bad experience on wrong connection pooling settings with
Oracle. The database was set to allow, let's say, 150 connections, but
the pool was allowed to open up to 200 connections. Once the limit of
150 connections was reached almost nothing worked ultil the service was
restarted, to be crashed again on the next heavy load situation. We
learned that database in our case must be set to accept some 20 to 50
connections more than the connection pool can open, because Oracle
itself is using some connections, and we need connections for direct
SQLPlus use, maintenance etc.

Regards,

-Jukka Rahkonen-