Replication

[jordansparks]

On http://www.open-dent.com/manual/multiplelocations.html, we list the Replication Support option as our currently preferred option for supporting multiple locations. We also provide a link to the mysql website, which shows this image:



The reason that we haven't gone forward with this is due to one complication. If we always write to a central server, then how can we ensure that the write is replicated quickly enough to the slave to be functional? A typical example is if we add a record using an INSERT. Now, the master contains the correct information. But immediately after most INSERTs, we perform a SELECT to obtain a list of records to refresh the screen. We currently assume that we can perform the SELECT a fraction of a second after the INSERT and reliably obtain a list which includes the new record. But the replication will not have had time to occur. In fact, the replication might not happen until minutes later.

Any ideas anyone?

By reading that page on the mysql website, they seem to indicate that this strategy works best when there are few writes. So we may actually be pushing the limits of what is possible with this setup. The possible strategies we've come up with so far to address this are:
1. Write to both the master and the slave. The master will still replicate back to the slave, so all queries will have to be optimized to not fail during replication. For example, use INSERT IGNORE instead of INSERT.
2. Always write to the local database, and create a management layer to handle passing changes up to the central server.
3. Use Oracle, which has built in features for multiple masters. (the central server would have multiple masters).

Keep in mind that with any of these solutions, we have already solved the problem of conflicting primary keys. We already have a great algorithm that ensures that each local server could add records which would never have primary key conflicts with any other office. This is done by automatically paritioning the range of possible primary keys into smaller ranges. Each remote office stays within their assigned range when assigning primary keys.

I'm leaning towards solution #1, and we will start testing that to see if it's as simple as I hope it is.

[dwkrueger]

Just my thoughts on two different approaches to this problem.

1) Inherit all tables from a basic table that contains a single bool. IsDirty. The IsDirty value may be usefull beacause of its small size and location of how it is stored. The value could then be checked. Still a lag time problem.

2) You could have a table that stores keys to record unsyncronized keys. Then the unsyncronized keys could be downloaded from the main server. Then then load for the data from the master could be minimized. Unsyncronized keys

3) There is a lot of research being done at the university level regarding this particular problem of potentially uncorrelated data. A quick peruse of academic literature may provide some ideas.

4) Solving this problem at the C# higher language level may not be a reliable workable solution.

5) Datalock tables if multiple locations are not to be in production at the same time. Give each table a separte datafield (like the IsDirty) such as OfficeID. LockData for each OfficeID. Allow write permission for specific OfficeID. Locks could be static or Dynamic.

Just rogue thoughts on the matter. Since you asked for them.

Dan

Dan

[ewlooney]

Just a few comments...

I think you may be making this a bit more complicated that it needs to be. There are a few types of data replication. The most common are:

1) Master -> Slave: This is the replication you described. Many databases support this and it's quite easy to implement in an RDBMS (from a software engineering perspective). This is mostly useful when you do a lot of reads and few writes (as you mentioned) as dictated by the hardware - 1 write (master) server and multiple read servers (slaves). This way read load can be disbursed across several servers.

2) Master <-> Master: Multiple masters allow both systems to write and two way synchronization. This is ideal for situations where data needs to be pushed to several locations (bandwidth allowing) or hot-spares are required. MySQL implements this by utilizing a configuration where server1 becomes a master to server2, then server2 becomes a master to server1. For a detailed setup, please see: http://www.howtoforge.com/mysql5_master ... ebian_etch

With replication, database engine's matter. I recommend using InnoDB as it's much more able to handle things like multi-master replication.

From a technology design perspective, this should be handled *only* on a RDBMS layer. With such a powerful product as MySQL (or postgres, or oracle) if you're programmatically trying to control physical distribution of data...you're probably going about things incorrectly.

[jordansparks]

Good post. Yes, I've been aware of the way it's handled in MySQL, which I refer to as circular replication. We do support circular replication. And certainly, the program must be specifically tailored for this. The program must prevent and handle duplicate keys. Or, as in the case of the flow chart above, it must write to one server, yet read from a different server. So there is actually quite a bit that goes into the program design itself, not just the database.

But keep in mind that we're generally trying to develop a solution that will scale to 50+ separate locations. Circular replication is not suitable. Now we're talking about a circular chain that is 50 links long. It will be slow and much more likely to fail; weakest link problem. So what we are looking for is a way to scale up our current strategy.

So PostgreSQL (did you type InnoDB by accident?) can handle multi-master replication, huh? That's interesting.

[ewlooney]

