缓存Slick DBIO操作 [英] Cache Slick DBIO Actions

问题描述

我正试图加快"SELECT * FROM WHERE name =?"的速度. Play中的一种查询！ + Scala应用程序.我正在使用Play 2.4 + Scala 2.11 + play-slick-1.1.1软件包.该软件包使用Slick-3.1版本.

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.

我的假设是，slick从DBIO动作生成Prepared语句，然后执行它们.因此，我尝试通过打开标志 cachePrepStmts = true 来缓存他们 但是，我仍然在日志中看到正在准备语句..."消息，这意味着PS没有被缓存！一个人应该如何指示光滑的人来缓存它们?

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?

如果我运行以下代码，是否不应该在某些时候缓存PS?

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.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
    }
  }

}

更新1

我按照@pawel关于使用编译查询的建议尝试了以下操作:

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 ...

此时间也保持不变.所需的输出是多少?我是否应该不再查看这些声明?如何验证Prepared语句是否确实被重用.

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.

推荐答案

对于MySQL，您需要设置一个附加的jdbc标志useServerPrepStmts=true

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

HikariCP的 MySQL配置页面链接到一个非常有用的文档，该文档提供了一些内容简单的性能调整配置选项MySQL jdbc.

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

以下是我发现有用的一些内容(对于Hikari的API未公开的选项，您需要&将它们附加到jdbc url中).请确保通读每个选项的链接文档和/或MySQL文档.应该大部分可以安全使用.

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

此外，请注意，每个线程都缓存了语句；根据您为Hikari连接maxLifetime设置的内容以及服务器的负载量，服务器和客户端上的内存使用量将相应增加(例如，如果将连接最大生存期设置为低于MySQL默认值8小时，则服务器和客户端都将保持在每个连接的生命期内，N个准备好的语句在内存中仍然存在.

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.好奇瓶颈是否确实是语句缓存或Slick特有的东西.

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

编辑

to log语句启用查询日志.在MySQL 5.7上，您将添加到my.cnf:

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

，然后sudo touch /var/log/mysqlgeneral.log，然后重新启动mysqld.注释掉上面的配置行，然后重新启动以关闭查询日志记录.

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.

这篇关于缓存Slick DBIO操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！