In the end, I ended up comparing\u00c2\u00a0the following storage options:<\/p>\n
- \n
- sqlite: Load rows as tuples, directly using the sqlite DB-API interface.<\/li>\n
- sqlite-obj:\u00c2\u00a0Similar, but also convert loaded tuples into Python objects (one member variable per column).<\/li>\n
- sqlalchemy-core: Load rows as tuples, using SqlAlchemy core (on top of sqlite) instead of DB-API directly.<\/li>\n
- sqlalchemy-orm: Use the full-blown ORM and session features from SqlAlchemy (on top of sqlite).<\/li>\n
- csv: CSV files, manually parsed (file iteration and string split).<\/li>\n
- csv-numpy: CSV files parsed using the I\/O functions in numpy.<\/li>\n
- cpickle: Store records as tuples, using cPickle.<\/li>\n
- cpickle-obj: Store records as Python objects (one member variable per column), using cPickle.<\/li>\n
- zodb: ZOPE object store, using BTrees.<\/li>\n
- pytables: A library for tabular data built on top of HDF5.<\/li>\n
- h5py: Another, lower-level library for HDF5 files.<\/li>\n<\/ul>\n
Furthermore, I also wanted to get an idea of how easily Python code can be optimized. \u00c2\u00a0In the past, I’d hand-coded C extensions when really necessary, I had played a little bith with cython, and I had heard of PyPy (but never tried it). \u00c2\u00a0So, while at it, I also considered\u00c2\u00a0the following Python implementations and tools:<\/p>\n
- \n
- cpython: \u00c2\u00a0The usual implementation of Python.<\/li>\n
- cython: Python extension that allows compiling to C extensions; no substantial benefit compared to cpython (at least without investing major effort: I only annotated some of the loop variables, the rest of the code was unmodified), so results are omitted.<\/li>\n
- pypy: Python JIT compiler; some libraries won’t run on this, so those are missing from the results.<\/li>\n<\/ul>\n
The dataset used was very simple, consisting of five columns\/fields of random floating point numbers (so the data are, hopefully, incompressible), with sizes of up to 500M records. \u00c2\u00a0The dataset size is quite modest, but should be sufficient for the goals stated above (comparing relative Python overheads, not actual disk I\/O performance). File sizes (relative to sqlite, again) are shown below. \u00c2\u00a0For the record, the ‘raw’ data (500,000 rec x 5 floats\/rec x 8 bytes\/float) would have stood at 0.74, same as pytables which has zero overhead (well, 64KB to be exact); sqlite has a 36% overhead. \u00c2\u00a0ZODB size includes the index, but that’s just 2.7% of the total (caveat: although records were only added, never deleted, I’m not familiar with ZODB and didn’t check if I should still have done any manual garbage collection).<\/p>\n
![\"filesizes\"](\"https:\/\/i0.wp.com\/bitquill.net\/blog\/wp-content\/uploads\/2015\/07\/filesizes.png?resize=460%2C173\")
<\/a><\/p>\nRuns were performed on a machine with an ext4-formatted Samsung EVO850 1TB SSD, Ubuntu 14.04LTS and, FWIW, a Core i7-480K at 3.7GHz. RAM was 64GB and, therefore, the buffercache was\u00c2\u00a0more than large enough to fit all dataset sizes. \u00c2\u00a0One run was used to warm up the cache, and results shown\u00c2\u00a0are from a second run. \u00c2\u00a0Note that, particularly in this setting (i.e., reading from memory), many (most?) of the libraries appear to be\u00c2\u00a0CPU-bound (due to serialization\/deserialization and object construction overheads), not I\/O-bound. \u00c2\u00a0I cautiously say “appear to be” since this statement is based on eyeballing “top” output, rather than any serious profiling.<\/p>\n
For full disclosure, here’s\u00c2\u00a0a dump of the\u00c2\u00a0source files and timing data<\/a>, provided as-is\u00c2\u00a0(so, caveat: far from release quality, not intended for reuse,\u00c2\u00a0really messy, undocumented, etc etc\u00e2\u20ac\u201dsome bits need to be run manually through an iPython prompt and\/or commented-in\/commented-out, don’t ask me which, I don’t remember :). If, however, anyone finds anything stupid there, please do let me know.<\/p>\n
First a sanity check, wall clock time vs. dataset size is perfectly linear, as expected:<\/p>\n
![\"Wall-clock](\"https:\/\/i0.wp.com\/bitquill.net\/blog\/wp-content\/uploads\/2015\/05\/wall-time-1.png?resize=450%2C324\")
<\/a><\/p>\nThe next plot shows average wall-clock time (over all dataset sizes) for both cpython and pypy, normalized against that of raw\u00c2\u00a0sqlite with cpython:<\/p>\n
![\"time-bar-1\"](\"https:\/\/i0.wp.com\/bitquill.net\/blog\/wp-content\/uploads\/2015\/05\/time-bar-1.png?resize=480%2C281\")
<\/a><\/p>\nAs usual, I procrastinated several weeks before posting any of this. In the meantime, I added a second EVO850 and migrated from ext4 to\u00c2\u00a0btrfs with RAID-0 for data and LZO compression. \u00c2\u00a0Out of curiosity I reran the code. \u00c2\u00a0While at it, I added ZODB to the mix. Here are the results (cpython only, normalized against sqlite on btrfs):<\/p>\n
![\"filesizes\"](\"https:\/\/i0.wp.com\/bitquill.net\/blog\/wp-content\/uploads\/2015\/07\/filesizes.png\")
<\/a><\/p>\n