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

141–150 of 208 posts

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

#141

Post by fnord123 »

icebraining wrote: post #121
rusanu wrote: post #116

Earlier quoted context omitted.

In this case nginx is the 'application'. the requests is still going to be expressed as a SQL query, sent to the PG, parsed, compiled, optimized, executed, then the tabular response formatted as the HTTP response. Many more steps compared to a file-on-disk response. But I second that is an interesting nginx module

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.

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

#142

Post by jstimpfle »

jstimpfle wrote: post #49
paulddraper wrote: post #23

Earlier quoted context omitted.

Prompting the question: why isn't the whole disk just a SQLite database?

You got me wrong. I explained why appending to a CSV is faster than inserting into a "real" database.

Now I'm confused myself: Parent's context was: Why appending to a CSV is faster than opening a file and appending to that. There was no word about a database.

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

#143

Post by wyc »

Quarrelsome wrote: post #83
wyc wrote: post #35

I know that SQLite performs exceptionally on embedded devices including most phones, IoT devices, and more. However, for better or worse, it's a flat file. Does anyone know of a TCP/IP-speaking SQL database that would work well on an embedded device? PostgreSQL/MariaDB seem kinda heavy, and the net couplers for SQLite look pretty unsupported.

and what exactly do you think any database is at the end of the day? It's not magic, it all ends up on the disk as a file. I mean why is a page file called a FILE?

Sure, we can agree that (almost) everything's a file. However, through their related programs and daemons, we can ask files for more useful functionality such as indexes, versioning, and yes, socket communications.

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

#144

Post by jstimpfle »

klodolph wrote: post #74
jstimpfle wrote: post #17

Earlier quoted context omitted.

Opening a file means locating it physically on the disk by following references (usually at least one per directory level). This is a costly operation. Compare to the typical case of adding another row by appending to an already allocated disk block. No seek operation needed.

This analysis is incorrect. SQLite must allocate database pages for rows just like the kernel must allocate filesystem blocks for files. Reading a row in SQLite means traversing a B-tree to find the row. Reading a file from the filesystem means traversing a B-tree to find the file metadata. Very similar. The kernel doesn't need to "seek" any more than SQLite does, the article says that all the hard drives are SSDs wh…

No, my analysis is totally correct.

This was just about opening a file and appending a few bytes vs appending to an open file. Obviously the latter can't be slower (and is typically faster).

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

#145

Post by mpweiher »

wyoung2 wrote: post #128
mpweiher wrote: post #109

Not surprised by the results, very surprised by the explanation given, the overhead of open/close calls. As far as I know (from many measurements and talking to kernel people and researching the mechanism involved), the difference is due to the fact that buffers are shared between all pages of a single file, and not between files. So for reads, the filesystem will do read-ahead of significantly more data than request…

> The same effect will be reproducible with any format storing multiple objects in a single file Given unbounded developer time, any advantage SQLite has here can of course be matched or beaten by custom code. It's just another C library, not magic. The real issue is, how much work will it take you to do that? SQLite is billed as competing with `fopen()`. That's the proper way to compare it: given equal development t…

> how much work will it take you to do that?

Dunno, use a zip library?

> [inconsistencies]

No, SQLite doesn't have the same tools available as filesystems. It is very good, but not quite as good.

> [Pseudo FS mapped onto a single file] That pretty much describes SQLite.

Not really. For one, it doesn't provide a filesystem-like interface.

>> avoid overheads of multiple kernel round trips

> How are you going to avoid kernel round trips when I/O is involved?

Added emphasis.

> If you think user-space filesystems are fast, go try FUSE.

Exactly, because you tend to make multiple round trips. If you can flatten that to just one, you avoid those overheads.

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

#146

Post by TekMol »

wyoung2 wrote: post #130
TekMol wrote: post #89

The reason I still use files rather then SQLite is that I don't know how SQLite handles concurrency. For example I have a PHP app that is used by 10k users a day and it happily handles 100k tmp files in a single directory. On each request, it checks the file age via filemtime() and if new enough, includes the tmp file with a simple include(). (I write PHP arrays into the tmp files). If too old, it recalculates the da…

> I don't know how SQLite handles concurrency. Why remain in ignorance? There are several articles in the SQLite documentation that address concurrency: https://www.sqlite.org/wal.html https://www.sqlite.org/lockingv3.html https://www.sqlite.org/whentouse.html > Somehow I would expect that if I simply replaced it with SQLite, I would run into concurrency problems. SQLite wouldn't really be ACID-compliant if multiple…

    SQLite wouldn't really be ACID-compliant if
    multiple threads were enough to defeat the
    Durability guarantee, would it?
I'm not so much concerned about durability. More about SQLite not responding to "SELECT v FROM t WHERE id=123" with value v but instead with something like "Error: v is currently being written by another process. Try again later" or something.

No idea if that is a realistic scenario. I'm kind of surprised I don't have this kind of problem with my filebased solution. What happens if process A reads from a file while process B writes it? No clue.

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

#147

Post by wyoung2 »

TekMol wrote: post #146
wyoung2 wrote: post #130

Earlier quoted context omitted.

> I don't know how SQLite handles concurrency. Why remain in ignorance? There are several articles in the SQLite documentation that address concurrency: https://www.sqlite.org/wal.html https://www.sqlite.org/lockingv3.html https://www.sqlite.org/whentouse.html > Somehow I would expect that if I simply replaced it with SQLite, I would run into concurrency problems. SQLite wouldn't really be ACID-compliant if multiple…

SQLite wouldn't really be ACID-compliant if multiple threads were enough to defeat the Durability guarantee, would it? I'm not so much concerned about durability. More about SQLite not responding to "SELECT v FROM t WHERE id=123" with value v but instead with something like "Error: v is currently being written by another process. Try again later" or something. No idea if that is a realistic scenario. I'm kind of surp…

> What happens if process A reads from a file while process B writes it?

If process B started first, the writer blocks access to the table being written to, so the reader waits for the writer to complete. There are timeout and retry behaviors, but within those configurable limits, that's what happens.

You can make SQLite behave as you worry about if you set the retries to 0 and timeout to 0, but that's not the default.

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

#148

Post by cat199 »

Walf wrote: post #131
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…

Just don't let anyone mount with noatime, for performance. I found that little gem on a drive where paring down to used content would have been very helpful.

Or, consider your use case for this at least.

Some user-level backups for example, will make the atime useless.

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

#149

Post by troutaway123 »

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…

¿Porque no los dos?

Store the files in a database but expose them via FUSE.

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

#150

Post by cat199 »

icebraining wrote: post #121
rusanu wrote: post #116

Earlier quoted context omitted.

In this case nginx is the 'application'. the requests is still going to be expressed as a SQL query, sent to the PG, parsed, compiled, optimized, executed, then the tabular response formatted as the HTTP response. Many more steps compared to a file-on-disk response. But I second that is an interesting nginx module

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.

Does your database transparently DMA the buffer cache to the NIC after loading the file in the background using read-ahead without any process context switching or upcalls to userland?

That's what I thought.

https://www.freebsd.org/cgi/man.cgi?query=sendfile&sektion=2

http://man7.org/linux/man-pages/man2/sendfile.2.html

Post reply on HN