英文:
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<MyDatatype> runQuery(MyParams params) {
String sql = createSqlFromParams(params);
List<Object> 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 <-> 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论