SQLite small blob storage: 35% Faster Than the Filesystem • Hacker News Board

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#181

Post by rusanu » Wed, Jun 14, 2017, 6:50 PM UTC

wyoung2 wrote: post #125

Spooky23 wrote: post #111

Earlier quoted context omitted.

Great catch. The whole premise of the article is undermined by that.

The article may be unclear on this point: anti-virus was turned off for these tests. What that paragraph is pointing out is that performance tanks badly (roughly a factor of 10x) when you turn on Windows Defender, and presumably even worse with other anti-virus software. Credential: I was one of several people who vetted the article before it was published. I argued that Defender should be enabled, since it is a plat…

Can you clarify then wether the AV was simply not scanning the SQLite files (ie. the file extension or the file location was exempt in AV config) ?

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#182

Post by octetta » Wed, Jun 14, 2017, 6:56 PM UTC

troutaway123 wrote: post #149

patio11 wrote: post #114

Earlier quoted context omitted.

My experience (for an application which had a working set of under 1 GB of files in the 50kb to N MB range, and approximately 50 GB persisted at any given time) was that preserving access to the toolchain which operates trivially with files was worth the additional performance overhead of working with the files and, separately, occasionally having to retool things to e.g. not have 10e7 files in a single folder, which…

¿Porque no los dos? Store the files in a database but expose them via FUSE.

...or v9fs... but the overhead might make the whole exercise pointless.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#183

Post by cm2187 » Wed, Jun 14, 2017, 7:12 PM UTC

jamie_ca wrote: post #170

cm2187 wrote: post #166

Earlier quoted context omitted.

I never understood why it mattered that files would be in a single directory or split over multiple directories. At the end of the day it is the same file system with the same number of files. The directories are just a logical structure. Why would there be a difference?

Spinning disks. If you want raw file access, your ideal filesystem is a giant key-value store that keys on the full path to the file. This choice means doing a directory listing will involve a lot of random access reads across the disk, and in the days before SSDs this would be a performance killer. So instead, a directory would have a little section of disk to write its file entries to. These would be fairly small,…

Ok but listing 100 directories with 10,000 files each should take the same time than listing one directory with 1,000,000 files (what you are describing is a filesystem with more files vs less files, not with more subdirectories than less subdirectories).

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#184

Post by ngrilly » Wed, Jun 14, 2017, 8:05 PM UTC

the8472 wrote: post #172

jamie_ca wrote: post #170

Earlier quoted context omitted.

Spinning disks. If you want raw file access, your ideal filesystem is a giant key-value store that keys on the full path to the file. This choice means doing a directory listing will involve a lot of random access reads across the disk, and in the days before SSDs this would be a performance killer. So instead, a directory would have a little section of disk to write its file entries to. These would be fairly small,…

ext4 uses btrees for the directory index, so accessing files by name is just as fast as manually splitting it into a prefix-based directory tree. Searching by filename on the other hand can be much faster with a prefix tree because directories only allow linear scans, not range-based ones.

I'm missing something. What difference do you see between "accessing files by name" and "searching by filename"?

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#185

Post by rkeene2 » Wed, Jun 14, 2017, 8:18 PM UTC

tetha wrote: post #91

rusanu wrote: post #70

I must point out Jim Gray's paper To Blob or Not To Blob[0]. His team considered NTFS vs. SQL Server, but most rationale applies to any filesystem vs. database decision. The summary was "The study indicates that if objects are larger than one megabyte on average, NTFS has a clear advantage over SQL Server. If the objects are under 256 kilobytes, the database has a clear advantage. Inside this range, it depends on how…

> Despite all this, my practical experience is that filesystem is better than blobs for things like uploaded content, images, pngs and jps etc. This is especially true if you want to deliver them back to the users in the context of a web application. If you place this kind of content in a database, you'll need to serve them with your application. If you use files for this content, you get two interesting options. For…

Completely shameless plug here: My webserver[1] will outperform nginx for serving up static content like this.

It has many optimizations, like not opening the file its serving (which reduces the latency of walking/interacting with the VFS layer); almost no runtime allocations on the heap (minimal stack frames and sizes as well) which can be completely disabled at the expense of logging; high performance logging which does not interfere with processing requests; the normal stuff in a fast webserver (sendfile, keep-alive, minimal code paths between reading the user request and delivering the first byte).

[1] http://filed.rkeene.org/

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#186

Post by bedros » Wed, Jun 14, 2017, 8:36 PM UTC

jdmichal wrote: post #180

bedros wrote: post #173

