Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite small blob storage: 35% Faster Than the Filesystem

sqlite.org

Post reply on HN
Print view View original

171–180 of 208 posts

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

#171

Post by firethief »

troutaway123 wrote: post #158
pjc50 wrote: post #156

Earlier quoted context omitted.

That gives you the time cost of a filesystem plus the time cost of a database plus an extra round-trip in and out of kernel space.

> preserving access to the toolchain which operates trivially with files was worth the additional performance overhead of working with the files Sounds like they're OK with that. In any case, couldn't you avoid the kernel trips with some dylib foolery (assuming the toolchain is dynamically linked)?

So: store the files in a database, expose them via FUSE, bypass FUSE and access the database?

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

#172

Post by the8472 »

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,…

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.

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

#174

Post by ccommsxx »

patio11 wrote: post #114
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…

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…

50GB of files in the 50KB-inf range can be at most 10e5 files, not 10e7. (10e7 would be 5TB of 50KB files)

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

#175

Post by the8472 »

fnord123 wrote: post #141
icebraining wrote: post #121

Earlier quoted context omitted.

Ah, but the filesystem has to do all that as well! It must receive a path, parse it, and then execute the query, with possible optimizations (eg. ext4 even has indexes implemented with hashed b-trees). A filesystem is just an hierarchical database.

>A filesystem is just an hierarchical database. Loosely. Today's file systems aren't transactional (i.e. acid compliance), which is a basic property that most people consider necessary for a database.

Many filesystems are logged and provide atomic operations. You can build multi-step transactions on top of them with various operations. And with CoW filesystems you can even get read consistency. Oh, and optimistic locking also is an option.

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

#176

Post by rusanu »

_jal wrote: post #160
Klathmon wrote: post #129

Earlier quoted context omitted.

I figured there was something massively annoying with it. I'm hoping that PostgreSQL can try to tackle this use-case somehow because it's so damn common and 99% of the time the solution that is used completely throws out all the guarantees that the database gives you.

I'm not sure there's a non-annoying way to bridge the problem. The semantics are just so different that there's a lot of friction that needs to be handled somewhere. (What does O_DIRECT mean in this context? What happens when someone decides to store files that are never normally closed?) In some ways, the problem mirrors OR mappers. A lot of common cases can be handled, but there are always situations where you're c…

Since you mention the OR impedance mismatch problem, I have to link to The Vietnam of CS article: http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...

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

#177

Post by HeavyStorm »

cm2187 wrote: post #166
Twirrim wrote: post #161

Earlier quoted context omitted.

> e.g. not have 10e7 files in a single folder, which is something that Linux has some opinions (none of them good) about. I keep running in to this as a common anti-pattern in software, even with software developers that should know better (having fallen afoul of it before). I ran in to it with some semi-bespoke software once, used by a remarkable number of state departments across the country. It was one of many, ma…

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?

Well, my Linux-fu isn't very up-to-date, but my guess (which actually goes for any file system) is that a directory is a bit more than just a logical organization. It's an actual "thing" in your hard drive which keeps track of everything beneath it. And so it grows in size when more files are there, which slow down access.

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

#178

Post by Dylan16807 »

45h34jh53k4j wrote: post #162
Dylan16807 wrote: post #110

Earlier quoted context omitted.

Data chunks inside another file are effectively quarantined by default. There is no exempting or security issue, it's safe by design. It will get scanned occasionally but not on every write.

Except when they are not. Just because malcode is encapsulated doesn't mean it is neutralised. The issue is untrusted data, not how it is stored. Word Docs with embedded content or can trigger code execution on parsing, data structures that break AV parsers, image or font data that causes system libraries to overflow/UAF/etc; There all could potentially be a security issue in a DB, the same as in a FS.

You don't need to worry about a single process writing data to a file and then reading it back and performing malicious actions. That process could just as easily have been exploited entirely in-memory. Scan the database when it exits and you're fine.

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

#179

Post by Const-me »

JBReefer wrote: post #154
Const-me wrote: post #86

SQLite is good when you’re mostly reading. For writing, the major drawback of SQLite is it doesn’t support concurrent writes. All filesystems do (at least when writing different files), all full-fledged RDBMS-es do, even some embedded databases do (like ESENT). Yet, in SQLite only a single thread can write. Even embedded chips are multicore these days…

Isn't that because SQLLite's whole use case is "simple, small, fast database that competes with fopen() and not Postgres"? At least in my experience on NTFS, unless you know the section that 2 threads are writing to, and that section is different, you can't write to a file concurrently (N.B.: in C#) I'd be happy to be wrong (seriously that would make my day if someone could explain a clean way to do the above) but if…

> simple, small, fast database that competes with fopen() and not Postgres"

The hardware evolved in a way so even very small systems (phones, embedded, Raspberry Pi and other IoT devices) are now multi core. Multithreading is required to benefit from a multi-core CPU. For IO-heavy tasks, it’s sometimes a good idea to implement multi-threaded IO as well.

> you can't write to a file concurrently

I can concurrently write different files. Or to different streams/blobs/records for most other IO methods, except SQLite.

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

#180

Post by jdmichal »

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()?
Post reply on HN