Should I use a database to handle large amounts of results?

Question

Background:

I am currently running a large amount parameter variation experiments. They are being run in Python 2.6+, using numpy. These experiments are going to take about 2 weeks to run.

Roughly I am varying 3 parameters (independent variables) over a range of values. I am fixing 6 further independent variables (for now) I am reporting on 4 dependent variables.

One of the parameters I am varying is being distributed across several processes (and computers). For each of these parameters, I generate a separate csv files with each row containing the values of all the variables (including independent, fixed and dependent). Across all the variation expect to generate about 80,000 rows of data

Most of the time I am only looking at the value of one of the dependent variables, however I keep the others around, as they can explain what is going on when something unexpected happens.

In a earlier version of this experiment, varying across only 2 parameters (each though only 2 values) I was copying pasting this csv file into a spreadsheet program and doing a bunch of copy pasting to make a table of just the dependent variable I was interested in. The doing some awkward things in MS-Excel to let me sort by formulas. This was painful enough for the 6 experiment results sets I had. By the time this run is finished I am going to have 2 orders of magnitude more results.

Question:

I was thinking once done, I could dump all the results from the csv files into a database, and the query out the parts that are interesting. Then take those results and put them into a spreadsheet for analysis. Making graphs, finding scored relative to the control results etc

Am I thinking along the right lines? (Is this what people do?)

My database foo is fairly rusty these days, even when it was good I was using MS-Access. I was intending on using MS-Access for this as well.

Answer 1

I would suggest that a full database may be overkill for your purposes, though it would certainly work. Even $5 \cdot 10^5$ rows should be no more than around 25mb of data.

I would strongly recommend doing the analysis/plotting/etc with the same tool that you will use for querying your data. It is my experience that when changing what to analyse only takes changing 1 line of code and waiting 2 seconds, it is much easier to get the most out your data. Copy pasting is also HIGHLY error prone. I have seen several people at the point of desperation because their data did not make sense, only to realise they made a mistake when copying data in their excel sheet.

If your are at all familiar with python, I would suggest using pandas or (if you have more data than you can fit in memory) pytables, which will give you all the advantages of a database (including speed). Pandas has a lot of utility functions for plotting and analysing data, and you would have the full scientific python stack as well. Take a look at this ipython notebook for an example of pandas use.

I believe similar tools exist for R, as well as commercial software such as Matlab or Stata.

HDF5 is a good generic way of storing the data initially, and has good library support in many languages.

Answer 2

I highly recommend using a tool such as Sumatra for this. I used to have a similar "pedestrian" approach to yours for keeping track of many simulation runs with varying parameters, but in the end it just becomes a huge mess because it's next to impossible to design such an ad-hoc approach correctly upfront and to anticipate all the use cases and extensions needed (e.g., what happens if you need to introduce an additional parameter).

Sumatra keeps track of all your simulation runs and stores them in a database which can later be queried (using its Python API) to filter and analyse the records you are interested in. It is very flexible and doesn't impose a workflow on you, which I find a big plus. Also, it comes with a web interface that allows you to quickly browse results (or inspect/download generated files), which is tremendously useful. The default database uses SQLite and I could imagine that it becomes a bit slow if you use it to store 80,000+ simulation outcomes. There is a PostgreSQL backend but I have never used it so can't vouch for its performance.

I should say that it's still in its early-ish development stages and there are a few things missing, but I have used it for pretty much all my simulations in the past year and it has saved my day so many times that I could not imagine what I would do without it. Personally, I never used it for computations across different computers (or on a cluster), but I think it supports this kind of workflow. Do ask on the mailing list if you're unsure or can't find exactly what you need, it's a small but very friendly and helpful community.

Give me a shout if this is something you're interested in and I'm happy to share my workflow and boilerplate code to get you going (or just for inspiration).

For the actual data analysis, I agree with LKlevin that pandas and the IPython notebook are extremely useful tools to know about (Sumatra allows you to import the records into pandas, although this is a bit at the moment but I'm sure it'll soon be improved). I could also imagine that saving data/simulation outcomes to HDF5 format could be useful, in which case pytables is a good tool in the toolbox. (I seem to remember that support for HDF5 is planned in Sumatra, but I can't find the information right now and I'm not sure this is implemented yet.)

Lastly, I'm sure there are other tools that help with these kinds of tasks (see the "short list" on this presentation slide). But personally I haven't tried any of those because I've been very happy with the functionality and flexibility that Sumatra offers.

Answer 3

Yes, you can dump all the results into a database, and yes, some people elect to use databases. I haven't yet had to deal with situations using databases, but I have taught at workshops where other instructors teach about using databases to gather data. For databases that aren't massive, from what I understand, the underlying technology doesn't matter a whole lot. My co-instructor used SQLite3. It's easy to install in Linux, comes standard in OS X, and I believe it's available for Windows.

It's possible to access SQLite databases through a terminal in OS X and Linux; I'm not sure how it's done on Windows. It's also possible to leverage Python packages to read from and write to your database programmatically, for instance, using the sqlite3 package in the Python standard library.

If your data sets get really big, other database implementations are better, and at that point, you probably want to consult a database specialist.

Answer 4

If all your data fits comfortably in memory (say, below 1 GB, so you have margin for the analysis), a DB is overkill. You can just read the whole file in memory and select the pieces you want. On the other hand, when your data starts to grow (or could potentially grow too big), a DB can offer you fast and easy queries ("give me all the speeds for which the energy was exactly 2 and the temperature bigger than 27").

Another topic is the generation of data. As your data takes two weeks I am assuming you are generating them on a computing cluster in parallel. Setting a DB for parallel writing is complex, and can potentially slow down the process, as data is being transferred and locks are in place. As you only need to write things once, you can have each process generate its own temporary text file, write the results there, and have a central process read each one of this and dump it on a master DB. For the simplest case, this script can be a simple cat, and save the result as plain text.

Now, let's assume you want to use a DB. If your use case is something slightly more advanced than a text file (what you would do if you had loaded a CSV with Numpy), I recommend HDF5 through PyTables. It is fast, simple to set up, Numpy-aware, and with a bunch of advanced features if you want to tune things. It also supports compression, querying, and saving arrays. It is also easy to install in Linux, Windows, and Mac. HDF5 data layout are nothing more than tables, like a spreadsheet. The resulting .h5 file can be read from many computer languages if they have the proper library installed.

On the other hand you have SQL. You have one in Python's stdlib, so you would have it already installed, but it is not very well suited for numerical work (you can't save and recover Numpy arrays so easily, for example). This is the best option if you need third parties to interface from other languages, as it is very well known and there are wrappers to almost any language, many of them come by default.