35% Faster Than The Filesystem (2017)
sqlite.org
35% Faster Than The Filesystem (2017)
1–10 of 166 posts
Re: 35% Faster Than The Filesystem (2017)
#2While an impressive feat on SQLite’s end, I sincerely hope no engineers see this and take it at face value. The filesystem is slower because it is more flexible, and I would imagine that once you have concurrent reads and writes to your thumbnails, a filesystem is to prefer.
Re: 35% Faster Than The Filesystem (2017)
#3> 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 greater than the overhead of using the database. The size reduction arises from the fact that individual files are padded out to the next multiple of the filesystem block size, whereas the blobs are packed more tightly into an SQLite database.
open() and close() are significantly slower[1] on non-Linux OSes so this would be even more drastic on say Windows or macOS.
[1] I once had to debug a library that was lot slower on Mac and Windows and it mostly turned out to be because of doing open/close of files instead of caching the handles - on Linux it was barely showing up on the perf traces and I had no easy way to debug on the other OSes. On a hunch I cached the file handles and the performance difference was mostly gone.
Re: 35% Faster Than The Filesystem (2017)
#4> 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 greater than the overhead of using the database. The size reduction arises from the fact that individ…
Also doesn't make any sense, of course something that use open/close once will be faster than using open/close n time.
Re: 35% Faster Than The Filesystem (2017)
#5While an impressive feat on SQLite’s end, I sincerely hope no engineers see this and take it at face value. The filesystem is slower because it is more flexible, and I would imagine that once you have concurrent reads and writes to your thumbnails, a filesystem is to prefer.
IMO the point of this article is pretty clearly to answer the developer question "my application needs to read and write data, should I use the filesystem or should I use a sqlite db?" and not make some kind of sweeping "sqlite > fs" claim. In this context, face value is fine.
Re: 35% Faster Than The Filesystem (2017)
#6For small- to mid-sized projects, I’ve always realized huge gains in simplicity by haves “Files” tables to store various assets.
It means instances in a web-farm can pull the files down when they initialize easily, it means files are automatically versioned, it provides an obvious place to put the files when they are being uploaded on the Admin panel. It means all the files are getting backed up as part of the database snapshots automatically.
Things I’ve used this for range from static HTML assets, to logos which are used to dynamically brand a page, to files that get attached to transactional emails, etc.
So yes, if you are building a massive scale system holding millions of files, sure, find a purpose-built system to store, distribute, version, and backup your files.
But for many projects I believe the files-in-database approach is dismissed too early for not being “clean” enough. But having a single source (the DB) for everything the app needs to initialize is a massive simplification. You may find it’s well worth the trade-offs. Really the only downside I ever found — at the scale I was operating at — was that it bloats the database backups.
Re: 35% Faster Than The Filesystem (2017)
#7While an impressive feat on SQLite’s end, I sincerely hope no engineers see this and take it at face value. The filesystem is slower because it is more flexible, and I would imagine that once you have concurrent reads and writes to your thumbnails, a filesystem is to prefer.
Theoretically there could exist filesystems that can do well on thumbnails and small files, but in practice this has never been true. Databases are typically better at this and specialized solutions can get you even farther, like over an order of magnitude barrier farther.
Re: 35% Faster Than The Filesystem (2017)
#8> 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 greater than the overhead of using the database. The size reduction arises from the fact that individ…
Also doesn't make any sense, of course something that use open/close once will be faster than using open/close n time.
It gets weird when you throw Linux in the mix - open/close are extremely cheap on Linux, to the point of being free - at least used to be before Spectre and Meltdown and what not. So on Linux doing n more open/closes isn't much different than doing a single pair if n is not humongous and the program is doing other things besides just open close.
Re: 35% Faster Than The Filesystem (2017)
#9For small- to mid-sized projects, I’ve always realized huge gains in simplicity by haves “Files” tables to store various assets. It means instances in a web-farm can pull the files down when they initialize easily, it means files are automatically versioned, it provides an obvious place to put the files when they are being uploaded on the Admin panel. It means all the files are getting backed up as part of the databa…
> was that it bloats the database backups.
Having a deduplicating backup solution might help with that. Simplest way to do that (in terms of ease-of-implementation), would be to keep backups on a ZFS or btrfs filesystem. Note that (AFAIK) with btrfs, deduplication must be triggered manually, so you'd need to run `btrfs filesystem defragment` on the backups to take advantage of it.
Re: 35% Faster Than The Filesystem (2017)
#10In the past I've had huge speedups by moving simple single-table databases that had grown a bit (e.g., time series data) from sqlite to postgres. Insert performance is also quite bad forcing you to write applications with extra caching layers to be able to do a bunch of inserts at once. Sqlite is great for many applications but it's speed is somewhat oversold. I really wish the postgres engine was embeddable into applications easily.