Re: [sqlite] Internal v. External BLOBs

[Petite Abeille]

On Oct 1, 2011, at 2:09 AM, Simon Slavin wrote:

>> Thanks for sharing. What about insert time (i.e. insert blob vs. write file)?
> 
> This would be far more dependent on your combination of operating system and 
> file system.  Reading a file is pretty-much the same on everything.  Creating 
> a new one is done very differently.

Well, I guess the same disclaimer applies to both operation:

"This is on Ubuntu with EXT4 and a fast SATA disk - your mileage may vary with 
different operating systems, filesystems, and hardware."

http://www.sqlite.org/intern-v-extern-blob.html

That said, it would still be interesting to see how the write operations 
compare with such a setting.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Internal v. External BLOBs

[Simon Slavin]

On 1 Oct 2011, at 12:37am, Petite Abeille wrote:

> On Sep 21, 2011, at 2:19 AM, Richard Hipp wrote:
> 
>> If you are storing large BLOBs in SQLite, can you read them faster if they
>> are stored directly in the database file, or can you get to them quicker if
>> you store just a filename in the database and read the BLOB content from a
>> separate file?
> 
> Thanks for sharing. What about insert time (i.e. insert blob vs. write file)?

This would be far more dependent on your combination of operating system and 
file system.  Reading a file is pretty-much the same on everything.  Creating a 
new one is done very differently.

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

Re: [sqlite] Internal v. External BLOBs

[Petite Abeille]

On Sep 21, 2011, at 2:19 AM, Richard Hipp wrote:

> If you are storing large BLOBs in SQLite, can you read them faster if they
> are stored directly in the database file, or can you get to them quicker if
> you store just a filename in the database and read the BLOB content from a
> separate file?

Thanks for sharing. What about insert time (i.e. insert blob vs. write file)?

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

Re: [sqlite] Internal v. External BLOBs

[Teg]

My experience is that having larger blobs impacts performance too. It
seems like SQlite struggles to seek over the blobs when traversing
non-blob containing tables. I haven't characterized it beyond that. It
might even be disk caching. I tend to keep my blobs in a different DB
file than the tables that describe the blob contents though because of
this.

I have some 30 GB blob containing files. I use these DB's just for
organization of the blobs. Performance wasn't that important.



Wednesday, September 21, 2011, 5:29:16 AM, you wrote:

AP> There is a problem with access to file in directory with big amount of 
files.
AP> FS directory indicies are not really good. I did test 100 millions of 1k 
files
AP> in SQLite and results were better than reading from set of directories in 
FS.
AP> But for files about 1 Mb and more the SQLIte performance is not good.
AP> Is any reason why SQLite big blobs reading may be slowly? The
AP> performance of the BLOBS may limit performance of FTS and
AP> other custom storage/index realizations (Spatialite, etc). And it's more
AP> important I think. Especially when we need FTS index as fast hash index.

AP> 2011/9/21 Richard Hipp :
>> If you are storing large BLOBs in SQLite, can you read them faster if they
>> are stored directly in the database file, or can you get to them quicker if
>> you store just a filename in the database and read the BLOB content from a
>> separate file?
>>
>> We did some experiments to try to answer this question, and the results
>> seemed interesting enough to share with the community at large.  Bottom
>> line:  On Linux workstations, it is faster to store BLOBs in the database if
>> they are less than about 100KB in size, and faster to store them in a
>> separate file if they are larger than about 100KB.  This is on Ubuntu with
>> EXT4 and a fast SATA disk - your mileage may vary with different operating
>> systems, filesystems, and hardware.
>>
>> The complete report is here:
>> http://www.sqlite.org/intern-v-extern-blob.html
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>






-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] Internal v. External BLOBs

[Alexey Pechnikov]

There is a problem with access to file in directory with big amount of files.
FS directory indicies are not really good. I did test 100 millions of 1k files
in SQLite and results were better than reading from set of directories in FS.
But for files about 1 Mb and more the SQLIte performance is not good.
Is any reason why SQLite big blobs reading may be slowly? The
performance of the BLOBS may limit performance of FTS and
other custom storage/index realizations (Spatialite, etc). And it's more
important I think. Especially when we need FTS index as fast hash index.

2011/9/21 Richard Hipp :
> If you are storing large BLOBs in SQLite, can you read them faster if they
> are stored directly in the database file, or can you get to them quicker if
> you store just a filename in the database and read the BLOB content from a
> separate file?
>
> We did some experiments to try to answer this question, and the results
> seemed interesting enough to share with the community at large.  Bottom
> line:  On Linux workstations, it is faster to store BLOBs in the database if
> they are less than about 100KB in size, and faster to store them in a
> separate file if they are larger than about 100KB.  This is on Ubuntu with
> EXT4 and a fast SATA disk - your mileage may vary with different operating
> systems, filesystems, and hardware.
>
> The complete report is here:
> http://www.sqlite.org/intern-v-extern-blob.html
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Internal v. External BLOBs

[Filip Navara]

> We did some experiments to try to answer this question, and the results
> seemed interesting enough to share with the community at large.

Are the test cases available somewhere?

I'd be interested in running them on Windows setup with/out SSD.

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

Re: [sqlite] Internal v. External BLOBs

[Simon Slavin]

On 21 Sep 2011, at 1:19am, Richard Hipp wrote:

> On Linux workstations, it is faster to store BLOBs in the database if
> they are less than about 100KB in size, and faster to store them in a
> separate file if they are larger than about 100KB.

Looking at the '10k' column in your graph, do you have any idea why the figures 
seem destined to climb as the page size climbs, but then drop severely for the 
last two rows ?  It's evident in the next column too, but not as obvious.

I looked at some image files we keep at work which are passport-style photos of 
people.  They're about 225k.  So at default page size for our computers, it's 
around three times as fast to keep the pictures as files, and store just the 
filename.  Wow.

That's assuming performance on Macs is about the same as performance under 
Linux.  Pretty identical hardware and OS, but radically different file system.  
Might give very different results.

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

[sqlite] Internal v. External BLOBs

[Richard Hipp]

If you are storing large BLOBs in SQLite, can you read them faster if they
are stored directly in the database file, or can you get to them quicker if
you store just a filename in the database and read the BLOB content from a
separate file?

We did some experiments to try to answer this question, and the results
seemed interesting enough to share with the community at large.  Bottom
line:  On Linux workstations, it is faster to store BLOBs in the database if
they are less than about 100KB in size, and faster to store them in a
separate file if they are larger than about 100KB.  This is on Ubuntu with
EXT4 and a fast SATA disk - your mileage may vary with different operating
systems, filesystems, and hardware.

The complete report is here:
http://www.sqlite.org/intern-v-extern-blob.html

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