[Geoserver-devel] WMS and postgreSQL connexions

GeoServer GeoServer Developer
1

Hi all,

using the following WMS request :
-----
http://localhost:8080/geoserver/wms?request=getMap&bbox=-5.37588,41.11496,10.51521,51.10804&width=548&height=520&srs=4326&format=image/png&layers=map2x0:serie_rouge,map2x0:serie_rouge,map2x0:serie_rouge&styles=sr250c,R21,sr250n&
-----

The first WMS request and postgreSQL connexions :
-----
postgres 1532 1 0 12:20 ? 00:00:00 /usr/bin/postmaster
postgres 1534 1532 0 12:20 ? 00:00:00 postgres: stats buffer process
postgres 1535 1534 0 12:20 ? 00:00:00 postgres: stats collector
process
postgres 1627 1532 0 12:20 ? 00:00:00 postgres: mapmaster cartes
127.0.0.1 idle in transaction
postgres 1629 1532 0 12:20 ? 00:00:00 postgres: mapmaster cartes
127.0.0.1 idle in transaction
postgres 2852 1532 0 15:34 ? 00:00:00 postgres: mapmaster cartes
127.0.0.1 idle in transaction
postgres 2855 1532 0 15:34 ? 00:00:00 postgres: mapmaster cartes
127.0.0.1 idle in transaction
postgres 2857 1532 0 15:34 ? 00:00:00 postgres: mapmaster cartes
127.0.0.1 idle in transaction
postgres 2859 1532 0 15:34 ? 00:00:00 postgres: mapmaster cartes
127.0.0.1 idle in transaction
ricci 2865 2041 0 15:34 pts/1 00:00:00 grep postgres
-----
The image is rendered correctly.

The second WMS (same one) shows 6 more connexions ...
Each WMS request gets 6 more connexions !
At the fifth one, I get no more response. Instead, I got a blank page with the
following log :

-----
11966851 [ATTENTION] org.geotools.renderer.lite.LiteRenderer - Exception
org.geotools.data.DataSourceException: Could not get connection rendering
layer org.geotools.map.DefaultMapLayer@anonymised.com
org.geotools.data.DataSourceException: Could not get connection
        at
org.geotools.data.jdbc.JDBCDataStore.getConnection(JDBCDataStore.java:930)
        at
org.geotools.data.jdbc.JDBCDataStore.executeQuery(JDBCDataStore.java:795)
        at
org.geotools.data.jdbc.JDBCDataStore.getFeatureReader(JDBCDataStore.java:563)
        at
org.geotools.data.jdbc.JDBCFeatureSource$1.reader(JDBCFeatureSource.java:202)
        at
org.geotools.renderer.lite.LiteRenderer.processStylers(LiteRenderer.java:894)
        at
org.geotools.renderer.lite.LiteRenderer.paint(LiteRenderer.java:496)
        at
org.vfny.geoserver.responses.wms.map.JAIMapResponse.execute(JAIMapResponse.java:294)
        at
org.vfny.geoserver.responses.wms.map.GetMapDelegate.execute(GetMapDelegate.java:135)
        at
org.vfny.geoserver.responses.wms.map.GetMapDelegate.execute(GetMapDelegate.java:95)
        at
org.vfny.geoserver.responses.wms.GetMapResponse.execute(GetMapResponse.java:73)
        at
org.vfny.geoserver.servlets.AbstractService.doService(AbstractService.java:365)
        at
org.vfny.geoserver.servlets.AbstractService.doGet(AbstractService.java:233)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
        at
org.vfny.geoserver.servlets.wms.WmsDispatcher.doResponse(WmsDispatcher.java:135)
        at
org.vfny.geoserver.servlets.wms.WmsDispatcher.doGet(WmsDispatcher.java:106)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
        at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:284)
        at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
        at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:257)
        at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
        at
org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:245)
        at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:199)
        at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
        at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:184)
        at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
        at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:164)
        at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
        at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:156)
        at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
        at
