Slick confused about numThreads and best practice for good performance

Question

I am using the PlayFrameWork with Slick and using it in a system that is all I/O database heavy. In my application.conf file I have this setting:

play {
  akka {
    akka.loggers = ["akka.event.slf4j.Slf4jLogger"]
    loglevel = WARNING
    actor {
      default-dispatcher = {
        fork-join-executor {
          parallelism-factor = 20.0
        }
      }
    }
  }
}

This obviously gives me 20 threads per core for the play application and as I understand it Slick creates it's own threadpool, is the NumThreads field in Slick mean that that's the total number of threads or is it (NumThreads x CPU's)? And is there any best practice for best performance? I currently have my settings configured as:

database {
  dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"
  properties = {
    databaseName = "dbname"
    user = "postgres"
    password = "password"
  }
  numThreads = 10
}

Answer 1

numThreads is simple number of thread in Thread pool. Slick use this thread pool for executing querying.

The following config keys are supported for all connection pools, both built-in and third-party:

• numThreads (Int, optional, default: 20): The number of concurrent threads in the thread pool for asynchronous execution of database actions. See the HikariCP wiki for more imformation about sizing the thread pool correctly. Note that for asynchronous execution in Slick you should tune the thread pool size (this parameter) accordingly instead of the maximum connection pool size.

• queueSize (Int, optional, default: 1000): The size of the queue for database actions which cannot be executed immediately when all threads are busy. Beyond this limit new actions fail immediately. Set to 0 for no queue (direct hand-off) or to -1 for an unlimited queue size (not recommended).

The pool is tuned for asynchronous execution by default. Apart from the connection parameters you should only have to set numThreads and queueSize in most cases. In this scenario there is contention over the thread pool (via its queue), not over the connections, so you can have a rather large limit on the maximum number of connections (based on what the database server can still handle, not what is most efficient). Slick will use more connections than there are threads in the pool when sequencing non-database actions inside a transaction.

The following config keys are supported for HikariCP:

• url (String, required): JDBC URL

• driver or driverClassName (String, optional): JDBC driver class to load user (String, optional)*: User name

• password (String, optional): Password

• isolation (String, optional): Transaction isolation level for new connections. Allowed values are: NONE, READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, SERIALIZABLE.

• catalog (String, optional): Default catalog for new connections.

• readOnly (Boolean, optional): Read Only flag for new connections.

• properties (Map, optional): Properties to pass to the driver or DataSource.

• dataSourceClass (String, optional): The name of the DataSource class provided by the JDBC driver. This is preferred over using driver. Note that url is ignored when this key is set (You have to use properties to configure the database connection instead).

• maxConnections (Int, optional, default: numThreads * 5): The maximum number of connections in the pool.

• minConnections (Int, optional, default: same as numThreads): The minimum number of connections to keep in the pool.

• connectionTimeout (Duration, optional, default: 1s): The maximum time to wait before a call to getConnection is timed out. If this time is exceeded without a connection becoming available, a SQLException will be thrown. 1000ms is the minimum value.

• validationTimeout (Duration, optional, default: 1s): The maximum amount of time that a connection will be tested for aliveness. 1000ms is the minimum value.

• idleTimeout (Duration, optional, default: 10min): The maximum amount of time that a connection is allowed to sit idle in the pool. A value of 0 means that idle connections are never removed from the pool.

• maxLifetime (Duration, optional, default: 30min): The maximum lifetime of a connection in the pool. When an idle connection reaches this timeout, even if recently used, it will be retired from the pool. A value of 0 indicates no maximum lifetime.

• connectionInitSql (String, optional): A SQL statement that will be executed after every new connection creation before adding it to the pool. If this SQL is not valid or throws an exception, it will be treated as a connection failure and the standard retry logic will be followed.

• initializationFailFast (Boolean, optional, default: false): Controls whether the pool will "fail fast" if the pool cannot be seeded with initial connections successfully. If connections cannot be created at pool startup time, a RuntimeException will be thrown. This property has no effect if minConnections is 0.

• leakDetectionThreshold (Duration, optional, default: 0): The amount of time that a connection can be out of the pool before a message is logged indicating a possible connection leak. A value of 0 means leak detection is disabled. Lowest acceptable value for enabling leak detection is 10s.

• connectionTestQuery (String, optional): A statement that will be executed just before a connection is obtained from the pool to validate that the connection to the database is still alive. It is database dependent and should be a query that takes very little processing by the database (e.g. "VALUES 1"). When not set, the JDBC4 Connection.isValid() method is used instead (which is usually preferable).

• registerMbeans (Boolean, optional, default: false): Whether or not JMX Management Beans ("MBeans") are registered.

Slick have very transparent configuration setting.Best practice for good performance, There is no thumb rule. It depends on your database(how many parallel connection provides) and your application. It is all about tuning between database & application.