Bulk Storage Systems

Why External Bulk Storage?

DataJoint supports the storage of large data objects associated with relational records externally from the MySQL Database itself. This is significant and useful for a number of reasons.

Cost

One of these is that the high-performance storage commonly used in database systems is more expensive than that used in more typical commodity storage, and so storing the smaller identifying information typically used in queries on fast, relational database storage and storing the larger bulk data used for analysis or processing on lower cost commodity storage can allow for large savings in storage expense.

Flexibility

Storing bulk data separately also facilitates more flexibility in usage, since the bulk data can managed using separate maintenance processes than that in the relational storage.

For example, larger relational databases may require many hours to be restored in the event of system failures. If the relational portion of the data is stored separately, with the larger bulk data stored on another storage system, this downtime can be reduced to a matter of minutes. Similarly, due to the lower cost of bulk commodity storage, more emphasis can be put into redundancy of this data and backups to help protect the non-relational data.

Performance

Storing the non-relational bulk data separately can have system performance impacts by removing data transfer, disk I/O, and memory load from the database server and shifting these to the bulk storage system. Additionally, datajoint supports caching of bulk data records which can allow for faster processing of records which already have been retrieved in previous queries.

Data Sharing

DataJoint provides pluggable support for different external bulk storage backends, which can provide benefits for data sharing by publishing bulk data to S3-Protocol compatible data shares both in the cloud and on locally managed systems and other common tools for data sharing, such as Globus, etc.

Bulk Storage Scenarios

Typical bulk storage considerations relate to the cost of the storage backend per unit of storage, the amount of data which will be stored, the desired focus of the shared data (system performance, data flexibility, data sharing), and data access. Some common scenarios are given in the following table:

Scenario

Storage Solution

System Requirements

Notes

Local Object Cache

Local External Storage

Local Hard Drive

Used to Speed Access to other Storage

LAN Object Cache

Network External Storage

Local Network Share

Used to Speed Access to other storage, reduce Cloud/Network Costs/Overhead

Local Object Store

Local/Network External Storage

Local/Network Storage

Used to store objects externally from the database

Local S3-Compatible Store

Local S3-Compatible Server

Network S3-Server

Used to host S3-Compatible services locally (e.g. minio) for internal use or to lower cloud costs

Cloud S3-Compatible Storage

Cloud Provider

Internet Connectivity

Used to reduce/remove requirement for external storage management, data sharing

Globus Storage

Globus Endpoint

Local/Local Network Storage, Internet Connectivity

Used for institutional data transfer or publishing.

Bulk Storage Considerations

Although external bulk storage provides a variety of advantages for storage cost and data sharing, it also uses slightly different data input/retrieval semantics and as such has different performance characteristics.

Performance Characteristics

In the direct database connection scenario, entire result sets are either added or retrieved from the database in a single stream action. In the case of external storage, individual record components are retrieved in a set of sequential actions per record, each one subject to the network round trip to the given storage medium. As such, tables using many small records may be ill suited to external storage usage in the absence of a caching mechanism. While some of these impacts may be addressed by code changes in a future release of DataJoint, to some extent, the impact is directly related from needing to coordinate the activities of the database data stream with the external storage system, and so cannot be avoided.

Network Traffic

Some of the external storage solutions mentioned above incur cost both at a data volume and transfer bandwidth level. The number of users querying the database, data access, and use of caches should be considered in these cases to reduce this cost if applicable.

Data Coherency

When storing all data directly in the relational data store, it is relatively easy to ensure that all data in the database is consistent in the event of system issues such as crash recoveries, since MySQL’s relational storage engine manages this for you. When using external storage however, it is important to ensure that any data recoveries of the database system are paired with a matching point-in-time of the external storage system. While DataJoint does use hashing to help facilitate a guarantee that external files are uniquely named throughout their lifecycle, the pairing of a given relational dataset against a given filesystem state is loosely coupled, and so an incorrect pairing could result in processing failures or other issues.

Talk to the Community