org.apache.catalina.valves.RequestFilterValve.process(RequestFilterValve.java:323)
        at
org.apache.catalina.valves.RemoteAddrValve.invoke(RemoteAddrValve.java:131)
        at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
        at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972)
        at
org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:206)
        at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:833)
        at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:732)
        at
org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:619)
        at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:688)
        at java.lang.Thread.run(Thread.java:534)
Caused by: org.postgresql.util.PSQLException: Backend start-up failed: FATAL
1: Sorry, too many clients already

        at
org.postgresql.jdbc1.AbstractJdbc1Connection.openConnectionV2(AbstractJdbc1Connection.java:715)
        at
org.postgresql.jdbc1.AbstractJdbc1Connection.openConnectionV3(AbstractJdbc1Connection.java:323)
        at
org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(AbstractJdbc1Connection.java:213)
        at org.postgresql.Driver.connect(Driver.java:139)
        at java.sql.DriverManager.getConnection(DriverManager.java:512)
        at java.sql.DriverManager.getConnection(DriverManager.java:140)
        at
org.postgresql.jdbc2.optional.BaseDataSource.getConnection(BaseDataSource.java:77)
        at
org.postgresql.jdbc2.optional.BaseDataSource.getConnection(BaseDataSource.java:53)
        at
org.postgresql.jdbc2.optional.ConnectionPool.getPooledConnection(ConnectionPool.java:47)
        at
org.geotools.data.jdbc.ConnectionPool.getConnection(ConnectionPool.java:121)
        at
org.geotools.data.jdbc.JDBCDataStore.getConnection(JDBCDataStore.java:928)
        ... 44 more
-----

Any idea ?

OS: Linux
postgreSQL: 7.2
geoserver: 1.2.0rc1

didier

2

Richard and Lists,

I cross-posted this bug as I think this may be bug either in the way I use the dataStore, or in the way the dataStore connects to the database (GeoTools land here). Hoping some of these guys might look it over (Jody, Chris?). I think you are hitting your thread max in you postGis database, resulting in the error. Wondering if this has to do with the implementation of the DataStore, or is it possible I'm not cashing the DataStore object properly.

Thanks in advance, David

Richard didier wrote:

Hi all,

using the following WMS request :
-----
http://localhost:8080/geoserver/wms?request=getMap&bbox=-5.37588,41.11496,10.51521,51.10804&width=548&height=520&srs=4326&format=image/png&layers=map2x0:serie_rouge,map2x0:serie_rouge,map2x0:serie_rouge&styles=sr250c,R21,sr250n&
-----

The first WMS request and postgreSQL connexions :
-----
postgres 1532 1 0 12:20 ? 00:00:00 /usr/bin/postmaster
postgres 1534 1532 0 12:20 ? 00:00:00 postgres: stats buffer process
postgres 1535 1534 0 12:20 ? 00:00:00 postgres: stats collector process
postgres 1627 1532 0 12:20 ? 00:00:00 postgres: mapmaster cartes 127.0.0.1 idle in transaction
postgres 1629 1532 0 12:20 ? 00:00:00 postgres: mapmaster cartes 127.0.0.1 idle in transaction
postgres 2852 1532 0 15:34 ? 00:00:00 postgres: mapmaster cartes 127.0.0.1 idle in transaction
postgres 2855 1532 0 15:34 ? 00:00:00 postgres: mapmaster cartes 127.0.0.1 idle in transaction
postgres 2857 1532 0 15:34 ? 00:00:00 postgres: mapmaster cartes 127.0.0.1 idle in transaction
postgres 2859 1532 0 15:34 ? 00:00:00 postgres: mapmaster cartes 127.0.0.1 idle in transaction
ricci 2865 2041 0 15:34 pts/1 00:00:00 grep postgres
-----
The image is rendered correctly.

The second WMS (same one) shows 6 more connexions ...
Each WMS request gets 6 more connexions !
At the fifth one, I get no more response. Instead, I got a blank page with the following log :

