Spring Boot JdbcTemplate – 禁用语句缓存?

huangapple go评论71阅读模式
英文:

Spring Boot JdbcTemplate - disable statement cache?

问题

我有一个使用REST API与Spring Boot后端通信的Spring Boot后端。此后端使用Spring JdbcTemplate与PostgreSQL数据库连接并执行查询。

我发现一个问题,重复执行同样的请求10次后,其中一个请求的速度显著变慢。我已经将问题缩小到使用JdbcTemplate从数据库中检索数据的代码部分。更具体地说,问题发生在每个Tomcat工作线程的第二次迭代:

[nio-8080-exec-1] --- GET /myresource - 执行时间 400 毫秒
[nio-8080-exec-2] --- GET /myresource - 执行时间 300 毫秒
[nio-8080-exec-3] --- GET /myresource - 执行时间 285 毫秒
...
[io-8080-exec-10] --- GET /myresource - 执行时间 200 毫秒

现在每个Tomcat工作线程都已经接收并处理了一个请求,下一次其中一个工作线程再次接收相同的请求(使用完全相同的查询)时,执行时间会变长10-15倍:

[nio-8080-exec-1] --- GET /myresource - 执行时间 6000 毫秒
[nio-8080-exec-2] --- GET /myresource - 执行时间 5500 毫秒
[nio-8080-exec-3] --- GET /myresource - 执行时间 6700 毫秒

我尝试使用psql或pgAdmin运行相同的查询,没有任何问题。这让我相信JdbcTemplate在某种程度上为每个工作线程缓存了查询,第二次运行查询时,缓存生效,但出于某种原因变得更慢,但我不确定。我还尝试将Tomcat更改为Jetty / Undertow,但仍然存在同样的问题,因此我认为这必须与JdbcTemplate有关。

是否有任何方法可以禁用JdbcTemplate中的此类缓存,或者是否有其他方法可以避免这种行为?

谢谢!

编辑:
我的application.yaml:

spring:
    datasource:
        platform: postgres
        url: my-jdbc-url
        username: my-user
        password: my-password

代码根据请求中的参数动态创建带有WHERE / AND子句的查询,但是相同的请求参数总是创建相同的查询。代码示例:

public List<MyDatatype> runQuery(MyParams params) {
    String sql = createSqlFromParams(params);
    List<Object> params = createParamsList(params);
    return jdbcTemplate.query(sql, params.toArray(), myDatatypeRowMapper());
}

最终查询可能如下所示(使用postGIS函数按坐标之间的距离排序):

SELECT * FROM my_table 
WHERE x IN [1,2,3] 
AND y BETWEEN 0 AND 1000
AND z BETWEEN 0 AND 500
ORDER BY geom <-> other_geom
LIMIT 1000;

编辑 2:
根据@M.Deinum的建议,添加

spring.datasource.hikari.data-source-properties.preparedStatementCacheQueries=0

解决了这个问题!

英文:

I have a Spring Boot backend that I communicate with using a REST-API. This backend uses Spring JdbcTemplate to connect and execute queries on a PostgreSQL database.

I have found an issue where one request becomes significantly slower after being repeated exactly 10 times. I have narrowed the issue down to the part of code that is using the JdbcTemplate to retrieve data from the database. More specifically, the problem occurs on the second iteration of each tomcat worker-thread:

[nio-8080-exec-1] --- GET /myresource - Execution time 400 ms
[nio-8080-exec-2] --- GET /myresource - Execution time 300 ms
[nio-8080-exec-3] --- GET /myresource - Execution time 285 ms
...
[io-8080-exec-10] --- GET /myresource - Execution time 200 ms

Now each tomcat worker has received and handled one request each, the next time one of these workers receives the same request, which uses the exact same query, the execution time is 10-15 times longer:

[nio-8080-exec-1] --- GET /myresource - Execution time 6000 ms
[nio-8080-exec-2] --- GET /myresource - Execution time 5500 ms
[nio-8080-exec-3] --- GET /myresource - Execution time 6700 ms

I have tried running the same query using psql or pgAdmin and there is no issue. This leads me to believe that the JdbcTemplate is caching the query somehow for each worker and the second time the query is run the cache kicks in and for some reason it is much slower, but I am not sure. I have also tried changing tomcat to jetty/undertow but the same problem occurs there so I believe it must have to do with the JdbcTemplate.

Is there any way to disable this type of caching with the JdbcTemplate, or is there anything else I can do to avoid this behaviour?

Thanks!

EDIT:
My application.yaml:

spring:
    datasource:
        platform: postgres
        url: my-jdbc-url
        username: my-user
        password: my-password

The code creates the query with WHERE/AND clauses dynamically based on the parameters in the request, but the same request parameters always creates the same query. Code:

public List&lt;MyDatatype&gt; runQuery(MyParams params) {
    String sql = createSqlFromParams(params);
    List&lt;Object&gt; params = createParamsList(params);
    return jdbcTemplate.query(sql, params.toArray(), myDatatypeRowMapper());
}

The query would look like this in the end (using postGIS-functions to order by distance between coordinates):

SELECT * FROM my_table 
WHERE x IN [1,2,3] 
AND y BETWEEN 0 AND 1000
AND z BETWEEN 0 AND 500
ORDER BY geom &lt;-&gt; other_geom
LIMIT 1000;

EDIT 2:
As per the suggestion by @M.Deinum, adding
spring.datasource.hikari.data-source-properties.preparedStatementCacheQueries=0 solved the issue!

答案1

得分: 1

假设您正在使用Spring Boot中的默认连接池HikariCP,您可以使用 spring.datasource.hikari.data-source-properties 来提供附加的、特定于驱动程序的属性。

要禁用服务器预准备语句,您需要包括 preparedStatementCacheQueries 属性,并将其值设置为0(默认值为256)。

spring.datasource.hikari.data-source-properties.preparedStatementCacheQueries=0

这将禁用整个缓存,可能会影响您的应用程序的不同领域。

这些相关的问题这里这里似乎暗示您可能希望检查您的磁盘、索引等,而不是禁用查询缓存。

英文:

Assuming you are using the default connection pool, HikariCP, in Spring Boot you can use the spring.datasource.hikari.data-source-properties to supply additional, driver specific properties.

To disable Server Prepared Statements you need to include the preparedStatementCacheQueries property and set the value to 0 (default is 256).

spring.datasource.hikari.data-source-properties.preparedStatementCacheQueries=0

This will disable the whole caching and might influence different areas of your application.

These related questions here and here seem to hint that you might want to check your disk, indexes etc. instead of disabling the query cache.

huangapple
  • 本文由 发表于 2020年10月21日 21:53:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/64465108.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定