Wow! 50+ offices...I understood the problem to be more along the 3+ office scale...at that scale it's more likely called clustering rather than simply replication.

You might want to look into what MySQL calls "star replication". This is the idea that you have one central hub (currently your master) and many other masters (currently your slaves) that replicate to the center (which then pushes those changes out to other "slaves").

The idea of reading and writing to different databases can work and is occasionally necessary for projects with such diverse geographical and networking requirements. At that point, however, I would recommend creating or utilizing some type middleware transaction abstraction layer (and potentially an ORM). If this is the direction you choose to take the project, I'd like to help (with my thimble full of insight - I find this type of stuff very interesting).

> The program must prevent and handle duplicate keys.
Actually, with 5.1 you don't really need to worry about this anymore. MySQL can now be configured with two new variables (available in file or cmd line):
auto_increment_increment - how many "increments" to skip each iteration
auto_increment_offset - where to start the auto_increment number

Just as an aside: I've found the following project to be useful when testing mysql replication:
http://my-repl-play.sourceforge.net/

[jordansparks]

I know you can set those server variables, but that seems a lot harder to the end user than just letting OD take care of it. We do it automatically. Also, it works for Oracle. I don't believe MySQL can do the 'star' replication that you refer to. I don't believe you can have multiple masters replicating to one slave. But... I am now going to research middleware. Surely someone else has already solved this problem.

[ewlooney]

I stand (sit) corrected. There are several presentations from MySQL that make the claim of star replication. I decided to try it (as described) and no joy.

Does OD currently utilize an abstraction layer for database access (eg. ORM or something...)?

[fcarlier]

http://www.onlamp.com/pub/a/onlamp/2006 ... ation.html

