8 ms·
SQLite is 35% Faster Than The Filesystem (2017)
- whateveracct 5y agoLocality strikes again!
- nightfly 5y ago> 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.
- sumtechguy 5y agoOne trick I used to use is to put a small read/write cache in front of file operations. I would usually pick something like the size of a sector or cluster. Windows is pretty bad at small writes, and mearly 'ok' at reads because you usually can just get away with the windows file store cache in that case. I came across the write cache bit accidently when trying to minimize wear on an embedded flash device. It was borderline I thought I had done something wrong. Minimizing open/close and keeping your reads/writes close to sector/cluster size on many filesystems can produce some very nice results. As you can minimize the context switches from user space to kernel. In the case of adding in a 'db' layer packing probably helps as well as slack on small files is huge percentage wise of the total file. So you would be more likely to hit the file cache as well as any in built ones for your stack.
- vincent-toups 5y agoI'd be shocked if this weren't the case.
- bsaul 5y agoI guess you must be a low-level developer then, because to application developer it would seem that sqlite write speed is actually bound by the file system performance (which it depends on).
- harikb 5y agoThe correct comparison is “SQLite index” is faster compared to “file system inode index”
- tantalor 5y agoThe title is a joke, right? It's not actually "faster than the filesystem" unless it's not using the filesystem; it's faster than something else that also uses the filesystem because they use different system calls (context switching).
- naikrovek 5y agoyou can be faster than the filesystem, while using the filesystem, if you emulate something the filesystem does poorly in a more performant way. That's what's happening here. storing files in SQLite removes any per-file overhead in the filesystem. The filesystem now only has one file to deal with, instead of however many are stored inside the SQLite database. This is a very real phenomenon, and definitely not a joke.
- laurent123456 5y ago(2017)
- mirekrusin 5y agoI wondered the other day how things would behave in node if dependencies were in node_modules.sqlite3 (with posibility to eject to edit if needed).
- Already__Taken 5y agoShouldn't it be possible to make an sql structure and mount it like an FS to find out?
- nightfly 5y agoThat would be the worst of both worlds: The overhead of storing your data in SQL, plus the overhead of filesystem search and access. The better way to do this would be to have node access the modules in SQLite directly.
- naikrovek 5y agofilesystem search and access is different when you're talking a single larger file versus multiple smaller files. All filesystems have a per-file overhead that would effectively be eliminated if you could pack all of your files into a database structure. Indices would also speed up access to individual rows of the table significantly. There is overhead in small file storage anyway, if the files are not the exact size, or a multiple, of the sector size. storing 1kb files on a disk where the sector size is 16kb is far more of an overhead expense than storing those files in an SQL database.
- mirekrusin 5y agoI was thinking more about using ESM loader hooks [0] directly. [0] https://nodejs.org/api/esm.html#esm_loaders https://nodejs.org/api/esm.html#esm_loaders
- lukevp 5y agoWell pnpm centralizes it so that you’re only referencing a single location via symlinks and that is a major speed up, I think moving dependencies out of the file system altogether would be nice. I’ve explored the possibility of this, but I think the way snowpack does streaming imports[1] in version 3.0 may be the best solution overall. I haven’t spent much time testing it but it appears to be a major process improvement. [1] https://michalkuncio.com/how-to-use-snowpack-without-node-modules/ https://michalkuncio.com/how-to-use-snowpack-without-node-mo...
- mjevans 5y agoI would love for directories of small files to be stored this way under ZFS; like infrequently updated tar or zip (no compression) files. (Though the filesystem layer of compression might operate on the whole file, or maybe as two streams, one for the file and one for the index.)
- sixothree 5y agoI was under the impression that in NTFS files under 4k are stored in the MFT. I feel like they might have chosen 10k to break this barrier and end up in filespace land. edit: https://en.wikipedia.org/wiki/NTFS#Resident_vs._non-resident_attributes https://en.wikipedia.org/wiki/NTFS#Resident_vs._non-resident...
- simcop2387 5y agoI think this is kind of what reiserfs did back in the day for small files. Keep them in the tree and share pages with lots of small files. It worked rather well, it's just that the file system had plenty of other issues with reliability and recovery at the time. It was vitally impotant that you didn't store any plain text disk images that contained a reiserfs partition on a reiserfs mount, fsck could decide to merge thentwo together causing unknown amounts of corruption.
- laurent123456 5y agoIs it a known issue that the filesystem on Windows 10 is so slow? Being 5 times slower than macOS was roughly my experience but I thought there was just something wrong with my Windows laptop. I can't find any benchmark or explanation about this.
- deleted 5y ago[deleted]
- shawnz 5y agoSee here for some notes from the WSL team why certain filesystem operations that would be fast on Linux are slow on Windows: https://github.com/microsoft/WSL/issues/873#issuecomment-391810696 https://github.com/microsoft/WSL/issues/873#issuecomment-391... https://github.com/microsoft/WSL/issues/873#issuecomment-425272829 https://github.com/microsoft/WSL/issues/873#issuecomment-425...
- nivenhuh 5y agoThanks for the links -- one pro-tip that stood out to me was to use the D: drive (because it's likely to have less filter drivers attached). "Windows's IO stack is extensible, allowing filter drivers to attach to volumes and intercept IO requests before the file system sees them. This is used for numerous things, including virus scanning, compression, encryption, file virtualization, things like OneDrive's files on demand feature, gathering pre-fetching data to speed up app startup, and much more. Even a clean install of Windows will have a number of filters present, particularly on the system volume (so if you have a D: drive or partition, I recommend using that instead, since it likely has fewer filters attached). Filters are involved in many IO operations, most notably creating/opening files."
- hughrr 5y agoI'm going to have to complain about this because in real world use these are far less of a problem than MFT contention on sub-900 byte files generated by typical unix environments. Particularly things that are lockfile heavy and VCSs etc are the most painful. The WSL thing reads like an excuse here. If you actually go and look at what's happening it's small files which are the damage multiplier. I think the real issue is that you shouldn't mix the two operating system paradigms and it's far better to just run Linux in a VM and benefit from the near native performance at the cost of a tiny bit of inconvenience. It's not a bad option when you consider the remote IDE capabilities that VScode gives you, which is the one product they're doing 100% right.
- bahmboo 5y agoEverything is a cache
- sebyx07 5y agothen making a small cdn using nginx and sqlite can be a thing?
- metalliqaz 5y agobut for a CDN, you'd probably have caching. that would eliminate the need to take advantage of this kind of optimization
- xxs 5y agoCDN is effectively read only and served from memory for the most of the part.
- daenz 5y agoSomewhat related, I wrote a fuse-based file system in Rust recently that used SQLite as the backing store for file records, though not the file contents. I imagine I could use it for file content as well, so it's good to know more about its performance. https://amoffat.github.io/supertag/ https://amoffat.github.io/supertag/
- chungy 5y agoSQLite, iirc, has a limit of 1GB per row, and that might too severely limit the utility of your file system if you don't end up splitting files into multiple fragments (rows in the database).
- bombela 5y agoYou read too fast: > SQLite as the backing store for file records, though not the file contents
- imhoguy 5y agoYou are right, by default 1 billion bytes per row or blob. Can be raised up to 2GB with compilation parameter[0]. Storing video streams may be not a good idea, but e.g. photos, thumbnails or some JSON documents may be absolutely fine. [0] https://sqlite.org/limits.html https://sqlite.org/limits.html
- FractalHQ 5y ago
- mpweiher 5y agoHmmm. SQLite is 35% faster reading and writing within a large file than the filesystem is at reading and writing small files. Most filesystems I know are very, very slow at reading and writing small files and much, much faster at reading and writing within large files. For example, for my iOS/macOS performance book[1], I measured the difference writing 1GB of data in files of different sizes, ranging from 100 files of 10MB to 100K files of 10K each. Overall, the times span about an order of magnitude, and even the final step, from individual file sizes of 100KB each to 10KB each was a factor of 3-4 different. [1] https://www.amazon.com/gp/product/0321842847 https://www.amazon.com/gp/product/0321842847
- romwell 5y agoI wrote an ORM to use SQLite for serializing/persisting objects to disk (i.e. using SQLite DB as a file format). This was one of the reasons why SQLite was an easy choice.
- tibbydudeza 5y agoDid Microsoft not try embed SQL server as the backing store for files in Windows "Chicago" to make search a fundamental part of the OS ???.
- chungy 5y agoIt was a Memphis (NT 4.0) goal, canceled. Later, a Longhorn (Vista, "NT 6.0") goal, also canceled. The Windows file system is still as dumb as it was in NT 3.1 -- hardly any changes since then.
- mikestew 5y agoClose, it was Cairo/NT5 for initial incarnation. Then it kept getting pushed back, finally shipped as a beta, then just didn't ship at all: https://en.wikipedia.org/wiki/WinFS https://en.wikipedia.org/wiki/WinFS
- damagednoob 5y agoI believe you're thinking of WinFS[1] which was destined for Windows "Longhorn"[2] [1]: https://en.wikipedia.org/wiki/WinFS https://en.wikipedia.org/wiki/WinFS [2]: https://en.wikipedia.org/wiki/Development_of_Windows_Vista https://en.wikipedia.org/wiki/Development_of_Windows_Vista
- deleted 5y ago[deleted]
- deleted 5y ago[deleted]
- xxs 5y agoNo idea what's with the sqlite articles making the front page every other day. This one is pretty old as well. The measurements in this article were made during the week of 2017-06-05 using a version of SQLite in between 3.19.2 and 3.20.0.
- blunte 5y agoEither it's a concerted effort by some number of people to promote it, or it's that most people didn't see the original news and now find it interesting.
- kissgyorgy 5y agoI think it's just simply a lot of folks on HN (including me) just like SQLite very much and instantly voting up an article about SQLite (even when we already saw it :D).
- yashap 5y agoYeah, IMO there’s certain tech that HN, in aggregate, likes a lot, and readily upvotes positive articles about - SQLite is in that category, along with Postgres, CockroachDB, Go, Rust, etc. There’s also certain tech HN, in aggregate, strongly dislikes, and readily upvotes negative articles - Mongo, anything “modern JS ecosystem”, systemd, etc.
- 7thaccount 5y agoOften for good reason too. SQLite is amazing. Modern JS and frameworks...not so much.
- yashap 5y agoI think tech like a lot of modern JS frameworks, and Mongo, are really good at dev productivity, especially in the earlier days of products. If you're at a very new startup where the company could die any day, and you must ship absolutely as fast as possible to keep the company alive, that can be a truly essential feature. But then if said startup gains traction and the team/codebase/systems grow a lot, it can easily become hard to maintain, and you probably wish your backend was implemented in, say, Go/Postgres over Node/Mongo. Or that your mobile apps were written in Swift and Kotlin over React Native. And I think a lot of the HN crowd works at "startups becoming big businesses", so this is probably a common headache. But it doesn't necessarily mean the tech is BAD, just that it's good for certain things (like early days productivity), but a pain for others (maintainability as the system scales). SQLite is a bit unique in that it's just a super high quality piece of software, that is arguably the best short AND long term solution for the problem it solves (mostly being an embedded DB). But for software where it's more of a tradeoff around early productivity vs. long term maintainability, I think HN is pretty strongly on the long term maintainability side, and that's more of an opinion/choice than a clearly "correct" answer.
- deleted 5y ago[deleted]
- jamal-kumar 5y agoThis gets posted a lot here and I really wonder if anyone's bothered to try and replicate these results or test them with different sizes of data on different filesystems - You can see that the greatest disparity is with NTFS/windows while linux is pretty close in performance, but they don't bother to mention if they've got ubuntu formatted for ext4 or whatever. I can't really seem to find anything looking around, and this article is like 5 years old now. I remember showing this very article to a supervisor at one point and he scoffed it off as unnecessary overhead when all I needed was a plain file store and no relational queries at all. It looks like this is the benchmarking code, I'll have to go over it for curiosity sometime: https://www.sqlite.org/src/file/test/kvtest.c https://www.sqlite.org/src/file/test/kvtest.c
- zmj 5y agoI tried this recently (~2 years ago). SQLite is faster, but returning unused space to the OS is a pain. If you don't need that to be prompt it's a good solution.
- smoldesu 5y agoI'd like to see them bench SQLite against different filesystems, since both fields have progressed quite a bit in the past 4 years.
- 71a54xd 5y agoIt's surprising how fast you can get DETS (the persistent storage version of Elixir's in memory kv-store ETS) to act as a database of sorts. Even on a relatively slow SSD.
- dang 5y agoPast related threads: 35% Faster Than The Filesystem (2017) - https://news.ycombinator.com/item?id=20729930 https://news.ycombinator.com/item?id=20729930 - Aug 2019 (164 comments) SQLite small blob storage: 35% Faster Than the Filesystem - https://news.ycombinator.com/item?id=14550060 https://news.ycombinator.com/item?id=14550060 - June 2017 (202 comments)
- deleted 5y ago[deleted]
- xen2xen1 5y agoSo the answer is that NTFS needs a Raid controller with cache RAM? That seems to fix a lot of stuff.
- Seb-C 5y agoAs much as I love sqlite, if I understand correctly, this is telling that sqlite doing fread on an already opened file is 35% faster than not-sqlite doing fopen+fread on a single file. Is this just a blatantly dishonest comparison, or did I miss something?
- peter_d_sherman 5y agoThere's a great Software Engineering question here, and that is, Should SQLite be modified such that its code, when compiled with a specific #define compilation flag set, be able to act as a drop-in filesystem replacement source code -- for an OS? ? I wonder how hard it would be to retool SQLite/Linux -- to be able to accomplish that -- and what would be learned (like, what kind of API would exist between Linux/other OS and SQLite) -- if that were attempted? Yes, there would be some interesting problems to solve, such as how would SQLite write to its database file -- if there is no filesystem/filesystem API underneath it? Also -- one other feature I'd like -- and that is the ability to do strong logging of up to everything that the filesystem does (if the appropriate compilation switches and/or runtime flags are set) -- but somewhere else on the filesystem! So maybe two instances of SQLite running at the same time, one acting as the filesystem and the other acting as the second, logging filesystem -- for that first filesystem...
- Seattle3503 5y agoSQLite also performs well when there is a large number of "files". A simulation I wrote used a large number of files (10k+). Eventually I had to transition away from using the file system as a key-value store because open, reads, writes, and directory listings were slow when you have that many files in a single directory and that many open file descriptors.