SQLite speed tests BLOB vs flat-file for those interested
A
results. Some are expected - some are surprise.
My main concern was speed of reading rows from database.
The comparison was measured by QueryPerformanceCounter which is most precise
AFAIK on Windows. A couple of hundred runs for each test to eliminate
statistical error.
In tests there were 3 SQLite tables and one flat-file with binary data. The
measurement was how to place this data most effectively so it can be
retrieved as quickly as possible.
Flat-file had a simple | LENGTH | DATA | binary data in it. Seek to offset
position, read LENGTH then read binary DATA. As easy as that.
Database1 had 16 fields (some INT64, some TEXT with small amount of text
data, some REAL). Last field was 64bit offset in flat-file.
Database2 had also 16 fields except the very last field instead of offset
was a BLOB with actual binary data (about 5-50 kb of data).
Database3 was only ID + BLOB fields (2 fields). In other words - a
replacement for flat-file.
No indexes in any database file.
Here are the tests:
Test 1:
- SELECT * FROM db1 ORDER BY RANDOM() LIMIT 1
- use offset field to seek to file position
- read binary data len from file and read binary data
Test 2:
- SELECT * FROM db2 ORDER BY RANDOM() LIMIT 1
- read binary data from BLOB field in db2 already in result set
Test 3:
- SELECT [col1],[col2]... [except blob column] FROM db2 ORDER BY RANDOM()
LIMIT 1
- just measure speed of reading
Test 4:
- SELECT * FROM db1 ORDER BY RANDOM() LIMIT 1
- SELECT [blob_column] FROM db3 WHERE id = [col_id]
- use blob from database3 field
Test 5:
- SELECT [col1],[col2]... [except blob column] FROM db2 ORDER BY RANDOM()
LIMIT 1
- SELECT [blob_column] FROM db2 WHERE id=[col_id];
- use blob from second read
Results:
Test1:
237.216 - 100%
Test2:
7562.158 - 3187%
Test3:
320.832 - 135%
Test4:
246.821 - 104%
Test5:
388.566 - 163%
Comments -
test1 was the fastest. Reading a row from DB and then seeking to flat-file
and reading binary data is the fastest. no surprises there.
test4 came very close. it is quite fast to put BLOB in another database file
and then just read it based on ID. so it may be worth to actually put BLOB
in separate database file to be able to read it much faster than in the same
database file.
test5 was still much much faster than test2. although you'd expect that
reading BLOB along with the rest of fields is the same speed or even
faster - it was VERY VERY SLOW. to verify - I made a couple of queries in
SQLite admin tools - they also had about 2000ms per query which usually took
60-100ms to finish. strange?
test3 - it is obviously much faster to avoid putting BLOBs in same database
file even if not reading them - even when skipped reading speed is about 35%
slower than initial database file
So the conclusion may be:
- where speed is of concern - put BLOB data in flat-file and put just an
offset in database file where to seek to read BLOB data
- if you want to write safely, the best option is to put BLOB in another
database and use ID from first database to read from another
- BLOB data should be avoided along with the rest of smaller row data as it
seems to slow things down considerably, the smaller the row and the simpler
data structure - the better.
Maarten Wiltink
> As Maarten in previous discussion suggested I'm sharing some speed-test
> results.
For which, thanks.
[...]
> No indexes in any database file.
That means retrieving records by primary key requires a table scan
instead of a tree search. Probably not much slower when there are not
many records, and BLOBs are stored away from the fixed-length part of
the records.
Groetjes,
Maarten Wiltink
Arivald
> As Maarten in previous discussion suggested I'm sharing some speed-test
> results. Some are expected - some are surprise.
> My main concern was speed of reading rows from database.
>
> The comparison was measured by QueryPerformanceCounter which is most precise
> AFAIK on Windows. A couple of hundred runs for each test to eliminate
> statistical error.
>
> In tests there were 3 SQLite tables and one flat-file with binary data. The
> measurement was how to place this data most effectively so it can be
> retrieved as quickly as possible.
>
> Flat-file had a simple | LENGTH | DATA | binary data in it. Seek to offset
> position, read LENGTH then read binary DATA. As easy as that.
>
> Database1 had 16 fields (some INT64, some TEXT with small amount of text
> data, some REAL). Last field was 64bit offset in flat-file.
> Database2 had also 16 fields except the very last field instead of offset
> was a BLOB with actual binary data (about 5-50 kb of data).
> Database3 was only ID + BLOB fields (2 fields). In other words - a
> replacement for flat-file.
>
> No indexes in any database file.
>
> Here are the tests:
>
> Test 1:
> - SELECT * FROM db1 ORDER BY RANDOM() LIMIT 1
> - use offset field to seek to file position
> - read binary data len from file and read binary data
This format may be very fast to read BLOB, but every update may be pain.
You can't move existing BLOBs, because it will require updating offset
in db1 table.
With every insert You will add new BLOBS on end of file. It is fast.
With every delete You will make "holes" in BLOBs file. Eventually there
will be more "holes" than actual data.
This is known problem in MBOX mailbox format. Thunderbird solve it by
periodic compacting, which take time.
As solution You may try to have every BLOB in one file. Plus some kind
of directories tree, to have max 10000 files/directoruies in one directory.
As example You may look how SQID http cache proxy manage cache folder.
> Test 2:
> - SELECT * FROM db2 ORDER BY RANDOM() LIMIT 1
> - read binary data from BLOB field in db2 already in result set
>
> Test 3:
> - SELECT [col1],[col2]... [except blob column] FROM db2 ORDER BY RANDOM()
> LIMIT 1
> - just measure speed of reading
>
> Test 4:
> - SELECT * FROM db1 ORDER BY RANDOM() LIMIT 1
> - SELECT [blob_column] FROM db3 WHERE id = [col_id]
> - use blob from database3 field
try
Test 4.5:
- SELECT db1.*, db3.[blob_column] FROM db1
LEFT OUTER JOIN db3 on db3.id = db1.[col_id]
ORDER BY RANDOM() LIMIT 1
- use blob from database3 field
It basically do same work (select fields from db1, and blob from db3),
but in single query.
Note that every joins are much faster if field You use to join (db3.id,
db1.[col_id]) is indexed.
Basically both db3.id and db1.[col_id] should be primary keys. Primary
key is index too, also it forces that all values in this field are unique.
There are 2 very well known problems.
First, if You use row of variable width (one of fields is VARCHAR or
BLOB), all seeks become much slower. It is caused by inability to
precisely calculate position of row, DB-engine must approximate
position, then seek.
If table row have fixed size (Bo BLOB, no VARCHAR), db-engine can
exactly calculate where given row begins, no seeking needed.
BTW, seems that SQLite did not discriminate between CHAR and VARCHAR,
both of them are turned into TEXT type... So probably SQLlite always use
rows with variable width.
Second problem is row size. Of course seeking in table with long rows is
slower, there is much more data to read from media.
Many DB-engines solve it by putting BLOBs in separate structures, and
hold only pointer to BLOB data in row.
Don't know how SQLite handle it. Probably it does not use this
optimization, it is very simple SQL engine.
> test3 - it is obviously much faster to avoid putting BLOBs in same database
> file even if not reading them - even when skipped reading speed is about 35%
> slower than initial database file
>
> So the conclusion may be:
>
> - where speed is of concern - put BLOB data in flat-file and put just an
> offset in database file where to seek to read BLOB data
Speed of read. Speeed of update. But at cost of lot of wasted space, or
complex logic to reuse holes.
> - if you want to write safely, the best option is to put BLOB in another
> database and use ID from first database to read from another
Another database? You really do this test with all tables in different
database files? Or all tables was in one database?
Hope You know difference between database and table in database.
> - BLOB data should be avoided along with the rest of smaller row data as it
> seems to slow things down considerably, the smaller the row and the simpler
> data structure - the better.
--
Arivald
Arivald
> W dniu 2011-09-05 16:51, A pisze:
>> test5 was still much much faster than test2. although you'd expect that
>> reading BLOB along with the rest of fields is the same speed or even
>> faster - it was VERY VERY SLOW. to verify - I made a couple of queries in
>> SQLite admin tools - they also had about 2000ms per query which
>> usually took
>> 60-100ms to finish. strange?
>
> There are 2 very well known problems.
>
> First, if You use row of variable width (one of fields is VARCHAR or
> BLOB), all seeks become much slower. It is caused by inability to
> precisely calculate position of row, DB-engine must approximate
> position, then seek.
>
> If table row have fixed size (Bo BLOB, no VARCHAR), db-engine can
> exactly calculate where given row begins, no seeking needed.
>
> BTW, seems that SQLite did not discriminate between CHAR and VARCHAR,
> both of them are turned into TEXT type... So probably SQLlite always use
> rows with variable width.
>
Out of curiosity I check SQLite documentation, and it seems that all
text fields in SQLite are BLOBs too ;-)
http://www.sqlite.org/datatype3.html
Only difference between text BLOB and binary blob is that expressions
work on them differently, and have no encoding.
Even date and time is stored as text BLOB (as ISO8601 text)!
--
Arivald
Jamie
Jamie
A
> instead of a tree search. Probably not much slower when there are not
> many records, and BLOBs are stored away from the fixed-length part of
> the records.
id field was indexed. every PRIMARY key is indexed - and the one that is
named "id" and INTEGER is automatically indexed (becomes alias for ROWID).
the BLOB table was: [id] PRIMARY INTEGER NOT NULL, [blobfield] BLOB.
So it was searched as binary tree when retrieved by id.
A
> You can't move existing BLOBs, because it will require updating offset in
> db1 table.
> With every insert You will add new BLOBS on end of file. It is fast.
> With every delete You will make "holes" in BLOBs file. Eventually there
> will be more "holes" than actual data.
>
> This is known problem in MBOX mailbox format. Thunderbird solve it by
> periodic compacting, which take time.
That's not really a problem - it is expected on every system that stores and
deletes data.
SQLite has built in compacting - VACUUM.
Just run VACUUM periodically on table. It works quite fast. You can also
predict fragmentation if you scan your [id] fields in the table a bit and if
a fragmentation passes a certain point then VACUUM so you don't VACUUM too
often. Outlook Express also compacts its mailbox files. But guess what -
disk files are also fragmented when deleted and new ones saved over the old
ones. So there is no framentation free system. Occasional defragmentation is
required anyways. Still, a large file performance is still better than a lot
of small files - especially if you need to read a lot of data from it
because it does not require constant closing and opening of smaller files.
> As solution You may try to have every BLOB in one file. Plus some kind of
> directories tree, to have max 10000 files/directoruies in one directory.
> As example You may look how SQID http cache proxy manage cache folder.
Like I said, that is possible but then different set of problems comes into
play - disk fragmentation, more disk space required (if you save 50 bytes,
you will save 4kb instead because of cluster size on disk), system will slow
down if you enter a folder with more than 2000-3000 files etc. One file has
its advantages and disadvantages but same goes for multi-file.
> - SELECT db1.*, db3.[blob_column] FROM db1
> LEFT OUTER JOIN db3 on db3.id = db1.[col_id]
> ORDER BY RANDOM() LIMIT 1
> - use blob from database3 field
>
> It basically do same work (select fields from db1, and blob from db3), but
> in single query.
>
> Note that every joins are much faster if field You use to join (db3.id,
> db1.[col_id]) is indexed.
> Basically both db3.id and db1.[col_id] should be primary keys. Primary key
> is index too, also it forces that all values in this field are unique.
Might be worth testing, but for my needs I only occasionally read BLOB
fields and very frequently read the rest - so in my case I wouldn't benefit
much if implemented like this. Besides, I don't even need to read BLOB along
with the rest of data but I read the data first and then eventually, when
needed I read BLOB.
I'll see if I can do such a test quickly and report back.
> BTW, seems that SQLite did not discriminate between CHAR and VARCHAR, both
> of them are turned into TEXT type... So probably SQLlite always use rows
> with variable width.
That could be improved by indexing fields. When they are indexed seeking
obviously becomes faster.
But indexes are not required if you seek only by [id] because that field is
already indexed as PRIMARY.
> Many DB-engines solve it by putting BLOBs in separate structures, and hold
> only pointer to BLOB data in row.
> Don't know how SQLite handle it. Probably it does not use this
> optimization, it is very simple SQL engine.
From my tests - there was no much difference in speed in seeking to file
based on offset or doing SELECT [blob] FROM db2 WHERE id=something.
for a very simple table seeking based on id is very very fast - the
difference is only a couple of percent. So it makes sense to still use
SQLite to store BLOB data because of the ease of maintaining the table (for
example you don't have to implement your own system for VACUUM).
> Speed of read. Speeed of update. But at cost of lot of wasted space, or
> complex logic to reuse holes.
SQLite already has built in this logic to reuse holes so why bother. and on
each VACUUM it simply rearranges it back again in order.
> Another database? You really do this test with all tables in different
> database files? Or all tables was in one database?
> Hope You know difference between database and table in database.
it was different files - so it was different database. In SQLite a file is a
"database" and can hold one or more "tables". Of course I know the
difference.
Arivald
>> This format may be very fast to read BLOB, but every update may be pain.
>> You can't move existing BLOBs, because it will require updating offset in
>> db1 table.
>> With every insert You will add new BLOBS on end of file. It is fast.
>> With every delete You will make "holes" in BLOBs file. Eventually there
>> will be more "holes" than actual data.
>>
>> This is known problem in MBOX mailbox format. Thunderbird solve it by
>> periodic compacting, which take time.
>
> That's not really a problem - it is expected on every system that stores and
> deletes data.
> SQLite has built in compacting - VACUUM.
Yea, but I was talking about Your "big flat-file" storage.
SQLite uses different methods to store BLOBS, and it do not waste space
after deleting. SQLite uses standard way to handle this problem: split
available space into blocks of equal size, then use whole blocks. This
way it is very easy to reuse space, because every block will fit into
empty hole.
There are a lot of implementation of block-based storage, particularly
every disk filesystem implements it.
Disadvantage of block-based storage is possible fragmentation of data
(involve more disk seeks). On the other hand, SSD drives completely
nullify this disadvantage ;-).
In SQLite block is often called a "page". Adjusting size of page can
greatly affect engine performance. One of mostly used optimizations is
to set page size to size of cluster on disk where db file is created.
In Your case, try make DB file for BLOBs with larger page (32 kb?), and
check performace.
You can set page size for database fie by PRAGMA page_size = xxx; , but
as far as I know it affect only newly created tables.
Also adjusting "PRAGMA cache_size" may increase performance.
> Just run VACUUM periodically on table. It works quite fast. You can also
Yes, VACUUM defragments data. But it work a bit differently. It cure
fragmentation caused by page/row difference in size.
For example if row have 1000 bytes, and page is 4kb, engine can put 4
rows on page. It means that remaining ~90 bytes on page will be unused.
Next, 5 row will be allocated on new page.
Now, when user delete row, engine will not relocate all rows in all
pages, it just remove data from single page... if all rows on page are
deleted, page is deallocated. But if even single row on page still
exists, engine can't free page. It only can add new row if applicable.
VACUUM command is designed to defragment in this case, and free as much
pages as possible.
> predict fragmentation if you scan your [id] fields in the table a bit and if
> a fragmentation passes a certain point then VACUUM so you don't VACUUM too
> often. Outlook Express also compacts its mailbox files. But guess what -
> disk files are also fragmented when deleted and new ones saved over the old
> ones.
Yes, but this are different types of fragmentation.
MBOX and Your flat-file" uses continuous space, and use raw offset to
identify data.
Filesystems and databases uses storage space splitted to blocks, and use
block numbers to identify all blocks related to data.
It have different performance and "wasted space" profile.
In both cases there is some wastes space, there is fragmentation. But
block-based systems perform far better than linear ones, except for very
specific usages.
> So there is no framentation free system. Occasional defragmentation is
> required anyways.
Yes, but fragmentation does not need to impact performance. SSD is best
example here. :-)
> Still, a large file performance is still better than a lot
> of small files - especially if you need to read a lot of data from it
> because it does not require constant closing and opening of smaller files.
I agree, it may be better... I just did not know Your use case.
Although it may be much simpler to implement.
On the other hand, BLOBS in SQLite database is simple too, and should be
faster.
>> As solution You may try to have every BLOB in one file. Plus some kind of
>> directories tree, to have max 10000 files/directoruies in one directory.
>> As example You may look how SQID http cache proxy manage cache folder.
>
> Like I said, that is possible but then different set of problems comes into
> play - disk fragmentation,
You can't avoid disk fragmentation, in all possible solutions
(flat-file, SQLlite, small files)
> more disk space required (if you save 50 bytes,
> you will save 4kb instead because of cluster size on disk),
Block-based systems, like filesystems, always waste some space. But they
are far better in reusing space of deleted elements.
And SQLlite is block based too... ;-)
> system will slow down if you enter a folder with more than 2000-3000 files etc.
System? No.
Explorer, or other software which iterate over files may slow down. This
slowness is created by in-memory structures not prepared to hold so much
entries. For example Explorer use LISTVIEW control, which start choking
with few thousands of items.
System itself uses special data to locate blocks of file, kind of index,
so opening file with known name is fast. Only very large number of files
can slow it down. It is why I mention 10000 files/directories in one
directory limit.
BTW, MS did not consider 10000 files a big number. For example, in this
article (
http://technet.microsoft.com/en-us/library/cc781134%28WS.10%29.aspx ),
MS says "If you use large numbers of files in an NTFS folder (300,000 or
more), ..."
>
>> - SELECT db1.*, db3.[blob_column] FROM db1
>> LEFT OUTER JOIN db3 on db3.id = db1.[col_id]
>> ORDER BY RANDOM() LIMIT 1
>> - use blob from database3 field
>>
>> It basically do same work (select fields from db1, and blob from db3), but
>> in single query.
>>
>> Note that every joins are much faster if field You use to join (db3.id,
>> db1.[col_id]) is indexed.
>> Basically both db3.id and db1.[col_id] should be primary keys. Primary key
>> is index too, also it forces that all values in this field are unique.
>
> Might be worth testing, but for my needs I only occasionally read BLOB
> fields and very frequently read the rest - so in my case I wouldn't benefit
> much if implemented like this. Besides, I don't even need to read BLOB along
> with the rest of data but I read the data first and then eventually, when
> needed I read BLOB.
> I'll see if I can do such a test quickly and report back.
You right, if you did not use BLOB often, it is better to read them in
separate query.
Basically I was curious how DB will behave in this case, it will be
faster or slower.
>> Many DB-engines solve it by putting BLOBs in separate structures, and hold
>> only pointer to BLOB data in row.
>> Don't know how SQLite handle it. Probably it does not use this
>> optimization, it is very simple SQL engine.
>
> From my tests - there was no much difference in speed in seeking to file
> based on offset or doing SELECT [blob] FROM db2 WHERE id=something.
> for a very simple table seeking based on id is very very fast - the
> difference is only a couple of percent. So it makes sense to still use
> SQLite to store BLOB data because of the ease of maintaining the table (for
> example you don't have to implement your own system for VACUUM).
Yes, I agree.
But try BLOB performance after You adjust page size in database with BLOBS.
And You really does not need to use VACUUM if you have lot of space,
internal mechanisms to reuse disk space are efficient enough. VACUUM
option was designed for embedded systems, where every byte counts.
--
Arivald