3 ms·

> So let your take-away be this: read/write latency for SQLite is competitive with read/write latency of individual files on disk. Often SQLite is faster. Somet

by dsacco 9y ago

> So let your take-away be this: read/write latency for SQLite is competitive with read/write latency of individual files on disk. Often SQLite is faster. Sometimes SQLite is almost as fast. Either way, this article disproves the common assumption that a relational database must be slower than direct filesystem I/O. This is under the 1.1 Caveats heading, which makes me feel the title is a little misleading (but only in the way most benchmark headings probably are, I guess). Incidentally, can someone more experienced with filesystem and database I/O confirm or contest the assertion here? Specifically, I'm not sure it's fair to generalize these results (even if valid) to categorical relational databases. But this is not a special area of expertise for me.

cbanek 9y ago
For small files, I could see this being true, but not in a way that would scale to large files. For example, instead of a database, you could make a folder (instead of a table) and then a bunch of 4 byte files with ints in them. Doing a table scan would be like cat *. Obviously this uses a lot more open/close/read calls, with more seeks and iops, because those files may or may not be clustered together on disk. If you packed it all into the same file, it would go faster because of the locality and less system calls. You can also be smarter about caching and other things that the file system does well, but probably isn't optimized for the small file use case.
0xbadcafebee 9y ago
You're asking whether a database can read/write records faster than disk? Yes. The opposite is also true. I'm not an expert in either fs or db, but it's obvious that the design and use of one is not the same as the design and use of the other, and given the right circumstances one will do a particular job better than another. An append-only transaction log is probably going to perform better on a spinning disk than random writes. An intelligent app sorting and committing writes in order is probably going to perform better than an fs on a disk with no queue ordering facility. As they said in the post, block-aligned writes in bulk are going to be more efficient than spreading out a bunch of writes not block-aligned. And latency for a db write is going to be lower if you don't close an fd, assuming that closing an fd would always trigger an fsync. But a properly tuned filesystem, with the right filesystem, with the right kernel driver, with the right disk, with the right bus, with the right control plane, etc etc may work fantastically better than an unoptimized database, and a given workload may fit that fs perfectly. It's important to remember that a benchmark is only meaningful to the person who is running the benchmark. If you want it to be meaningful to you, you have to try it yourself (and then presumably blog about it).

by dsacco 9y ago

cbanek 9y ago

For small files, I could see this being true, but not in a way that would scale to large files. For example, instead of a database, you could make a folder (instead of a table) and then a bunch of 4 byte files with ints in them. Doing a table scan would be like cat *. Obviously this uses a lot more open/close/read calls, with more seeks and iops, because those files may or may not be clustered together on disk. If you packed it all into the same file, it would go faster because of the locality and less system calls. You can also be smarter about caching and other things that the file system does well, but probably isn't optimized for the small file use case.

0xbadcafebee 9y ago

You're asking whether a database can read/write records faster than disk? Yes. The opposite is also true. I'm not an expert in either fs or db, but it's obvious that the design and use of one is not the same as the design and use of the other, and given the right circumstances one will do a particular job better than another. An append-only transaction log is probably going to perform better on a spinning disk than random writes. An intelligent app sorting and committing writes in order is probably going to perform better than an fs on a disk with no queue ordering facility. As they said in the post, block-aligned writes in bulk are going to be more efficient than spreading out a bunch of writes not block-aligned. And latency for a db write is going to be lower if you don't close an fd, assuming that closing an fd would always trigger an fsync. But a properly tuned filesystem, with the right filesystem, with the right kernel driver, with the right disk, with the right bus, with the right control plane, etc etc may work fantastically better than an unoptimized database, and a given workload may fit that fs perfectly. It's important to remember that a benchmark is only meaningful to the person who is running the benchmark. If you want it to be meaningful to you, you have to try it yourself (and then presumably blog about it).