Database issues moving into the CIRN/ICON future

[jstanleyx]

Database issues moving into the CIRN/ICON future

While visiting with Ian Turner and Mitch Harley, we had some discussion about the future of database use in an ICON environment. There were several questions about process brought up, and this is a summary of those issues. It is also a potential roadmap.

Existing database

Currently, the main database for Argus is being run at the CIL in Corvallis. The server there is open to any user. This is how the new system at Narrabeen at Ian's lab at UNSW was set up, for various reasons, mostly the difficulty in finding the main MySQL administrator password to add the current 'argus' data base to the existing server. The server at UWA in Perth has not yet been configured; it may become a testbed for part of the proposed solution described here.

Advantages

• Central management. One manger (or small set of them) instead of many.
• Central access. Everyone can get the same data. Updates made on one side of the planet appear immediately everywhere else.
• Uniform access. Everyone can use the same code to get the same things defined the same way.

Disadvantages

• Speed. Network latency will degrade processing.
• One basket. All it takes is one city bus. Or one broken vein. Or the one person who knows the password changes employment.
• One server. One power supply fails and the system is down until repaired.

Solutions

There are multiple prongs to any long-term solution to the issues listed above.

AWS

The main focus of the proposed solution is to find a central, high-reliability, distributed database server to host the database. Amazon AWS is one leading provider in this field. They will provide managed servers that require only a database manager to configure and administer. The Argus database has been tested on the free version of the AWS mariadb server and works well. The one year free test period has expired, however.

The biggest advantage to the use of AWS is worldwide access. (If the write-access to the database is not well managed, it can also be a big big disadvantage.)

The biggest issue with this solution is:

• Money. Someone has to pay for AWS or other commercial server.

Replication

A secondary focus is on providing local copies of the database where desired. Database replication is one method of solving the latency problem. Replication means that a local server is updated from a central database server. The changes made to the master server are copied to the local servers. There are several issues with this solution.

• Latency of the updates. Updates are not instantaneous. It is not currently known how fast these updates can occur; testing will be required.
• Local management. The local server requires some administration locally. It can run unattended for the most part, but there will inevitably be hiccups that need to be resolved.
• Local changes. The replication system does not allow local changes. All changes must be made through the master and then propagate to the local servers. Most of the accesses to the database will be 'select' (read) statements, and these will be much faster for a local server.

Final proposal

I would propose the following actions as the main roadmap for the future.

• Find funding for AWS to host the database service. I have no idea what this will cost in the long run. I believe it is based on transactions and compute time involved, and cannot predict how this will pan out.
• Configure replicated servers where desired.
• Create a process where database READS go to the most convenient server, either a local replicant or the master. This is easily configured in the current ArgusDB code by selecting the correct server to connect to.
• Create a process where all WRITES go to the master only and then propagate to the replicants. This will require selecting the correct database server using existing ArgusDB code. This is not an unusual thing to do already, since the default database connection is as a read-only user and writing to the database already requires a new connection as an authenticated user.

This solution could be set into process as soon as funding for AWS is obtained. The replication system will likely be tested at UWA since they are a system currently being installed.