Re: [sqlite] write internal blob vs external file

[Dan Kennedy]

On 09/15/2014 06:19 PM, Dominique Devienne wrote:

On Mon, Sep 15, 2014 at 12:29 PM, Dan Kennedy  wrote:


On 09/15/2014 03:18 PM, Dominique Devienne wrote:


On Fri, Sep 12, 2014 at 11:48 PM, Eric Rubin-Smith 
wrote:

  Looking at the sqlite web site and mailing lists shows that the SQLite

team
has taken a stab at answering the question, "is it faster to read a blob
out of sqlite or out of a file?".  See the links below.

Does the team have analogous guidance regarding write speeds?

  Depends if you care only about the initial write, or also about

incremental
writes.

SQLite is transactional, but does not have a level of indirection between
the row data, and the blob data. i.e. the blob is "in row". So whenever
you
want to update a single byte of a blob, you must rewrite the whole row,
i.e. the whole blob(s) in that row (and I think this applies to any column
of the row, not only blob columns). This is just prohibitive, and
unmanageable in practice.


I think the only exception to this is if you use the incremental-blob API:

   http://www.sqlite.org/c3ref/blob_open.html


What do you mean Dan? Are you saying that opening an existing blob, writing
1 byte somewhere, and closing it, does not yield the whole row to be
updated? Which therefore implies copying the whole old row value somewhere
for undo? (or writing the new row value to the WAL, old value + bytes
changed). Is this somehow not transactional? or SQLite supports sub-row or
sub-page deltas to implement its transactions? I'm confused by your remark.


SQLite always writes entire pages to disk. But if your blob is large 
enough it will be spread over multiple overflow pages. In this case if 
you update a single byte of the blob using the incremental-blob API, 
only the single modified page is updated on disk - not all the other 
pages that store data from the same row.


Dan.







I'm not saying SQLite lacks an API to do incremental updates to blobs, but
that its implementation and current file format lacks a way to implement
them efficiently as of now, like a file system can. Basically it want to
use SQLite as a mini transactional filesystem (w/o any need for directory
operations, just file ones, like SQLite own VFL more or less), in libraries
that like SQLite support a VFL abstraction. I'd implement those libraries
VFLs on top of SQLite blob incremental APIs. It's possible, but nowhere
near efficient enough ATM, for the partial update case which is required by
those libraries. Just imagine how slow SQLite itself would be, if you based
its own VFL on the SQLite's blob APIs. Nest an "inner" SQLite DB inside a
blob inside the "outer" SQLite, and you'll get what I mean I think, which
is that doing any SQL update on the inner DB implies doing copies of the
whole inner DB. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] write internal blob vs external file

[Eric Rubin-Smith]

Dominique Devienne wrote: 

> > Looking at the sqlite web site and mailing lists shows that the SQLite 
> team 
> > has taken a stab at answering the question, "is it faster to read a blob 
> > out of sqlite or out of a file?".  See the links below.  
> > 
> > Does the team have analogous guidance regarding write speeds?  
> > 
> 
> Depends if you care only about the initial write, or also about 
> incremental writes.  

Thanks to those of you who responded already!  I'll give a little 
more flavor for those who are interested.  

My particular application is a cache of large, immutable files.  The 
UPDATE case doesn't apply to me for this particular application.  

I already have a system on top of SQLite's blobs in which I store the 
file data in chunks, one chunk per row.  (I'm downloading the files from
the network, and at the moment am using a version of the Python SQLite 
API that does not have hooks to the incremental blob I/O.)  So I 
download a few megabytes, start the next hunk download in the 
background (using the "requests-futures" Python library, which is 
really sexy BTW), write out the current hunk, commit, continue.  The 
row also has the byte range that it is storing: 

  CREATE TABLE IF NOT EXISTS file( 
  fileID TEXT NOT NULL, 
  start  INTEGER NOT NULL,  -- offset of first byte of chunk 
  endINTEGER NOT NULL,  -- offset of last byte of chunk 
  bytes  BLOB NOT NULL, 

  CONSTRAINT BlobSize CHECK(length(bytes) == (end-start+1))
  ); 

All requests are for retrieval of a particular range of bytes for a 
particular file.  So I have an index: 

  CREATE INDEX IF NOT EXISTS FileIdxNameStart ON file(
  fileID, 
  start
  ); 

and I create my own little "query plan" in application logic for 
servicing a particular request on the read side.  The read side is 
fast enough for my purposes.  

