Best practice for storing hierarchical simulation data

Question

TL,DR

What is the accepted best practice in scientific computing circles for storing large quantities of hierarchically structured data? For example, SQL does not play nicely with large sparse matrices. Is there a good tool out there for structuring, warehousing, and analyzing this kind of data? What do the guys at the LHC use?

Use case details

I want to store data from protein simulations according to the following hierarchy:

protein
  |__simulation conditions
  |____|__residues
  |____|____|__conformers
  |____|____|____|__atoms

Every protein should be aware of each of its residues, every atom should know the conditions used for its simulation, etc. and vice versa.

Originally I figured that a relational database would be perfect for this application, and so I wrote a program using python and sqlalchemey that stores the data in an SQL database. In practice, however, this program is not working so well.

The biggest issue relates to the fact that there is an N x N matrix at the conformer data level that stores the potential energy due to the pairwise interactions between every possible pair of conformers. Most of the entries in the matrix are zeros, so I'm storing the matrix in a separate table in the database in a kind of sparse format, one row per entry. Unfortunately, for a simulation involving several thousand conformers the pairwise table still ends up with several hundred thousand rows and:

a) builds and queries very slowly (hours)
b) takes up an order of magnitude more space on my hard drive than an equivalent plain text representation of the data as a non-sparse matrix
c) takes up more than ten gigabytes of memory when the table is read into memory

My ultimate goal is to store tens of thousands of runs (derived from thousands of proteins under several dozen simulation conditions) in the database so they can all be analyzed together. This would mean that the table representing the pairwise matrices would likely grow to around a billion rows. Currently it's seeming like I'm going to need a Cray or some other shared-memory monster in order to even run a single query on this database.

Do I have any better options here? What do the guys at the LHC use?

update

One PhD, two jobs, and eight years later and I still don't have a good answer to this question (even though I did publish a paper about my own custom format for comp bio data). HDF5 is okay up to 10's of GBs, but is incompatible with Hadoop/Spark and so is unsuitable for truly large data. The most promising new thing is zarr, a Spark-compatible HDF5 alternative

Answer 1

Consider using the HDF5 file format. HDF5 is a hierarchical data storage format with several nice features:

• platform independent storage: the library takes care of little/big endianness for you
• hierarchical layout of datasets: like a filesystem within a file
• large, growable n-dimensional array storage
• mixed dataset types can exist within one file (ie, integers, floats etc)
• automatic compression is available
• binary storage
• parallel i/o

There are C and Fortran interfaces, as well as Python (h5py and pytables) wrappers. MATLAB can also read HDF5. HDF5 is fairly flexible, almost to a fault, ie, it is not "self-describing", hence the creation of XDMF.

I'm not exactly sure what you mean by "conditions used for its simulation", but if these are just small collections of parameters you could store them as attributes.

Answer 2

The use of a database is great for helping you organize/find simulation data (Search by protein, search by simulation parameters). The database should then tell you where to find the relevant information on disk, where I imagine it is likely best stored on a per-simulation run basis in whatever file type is most convenient to load for analysis (whether custom or from whichever simulation suite you're using).

This will let you swiftly find the simulations you wish, and give you the freedom/performance of using sparse matrixes or whatever tools you need to do efficient analysis.