用于具有地理位置不同用户的应用的后端数据库设置 [英] backend db setup for an app with geographically diverse users

问题描述

我工作的内部开发的软件通过我们的devexpress orm（XPO）直接连接到我们办公室的一个mysql服务器。表现非常棒。

我们正在开设另一个办公室。表现：不是那么好要求是这两个办公室的软件与本办公室一样响应，一个办公室的数据可以实时提供给另一个办公室。

这个尺度的东西对我来说是全新的。我不反对引进一个曾经做过这样的事情的顾问，但是我想先看一下这些选项。我确信这是一个常见的情况。

复制是一个好主意吗？足够快吗足够稳定？

如果复制不起作用，是否有解决这种情况的开发模式？

哎呀，我甚至不知道如何标记这个，所以如果有人知道更好，请随时重新标记

编辑>有关数据的详细信息

我猜，与一些企业软件相比，我们没有移动大量数据。该软件管理客户帐户，约会等，每个用户大约2-5个单独的账户/分钟（目前50个用户，计划扩展后的200-400个），每次更新数据。

当有人在办公室A为办公室B的某人创建约会时，实时方面发挥作用，理想情况下，需要立即查看其详细信息（<2分钟）。也就是说，每条记录通常每天只能突变5次。但这只是我怀疑的;我实际上没有使用任何统计信息。

解决方案

不能在不创建复制的情况下双向使用异步复制因此，您明显的选择是使用读/写分割 - 让应用程序从（只读）本地执行非关键性读取DB，并将所有写入命令直接发送给主控。这样做的缺点在于它意味着您无法立即读取自己的写入。

MySQL复制并不完美，需要一些努力来设置和持续监测维护;您必须经常检查数据在从站中是相同的。有些查询错误地复制;你需要了解那些并避免它们。

The in-house developed software where I work connects directly to a mysql server here in our office via our devexpress orm (XPO). Performance is great.

We're opening another office... cross country. Performance: not so great. The requirement is that the software be as responsive in both offices as it is in this office and that the data from one office be available to the other 'in real time'.

Something of this scale is totally new to me. I'm not averse to bringing in a consultant who's done something like this before, but I'd like to get a good picture of the options first. I'm sure that this is a common situation.

Is replication a good idea? Is it fast enough? stable enough?

Are there development patterns that tackle this kind of situation if replication won't work?

Heck, I'm not even sure how to tag this, so if someone knows better... please, feel free to re-tag

EDIT > Details about the data

I guess, compared to some enterprise software, we're not moving lots of data. The software manages customer accounts, appointments etc. and each user works on about 2-5 separate accounts/minute (50 users currently, 200-400 after planned expansion), updating data each time.

The real-time aspect comes into play when someone in office A creates an appointment for someone in office B who, ideally, needs to be able to view its details near immediately (<2 mins). That said, each record usually only gets mutated a maximum of 5 times a day. But that's only what I suspect; I don't actually have any usage statistics on me.

解决方案

You can't use asynchronous replication in both directions without creating replication conflicts which are impossible to resolve and break things.

Therefore, your obvious choice is to use read/write splitting - have the application do noncritical reads from a (readonly) local DB, and direct all writes to the master. The disadvantage of this is that it will mean that you can't immediately read back your own writes.

MySQL replication isn't perfect and requires some effort to set up and continuous monitoring to maintain; you must frequently check the data are the same in the slaves. Some queries get replicated incorrectly; you'll need to understand those and avoid them.

这篇关于用于具有地理位置不同用户的应用的后端数据库设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！