Inserts are slow and deletes are slow.  The disk I am using is capable 
of writing about 6.5MB/sec, but my app + SQLite only hit around 
600KB-1MB/sec under this scheme, depending on some variables.  This is 
approximately half of the overall speed that I expect (because if I use 
'wget' to just write out a downloaded file to disk, I get about 
2MB/sec sustained over hundreds of MB).  I've played with the chunk 
size, the synchronous pragma, the commit interval, the page size pragma 
and the cache size pragma.  

I have not cleared my application of all blame yet, since I have not
taken enough measurements.  I'm considering prototyping a C
implementation that uses incremental blob I/O to write out the file
and is cleverer about the download state machine, but was hoping to 
know from this group if that is a fool's errand (e.g. if you all
have some particular reason for knowing that huge blobs in SQLite
are a priori a bad idea for performance reasons).

Deletes by fileID are really awful compared to a simple filesystem 
delete -- like 2 orders of magnitude worse by my eyeballing, at least
with the default 1024-byte page size.  Again, I think you'd expect it 
to be -- an unlink(2) only has to touch a few pages of disk, whereas 
an SQLite delete of 1GB worth of blob rows I guess goes and touches 
something every page_size bytes (right?).

So I'm thinking of just storing the data out of the database, perhaps
retaining an SQLite index.  But I'm worried about the safety issues that
arise from that.  E.g. I finish writing my file, I write an index 
entry and commit it, and immediately afterward I get a power failure.
Am I sure that the file bytes are correct?  And so on.  All of those
considerations are right in SQLite's wheelhouse -- I'd hate not to be
able to take advantage of it.


-- 
Eric A. Rubin-Smith

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] write internal blob vs external file

[Simon Slavin]

On 15 Sep 2014, at 1:23pm, Dominique Devienne  wrote:

> On Mon, Sep 15, 2014 at 1:59 PM, Simon Slavin  wrote:
> 
>> It just rewrites
>> bytes wherever the existing row is on disk,
> 
> Then how is this transaction safe? I might not be reading the doc right,
> but I don't see it saying that the incremental bob APIs are not transaction
> safe.

Sorry, but I don't know the details.  That API requires that you 'open' the 
BLOB, do some writes to it and then 'close' it.  So perhaps it does a BEGIN 
before your 'open' and an END after your 'close'.  Or perhaps it just wraps the 
'close' in the transaction and never makes changes until the 'close'.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] write internal blob vs external file

[Dominique Devienne]

On Mon, Sep 15, 2014 at 1:59 PM, Simon Slavin  wrote:

> On 15 Sep 2014, at 12:19pm, Dominique Devienne 
> wrote:
> > On Mon, Sep 15, 2014 at 12:29 PM, Dan Kennedy 
> wrote:
> >> On 09/15/2014 03:18 PM, Dominique Devienne wrote:
> >>> want to update a single byte of a blob, you must rewrite the whole row,
> >>> i.e. the whole blob(s) in that row (and I think this applies to any
> column
> >>> of the row, not only blob columns). This is just prohibitive, and
> >>> unmanageable in practice.
> >>
> >> I think the only exception to this is if you use the incremental-blob
> API:
> >>
> >>  http://www.sqlite.org/c3ref/blob_open.html
> >
> > What do you mean Dan? Are you saying that opening an existing blob,
> writing
> > 1 byte somewhere, and closing it, does not yield the whole row to be
> > updated?
>
> Correct.  That's what the incremental-blob API does.  It just rewrites
> bytes wherever the existing row is on disk,


Then how is this transaction safe? I might not be reading the doc right,
but I don't see it saying that the incremental bob APIs are not transaction
safe.


> [...] and you can't use it to change the length of the BLOB.
>

And there lies the rub... Just like SQLite might need to "allocate" a new
page for the "inner" SQLite DB within an "outer" blob value, thus extending
the "file" / blob, so does those libraries I'm talking about.

In any case, I appreciate the details, even though they raise as many
questions in mind as they answer, obviously. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] write internal blob vs external file

[Simon Slavin]

On 15 Sep 2014, at 12:19pm, Dominique Devienne  wrote:

> On Mon, Sep 15, 2014 at 12:29 PM, Dan Kennedy  wrote:
> 
>> On 09/15/2014 03:18 PM, Dominique Devienne wrote:
>> 
>>> whenever
>>> you
>>> want to update a single byte of a blob, you must rewrite the whole row,
>>> i.e. the whole blob(s) in that row (and I think this applies to any column
>>> of the row, not only blob columns). This is just prohibitive, and
>>> unmanageable in practice.
>> 
>> I think the only exception to this is if you use the incremental-blob API:
>> 
>>  http://www.sqlite.org/c3ref/blob_open.html
> 
> What do you mean Dan? Are you saying that opening an existing blob, writing
> 1 byte somewhere, and closing it, does not yield the whole row to be
> updated?