You should distinguish between:
- Data that is "centrally" managed (e.g. the Open Dental translations won't differ from branch office to branch office, probably the treatments will be centrally managed, too). Have this data "read-only" to branch practices.
- Data that is specific to a certain dental clinic (e.g. patient data).

You could set up a central server that manages the central data. This data is replicated to the branch servers. Clients perform read-only actions on this data.

You could assign patients etc. to a specific practice (branch server) for a specific period of time (for example, a 1 day granularity). The data of this patient is stored on the branch server for this practice.
This means that for the practice the patient is assigned to, all data is local -- no performance problems.

If a patient shows up at another practice, this practice takes ownership of the patient. Because the data is already replicated, this isn't much of a problem.
If the patient shows up the same day at two different practices, the second practice will have to connect to the remote server. That will be slow.

You could implement something like this:
* Patient C1 assigned to practice P1. Data stored at the server of practice P1, replicated to all other practices (read-only).
* Patient C1 appears at practice P1. Data is edited locally.
* Patient C1 shows up at practice P2. Patient C1 gets assigned to practice P2. The patient's data is managed at this practice, again locally.
Moving the data from P1 to P2 isn't an issue, it is already available at the practice.

Basically: set up a master server for each practice. This master server controls the data specific to this practice. Allow data to move from master server to master server, with a specific time granularity.
If the same data is modified in two different practices, the data moves from one practice to another, except if the interval is less than the granularity. Then you'll have a remote connection.

[ewlooney]

I've not tested this out before...but you might be interested in:
http://forge.mysql.com/wiki/MySQL_Proxy

Looks like you're supposed to connect just like normal mysql and you can write statement interceptors (i.e. INSERT <>, UPDATE <>...) to create a home-grown replication platform.

more to follow...

[jordansparks]

Excellent idea Frederik. That's the direction to go. Let me think about it some more.

[fcarlier]

You could envision something like this:

Add to each table a "Owner" or "Server" column, type int. This specifies the server that "owns" a specific row (e.g. the master server for that row).

When a user opens a patient, for example, Open Dental checks the "Owner" column. If it is the same as the server the user is connected to, there is no problem.
If it is different, the user has the option of working read-only or taking ownership of the patient ("Does your clinic want to take ownership of this patient?") or something like that.

All tables (and table types) would need the "owner" column (on the other hand, it could be induced for patient data). That means that your table types need to be modified.
You could use the data access framework for this, by implementing the "owner" field at the data object base class. By doing so, you can have the data access framework handle some of the overhead:
- take care of adding the "owner" column to all tabletypes
- take care of checking the "owner" and making sure the data is read-only
- take care of taking ownership.

Hope it helps.

[jordansparks]

I don't know. The "owner", as you call it, seems like a secondary consideration. I'm much more interested with how to actually pass the data up to the centralized server. Data that's already on the centralized server can be replicated; no problem. But pumping data back the other direction is going to be a big headache. I refer back go my initial post, solution #1. This seems like a much more important issue than worrying about the owner. In fact, I have to confess, I'm still struggling with why we even need to worry about the owner at this point.

[ewlooney]

So, I'm confused...are you wanting to replicate all data or just patient (and supporting) data?

[jordansparks]

ewlooney, are you asking me or Frederik? If you're asking me, I think we're talking about having one virtual database common to 50+ offices. Due to the complexity, I would be happy with a replication delay of up to an hour. Or even a delay as high as one day could be compensated for as Frederik suggest. But I would hope that the actual delay would something on the order of less than five minutes. Even with one large virtual database, there are a couple of tables that would not be replicated between offices. Particularly the signal table, which handles the message buttons and appointment screen refreshes.

Remember, that in MySQL, replication is a ONE way process. If there is a central server, replication would handle data moving out from the central server to the other servers. It would NOT in any way be able to handle data moving from the outer servers in towards the central server. I just wanted to make that specific point one more time, because the term 'replication' seems to be getting thrown around as a general solution for moving data between any set of servers at will. This is not the case. Data only moves out from the center towards the periphery when using replication. The rest is up to us.

[fcarlier]

The beauty is that you can have many-to-many replication, as long as you correctly "partition" your data.

If each sever is the "master" for the data managed by the practice it belongs to, you can have all that data replicate.

Then, all data is available at all branches. They have read-only access. If the they take ownership, they have full access.

[ewlooney]

After doing more research, another way to accurately describe the mysql "replication" (as you note, it's not exactly as robust as required) is that slaves may only have one master. Masters can have multiple slaves (as you state). So, I thought I would be clever...

I set up to disjoint systems:
A <-> B
C <-> D

and replication was working fine. I then used the federated storage engine (similar to a dblink in oracle) that allows me to define a virtual table that only points to another table. I did this so that table1 was a virtual table that resides on B. So I then had:
A<->B
C(dblink -> B) <-> D

Which is triangular "replication" (or true multi-master replication) between 3 nodes.

And it worked!...only a little too well. When I inserted a row in A it showed up in B, C and D. Only problem is within 30 seconds I had 50,000 rows of it in all 4 databases. I guess somehow the above scenario creates data links that go from A->B-C-D->C->B->A->B---->infinite loop.

so, back to the drawing board....

[fcarlier]

This may be interesting: The "Microsoft Sync Framework", see
http://msdn2.microsoft.com/en-us/sync/default.aspx

[drtech]

only thing I worry about is getting locked into Microsoft when you add more of their stuff. But I guess we already use the .Net framework...

[jordansparks]

The beauty is that you can have many-to-many replication, as long as you correctly "partition" your data.

If each sever is the "master" for the data managed by the practice it belongs to, you can have all that data replicate.

Then, all data is available at all branches. They have read-only access. If the they take ownership, they have full access.

I absolutely don't understand this at all. Masters in MySQL are dumb. They just create a log file of all changes. Then, a slave MySQL server can be set up to read the log file of ONE dumb master. I don't know how you can possibly interpret this as a many-to-many relationship. I simply think you are wrong.

Or are you talking about using some external program to handle file transfers between computers? This would not be called repliction. This would be called file synchronization. Some sort of file synchronization is the only way I can possibly imagine implementing something like what you are talking about. Again, NOT replication. This is the first time that I have considered using an external program to synchronize files which would then be read by MySQL. Each time the files are synchronized, the mysql service would have to be stopped. Maybe that's why you are suggesting doing it only once per day. I am going to start working on a file synchronization strategy, because I think that it does have real potential.

[fcarlier]

That is not true. You have two options: either you have multiple MySql instances share the same database on the same machine. They each act as a slave to another master, but update the same data:


> According to the book "High Performance MySQL" (ISBN 0596003064)
> Page 145, paragraph 2, there is a subheading "Slave with two
> masters." Here is the excerpt in its entirety:
>
> "It would be nice to use a single slave to handle two unrelated
> master, as seen in Figure 7-2. That allows you to minimize hardware
> costs and still have a backup server for each master. However, it's
> a violation of the second rule: A slave cannot have two masters. To
> get around that limitation, you can run two copies of MySQL on the
> slave machine. Each MySQL instance is responsible for replicating a
> different master. Infact, there's no reason you couldn't do this
> for 5 or 10 distinct MySQL master. As long as the slave has
> sufficient disk space, I/O, and CPU power to keep up with all the
> masters, you should have any problems."
>
> You would need two separate MySQL installations (two different base
> directories) on the proposed slave machine. This means running a
> mysqld daemon from two different bin folders and configure the
> my.cnf files to point to two different datadir locations. If you
> point both my.cnf files at the same datadir, now it could dangerous
> if you ever had to recover a database to a point in time because
> there would be binary logs from two different masters to contend
> with.
>
> As long as you point the masters at separate mysqld processes, and
> each mysqld is pointing at a separate and distinct datadir, you
> should be fine.

Another option is circular replication (ring replication), in which you have replication like this:

Code: Select all

  -> A --> B --> C --> D --> ... --> Z --
  |                                                  |
  ---------------------------------------------------

Both options allow you to have one server retrieve data from multiple masters.

[fcarlier]

This is also interesting: http://blog.kovyrin.net/2007/10/29/mysq ... -released/

[jordansparks]

Finally some technical explanation of what you had in mind. But I'm still far from convinced that it's workable. I can see that it's marginally possible with 2 masters and one slave, but they do point out that it's dangerous. So now you're talking about making the central server a slave to 50 other servers? That's 50 separate installed versions of mysql on one server, and all of them sharing the same data files. You can't be serious. As for circular replication, I thought we had already established that the practical limit was 5 to 10 servers. That would be A to K, not A to Z...

Can we please just all accept the fact that mysql does not have any practical support for multiple masters? We need to move on. We need to find a solution that would actually work. That means we find a middleware solution, or we build more intelligence into our program. Now I'm going to go look at the link you posted. At first glance, it looks like a middleware solution, which is more in line with what we need.

[fcarlier]

Have a look at mySql proxy, then: http://forge.mysql.com/wiki/MySQL_Proxy . You could have each office install MySql and a MySql proxy. They communicate with the MySql proxy. All reads get directed to their local MySql instance. All writes get redirected to their local MySql instance *and all other MySql Servers in all other offices*.

You may have some latency between offices (which shouldn't be very important); that's where "data ownership" comes into play.

[ewlooney]

Agreed FCarlier (see my post from Mon Nov 05, 2007 3:36 pm).

Another way (or perhaps a compliment to using mysqlproxy) is to have an "update log" with update "statements" (sql or otherwise) that all the interested parties could read/write against. The only job of the central server would be to catalog statements and order them...if you want to get complex, it could also resolve any conflicts.

At any rate, mysqlproxy + a catalog server could achieve your goals....?

[jordansparks]

Problems with mysql proxy:
1. We don't want certain writes to be passed to the other servers. Especially message buttons and certain other signal entries.
2. We would have to learn the language used for proxy functions.
3. Adding or removing a server node would require altering the proxy scripts on all the other server nodes.

The first two are not a big deal, but the last one certainly is. If adding a node, then we should have to configure that node, but not any other node. The obvious solution is to centrally manage tracking which nodes to send the updates to.

The complexity of this discussion and of all the mentioned solutions is leading me to believe that we should instead push forward with circular replication and continue adding better support for that. As for anything above about 10 servers, it will be up to the customer to develop their own solution to keep the servers synched. We can't currently support that ourselves. With some effort, circular replication might be able to work for a larger ring. It would require using the MySQL enterprise tools and probably some other tools to monitor all the servers. I'm just going to go forward with circular replication and leave the other complex scenarios out of the discussion.

[jordansparks]

I like the idea of a catalog server. But as I indicated in the last post, I think that's a battle for another day.

[ewlooney]

I agree completely with you Dr. Sparks on #3. That could be a nightmare!

When/if you do decide to look for "massive" multi-site replication please do repost...I happen to be working on the same type of problem.

[mimai]

I like to plug for circular replication. Inter-sites communication is getting faster, cheaper and more reliable all the time. Propagation times will be better and therefore A>>B>>....>>Z or beyond may be practical in the near future. Set up also much easier since each site have to communicate only to two or three sites ( third site is for central image folder). Adding more sites into the chain is probably easier too. One site can have a large bandwidth to act as the center for Images and accept new files from each site every night. Each site will have fast access to data and local Images. If Opendental can be configured to look for images remotely at the central location if it can not find it locally then each site would have complete access to data and images of ANY office the next day.

[jordansparks]

When large corporations call, they tend to ask us if we have a central database. If we use circular replication, we can't honestly answer yes. So I don't see it as the final solution for enterprises. But it may indeed scale up well beyond ten servers.