英文:
Detect and avoid 100% CPU usage due to incorrect join query
问题
我们有一个 C# 应用程序，最终用户可以连接到他们的数据库服务器并运行查询以提取数据。我们在代码中使用 ADO.NET 执行查询并根据我们的应用程序处理数据。
有时，最终用户会错误地输入不正确的连接查询或连接关系，这些语法上是正确的，但不是预期的查询。这些不正确的连接或错误查询会导致数据库服务器使用更多或100%的CPU，从而引发问题。在代码或数据库级别是否有任何选项可以验证查询，以查找不相关的连接条件或关系，并避免这种情况发生？
最终用户可以手动跟踪CPU使用情况，但这不是可行的选择。我们正在寻找自动避免这种情况发生的选项，无论是在代码级别还是数据库级别。
英文:
We have a C# application in which the end user can connect to their database server and run a query to fetch the data. We execute the query using ADO.NET in the codebase and process the data according to our application.
Sometimes, the end user will mistakenly enter an incorrect join query or join relationship, which is syntactically correct but not the intended query. This incorrect join or bad query causes the database server to use a larger or 100% of its CPU, causing an issue. Are there any options at the code or database level to validate the query to find irrelevant join conditions or relationships and to avoid such scenarios?
>The end-user can manually track the CPU usage, but this is not a viable option. We are looking for options to avoid this automatically, either at the code level or the database level.
答案1
得分: 4
你可以通过以下方式来防止这种情况发生：
-
将
max_parallel_workers_per_gather设置为 0，这样单个SQL语句永远不会使用多个CPU核心。 -
将
statement_timeout设置为相对较低的值，这样执行时间过长的语句会被自动取消。
英文:
You can prevent that by configuring PostgreSQL as follows:
-
set
max_parallel_workers_per_gather = 0, so that a single SQL statement can never use more than a single CPU core -
set
statement_timeoutto a reasonably low value, so that statements that take too long are automatically canceled
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论