Azure太容易达到数据库cpu限制 [英] Azure hits database cpu limits too easily

问题描述

背景简介:-我们有一个具有以下主要组成部分的SAAS解决方案.1.我们有一个Web门户后端，管理员可以在其中编辑数据.2.我们有一个由移动设备调用的Web API.移动设备可跟踪或报告学生的阅读进度

Background in a nutshell: - We have a SAAS solution with the following main components. 1. We have a web-portal back-end where administrators can edit data. 2. We have a web API that is called by mobile devices. The mobile devices track or report on students reading progress

到目前为止，该解决方案已托管在虚拟服务器上.现在，我们将解决方案迁移到Azure框架，以便我们可以利用弹性数据库池的可伸缩性.当帖子可以异步处理时，我们使用事件主题来处理来自移动设备的大量帖子，但是有些帖子需要同步处理，而且当涉及多个并发连接时，我们发现Azure的结构确实很慢.

Up to now the solution was hosted on virtual servers. Now we are migrating the solution to the Azure framework so that we can take advantage of the scalability of elastic database pools. We are using event topics to handle large volumes of posts from the mobile devices when the posts can be processed asynchronously, but there are some posts that need to be processed synchronously, and we are finding Azure's fabric really slow when it comes to multiple concurrent connections.

问题的一个示例:-

因此，当Azure运行如下查询时:-

So when Azure runs a query like the following: -

SELECT q.Category, COUNT(*)
FROM Question q
JOIN Answer a
ON a.QuestionId = q.QuestionId
GROUP BY q.Category
ORDER BY q.Category

在以下所有情况下，SQL CPU的峰值都超过97％:-
1. DTU是50，并且有多个并发呼叫.
2. DTU为1500，并且有5个或更多并发呼叫.
3. DTU是4000，并且有20个或更多并发呼叫.

The SQL CPU peaks above 97% in all the following scenarios: -
1. The DTU's are 50 and there is more than one concurrent call.
2. The DTU's are 1500 and there are 5 or more concurrent calls.
3. The DTU's are 4000 and there are 20 or more concurrent calls.

因此，我们与Microsoft展开了支持电话.我们花了一个多星期的时间来研究从sql统计信息和索引到Web api定价层的事物.毕竟，我们仍然提供了证据，证明在上述情况下CPU在SQL数据库中达到峰值.

So we opened a support call with Microsoft. We spent more than a week of investigating things from sql statistics and indexes up to web api pricing tiers. After all that we still came up with the evidence that the CPU was peaking in the SQL database with the scenarios as outlined above.

这导致了不可避免的重写系统的大块"这种说法.

This leads to the inevitable "re-write large chunks of your system" kind of argument.

因此，潜在的问题是弹性数据库池似乎无法在标准SQL数据库的能力范围内执行任何操作.而且，独立数据库的性能似乎无法与虚拟服务器的性能竞争.

So the underlying issue is that elastic database pools can't seem to perform anywhere near the ability of standard SQL databases. Also, the stand-alone database's performance doesn't seem to compete with the virtual server's performance.

这太令人沮丧了，因为出于维护性能和增加可伸缩性的原因，我们建议使用弹性数据库池.目前，我们在一台虚拟服务器上运行着700多个客户.并期望为每个客户创建一个分片数据库.当时的想法是，我们可以从数百个客户扩展到成千上万的客户.实际上，我们正在努力使Azure结构在与虚拟服务器上类似的性能下可以在任何地方执行.因此，问题是要问是否有人在使Azure以合理的速度执行不重要的任务方面有丰富的经验?(最好不必重写系统的大块内容)

This is so frustrating because Elastic databases pools were recommended for us for reasons of maintaining performance and adding scalability. We currently run 700+ customers on one virtual server; and were expecting to create one shard database per customer. The idea being that we could then scale up from hundreds of customers to tens of thousands of customers. In reality we are fighting to get the Azure fabric to perform anywhere near the kind of performance we have on virtual servers. So this question is to ask if there is anyone with significant experience in making Azure perform non-trivial tasks at a reasonable pace? (preferably without having to re-write large chunks of the system)

推荐答案

在将SQL数据库迁移到云中时，思维方式发生了转变.

It takes a shift in thinking when migrating your SQL databases to the cloud.

在本地世界中，我们已经习惯了功能强大的机器，足以应付大量的工作负载.这是因为物理计算机是使用所需的资源构建的，以处理繁重的工作量(为处理它们需要处理的最大任务而不是最小的任务而构建).由于资源的过度可用性，我们经常允许低效率处理查询和基础架构.由于资源的可用性过高，因此影响通常很小.

In the on-premises world, we are used to powerful machines which are beefy enough to handle intense workloads. This is because physical machines are built with the needed resources to handle big workloads with heavy processing (built for the biggest task they need to handle, rather than the smallest task). Due to the over availability of resources, we often allow inefficiencies to work into queries and underlying schemas. With the excess availability of resources, the affect is often minimal.

但是，然后您尝试将这些相同的数据库移至Azure中，但效果却不尽如人意.请记住，Azure是按使用付费的模型.您为X的资源支付X，而当您需要更多的资源时，则为X支付更多的X.由于采用了这种模型，您必须考虑到您在数据库中所做的一切实际上都在花钱.每个查询都会花费您金钱.效率低下会给您带来越来越多的金钱.等当每个月明确地为资源付费时，我们倾向于购买不足(通常用于最小的任务)，因为我们感觉自己在浪费钱.这意味着，当偶尔需要执行大任务时，我们没有足够的资源来处理它，并且性能降低了.这使我们认为Azure成本更高，但性能却较差.

But, then you try and move those same databases into Azure and things don't work quite as well. Remember that Azure is a pay-per-use kind of model. You pay X for Y resources, and when you need more, you pay more X for more Y. Because of this model, you have to consider that everything you do in your database effectively costs you money. Every query costs you money. Each and every inefficiency costs you more and more money. Etc. Etc. When explicitly paying for resources every single month, we tend to under buy (generally for the smallest task) because we feel like we are wasting money otherwise. This means that when an occasional big task needs to run, we don't have enough resources to handle it and performance is degraded. This leads us to think that Azure costs more but has worse performance.

因此，如果您愿意为此付费，则可以随时增加Azure中的资源来改善您的状况.或者，您也可以像其他人一样建议并优化查询和基础架构，并在每次操作时节省成本.

So to improve your situation, you can always increase your resources in Azure if you are willing to pay for it. Or you can do as others suggest and optimize your queries and underlying schemas and realize cost savings each time you do it.

这篇关于Azure太容易达到数据库cpu限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！