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

191–200 of 208 posts

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

#191

Post by tetha »

rkeene2 wrote: post #185
tetha wrote: post #91

Earlier quoted context omitted.

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

Color me interested. How do I health-check this in a LB? What kind of caching headers do you give me out of the box?

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

#192

Post by rkeene2 »

tetha wrote: post #191
rkeene2 wrote: post #185

Earlier quoted context omitted.

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…

Color me interested. How do I health-check this in a LB? What kind of caching headers do you give me out of the box?

You can check its health by making a request to a known resource -- they're practically free.

It delivers an ETag header that it computes the first time it opens a file and maintains as long as that file is open. If it has to re-open the file it will generate a new ETag value.

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

#193

Post by hulahoof »

cm2187 wrote: post #183
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,…

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).

The benefit is with accessing single files and not requiring a 1,000,000 directory listing lookup

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

#194

Post by fnord123 »

the8472 wrote: post #175
fnord123 wrote: post #141

Earlier quoted context omitted.

>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.

Are we talking about the POSIX interface to files or is there a new transactional API that allows for atomic writes (all pages are written to disk or none are), consistency, isolation (opening a file in another process which is currently being written won't show the pages that are being written), and makes the writes always durable (sync; not fsync)?

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

#195

Post by nullnilvoid »

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…

After years of development, databases and file systems borrow ideas from each other. I am not surprised that the breakeven range might be expanded. It would be interesting to re-visit the conclusions with current databases, file systems, hardware (SSD vs HDD) etc.

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

#196

Post by the8472 »

fnord123 wrote: post #194
the8472 wrote: post #175

Earlier quoted context omitted.

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.

Are we talking about the POSIX interface to files or is there a new transactional API that allows for atomic writes (all pages are written to disk or none are), consistency, isolation (opening a file in another process which is currently being written won't show the pages that are being written), and makes the writes always durable (sync; not fsync)?

> Are we talking about the POSIX interface to files

Those give you these atomic ops and optimistic or cooperative locking: https://rcrowley.org/2010/01/06/things-unix-can-do-atomicall...

Multi-file transactions can be built by moving whole directories over symlinks to the previous version. The linux-specific RENAME_EXCHANGE flag can simplify this.

> or is there a new transactional API

CoW filesystems give you snapshots and reflink copies via ioctls. Under heavy concurrency this can provide cheaper isolation than locking.

> and makes the writes always durable (sync; not fsync)?

For durability fsync is sufficient if you do it on the file and the directory. To combine atomic and durable you can do the write, fsync, rename, fsync dir dance.

https://lwn.net/Articles/457667/

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

#197

Post by YZF »

wyoung2 wrote: post #135
YZF wrote: post #38

What about mixed delete/write/read workloads? How well does SQLite deal with fragmentation?

> What about mixed delete/write/read workloads? The source code to the test program is part of the SQLite source tree. It should be fairly easy to modify it to test the scheme you have in mind. > How well does SQLite deal with fragmentation? SQLite operates a lot like a modern filesystem: tree-based indexing structures, writes go to empty spaces, deletes leave holes that may later be filled by inserts, etc. SQLite ha…

Sounds good. I might give it a test in our product to replace some file based storage scheme.

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

#198

Post by bedros »

jdmichal wrote: post #188
bedros wrote: post #186

Earlier quoted context omitted.

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

sqlite works in memory most of the time, on disk sometime when flushing, faster

filesystem, works on disk most of time, in memory some time (some buffering), slower

which makes my original comment true,

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

#199

Post by [deleted] »

bedros wrote: post #198
jdmichal wrote: post #188

Earlier quoted context omitted.

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

sqlite works in memory most of the time, on disk sometime when flushing, faster filesystem, works on disk most of time, in memory some time (some buffering), slower which makes my original comment true,

[deleted]

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

#200

Post by coldtea »

cat199 wrote: post #150
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.

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

Does your filesystem provide for its files that are referenced by your DB the same versioning, consistent backups, and ACID guarantees as the DB does?

That's what I thought.

Post reply on HN