On Tue, 12 Jun 2018 14:13:33 +0200 Dominique Devienne <ddevie...@gmail.com> wrote:
> > You're right of course. Thank you Clemens. > > With synchronous = OFF, which suits my use-case here, the commit-time > just vanishes, > and even out-performs HDF5 now (see below). I might still prefer HDF5, > mainly because > the N-d typed arrays are self-describing and thus visible using > Hdf5View for example, instead > of completely opaque in SQLite, but now I know SQLite in non-durable > mode is as fast or even > faster for these smallish blob sizes than HDF5. I'll need to > double-check for larger sizes > (individual blobs get upward of 1GB in size), with my large 31GB, 200K > blobs, 1.2M entities project. I join late to this thread but here are some comments, I use sqlite in environments similar to yours: - Don't use synchronous=OFF if you need more speed, use pragma journal=memory or pragma journal=none. They aren't safer but faster, - As you noted, don't preallocate a zero-blob, insert directly the blob, - Don't delete/update blobs, instead mark the row as deprecated/invalid and insert a new one, - If you have blobs larger than 2GB you'll need to split them, sqlite can't work with blobs larger than 2GB, - Use misc/unionvtab extension if you need a safe environment, it's a bit tricky to use but you will get a RAIDB0/JBODB0 (raid 0/jbod db split in many files) easily and is faster, - Compile sqlite with SQLITE_DIRECT_OVERFLOW_READ, with this, you blob reads don't pollute sqlite cache, - Compress the blobs with misc/compress extension while writing, less you write, faster you read, see sqlar project (http://sqlite.org/sqlar). HTH --- --- Eduardo Morras <emorr...@yahoo.es> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users