Confluence instance stops after high db utilisation

[Anuj Sapate]

Hello there,

Our Confluence server stops working frequently. There are following errors keeps appearing in the logs:

25-May-2018 02:06:48.136 WARNING [ContainerBackgroundProcessor[StandardEngine[Standalone]]] org.apache.catalina.valves.StuckThreadDetectionValve.notifyStuckThreadDetected Thread "http-nio-8090-exec-2" (id=179) has been active for 65,047 milliseconds (since 5/25/18 2:05 AM) to serve the same request for https://myconfluence/rest/page-tracking-services/1.0/page-tracking/page-view and may be stuck (configured threshold for this StuckThreadDetectionValve is 60 seconds). There is/are 1 thread(s) in total that are monitored by this Valve and may be stuck.
 java.lang.Throwable
 at java.net.SocketInputStream.socketRead0(Native Method)
 at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
 at java.net.SocketInputStream.read(SocketInputStream.java:170)
 at java.net.SocketInputStream.read(SocketInputStream.java:141)
 at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:140)
 at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:109)
 at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:67)
 at org.postgresql.core.PGStream.receiveChar(PGStream.java:280)
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1916)
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
 at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
 at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
 at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
 at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116)
 at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:379)
 at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
 at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.getResultSet(AbstractLoadPlanBasedLoader.java:434)
 at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:186)
 at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:121)
 at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:86)
 at org.hibernate.loader.entity.plan.AbstractLoadPlanBasedEntityLoader.load(AbstractLoadPlanBasedEntityLoader.java:167)
 at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:4004)
 at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:508)
 at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:478)
 at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:219)
 at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:278)
 at org.hibernate.event.internal.DefaultLoadEventListener.doOnLoad(DefaultLoadEventListener.java:121)
 at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:89)
 at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1218)
 at org.hibernate.internal.SessionImpl.internalLoad(SessionImpl.java:1101)
 at org.hibernate.type.EntityType.resolveIdentifier(EntityType.java:639)
 at org.hibernate.type.ManyToOneType.assemble(ManyToOneType.java:266)
 at org.hibernate.cache.internal.StandardQueryCache.assembleCachedResult(StandardQueryCache.java:208)
 at org.hibernate.cache.internal.StandardQueryCache.get(StandardQueryCache.java:193)
 at org.hibernate.loader.Loader.getResultFromQueryCache(Loader.java:2542)
 at org.hibernate.loader.Loader.listUsingQueryCache(Loader.java:2450)
 at org.hibernate.loader.Loader.list(Loader.java:2422)
 at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502)
 at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)
 at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
 at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1458)
 at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1426)
 at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1398)
 at com.atlassian.confluence.pages.persistence.dao.hibernate.HibernatePageDao.lambda$getPages$0(HibernatePageDao.java:164)
 ...

The number of threads gets increased and the instances stops working after some time.

Is there any workaround available to get the rid of this issue?

UPDATES:

The logs before stopping the instance:

SEVERE [http-nio-8092-Acceptor-0] org.apache.tomcat.util.net.NioEndpoint$Acceptor.run Socket accept failed
 java.io.IOException: Too many open files
 at sun.nio.ch.ServerSocketChannelImpl.accept0(Native Method)
 at sun.nio.ch.ServerSocketChannelImpl.accept(ServerSocketChannelImpl.java:422)
 at sun.nio.ch.ServerSocketChannelImpl.accept(ServerSocketChannelImpl.java:250)
 at org.apache.tomcat.util.net.NioEndpoint$Acceptor.run(NioEndpoint.java:693)
 at java.lang.Thread.run(Thread.java:745)

[Mirek]

I think this doc should help you

https://confluence.atlassian.com/confkb/too-many-open-files-error-659784628.html

[Anuj Sapate]

Thanks Mirek.

Previously, the limit was set to 16384 but due to the commented lines  in pam_limits.so file, it was not reflected. So this will solve my problem of too many open files. But the the issue with stuck threads is still there.