Ok, now my answers .... hope your holiday is going well ....
For JNDI, I was initially using the JNDI PostGIS Example settings from
http://geoserver.geo-solutions.it/multidim/en/imagemosaic/mosaic_datastore.html,
so my configuration looked like:
<Resource
name="jdbc/stormprint"
auth="Container"
type="javax.sql.DataSource"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://localhost/stormprint"
username="XXXXXXXXXXXXXXXXX"
password="XXXXXXXXXXXXXXXXX"
maxActive="20"
maxIdle="10"
maxWait="10000"
minEvictableIdleTimeMillis="300000"
timeBetweenEvictionRunsMillis="300000"
validationQuery="SELECT 1"/>
So, I followed the textbook example for JNDI for GeoServer, including a
validation query. I was experiencing the described problem under this
example configuration.
I then started experimenting with the configuration, and found that if I
manually killed the db connections in postgres: "SELECT
pg_terminate_backend(PID#);", GeoServer would absolutely not recover from
this ... for display ... for anything ... it was just choking on any
JNDI-backed stores. If I removed both of the eviction settings in the last
few rows (keeping validation), I could kill the connections as much as I
wanted, and GeoServer would recover.
So, what I ended up with now is:
<Resource
name="jdbc/stormprint"
auth="Container"
type="javax.sql.DataSource"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://localhost/stormprint"
username="XXXXXXXXXXXXXXXXXXXXXXXXX"
password="XXXXXXXXXXXXXXXXXXXXXXXXX"
maxActive="20"
maxIdle="10"
maxWait="10000"
testOnBorrow="true"
testOnReturn="true"
validationQuery="SELECT 1"
validationQueryTimeout="2"/>
As you can see, I added testOnBorrow and testOnReturn.
Looking at your code, which seems to be in
https://github.com/geotools/geotools/blob/12.x/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStoreFactory.java
(for my future reference), maybe I should be adding in a testWhileIdle to
the JNDI configuration?
Thoughts?
Thanks again,
Mike
On Fri, May 1, 2015 at 9:45 AM, Andrea Aime <[email protected]>
wrote:
> On Fri, May 1, 2015 at 3:40 AM, Mike Grogan <[email protected]>
> wrote:
>
>> (GeoServer 2.6.2, Postgres 9.3.6; Ubuntu 14.04; JNDI pools via Tomcat)
>>
>> I am experiencing another hard to track down issue with harvesting /
>> creating new image mosaics via REST ... that seems to be occurring after
>> time passes.
>>
>
> By the description this is similar to a typical connection pool issue with
> lack of use for several hours, the connections are closed by the database
> end, the connection pool has no way to know, and grabbing the connection
> ends up having to time out, with everything locked up (if you have a
> validation query, otherwise it fails in other ways).
>
> Do you have the validation query setup in your JNDI connection pool?
> Also, what about the followoing params, that we are setting up in our
> normal data stores, that are there to deal with the issues
> of connections not being used for several hours? (sorry for pasting code,
> today is a holiday in Italy, have to be expedient as I'm
> getting out of the house):
>
> Boolean testWhileIdle = (Boolean) TEST_WHILE_IDLE.lookUp(params);
> if(testWhileIdle != null) {
> dataSource.setTestWhileIdle(testWhileIdle);
> }
>
> Integer timeBetweenEvictorRuns = (Integer)
> TIME_BETWEEN_EVICTOR_RUNS.lookUp(params);
> if(timeBetweenEvictorRuns != null && timeBetweenEvictorRuns > 0) {
>
> dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictorRuns * 1000l);
> }
>
> Integer minEvictableTime = (Integer)
> MIN_EVICTABLE_TIME.lookUp(params);
> if(minEvictableTime != null) {
> dataSource.setMinEvictableIdleTimeMillis(minEvictableTime *
> 1000l);
> }
>
> Integer evictorTestsPerRun = (Integer)
> EVICTOR_TESTS_PER_RUN.lookUp(params);
> if(evictorTestsPerRun != null) {
> dataSource.setNumTestsPerEvictionRun(evictorTestsPerRun);
> }
>
> Cheers
> Andrea
>
> --
> ==
> GeoServer Professional Services from the experts! Visit
> http://goo.gl/NWWaa2 for more information.
> ==
>
> 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
>
> *AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*
>
> Le informazioni contenute in questo messaggio di posta elettronica e/o
> nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
> loro utilizzo è consentito esclusivamente al destinatario del messaggio,
> per le finalità indicate nel messaggio stesso. Qualora riceviate questo
> messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
> darcene notizia via e-mail e di procedere alla distruzione del messaggio
> stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
> divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
> utilizzarlo per finalità diverse, costituisce comportamento contrario ai
> principi dettati dal D.Lgs. 196/2003.
>
>
>
> The information in this message and/or attachments, is intended solely for
> the attention and use of the named addressee(s) and may be confidential or
> proprietary in nature or covered by the provisions of privacy act
> (Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
> Code).Any use not in accord with its purpose, any disclosure, reproduction,
> copying, distribution, or either dissemination, either whole or partial, is
> strictly forbidden except previous formal approval of the named
> addressee(s). If you are not the intended recipient, please contact
> immediately the sender by telephone, fax or e-mail and delete the
> information in this message that has been received in error. The sender
> does not give any warranty or accept liability as the content, accuracy or
> completeness of sent messages and accepts no responsibility for changes
> made after they were sent or for other risks which arise as a result of
> e-mail transmission, viruses, etc.
>
> -------------------------------------------------------
>
------------------------------------------------------------------------------
One dashboard for servers and applications across Physical-Virtual-Cloud
Widest out-of-the-box monitoring support with 50+ applications
Performance metrics, stats and reports that give you Actionable Insights
Deep dive visibility with transaction tracing using APM Insight.
http://ad.doubleclick.net/ddm/clk/290420510;117567292;y
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
