Good afternoon,
What's the meaning of connection 0/8 in database stats???
Thank you
Ignition uses connection pooling. That is, it makes multiple simultaneous connections, per each data source's advanced settings, and reuses them over and over and over.
The default is eight. "0/8" means no connections were running any query at that instant, and there were eight connections idling.
Based on the long-running queries in your screenshot, you desperately need a database administrator to analyze your operation and likely create indexes on critical table columns.
For this reason, sometimes in the Log of server ignition there is this Database error????
java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:142)
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:734)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:299)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnectionImpl(DatasourceManagerImpl.java:169)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnection(DatasourceImpl.java:292)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnection(DatasourceManagerImpl.java:152)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.getConnection(GatewayDBUtilities.java:100)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:50)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:431)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1450)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:799)
at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1631)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:548)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:600)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1624)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1440)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:501)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1355)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.Server.handle(Server.java:516)
at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:487)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:732)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:479)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:338)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:315)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:173)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:409)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:321)
at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:428)
at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:232)
at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134)
... 51 common frames omitted
Yes, that is what you get when all of the connections in the pool are busy (typically with pathologically long-running queries) when another query needs to run.
Fix your pathologically long queries.
1 Like
When in the table there are much record, select query are very slow to execute.
How can I speed up execution of this query?
I try to create views but nothing change.
Than you
The most important thing would be to index your tables to suit the queries.
- What database are you using? (MySQL, MS SQL, something else?)
- What database management tool are you using?
I use Microsoft Sql Server 2014 with Microsoft Sql Server Management Studio
you'll want some indexes on those two id columns you use to filter
1 Like
Excusme, I don't understand
Suggest you outsource this task to a DBA (dataBase Administrator), in that case
2 Likes
Hi,
in the property Connection Pooling of database connection , I increase number of connection from 8 to 20. Is it possible to increase this value???
In your OP, you were using 0/8. Changing to 20 would only help if you were at 8/8. You have already been advised on how to reduce the database load.
The snapshot shown is a point in time where pool activity was 0/8, but the error indicates that it was stuck at 8/8 for an extended period of time.
But, the underlying cause (poor indices) apparently hasn't been fixed, so a change to a pool size of 20 would simply move the error to 20/20.
@stefano.sforna : The pool size isn't the problem. The long queries times are the problem. You must fix them.
Edit: after another look at the original screen shot, I noticed that some of the long queries have this fragment in their where clauses:
.... WHERE .... cast(someColumn as datetime) BETWEEN 'someStartTS' AND 'someEndTS' ....
That is a pathological filter, as it requires the cast() to be executed on every row in the table before the BETWEEN operator can be applied.
Such must be rewritten in this form:
.... WHERE ... someColumn BETWEEN cast('someStartTS` as someColumnType) AND cast('someEndTS' as someColumnType) ....
In this latter form, the casts are executed just for the endpoints, and those endpoints can then be efficiently applied to any index of someColumn.
Even better would be this form:
.... WHERE ... someColumn BETWEEN ? AND ? ....
where the SQL has no cast at all, and the start/end timestamps are passed unambiguously as JDBC parameters.
1 Like