英文:
SpringBoot repository find response time increases with many concurrent calls, while manual database search not affected
问题
我正在尝试提高我的REST API的响应时间（我正在使用SpringBoot 2.1.3与Groovy 2.4.14和MSSQL）。我注意到，更受欢迎的GET API在某些时间段内所需的时间比它们应该的时间长得多（>4秒，而不是0.3秒）。我已经查看了CPU使用情况、内存、阻塞线程、阻塞数据库、DeferredResult、获取方案、SpringBoot和JPA设置等，这些都不是瓶颈，或者根本与问题无关（数据库搜索是一个简单的repository.findById()，针对一个具有一些基本字段的域对象）。
List<Object> getObjectForId(String id) {
curCallCount++
List<Object> objList = objectRepository.findAllById(id)
curCallCount--
objList
}
问题似乎在于服务中存在尚未退出的越多调用，API调用的响应时间就越长（几乎存在线性相关性，如果服务中有50个现有调用，则repository.findbyId()需要5秒，如果有200个，则需要20秒。与此同时，即使存在200个并发调用，手动数据库查询仍然很快（0.3秒）。
在Spring repository调用中，这是否是预期的行为？在存在许多并发调用服务的环境中，即使手动数据库搜索性能不受影响，repository.findById()的这种开销是从哪里产生的？
英文:
I am trying to improve the response time of my REST APIs (I am using SpringBoot 2.1.3 with Groovy 2.4.14 and MSSQL). I noticed that the more popular GET API are at certain time periods taking much longer than they should be (>4 seconds as opposed to 0.3 seconds). I've looked into CPU usage, memory, blocking threads, blocking DBs, DeferredResult, fetching schemes, SpringBoot and JPA settings, etc none of these were a bottleneck or were just not relevant (the database search is a simple repository.findById() for a domain object with a few primitive fields).
> List<Object> getObjectForId(String id) {
> curCallCount++
> List<Object> objList = objectRepository.findAllById(id)
> curCallCount--
> objList
> }
The issue seems to be that the more existing calls to the service that have not exited at the time of the call, the longer the response time of the API call (there is almost a linear correlation, if there are 50 existing calls to the service, repository.findbyId() takes 5 seconds, and if there are 200, it would take 20 seconds. Meanwhile while there are 200 concurrent calls, the manual database query is still fast (0.3 seconds).
Is this expected for the Spring repository calls? Where is this overhead from repository.findById() coming from in an environment when there are many concurrent calls to the service, even though the manual database search performance is not affected?
答案1
得分: 0
不了解API端，但我肯定会从查看SQL Server中的编译/重新编译率以及查看计划缓存使用情况开始。使用字符串变量可能会将所有参数作为nvarchar(max)传递，并限制查询计划的重用。
英文:
Don't know about the API side, but I would certainly start by looking at the compilation/recompilation rate in SQL Server and looking at your plan cache usage. Using a string variable might be passing all your parameters in as nvarchar(max) and limiting the reuse of your query plans.
答案2
得分: 0
问题出在Hikari池大小太小（默认为10），因此当有超过10个并发调用时，进程必须等待空闲线程。将其增加（例如增加到150）解决了这个问题。
英文:
The issue was the Hikari pool size being too small (10 by default, so that when there are more than 10 concurrent calls, processes must wait for a free thread). Increasing this (to 150, for example) resolved this issue.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论