Re: [sqlite] write internal blob vs external file
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
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
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
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
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
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
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
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
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
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
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