Correct.  That's what the incremental-blob API does.  It just rewrites bytes 
wherever the existing row is on disk, updates indexes and performs TRIGGERS 
where appropriate.  It does not rewrite the whole row record, and you can't use 
it to change the length of the BLOB.

Simon. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] write internal blob vs external file

[Dominique Devienne]

On Mon, Sep 15, 2014 at 12:29 PM, Dan Kennedy  wrote:

> On 09/15/2014 03:18 PM, Dominique Devienne wrote:
>
>> On Fri, Sep 12, 2014 at 11:48 PM, Eric Rubin-Smith 
>> wrote:
>>
>>  Looking at the sqlite web site and mailing lists shows that the SQLite
>>> team
>>> has taken a stab at answering the question, "is it faster to read a blob
>>> out of sqlite or out of a file?".  See the links below.
>>>
>>> Does the team have analogous guidance regarding write speeds?
>>>
>>>  Depends if you care only about the initial write, or also about
>> incremental
>> writes.
>>
>> SQLite is transactional, but does not have a level of indirection between
>> the row data, and the blob data. i.e. the blob is "in row". So whenever
>> you
>> want to update a single byte of a blob, you must rewrite the whole row,
>> i.e. the whole blob(s) in that row (and I think this applies to any column
>> of the row, not only blob columns). This is just prohibitive, and
>> unmanageable in practice.
>>
>
> I think the only exception to this is if you use the incremental-blob API:
>
>   http://www.sqlite.org/c3ref/blob_open.html


What do you mean Dan? Are you saying that opening an existing blob, writing
1 byte somewhere, and closing it, does not yield the whole row to be
updated? Which therefore implies copying the whole old row value somewhere
for undo? (or writing the new row value to the WAL, old value + bytes
changed). Is this somehow not transactional? or SQLite supports sub-row or
sub-page deltas to implement its transactions? I'm confused by your remark.

I'm not saying SQLite lacks an API to do incremental updates to blobs, but
that its implementation and current file format lacks a way to implement
them efficiently as of now, like a file system can. Basically it want to
use SQLite as a mini transactional filesystem (w/o any need for directory
operations, just file ones, like SQLite own VFL more or less), in libraries
that like SQLite support a VFL abstraction. I'd implement those libraries
VFLs on top of SQLite blob incremental APIs. It's possible, but nowhere
near efficient enough ATM, for the partial update case which is required by
those libraries. Just imagine how slow SQLite itself would be, if you based
its own VFL on the SQLite's blob APIs. Nest an "inner" SQLite DB inside a
blob inside the "outer" SQLite, and you'll get what I mean I think, which
is that doing any SQL update on the inner DB implies doing copies of the
whole inner DB. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] write internal blob vs external file

[Dan Kennedy]

On 09/15/2014 03:18 PM, Dominique Devienne wrote:

On Fri, Sep 12, 2014 at 11:48 PM, Eric Rubin-Smith 
wrote:


Looking at the sqlite web site and mailing lists shows that the SQLite team
has taken a stab at answering the question, "is it faster to read a blob
out of sqlite or out of a file?".  See the links below.

Does the team have analogous guidance regarding write speeds?


Depends if you care only about the initial write, or also about incremental
writes.

SQLite is transactional, but does not have a level of indirection between
the row data, and the blob data. i.e. the blob is "in row". So whenever you
want to update a single byte of a blob, you must rewrite the whole row,
i.e. the whole blob(s) in that row (and I think this applies to any column
of the row, not only blob columns). This is just prohibitive, and
unmanageable in practice.


I think the only exception to this is if you use the incremental-blob API:

  http://www.sqlite.org/c3ref/blob_open.html


Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] write internal blob vs external file

[Dominique Devienne]

On Fri, Sep 12, 2014 at 11:48 PM, Eric Rubin-Smith 
wrote:

> Looking at the sqlite web site and mailing lists shows that the SQLite team
> has taken a stab at answering the question, "is it faster to read a blob
> out of sqlite or out of a file?".  See the links below.
>
> Does the team have analogous guidance regarding write speeds?
>

Depends if you care only about the initial write, or also about incremental
writes.

SQLite is transactional, but does not have a level of indirection between
the row data, and the blob data. i.e. the blob is "in row". So whenever you
want to update a single byte of a blob, you must rewrite the whole row,
i.e. the whole blob(s) in that row (and I think this applies to any column
of the row, not only blob columns). This is just prohibitive, and
unmanageable in practice.

