Using GeoServer 2.2.1 and 2.3 with a large number of PostGIS-backed datastores under TomCat 6.2. These stores are rarely used, so we’d like them to not tie up a PostgreSQL connection until one is actually needed. Thus, we set min_connections=0 in the datastore.xml associated with each store. Interestingly, when GeoServer is bounced, there is a single connection bound to PostgreSQL for each store anyway. If we use the admin interface and open the store definition, “min_connections” shows 0, which is what one would expect. Now, simply saving the store definition without modifying anything causes the corresponding DB connection to be dropped, and a connection is not bound again until the datastore is used or until GeoServer is bounced again.
We were speculating that the
0
line in the datastore XML is perhaps being treated and a “falsey” or invalid value, and a default value of one to be assigned.
Can anyone else recreate this?
Thanks in advance.
On Wed, Jun 26, 2013 at 4:46 AM, David Niedzielski <
david.niedzielski@anonymised.com> wrote:
Using GeoServer 2.2.1 and 2.3 with a large number of PostGIS-backed
datastores under TomCat 6.2. These stores are rarely used, so we'd like
them to not tie up a PostgreSQL connection until one is actually needed.
Thus, we set min_connections=0 in the datastore.xml associated with each
store. Interestingly, when GeoServer is bounced, there is a single
connection bound to PostgreSQL for each store anyway. If we use the admin
interface and open the store definition, "min_connections" shows 0, which
is what one would expect. Now, simply saving the store definition without
modifying anything causes the corresponding DB connection to be dropped,
and a connection is not bound again until the datastore is used or until
GeoServer is bounced again.
On startup GeoServer verifies that each and every feature type is actually
accessible (the store is up, the feature type structure can be computed),
this requires a connection (which is going to be dropped after a timeout I
believe, but I don't remember what the timeout is).
If you have many stores pointing at the same database, but using different
schemas, you're "doing it wrong (tm)", the way to go is
to setup a connection pool inside Tomcat and then call onto it via JNDI,
there is an example of how this can be done here:
http://docs.geoserver.org/stable/en/user/tutorials/tomcat-jndi/tomcat-jndi.html
This way all stores will share the same connection pool, and during startup
a single connection will be taken and reused
over and over to check the feature types are good.
Cheers
Andrea
--
Ing. Andrea Aime
@geowolf
Technical Lead
GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
-------------------------------------------------------
Thanks for the reply. In our case, sadly, we have separate databases. One for each data store. The timeouts to which you allude seems to be key, because they obviously do not abide by the connection timeout associated with the stores. Indeed, these single connections are up for as long as geoserver itself - we have yet to see a timeout after weeks of uptime. Are they perhaps reused periodically? The layers associated with the data stores are never referenced. Where does one set the timeout for these initial connections, as they seem to be eternal and the heart of the problem?
···
Using GeoServer 2.2.1 and 2.3 with a large number of PostGIS-backed datastores under TomCat 6.2. These stores are rarely used, so we’d like them to not tie up a PostgreSQL connection until one is actually needed. Thus, we set min_connections=0 in the datastore.xml associated with each store. Interestingly, when GeoServer is bounced, there is a single connection bound to PostgreSQL for each store anyway. If we use the admin interface and open the store definition, “min_connections” shows 0, which is what one would expect. Now, simply saving the store definition without modifying anything causes the corresponding DB connection to be dropped, and a connection is not bound again until the datastore is used or until GeoServer is bounced again.
On startup GeoServer verifies that each and every feature type is actually accessible (the store is up, the feature type structure can be computed),
this requires a connection (which is going to be dropped after a timeout I believe, but I don’t remember what the timeout is).
If you have many stores pointing at the same database, but using different schemas, you’re “doing it wrong ™”, the way to go is
to setup a connection pool inside Tomcat and then call onto it via JNDI, there is an example of how this can be done here:
http://docs.geoserver.org/stable/en/user/tutorials/tomcat-jndi/tomcat-jndi.html
This way all stores will share the same connection pool, and during startup a single connection will be taken and reused
over and over to check the feature types are good.
Cheers
Andrea
–
Ing. Andrea Aime
@geowolf
Technical Lead
GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
On Wed, Jun 26, 2013 at 10:45 AM, David Niedzielski <
david.niedzielski@anonymised.com> wrote:
Thanks for the reply. In our case, sadly, we have separate databases. One
for each data store. The timeouts to which you allude seems to be key,
because they obviously do not abide by the connection timeout associated
with the stores. Indeed, these single connections are up for as long as
geoserver itself - we have yet to see a timeout after weeks of uptime. Are
they perhaps reused periodically? The layers associated with the data
stores are never referenced. Where does one set the timeout for these
initial connections, as they seem to be eternal and the heart of the
problem?
Hmm... I guess I was wrong, don't see any time out in the code setting up
the connection pool:
https://github.com/geotools/geotools/blob/master/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStoreFactory.java#L420
We use Commons DBCP as the connection pool, if you find a way to setup a
timeout for idle connections
please share a patch/make a pull request
Cheers
Andrea
--
Ing. Andrea Aime
@geowolf
Technical Lead
GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
-------------------------------------------------------