it's basically storing in memory vs storing on a disk it's not sqlite vs filesystem it's memory storage vs disk storage

Are you stating that the SQLite database did not write the files to disk? Or simply that it's caching them in-memory? In the latter case, doesn't an operating system typically offer in-memory file buffering / caching? Otherwise, why would we need O_DIRECT or O_SYNC or fsync()?

I'm no expert in FS or sqlite, but as everyone knows, filesystems are not good processing small files, having an application that bundles in memory small blobs and then flush them to disk as a single file, is what make it faster.

ReiserFS 4 was (I believe still is) the only FS that improves small files performance by bundling small files and stored them on disk as a single blob.

as for that person who downvoted my original comment, I'd love to hear from you why you think I'm wrong. you don't just downvote comments because you disagree.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#187

Post by the8472 » Wed, Jun 14, 2017, 8:46 PM UTC

ngrilly wrote: post #184

the8472 wrote: post #172

Earlier quoted context omitted.

ext4 uses btrees for the directory index, so accessing files by name is just as fast as manually splitting it into a prefix-based directory tree. Searching by filename on the other hand can be much faster with a prefix tree because directories only allow linear scans, not range-based ones.

I'm missing something. What difference do you see between "accessing files by name" and "searching by filename"?

    f = open("/path/to/file");
    // do something with file

vs.

    d = opendir("path/to")
    while((dent = readdir(d)) != NULL) {
       if(!dent.name.startsWith("file"))
          continue;
       // do something with first matched entry
    }

The former is O(log n) on ext4, O(n) on older filesystems that use flat lists.

The latter is always O(n)

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#188

Post by jdmichal » Wed, Jun 14, 2017, 8:54 PM UTC

bedros wrote: post #186

jdmichal wrote: post #180

Earlier quoted context omitted.

Are you stating that the SQLite database did not write the files to disk? Or simply that it's caching them in-memory? In the latter case, doesn't an operating system typically offer in-memory file buffering / caching? Otherwise, why would we need O_DIRECT or O_SYNC or fsync()?

I'm no expert in FS or sqlite, but as everyone knows, filesystems are not good processing small files, having an application that bundles in memory small blobs and then flush them to disk as a single file, is what make it faster. ReiserFS 4 was (I believe still is) the only FS that improves small files performance by bundling small files and stored them on disk as a single blob. as for that person who downvoted my or…

I do think your original claim is wrong. Your explanation in this response is not the same as your original claim. And what you wrote here is exactly in line with claims in the article:

"The size of the blobs in the test data affects performance. The filesystem will generally be faster for larger blobs, since the overhead of open() and close() is amortized over more bytes of I/O, whereas the database will be more efficient in both speed and space as the average blob size decreases."

Yes, of course SQLite is going to do some operations in-memory. Raw file writes using fwrite are also going to be initially written in-memory, unless O_DIRECT or some other mechanism is involved. And the article explicitly outlines that they made no effort to bypass file buffering, to the point of not even explicitly flushing to disk.

If both processes are writing the BLOBs to disk, then I don't see how your original claim applies. Writing to disk in a manner that is more efficient for small files is still writing to disk, and so the test is not in-memory vs on-disk as your original claim.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#189

Post by ngrilly » Wed, Jun 14, 2017, 9:01 PM UTC

the8472 wrote: post #187

ngrilly wrote: post #184

Earlier quoted context omitted.

I'm missing something. What difference do you see between "accessing files by name" and "searching by filename"?

f = open("/path/to/file"); // do something with file vs. d = opendir("path/to") while((dent = readdir(d)) != NULL) { if(!dent.name.startsWith("file")) continue; // do something with first matched entry } The former is O(log n) on ext4, O(n) on older filesystems that use flat lists. The latter is always O(n)

Understood. Thanks ;-)

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#190

Post by jdmichal » Wed, Jun 14, 2017, 9:07 PM UTC

copenja wrote: post #68

mcpherrinm wrote: post #63

Earlier quoted context omitted.

The article is basically entirely an answer to that question. The third sentence in particular is: The performance difference arises (we believe) because when working from an SQLite database, the open() and close() system calls are invoked only once, whereas open() and close() are invoked once for each blob when using blobs stored in individual files. It appears that the overhead of calling open() and close() is grea…

The advice to use a single open/close call instead of multiple is great, but that is advice on how to effectively use the filesystem. It isn't in anyway, shape, or form beating the filesystem.

You can't use a single open/close call to write multiple BLOBs, unless you are concatenating them into one file. That's not typically what happens nor what people are advocating when they recommend not using database BLOBs and using the filesystem instead.