5 ms·

SQLite needs some 'put your money where your mouth is' benchmarks. EDIT:Lots of devs have (unfounded) doubts about performance regarding SQLite for web apps, a

by ithrow 4y ago

SQLite needs some 'put your money where your mouth is' benchmarks. EDIT:Lots of devs have (unfounded) doubts about performance regarding SQLite for web apps, a single machine benchmark against postgres (with postgres and the app in the same server) would clear many doubts and create awareness that SQLite is going to be more than enough for many apps.. The app doesn't have to be a web app (we are not testing web servers) but maybe some code with some semi-complex domain models.

kevingadd 4y ago
As someone who had to replace SQLite with a custom key value store in an app for throughput (needed to be able to write structured data at 4GB/s from multiple threads) I assure you that the claims about it being fast are not false - it wasn't fast enough for me but it's very fast
- randito 4y ago
  4GB/s is a pretty high requirement. What did you end up using? Redis looks like it would struggle with this requirement also without some serious hardware, tweaks, or configuration. https://redis.io/docs/reference/optimization/benchmarks/ https://redis.io/docs/reference/optimization/benchmarks/
  - kevingadd 4y ago
    The app was a C# frontend (https://github.com/kg/HeapProfiler https://github.com/kg/HeapProfiler) that drove windows OS services to take heap snapshots and capture stack traces, so I ended up writing a custom key/value store in C# to avoid having to do cross-language interop, marshaling, etc (the cost of sending blobs to SQLite and running queries was adding up.). It's hard to beat the best-in-class optimized databases on their own turf but if you can just grab a spot to dump your data into, you end up being a lot faster. By the end it ran fast enough that it was able to saturate the kernel's paging infrastructure and make my mouse cursor stutter, and I was able to take 1-2 snapshots per second of a full running Firefox process with real webpages in it, so it was satisfactory. SQLite couldn't process the amount of data I was pumping in at that rate (but it still performed pretty well - maybe a few snapshots per minute) At the time I did investigate other data stores and the only good candidates I ran across used incompatible open source licenses, so I was stuck doing it myself. Fun excuse to learn how to write and optimize btrees for throughput :-)
    - liuliu 4y ago
      Yeah, most databases probably will have pathological behaviors against your requirements (especially on tail-latency, which you would care about). Many implement similar tools would put a lightweight compression on top and just dump these snapshots to disk and then run a post-processing for queries. Dumping snapshots is also preferred because you can insert checksums and checkpoints for partial data recovery if there are failures.
- ithrow 4y ago
  Yeah but in every thread about SQLite many mention the fear of having to migrate to postgres as they reach some quantity of concurrent users (web apps), a benchmark would calm these fears for some. (edited my original comment). Most won't go through the trouble to see if SQLite is good enough, they'll just go with the safe choice.
marginalia_nu 4y ago
Dunno if it's still the case, but for a long time, it was a license violation to publish benchmarks of Oracle and (IIRC) Microsoft databases.
- queuebert 4y ago
  That's not suspicious at all. /s
- 0x457 4y ago
  Well, luckily we have another two major RDBMS that allow it.
simlevesque 4y ago
Needs for what ? It is the most deployed database ever. https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html
chasil 4y ago
The big benchmark that I know is TPC-C. This benchmark requires concurrent writes in a massive OLTP client-server model. SQLite shines in SQL92 compliance (without grant/revoke or other permission-based aspects), but it was simply not designed to compete in any of the TPC tests. The current holder of the top TPC-C score is OceanBase, which defeated Oracle's previous record with 11g/Solaris/SPARC. https://www.tpc.org/tpcc/results/tpcc_perf_results5.asp?resulttype=all https://www.tpc.org/tpcc/results/tpcc_perf_results5.asp?resu...

by ithrow 4y ago

kevingadd 4y ago

As someone who had to replace SQLite with a custom key value store in an app for throughput (needed to be able to write structured data at 4GB/s from multiple threads) I assure you that the claims about it being fast are not false - it wasn't fast enough for me but it's very fast

randito 4y ago
4GB/s is a pretty high requirement. What did you end up using? Redis looks like it would struggle with this requirement also without some serious hardware, tweaks, or configuration. https://redis.io/docs/reference/optimization/benchmarks/ https://redis.io/docs/reference/optimization/benchmarks/
- kevingadd 4y ago
  The app was a C# frontend (https://github.com/kg/HeapProfiler https://github.com/kg/HeapProfiler) that drove windows OS services to take heap snapshots and capture stack traces, so I ended up writing a custom key/value store in C# to avoid having to do cross-language interop, marshaling, etc (the cost of sending blobs to SQLite and running queries was adding up.). It's hard to beat the best-in-class optimized databases on their own turf but if you can just grab a spot to dump your data into, you end up being a lot faster. By the end it ran fast enough that it was able to saturate the kernel's paging infrastructure and make my mouse cursor stutter, and I was able to take 1-2 snapshots per second of a full running Firefox process with real webpages in it, so it was satisfactory. SQLite couldn't process the amount of data I was pumping in at that rate (but it still performed pretty well - maybe a few snapshots per minute) At the time I did investigate other data stores and the only good candidates I ran across used incompatible open source licenses, so I was stuck doing it myself. Fun excuse to learn how to write and optimize btrees for throughput :-)
  - liuliu 4y ago
    Yeah, most databases probably will have pathological behaviors against your requirements (especially on tail-latency, which you would care about). Many implement similar tools would put a lightweight compression on top and just dump these snapshots to disk and then run a post-processing for queries. Dumping snapshots is also preferred because you can insert checksums and checkpoints for partial data recovery if there are failures.
ithrow 4y ago
Yeah but in every thread about SQLite many mention the fear of having to migrate to postgres as they reach some quantity of concurrent users (web apps), a benchmark would calm these fears for some. (edited my original comment). Most won't go through the trouble to see if SQLite is good enough, they'll just go with the safe choice.

marginalia_nu 4y ago

Dunno if it's still the case, but for a long time, it was a license violation to publish benchmarks of Oracle and (IIRC) Microsoft databases.

queuebert 4y ago
That's not suspicious at all. /s
0x457 4y ago
Well, luckily we have another two major RDBMS that allow it.

simlevesque 4y ago

Needs for what ? It is the most deployed database ever. https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html

chasil 4y ago

The big benchmark that I know is TPC-C. This benchmark requires concurrent writes in a massive OLTP client-server model. SQLite shines in SQL92 compliance (without grant/revoke or other permission-based aspects), but it was simply not designed to compete in any of the TPC tests. The current holder of the top TPC-C score is OceanBase, which defeated Oracle's previous record with 11g/Solaris/SPARC. https://www.tpc.org/tpcc/results/tpcc_perf_results5.asp?resulttype=all https://www.tpc.org/tpcc/results/tpcc_perf_results5.asp?resu...