13 ms·
SQLite: 35% Faster Than the Filesystem
- me551ah 2y agoWhy hasn’t someone made sqlitefs yet?
- sspiff 2y agoWould you put sqlite in the kernel? Or using something like FUSE? It seems to me that all the extra indirection from using FUSE would lead to more than a 35% performance hit. Statically linking an sqlite into a kernel module and providing it with filesystem access seems like something non trivial to me.
- k__ 2y agoCould we expect performance gains from Sqlite being in the kernel?
- skissane 2y agoThe idea of embedding SQLite in the kernel, reminds me of IBM OS/400 (the operating system of the IBM AS/400, nowadays known as IBM i). It contains a built-in relational database, although exactly how deeply integrated it is, is not entirely clear, due to lack of details of its inner workings. Putting a relational database in the OS kernel is an interesting violation of standard layering. Obviously has the potential to unleash a lot of issues, but also could possibly enable novel features.
- sedatk 2y agoBecause SQLite not being an FS is apparently the reason why it’s fast: > 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.
- supriyo-biswas 2y agoAdditionally, it may also be that accesses to a single file allows the OS to efficiently retrieve (and IIRC in the case of Windows, predict) the working set allowing the reduction of access times; which is not the case if you open multiple files.
- pjc50 2y agoNot so much working set, as you only have to check the access control once. Windows does a lot of stuff when you open a file, and creating a process is even worse.
- xav0989 2y agoProxmox puts the VM configuration information in a SQLite database and exposes it through a FUSE file system. It even gets replicated across the cluster using their replication algorithm. It’s a bespoke implementation, but it’s a SQLite-backed filesystem.
- euroderf 2y agoThere are at least two for macOS. But they run into trouble nowadays because FUSE wants kernel extensions.
- written-beyond 2y agoI remember reading someone's comments about how instead of databases using their own data serialisation formats for persistence and then optimizing writes and read over that they should just utilize the FS directly and let all of the optimisations built by FS authors be taken advantage of. I wish I could find that comment, because my explanation doesn't do it justice. Very interesting idea, someone's probably going to explain how it's already been tried in some old IBM database a long time ago and failed due to whatever reason. I still think it should be tried with newer technologies though, sounds like a very interesting idea.
- pjc50 2y ago> instead of databases using their own data serialisation formats for persistence and then optimizing writes and read over that they should just utilize the FS directly and let all of the optimisations built by FS authors be taken advantage of. The original article effectively argues the opposite: if your use case matches a database, then that will be way faster. Because the filesystem is both fully general, multi-process and multi-user, it's got to be pessimistic about its concurrency. This is why e.g. games distribute their assets as massive blobs which are effectively filesystems - better, more predictable seek performance. Starting from the Doom WAD onwards. For an example of databases that use the file system, both the mbox and maildir systems for email probably count?
- eknkc 2y agoAs far as I can remember MongoDB did not have any dedicated block caching mechanism in its earlier releases. They basically mmap’ed the database file and argued that OS cache should do its job. Which makes sense but I guess it did not perform as well as any fune tuned caching mechanism.
- sausagefeet 2y agoEarly MongoDB design choices are probably not great to call out for anything other than ignorance. mmap is a very naive view on how to easily work with data but it falls over pretty hard for any situation where ensuring your data doesn't get corrupted is important. MongoDB has come a long way, but its early technical decisions were not based on technical insight.
- ruined 2y agojust point it at your block device
- 01HNNWZ0MV43FF 2y agoI don't think SQLite can run on a block device out of the box, it needs locking primitives and a second file for the journal or WAL plus a shared memory file in WAL mode
- skissane 2y ago> I don't think SQLite can run on a block device out of the box, it needs locking primitives and a second file for the journal or WAL It ships with an example VFS which shows you how to do this: https://www.sqlite.org/src/doc/trunk/src/test_onefile.c https://www.sqlite.org/src/doc/trunk/src/test_onefile.c
- bhawks 2y agoPOSIX interfaces (open, read, write, seek, close, etc) are very challenging to implement in an efficient/reliable way. Using SQLite let's you tailor your data access patterns in a much more rigorous way and side step the POSIX tarpit.
- dmurray 2y agoNo mention of how it performs when you need random access (seek) into files. Perhaps it underperforms the file system at that?
- bhawks 2y agoProbably because you wouldn't seek into a database row? I guess querying by PK has some similarities but it is not as unstructured and random as a seek. Also side effects such as sparse files do not mean much from a database interface standpoint.
- simonw 2y agoSQLite does have low-level C APIs for that: https://www.sqlite.org/c3ref/blob_open.html https://www.sqlite.org/c3ref/blob_open.html https://www.sqlite.org/c3ref/blob_read.html https://www.sqlite.org/c3ref/blob_read.html I've not seen performance numbers for those. Could make for an interesting micro-benchmark.
- shakna 2y agoThere's quite a number of sqlite FUSE implementations around, if you want to head in that direction.
- chipdart 2y ago> Why hasn’t someone made sqlitefs yet? What do you expect the value proposition of something loosely described as a sqlitefs to be? One of the main selling points of SQLite is that you can statically link it into a binary and no one needs to maintain anything between the OS and the client application. I'm talking about things like versioning. What benefit would there be to replace a library with a full blown file system?
- squarefoot 2y agoHere you go:) https://github.com/narumatt/sqlitefs https://github.com/narumatt/sqlitefs And it seems quite interesting: "sqlite-fs allows Linux and MacOS to mount a sqlite database file as a normal filesystem." "If a database file name isn't specified, sqlite-fs use in-memory-db instead of a file. All data will be deleted when the filesystem is closed."
- deleted 2y ago[deleted]
- RaiausderDose 2y agonumbers are from 2017, update would be cool
- lc64 2y agoThat's a very rigorously written article. Let's also note the 4x speed increase on windows 10, once again underlining just how slow windows filesystem calls are, when compared to direct access, and other (kernel, filesystem) combinations.
- wolfi1 2y agomaybe the malware detection program adds to the performance as well
- cjblomqvist 2y agoNTFS is really horrible handling many small files. When compiling/watching node modules (easily 10-100k files), we've seen a 10x size difference internally (same hardware, just different OSes). At some point that meant a compile time difference of 10-30 sec vs 6-10 min. Not fun.
- 01HNNWZ0MV43FF 2y agoMust be why windows 11 added that dev drive feature
- unchar1 2y agoThat may be due to a combination of Malware detection + most unix programs not really written to take advantage of the features NTFS has to offer This is a great talk on the topic https://youtu.be/qbKGw8MQ0i8?si=rh6WJ3DV0jDZLddn https://youtu.be/qbKGw8MQ0i8?si=rh6WJ3DV0jDZLddn
- robertclaus 2y agoI did some research in a database research lab, and we had a lot of colleagues working on OS research. It was always interesting to compare the constraints and assumptions across the two systems. I remember one of the big differences was the scale of individual records we expected to be working with, which in turn affected how memory and disk was managed. Most relational databases are very much optimized for small individual records and eventual consistency, which allows them to cache a lot more in memory. On the other hand, performance often drops sharply with the size of your rows.
- The_Colonel 2y ago* for certain operations. Which is a bit d'oh, since being faster for some things is one of the main motivations for a database in the first place.
- leni536 2y 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 I wonder how io_uring compares.
- 01HNNWZ0MV43FF 2y agoYeah but imagine a Beowulf cluster^H^H io_uring of SQLites
- xyzzy123 2y agoIt seems like nobody has suggested putting SQLite databases inside SQLite blobs yet... you could have SQLite all the way down.
- nilsherzig 2y agohttps://news.ycombinator.com/item?id=41085856 https://news.ycombinator.com/item?id=41085856
- account42 2y agoImagine the peformance we could reach. With enough SQLite layers anything would be possible.
- tantalor 2y agoRecordIO would be a good choice for this use case https://mesos.apache.org/documentation/latest/recordio/ https://mesos.apache.org/documentation/latest/recordio/
- _xnmw 2y agoThis is precisely why I'm considering appending to a sqlite DB in WAL2 mode instead of plain text log files. Almost no performance penalty for writes but huge advantages for reading/analysis. No more Grafana needed.
- 01HNNWZ0MV43FF 2y agoIt could probably work. For a peculiar application I even used sqlite to record key frame-only video. (There was a reason) One could flip it around and store logs in a multimedia container, but then you won't have nice indices like with sqlite, just the one big time index
- illusive4080 2y agoI didn’t know what WAL/WAL2 mode was, so I looked it up. For anyone else interested: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
- growse 2y agoCareful, some people will be along any second pointing out your approach limits your ability to use "grep" and "cat" on your log after recovering it to your pdp-11 running in your basement. Also something about the "Unix philosophy" :p Seriously though, I think this is a great idea, and would be interested in how easy it is to write sqlite output adaptors for the various logging libraries out there.
- sreitshamer 2y agoIs there a way to mount the sqlite tables as a filesystem?
- wiseowise 2y ago> some people will be along any second pointing out your approach limits your ability to use "grep" and "cat" on your log And they won’t be wrong.
- bqmjjx0kac 2y ago
- Kalanos 2y agoTLDR; don't do it. I've used SQLite blob fields for storing files extensively. Note that there is a 2GB blob maximum: https://www.sqlite.org/limits.html https://www.sqlite.org/limits.html To read/write blobs, you have to serialize/deserialize your objects to bytes. This process is not only tedious, but also varies for different objects and it's not a first-class citizen in other tools, so serialization kept breaking as my dependencies upgraded. As my app matured, I found that I often wanted hierarchical folder-like functionality. Rather than recreating this mess in db relationships, it was easier to store the path and other folder-level metadata in sqlite so that I could work with it in Python. E.g. `os.listdir(my_folder)`. Also, if you want to interact with other systems/services, then you need files. sqlite can't be read over NFS (e.g. AWS EFS) and by design it has no server for requests. so i found myself caching files to disk for export/import. SQLite has some settings for handling parallel requests from multiple services, but when I experimented with them I always wound up with a locked db due to competing requests. For one reason or another, you will end up with hybrid (blob/file) ways of persisting data.
- knighthack 2y agoThe idea to emulate hierarchical folder-like functionality ala filepaths is quite brilliant - I might try it out.
- thunderbong 2y agoStoring Hierarchical Data in Relational Databases https://medium.com/@rishabhdevmanu/from-trees-to-tables-storing-hierarchical-data-in-relational-databases-a5e5e6e1bd64 https://medium.com/@rishabhdevmanu/from-trees-to-tables-stor...
- stavros 2y agoCan you describe how you stored the paths in sqlite? I'm not entirely getting it.
- Kalanos 2y agojust a string field that points to the file path
- theGeatZhopa 2y agoDepends, depends.. but just of logic: All fs/drive access is managed by the OS. No DB systems have raw access to sectors or direct raw access to files. Having a database file on the disc, offers a "cluster" of successive blocks on the hard drive (if it's not fragmented), resulting in relatively short moving distances of the drive head to seek the necessary sectors. There will still be the same sectors occupied, even after vast insert/write/del operations. Absolutely no change of DB file's position on hard drive. It's not a problem with SSDs, though. So, the following apply: client -> DB -> OS -> Filesystem I think, you already can see the DB part is an extra layer. So, if one wouldn't have this, it would be "faster" in terms of execution time. Always. If it's slower, then you use the not-optimal settings for your use case/filesystem. My father did this once. He took H2 and made it even more faster :) incredible fast on Windows in direct comparison of H2/h2-modificated with same data. So having a DBMS is convenient and made in decisions to serve certain domains and their problems. Having it is convenient, but that doesn't mean it's the most optimized way of doing it.
- ndsipa_pomu 2y ago> No DB systems have raw access to sectors or direct raw access to files. Oracle can use raw disks without having a filesystem on them, though it's more common to use ASM (Automatic Storage Management) which is Oracle's alternative to raw or OS managed disks.
- kalleboo 2y agoMySQL also supported this a million years ago, I'm not sure if it still does
- theGeatZhopa 2y agoOh nooo, I forgot, there are real databases existing in the wild, too ... :) https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ntqrf/raw-partition-overview.html https://docs.oracle.com/en/database/oracle/oracle-database/1... Indeed. Offers 10-12 percent performance writing direct on a block device. But, this block device is still attached to a running system :)
- 2y ago
- throwaway211 2y agoi.e. opening and closing many files from disk is slower than opening and closing one file and using memory. It's important. But understandable.
- throwaway211 2y agoI was looking at self hosted RSS readers recently. The application is single user. I don't expect it to be doing a lot of DB intensive stuff. It surprised me that almost all required PostgreSQL, and most of those that didn't opted for something otherwise complex such as Mongo or MySQL. SQLite, with no dependencies, would have simplified the process no end.
- supriyo-biswas 2y agoLast I checked, FreshRSS[1] can use a SQLite database. [1] https://freshrss.org https://freshrss.org
- alberth 2y agoSlight OT: does this apply to SQLite on OpenBSD? Because with OpenBSD introduction of pinning all syscalls to libc, doesn’t this block SQLite from making syscall direct. https://news.ycombinator.com/item?id=38579913 https://news.ycombinator.com/item?id=38579913
- cedws 2y agoHow much more performance could you get by bypassing the filesystem and writing directly to the block device? Of course, you'd need to effectively implement your own filesystem, but you'd be able to optimise it more for the specific workload.
- miohtama 2y agoOracle, some other databases did this back in a day in 00s by wrong with block devices directly. I am not sure if this is done anymore, because the performance gains were modest compared to the hassle of a custom formatted partition.
- efilife 2y ago> Reading is about an order of magnitude faster than writing not a native speaker, what does it mean?
- deleted 2y ago[deleted]
- begrid 2y agoReading is about 10 times faster than writing
- Null-Set 2y ago(Very) approximately 10 times faster
- lionkor 2y agoAn order of magnitude is for example from 10 to 100, or from 1,000 to 10,000, so typically an increase by 10x or similar.
- MalcolmDwyer 2y agoOrder of magnitude usually refers to a 10x difference. Two orders of magnitude would be 100x difference. (Sometimes the phrase is casually used to just mean "a lot", but here I think they mean 10x).
- deleted 2y ago[deleted]
- deleted 2y ago[deleted]
- jstummbillig 2y agoLet's assume that filesystems are fairly optimized pieces of software. Let's assume that the people building them heard of databases and at some point along the way considered things like the costs of open/close calls. What is SQLite not doing that filesystems are?
- pjc50 2y agoA discussion in the comments of the cost of opening a file on Windows: https://stackoverflow.com/questions/21309852/what-is-the-memory-overhead-of-opening-a-file-on-windows https://stackoverflow.com/questions/21309852/what-is-the-mem... Access control is a big cost. Some AV systems (like everyone's favourite Crowdstrike) also hook every open/close.
- tgtweak 2y agoNo file system attributes or metadata on records which also means no (xattrs/fattrs) being written or updated, no checks to see if it's a physical file or a pipe/symlink, no permission checks, no block size alignment mismatches, single open command. Makes sense when you consider you're throwing out functionality and disregarding general purpose design. If you use a fuse mapping to SQLite, mount that directory and access it, you'd probably be very similar performance (perhaps even slower) and storage use as you'd need to add additional columns in the table to track these attributes. I have no doubt that you could create a custom tuned file system on a dedicated mount with attributes disabled, minimized file table and correct/optimized block size and get very near to this perf. Let's not forget the simplicity of being able to use shell commands (like rsync) to browse and manipulate those files without running the application or an SQL client to debug. Makes sense for developers to use SQLite for this use case though for an appliance-type application or for packaged static assets (this is already commonplace in game development - a cab file is essentially the same concept)
- lolinder 2y ago> If you use a fuse mapping to SQLite, mount that directory and access it Related ongoing discussion, if someone cares to test this: https://news.ycombinator.com/item?id=41085856 https://news.ycombinator.com/item?id=41085856
- pas 2y ago> tuned FS + dedicated mount For example, Ceph uses RocksDB as their metadata DB (and it's recommend to put it) directly on a block device, with the WAL on yet another separate raw device https://docs.ceph.com/en/latest/rados/configuration/bluestore-config-ref/ https://docs.ceph.com/en/latest/rados/configuration/bluestor...
- tgtweak 2y agoMore just this: mke2fs -t ext4 -b 1024 -N 100000 -O ^has_journal,^uninit_bg,^ext_attr,^huge_file,^64bit [/dev/sdx] (smaller block size, 100,000 inode file table entries (tuned to the number of blobs), no journal, no checksumming, no extended file attributes, use smaller integer file offset IDs, 32 bit padded vs 64 bit) Then mount it and run the same test. You could go even further and tune fopen BUFSIZE to be no greater than 12,000 bytes. You can even create this mount on a file inside your existing mount... which is essentially akin to having an sqlite file without needing a client library to read/write to it. Anyway - if the purpose is to speed up reads and save disk space on small blob files, there is little need to ditch the file system and it's many many upsides.
- throwaway984393 2y ago[dead]
- freedmand 2y agoI recently had the idea to record every note coming out of my digital piano in real-time. That way if I come up with a good idea when noodling around I don’t have to hope I can remember it later. I was debating what storage layer to use and decided to try SQLite because of its speed claims — essentially a single table where each row is a MIDI event from the piano (note on, note off, control pedal, velocity, timestamp). No transactions, just raw inserts on every possible event. It so far has worked beautifully: it’s performant AND I can do fun analysis later on, e.g. to see what keys I hit more than others or what my average note velocity is.
- thfuran 2y agoI wouldn't expect performance of pretty much any plausible approach to matter much. The notes just aren't going to be coming very quickly.
- 392 2y agoWhat counts as plausible nowadays may surprise you given your grasp on true performance. Observe these professionals. https://www.primevideotech.com/video-streaming/scaling-up-the-prime-video-audio-video-monitoring-service-and-reducing-costs-by-90 https://www.primevideotech.com/video-streaming/scaling-up-th...
- jazzyjackson 2y agoInsertion times are not a problem but maybe after a few years of playing you will be managing millions of records
- freedmand 2y agoWould you expect SQLite performance to degrade per insert once a table is very large (in a way that, say, an append-only log file wouldn’t)?
- freedmand 2y agoIf you play ten note chords — one for each finger — in quick succession, that can rack up a lot of inserts in short time period (say, medium-worst case, 100Hz, for playing a chord like that five times per second, counting both “on” and “off” events). It’s also worth taking into consideration damper pedal velocity changes. When you go from “off” (velocity 0) to fully “on” and depressed (velocity 127), a lot of intermediate values will get fired off at high frequency. Ultimately though you are right; it’s not enough frequency of information to overload SQLite (or a file system), probably by several orders of magnitude.
- Upvoter33 2y agoWhen something built on top of the filesystem is "faster" than the filesystem, it just means "when you use the filesystem in a less-than-optimal manner, it will be slower than an app that uses it in a sophisticated manner." An interesting point, but perhaps obvious...
- deleted 2y ago[deleted]
- deleted 2y ago[deleted]
- OttoCoddo 2y agoSQLite can be faster than FileSystem for small files. For big files, it can do more than 1 GB/s. On Pack [1], I benchmarked these speeds, and you can go very fast. It can be even 2X faster than tar [2]. In my opinion, SQLite can be faster in big reads and writes too, but the team didn't optimise it as much (like loading the whole content into memory) as maybe it was not the main use of the project. My hope is that we will see even faster speeds in the future. [1] https://pack.ac https://pack.ac [2] https://forum.lazarus.freepascal.org/index.php/topic,66281.msg509173.html#msg509173 https://forum.lazarus.freepascal.org/index.php/topic,66281.m...
- vagab0nd 2y agoReminds me of this talk: https://www.destroyallsoftware.com/talks/the-birth-and-death-of-javascript https://www.destroyallsoftware.com/talks/the-birth-and-death...
- throwaway81523 2y agoDeleting a lot of rows from an sqlite database can be awfully slow, compared with deleting a file.
- aydgn 2y agoThis discussion never gets old. * https://news.ycombinator.com/item?id=14550060 https://news.ycombinator.com/item?id=14550060 7 years ago * https://news.ycombinator.com/item?id=20729930 https://news.ycombinator.com/item?id=20729930 5 years ago * https://news.ycombinator.com/item?id=27137834 https://news.ycombinator.com/item?id=27137834 3 years ago * https://news.ycombinator.com/item?id=27897427 https://news.ycombinator.com/item?id=27897427 3 years ago * https://news.ycombinator.com/item?id=34387407 https://news.ycombinator.com/item?id=34387407 2 years ago