Cache Slick DBIO Actions

Question

I am trying to speed up "SELECT * FROM WHERE name=?" kind of queries in Play! + Scala app. I am using Play 2.4 + Scala 2.11 + play-slick-1.1.1 package. This package uses Slick-3.1 version.

My hypothesis was that slick generates Prepared statements from DBIO actions and they get executed. So I tried to cache them buy turning on flag cachePrepStmts=true However I still see "Preparing statement..." messages in the log which means that PS are not getting cached! How should one instructs slick to cache them?

If I run following code shouldn't the PS be cached at some point?

for (i <- 1 until 100) {
  Await.result(db.run(doctorsTable.filter(_.userName === name).result), 10 seconds)
}

Slick config is as follows:

slick.dbs.default {
  driver="slick.driver.MySQLDriver$"
  db {
    driver="com.mysql.jdbc.Driver"

    url="jdbc:mysql://localhost:3306/staging_db?useSSL=false&cachePrepStmts=true"

    user = "user"

    password = "passwd"

    numThreads = 1  // For not just one thread in HikariCP

    properties = {
      cachePrepStmts = true
      prepStmtCacheSize = 250
      prepStmtCacheSqlLimit = 2048
    }
  }

}

Update 1

I tried following as per @pawel's suggestion of using compiled queries:

val compiledQuery = Compiled { name: Rep[String] =>
  doctorsTable.filter(_.userName === name)
}


val stTime = TimeUtil.getUtcTime
for (i <- 1 until 100) {
  FutureUtils.blockFuture(db.compiledQuery(name).result), 10)
}
val endTime = TimeUtil.getUtcTime - stTime
Logger.info(s"Time Taken HERE $endTime")

In my logs I still see statement like:

2017-01-16 21:34:00,510 DEBUG [db-1] s.j.J.statement [?:?] Preparing statement: select ...

Also timing of this is also remains the same. What is the desired output? Should I not see these statements anymore? How can I verify if Prepared statements are indeed reused.

Answer 1

You need to use Compiled queries - which are exactly doing what you want.

Just change above code to:

val compiledQuery = Compiled { name: Rep[String] =>
    doctorsTable.filter(_.userName === name)
}

for (i <- 1 until 100) {
  Await.result(db.run(compiledQuery(name).result), 10 seconds)
}

I extracted above name as a parameter (because you usually want to change some parameters in your PreparedStatements) but that's definitely an optional part.

For further information you can refer to: http://slick.lightbend.com/doc/3.1.0/queries.html#compiled-queries

Answer 2

For MySQL you need to set an additional jdbc flag, useServerPrepStmts=true

HikariCP's MySQL configuration page links to a quite useful document that provides some simple performance tuning configuration options for MySQL jdbc.

Here are a few that I've found useful (you'll need to & append them to jdbc url for options not exposed by Hikari's API). Be sure to read through linked document and/or MySQL documentation for each option; should be mostly safe to use.

zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8
rewriteBatchedStatements=true
maintainTimeStats=false
cacheServerConfiguration=true
avoidCheckOnDuplicateKeyUpdateInSQL=true
dontTrackOpenResources=true
useLocalSessionState=true
cachePrepStmts=true
useServerPrepStmts=true
prepStmtCacheSize=500
prepStmtCacheSqlLimit=2048

Also, note that statements are cached per thread; depending on what you set for Hikari connection maxLifetime and what server load is, memory usage will increase accordingly on both server and client (e.g. if you set connection max lifetime to just under MySQL default of 8 hours, both server and client will keep N prepared statements alive in memory for the life of each connection).

p.s. curious if bottleneck is indeed statement caching or something specific to Slick.

EDIT

to log statements enable the query log. On MySQL 5.7 you would add to your my.cnf:

general-log=1
general-log-file=/var/log/mysqlgeneral.log

and then sudo touch /var/log/mysqlgeneral.log followed by a restart of mysqld. Comment out above config lines and restart to turn off query logging.