numThreads, maxConnections and SQLTransientConnectionException

[Nadav Samet]

Hi!

Slick's default is to set HikariCP's maxConnections to 5 times numThreads (see here). So by default, we are running with 20 threads and maxConnections=100.

Is it possible for a Slick thread to use more than one connection at a time? If so, what's the intuition behind having it 5 times - how can we determine the right number for us? 

We are occasionally getting exceptions like this, which indicate to me that one of the 20 slick threads can't get an available connection within 2 seconds:

Stack trace:
java.sql.SQLTransientConnectionException: db - Connection is not available, request timed out after 2018ms.
        at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:195)
        at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:147)
        at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:83)
        at slick.jdbc.hikaricp.HikariCPJdbcDataSource.createConnection(HikariCPJdbcDataSource.scala:12)
        at slick.jdbc.JdbcBackend$BaseSession.conn$lzycompute(JdbcBackend.scala:415)
        at slick.jdbc.JdbcBackend$BaseSession.conn(JdbcBackend.scala:414)
        at slick.jdbc.JdbcBackend$SessionDef$class.prepareStatement(JdbcBackend.scala:297)
        at slick.jdbc.JdbcBackend$BaseSession.prepareStatement(JdbcBackend.scala:407)
        at slick.jdbc.StatementInvoker.results(StatementInvoker.scala:33)
        at slick.jdbc.StatementInvoker.iteratorTo(StatementInvoker.scala:22)
        at slick.jdbc.Invoker$class.foreach(Invoker.scala:48)
        at slick.jdbc.StatementInvoker.foreach(StatementInvoker.scala:16)
        at slick.jdbc.StreamingInvokerAction$class.run(StreamingInvokerAction.scala:22)
        at slick.driver.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1.run(JdbcActionComponent.scala:218)
        at slick.driver.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1.run(JdbcActionComponent.scala:218)
        at slick.backend.DatabaseComponent$DatabaseDef$$anon$2.liftedTree1$1(DatabaseComponent.scala:237)
        at slick.backend.DatabaseComponent$DatabaseDef$$anon$2.run(DatabaseComponent.scala:237)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)

[Stefan Zeiger]

On 2016-03-03 20:53, Nadav Samet wrote:

Hi!

Slick's default is to set HikariCP's maxConnections to 5 times numThreads (see here). So by default, we are running with 20 threads and maxConnections=100.

Is it possible for a Slick thread to use more than one connection at a time?

No, but you can have a connection without an associated thread. This happens whenever you pin a session (e.g. for a transaction or streaming results) and give up the database thread to perform a non-I/O action.

If so, what's the intuition behind having it 5 times - how can we determine the right number for us?

It's just an estimate that can and should be changed as needed. The number of threads determines how many things you can *run* in parallel on the database server and the number of connections determines you many things you can *keep in memory* at the same time on the database server.

--
Stefan Zeiger
Senior Software Engineer, Scala & Slick
stefan...@lightbend.com
Twitter: @StefanZeiger