英文:
A weird query observed in Azure appinsight
问题
最近，我们公司决定将我们的Spring应用迁移到Microsoft的Azure平台。
Azure给了我们通过appinsight监控我们的DB（MySQL）查询和它们的运行时的可能性。
一个澄清 - 我们正在调查我们的应用（Azure云）与我们的DB（本地MySQL）之间的流量。
在调查日志（遥测日志）之后，我们发现在大多数查询之前都会运行一个单独的查询，即查询：
SELECT ?;
每次这个查询都需要多达300毫秒，然后在此之后才会运行“真正”的查询，其运行时间在几微秒到几百毫秒之间不等。
我们正在尝试理解这个孤立的查询以及它来自何处，以查看是否可以改进SQL查询的运行时。
到目前为止，我们已经做了以下工作：
- 我已更改了本地的logback.xml文件，以便将MySQL查询记录在日志文件中。
- 我已将本地的Spring实例集成到p6spy中，以便我能够查看发送到DB的查询。
我得出的结论是：
- 使用p6spy，我观察到每次运行“真正”查询之前都会运行一个孤立的
SELECT 1查询。
我调查了我们的配置并发现这是“validationQuery”查询（server.xml），但不应该像SELECT ?那样花费那么长的时间。 - 我已经了解到这个查询可能会预加载来自DB的数据以填充查询参数。但这个解释似乎太牵强了，而且运行时间是困扰我的问题。
你是否遇到过类似的情况？我可以采取什么措施来找出SELECT ?查询的来源呢？
任何帮助都将不胜感激。
英文:
Lately, my company have decided to migrate our Spring application to Microsoft's Azure.
Azure gave us the possibility to monitor our DB (MySQL) queries and their runtime using appinsight.
A clarification - we investigate the traffic between our app (Azure cloud) and our DB (on-prem MySQL).
Upon investigation of the logs (telemetry logs) we have found that before most of the queries, a single query is being ran, the query:
SELECT ?;
This query takes up to 300 ms each time, then after that, the "real" query gets ran, which takes anywhere between few micro seconds to few hundreds of milliseconds.
We are trying to understand this lone query and where does it come from, to see if we can improve SQL queries runtime.
So far what we have done:
- I have altered my local logback.xml so that MySQL queries gets logged in a log file.
- I have integrated my local Spring instance to use p6spy so that I am able to see the queries that gets sent to the DB.
Conclusions I have came to:
- With p6spy I observed a lone
SELECT 1query that being ran before each "real" query is being ran.
I have investigated our configurations and saw that this is the "validationQuery" query (server.xml), but that should not take that long as theSELECT ?does. - I have read about the possibility that this query pre-loads the data from the DB to populate queried parameters. But that explanation seems too far fetched, and again, the run time is what bothering me.
Have you encountered anything similar? What can I do next to find the origin of that SELECT ? query.
Any help will be great.
答案1
得分: 1
由于您已确认您正在使用spring.datasource.validationQuery=SELECT 1属性。您需要查看数据源配置中的其他属性。像'test-on-borrow'和'validation-interval'这样的属性确实会产生影响。在您的情况下，验证查询在连接到数据库之前每次都会执行。
英文:
Since you have confirmed that you are using spring.datasource.validationQuery=SELECT 1 property. You need to have a look at the other properties in your datasource configuration. The properties like 'test-on-borrow' and 'validation-interval' does make a difference. In your situation the validation query is being executed every time before connecting to the database
答案2
得分: 0
这实际上检查网络延迟，然后再进行任何查询。
> 在开始性能基准测试运行之前，可以通过执行简单的SELECT 1查询来确定客户端和数据库之间的网络延迟。
请参阅https://learn.microsoft.com/en-us/azure/mysql/single-server/concept-performance-best-practices
分布式网络内容的问题在于信息可以存储在任何地方，因此对于快速响应至关重要，您应该检查最佳服务器。
英文:
This actually check the network latency, before any query.
>A quick check before starting any performance benchmarking run is to determine the network latency between the client and database using a simple SELECT 1 query
see https://learn.microsoft.com/en-us/azure/mysql/single-server/concept-performance-best-practices
The problem of distributed webcontent is, that information can be stored anywhere and so it ican be votal for quick responces, you would check for the best server.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论