4 ms·
80K is a good figure in my experience. If you think about this in latency terms, you are able to insert a row and be done with the entire ceremony in about 12
by bob1029 4y ago
80K is a good figure in my experience.
If you think about this in latency terms, you are able to insert a row and be done with the entire ceremony in about 12 microseconds. This is serialized throughput too.
I think it is unlikely you would get this kind of performance with a hosted solution across the network. You could cheat with batching & caching, but for a cold, one-record non-query, nothing comes close to the latency of SQLite on a local NVMe device.
- electric_mayhem 4y agoDoes SQLite do direct disk I/o or something? I’m open to being convinced otherwise, but I would expect that inserts/s would vary a bunch by filesystem. But I do agree that local nvme with any file system is absurdly better than what you’re likely to find in typical cloud envs.
- alberth 4y ago> Many programs use fopen(), fread(), and fwrite() to create and manage files of data in home-grown formats. SQLite works particularly well as a replacement for these ad hoc data files. Contrary to intuition, SQLite can be faster than the filesystem for reading and writing content to disk. https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- dundarious 4y agoThat quotation is about a SQLite file db being a better choice than multiple ad-hoc binary files. It’s not saying whether SQLite uses direct IO.
- Scarbutt 4y agoPutting postgres and the app in the same server will shrink the latency and in the end it will win in throughput as your data and QPS increases.
- klabb3 4y agoPostgres over localhost is ~10x slower (as in RTT) than SQLite, I think mainly due to serialization and perhaps context switches? https://youtu.be/XcAYkriuQ1o https://youtu.be/XcAYkriuQ1o If you (more commonly) run on a different host, you’re looking more at 20x-50x and this is assuming you’re in the same region. To even get close to the SQLite level of throughput you’d have to stagger/pipeline your requests over a number of concurrent connections (proportional to the earlier x). You’ll eventually succeed at doing just that, with multiple machines. Congratulations, but now you have to also consider the N+1 problem, even for small Ns. In SQLite, you can afford much more back-and-forth with simple queries.
- jbverschoor 4y agoWhat about postgresql using a file socket
- 10000truths 4y agoThe benchmark in the video seems flawed: * He’s comparing an in-memory SQLite DB against a non-temp table in Postgres. Of course the RTT is lower when you don’t have to interact with the filesystem or flush to disk in the critical path. * He’s connecting to Postgres over a loopback interface, instead of using a UNIX socket. Of course the RTT is higher when requests and responses needlessly traverse the TCP/IP stack.
- ricardobeat 4y agoLoopback interfaces in Linux bypass the network stack (and maybe in windows server as well?).
- withinboredom 4y agoI believe you still have TCP serialization (including packet fragmentation) in a loopback, but not in a unix socket. I could be wrong though.
- klabb3 4y ago
- Maksadbek 4y agoI tried the same test on Hetzner VPS (4VCPU AMD EPYC, 8 GB RAM, 160 GB NVMe SSD), and the result is about 20K inserts per sec. It's not 80K, but still exciting, I would even be happy with 10K. I am sure there is a big room for optimizations, but I am planning to use SQLite3 with Python in a very simple way. Source code of Python test file: https://gist.github.com/maksadbek/2385b002b439e03dc948b055933390a3 https://gist.github.com/maksadbek/2385b002b439e03dc948b05593...