-----
11966851 [ATTENTION] org.geotools.renderer.lite.LiteRenderer - Exception org.geotools.data.DataSourceException: Could not get connection rendering layer org.geotools.map.DefaultMapLayer@anonymised.com
org.geotools.data.DataSourceException: Could not get connection
       at org.geotools.data.jdbc.JDBCDataStore.getConnection(JDBCDataStore.java:930)
       at org.geotools.data.jdbc.JDBCDataStore.executeQuery(JDBCDataStore.java:795)
       at org.geotools.data.jdbc.JDBCDataStore.getFeatureReader(JDBCDataStore.java:563)
       at org.geotools.data.jdbc.JDBCFeatureSource$1.reader(JDBCFeatureSource.java:202)
       at org.geotools.renderer.lite.LiteRenderer.processStylers(LiteRenderer.java:894)
       at org.geotools.renderer.lite.LiteRenderer.paint(LiteRenderer.java:496)
       at org.vfny.geoserver.responses.wms.map.JAIMapResponse.execute(JAIMapResponse.java:294)
       at org.vfny.geoserver.responses.wms.map.GetMapDelegate.execute(GetMapDelegate.java:135)
       at org.vfny.geoserver.responses.wms.map.GetMapDelegate.execute(GetMapDelegate.java:95)
       at org.vfny.geoserver.responses.wms.GetMapResponse.execute(GetMapResponse.java:73)
       at org.vfny.geoserver.servlets.AbstractService.doService(AbstractService.java:365)
       at org.vfny.geoserver.servlets.AbstractService.doGet(AbstractService.java:233)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
       at org.vfny.geoserver.servlets.wms.WmsDispatcher.doResponse(WmsDispatcher.java:135)
       at org.vfny.geoserver.servlets.wms.WmsDispatcher.doGet(WmsDispatcher.java:106)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:284)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
       at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:257)
       at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
       at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
       at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:245)
       at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:199)
       at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
       at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
       at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:184)
       at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
       at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:164)
       at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
       at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
       at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:156)
       at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
       at org.apache.catalina.valves.RequestFilterValve.process(RequestFilterValve.java:323)
       at org.apache.catalina.valves.RemoteAddrValve.invoke(RemoteAddrValve.java:131)
       at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
       at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
       at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972)
       at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:206)
       at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:833)
       at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:732)
       at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:619)
       at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:688)
       at java.lang.Thread.run(Thread.java:534)
Caused by: org.postgresql.util.PSQLException: Backend start-up failed: FATAL 1: Sorry, too many clients already

       at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnectionV2(AbstractJdbc1Connection.java:715)
       at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnectionV3(AbstractJdbc1Connection.java:323)
       at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(AbstractJdbc1Connection.java:213)
       at org.postgresql.Driver.connect(Driver.java:139)
       at java.sql.DriverManager.getConnection(DriverManager.java:512)
       at java.sql.DriverManager.getConnection(DriverManager.java:140)
       at org.postgresql.jdbc2.optional.BaseDataSource.getConnection(BaseDataSource.java:77)
       at org.postgresql.jdbc2.optional.BaseDataSource.getConnection(BaseDataSource.java:53)
       at org.postgresql.jdbc2.optional.ConnectionPool.getPooledConnection(ConnectionPool.java:47)
       at org.geotools.data.jdbc.ConnectionPool.getConnection(ConnectionPool.java:121)
       at org.geotools.data.jdbc.JDBCDataStore.getConnection(JDBCDataStore.java:928)
       ... 44 more
-----

Any idea ?

OS: Linux
postgreSQL: 7.2
geoserver: 1.2.0rc1

didier

-------------------------------------------------------
This SF.Net email is sponsored by: Oracle 10g
Get certified on the hottest thing ever to hit the market... Oracle 10g. Take an Oracle 10g class now, and we'll give you the exam FREE.
http://ads.osdn.com/?ad_id149&alloc_id?66&op=click
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel