Fwd: Connection Pooling...

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Fwd: Connection Pooling...

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

Corey

---------- Forwarded message ----------
From: Corey Puffalt <[hidden email]>
Date: Jun 13, 2006 11:26 AM
Subject: Connection Pooling...
To: GeoServer Users <[hidden email]>

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



_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: Connection Pooling...

Chris Holmes-2
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* <[hidden email] <mailto:[hidden email]>>
> Date: Jun 13, 2006 11:26 AM
> Subject: Connection Pooling...
> To: GeoServer Users <[hidden email]
> <mailto:[hidden email]>>
>
> 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
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
>
> !DSPAM:1003,4490275d216581527717022!
--
Chris Holmes
The Open Planning Project
http://topp.openplans.org


_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users

cholmes.vcf (281 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: Connection Pooling...

Yeroc
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 <[hidden email]> 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* <[hidden email] <mailto:[hidden email]>>
> Date: Jun 13, 2006 11:26 AM
> Subject: Connection Pooling...
> To: GeoServer Users <[hidden email]
> <mailto:[hidden email]>>
>
> 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
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
>
> !DSPAM:1003,4490275d216581527717022!

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





_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: Connection Pooling...

Yeroc
In reply to this post by Chris Holmes-2
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
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: Connection Pooling...

Chris Holmes-2


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
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
> !DSPAM:1003,449176d344701116498154!
>
--
Chris Holmes
The Open Planning Project
http://topp.openplans.org


_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users

cholmes.vcf (281 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: Connection Pooling...

Yeroc
Chris,

On 6/20/06, Chris Holmes <[hidden email]> 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



_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: Connection Pooling...

Chris Holmes-2


Corey Puffalt wrote:

> Chris,
>
> On 6/20/06, *Chris Holmes* <[hidden email]
> <mailto:[hidden email]>> 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


_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users

cholmes.vcf (281 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: Connection Pooling...

sfarber
In reply to this post by Yeroc
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...
>


_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: Connection Pooling...

Yeroc
In reply to this post by Chris Holmes-2


On 6/20/06, Chris Holmes <[hidden email]> 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


_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: Connection Pooling...

Yeroc
In reply to this post by sfarber
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 <[hidden email]> 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?)




_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: Connection Pooling...

sfarber
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).



_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: Connection Pooling...

Chris Holmes-2
In reply to this post by sfarber


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
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
> !DSPAM:1003,44995b9919991429667743!
>
--
Chris Holmes
The Open Planning Project
http://topp.openplans.org

All the advantages of Linux Managed Hosting--Without the Cost and Risk!
Fully trained technicians. The highest number of Red Hat certifications in
the hosting industry. Fanatical Support. Click to learn more
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=107521&bid=248729&dat=121642
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users

cholmes.vcf (281 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: Connection Pooling...

Rahkonen Jukka (Tike)
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-

All the advantages of Linux Managed Hosting--Without the Cost and Risk!
Fully trained technicians. The highest number of Red Hat certifications in
the hosting industry. Fanatical Support. Click to learn more
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=107521&bid=248729&dat=121642
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users