Oracle has this, and maintains in-row only a "lob index", which basically
points to the "pages" of the lob (applies to character lobs too, which
SQLite would store as text). Assuming a 4-byte page index, and an 8K page,
that's 2,000x less data to rewrite on row updates. You still have to
rewrite the row I think, since a single byte update to a blob implies an
update to the index to maintain transaction integrity (and Oracle's MVCC),
but you're rewriting a lot less data. Until SQLite introduces such "blob"
specific pages and a similar level of indirection (for both blob and text),
I'm afraid SQLite cannot reasonably to used for some use cases, which
happen to matter to what I'm doing. A new pragma to define a threshold
after which large blob and text columns switch from in-row to out-of-row
storage would just make my day :).

I'm neither an SQLite expert, nor an Oracle expert, so please feel free to
correct the above if it's not true or incorrect in any way. Thanks, --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] write internal blob vs external file

[Kees Nuyt]

On Fri, 12 Sep 2014 17:48:27 -0400, Eric Rubin-Smith
 wrote:

>Looking at the sqlite web site and mailing lists shows that the SQLite team
>has taken a stab at answering the question, "is it faster to read a blob
>out of sqlite or out of a file?".  See the links below.
>
>Does the team have analogous guidance regarding write speeds?

Good question, but I don't have an immediate answer.
Perhaps you can set up a quick benchmark with sqlar:
http://www.sqlite.org/sqlar
Use the -n option to disable compression.

> I'm also interested in the delete path.  It seems like SQLite doesn't have
> much hope of competing with a native filesystem unlink(2) call to delete a
> file that is many gigabytes long, for example.  Is that right?

sqlar yields a sqlite3 database, so you can benchmark DELETE
performance using SQL. 

Optimization opportunity:

printf "PRAGMA page_size=bytes; VACUUM;\n" \
| sqlite3 benchmark.sqlar 

>Eric
>
>References:
>
>http://sqlite.1065341.n5.nabble.com/Internal-v-External-BLOBs-td15515.html
>http://www.sqlite.org/intern-v-extern-blob.html

-- 
Groet,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] write internal blob vs external file

[Simon Slavin]

On 12 Sep 2014, at 10:48pm, Eric Rubin-Smith  wrote:

> Looking at the sqlite web site and mailing lists shows that the SQLite team
> has taken a stab at answering the question, "is it faster to read a blob
> out of sqlite or out of a file?".  See the links below.
> 
> Does the team have analogous guidance regarding write speeds?

I suspect that this would be very sensitive to the file system and storage 
drivers used.  You might get results with one setup which differ radically from 
those with another setup.  You might also get results which change depending 
how many files/rows there are when you update one file/row.

> I'm also interested in the delete path.  It seems like SQLite doesn't have
> much hope of competing with a native filesystem unlink(2) call to delete a
> file that is many gigabytes long, for example.  Is that right?

unlink(2) does a bit of checking to see that the user has permission to delete 
that file specifically and also to delete files from the folder that the file 
is in.  And once the file is deleted it has to add blocks to the volume's free 
list.  All SQLite has to do is change some bytes in flies it already has open.

I couldn't hope guess how this will come out and I will be interested in the 
result.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] write internal blob vs external file

[Richard Hipp]

On Fri, Sep 12, 2014 at 5:48 PM, Eric Rubin-Smith  wrote:

> Looking at the sqlite web site and mailing lists shows that the SQLite team
> has taken a stab at answering the question, "is it faster to read a blob
> out of sqlite or out of a file?".  See the links below.
>
> Does the team have analogous guidance regarding write speeds?
>
> I'm also interested in the delete path.  It seems like SQLite doesn't have
> much hope of competing with a native filesystem unlink(2) call to delete a
> file that is many gigabytes long, for example.  Is that right?
>

The fact that SQLite is faster at reading was originally discovered by the
Adobe Lightroom developers and was surprising to the team.  We have not
looked into write performance.  Why don't you do the measurements and
report back?


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] write internal blob vs external file

[Eric Rubin-Smith]

Looking at the sqlite web site and mailing lists shows that the SQLite team
has taken a stab at answering the question, "is it faster to read a blob
out of sqlite or out of a file?".  See the links below.

Does the team have analogous guidance regarding write speeds?

I'm also interested in the delete path.  It seems like SQLite doesn't have
much hope of competing with a native filesystem unlink(2) call to delete a
file that is many gigabytes long, for example.  Is that right?

Eric

References:

http://sqlite.1065341.n5.nabble.com/Internal-v-External-BLOBs-td15515.html
http://www.sqlite.org/intern-v-extern-blob.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users