Re: [sqlite] Internal v. External BLOBs
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
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
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
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
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
> 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
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
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