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).


---   ---
Eduardo Morras <emorr...@yahoo.es>
sqlite-users mailing list

Reply via email to