海上 SQL 连接缓慢 [英] Slow SQL connection over sea
问题描述
这是我们所知道的:英国的 web 服务器 + 英国的 SQL SERVER因为我们无法实时复制数据库，所以我们为美国提出了这个解决方案:美国的 Web 服务器 + 与英国的 SQL SERVER 交谈.
This is what we have know: web server in the UK + SQL SERVER in the UK Because we can't make live replication of the database we come up this solution for the US: web server in the US + talk with the SQL SERVER in the UK.
我们看到一个奇怪的结果，我们的页面连接很慢，从美国到英国的代理更慢，我们不明白为什么.
And we see a strange result, we got a slow connection of the page, it's more slow from making proxy from the US to the UK and we don't understand why.
逻辑告诉我们，sql数据小于代理(页面中的所有数据).
The logic said to us that the sql data is smaller then the proxy (of all the data in the page).
你有什么想法吗?
推荐答案
如果你想让你的 SQL 数据库离你的服务器那么远，你需要认真考虑减少使用的顺序查询的数量.
If you want your SQL database to be that far away from your server, you need to seriously think about reducing the number of sequential queries used.
如果您对 MySQL 服务器的往返 ping 为 0.2 毫秒，并且您进行了查询，则这将等待往返通信.如果按顺序进行 5 次往返查询(即在开始第二次查询之前等待第一个查询结束)，则需要 0.2ms * 5 = 1ms.
If your round-trip ping is 0.2ms to the MySQL server, and you make a query, this waits for round-trip communication. If you make 5 round-trip queries sequentially (that is, you wait for the first query to end before starting the second), it will take 0.2ms * 5 = 1ms.
增加 1 毫秒的额外延迟没什么大不了的.你可能不会注意到.
Adding 1ms extra latency is no big deal. You probably won't notice.
如果您的数据库服务器位于同一个数据中心之外，您可能会获得至少 20 毫秒的数据库延迟.连续五个查询将花费 100 毫秒.还不错.
If your database server is located outside the same datacenter, you'll probably get at least 20ms latency to the database. Five queries in a row would then take 100ms. Still not that bad.
如果您的数据中心位于大洋彼岸，则延迟可能为 100-200 毫秒.五个连续的查询需要长达一整秒的时间才能返回.
If you're located across the ocean from your datacenter, you're probably talking 100-200ms latency. Five sequential queries would then take as long as a full second to return.
如果您在整个后端使用 20-30 个查询，则加载页面可能需要 10 多秒的时间.
If you use 20-30 queries throughout the backend, it could take 10+ seconds to load your page.
解决方案?
- 将您的数据库服务器与您的网络服务器放在同一个数据中心.除非您可以并行执行所有查询，或者将系统减少到每页一个查询，否则在英国拥有网络服务器实际上比将网络服务器和数据库服务器隔海分开要快.
- 大大减少查询次数.
- 缓存.
这篇关于海上 SQL 连接缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
