Why SQLite is so great for the edge • Hacker News Board

Re: Why SQLite is so great for the edge

#121

Post by [deleted] » Tue, Jun 06, 2023, 6:58 PM UTC

kazinator wrote: post #86

magicalhippo wrote: post #45

Earlier quoted context omitted.

Could be three different processes interacting with different systems. Say one process listening to a CAN bus, another to local Ethernet[1] and a third to some I2C/SPI sensors. If they don't interact otherwise, which they probably wouldn't given they're just logging telemetry, it's more flexible to just have them as separate processes. The I2C/SPI might be a Python script, the others in C/C++ or Rust say, whatever is…

Another question is what is interesting in a speeding electric car in such a way that 4000 events per second must be captured about it.

[deleted]

Re: Why SQLite is so great for the edge

#122

Post by _cenw » Tue, Jun 06, 2023, 7:01 PM UTC

aarpmcgee wrote: post #94

samwillis wrote: post #6

For a tenanted SAAS app SQLite at the edge is a really compelling architecture. You have a single DB per customer/company/group with all their users woking against that. It can operate at the edge, closest to where the majority of the customers users are. SQLite could scale to even quite large customers with this. Another really compelling architecture is a DB per user, with partial/selective sync between the nodes.…

more at https://localfirstweb.dev/

the irony of "join our discord community" really hits pretty hard on that one

Re: Why SQLite is so great for the edge

#123

Post by magicalhippo » Tue, Jun 06, 2023, 7:16 PM UTC

kazinator wrote: post #113

magicalhippo wrote: post #106

Earlier quoted context omitted.

At 250kph and 4kHz sampling rate the car moves 17mm per sample, or about 2/3 of an inch. Doesn't seem unreasonable to want to sample at that rate, especially for tweaking suspension and such.

A sound wave moves 85 mm in that time at Mach 1, around 340 m/s. That doesn't mean we need to sample a sound that has no components above 20 Hz, using 4000 samples per second. The propagation speed can be misleading. All that matters is whether there are oscillations in the suspension that go up to 1800 Hz, not how fast the car is going forward. If there are, those would have to be harmonics. You'd think would be bey…

> All that matters is whether there are oscillations in the suspension that go up to 1800 Hz

Surely you care about more than just oscillations, for example how exactly the suspension compresses during breaking. If you've seen slow-mo shots from high-speed race cars riding a curb, it can be quite violent with lots of movement.

In this article[1] about Formula 1, they state they sample vibration data at 200kHz. This is then filtered to a lower rate for logging, but getting say 5kHz out of 200kHz raw sensor data doesn't seem unreasonable to me.

They also mention they collect about 30MB per lap of sensor data from more than 250 sensor, and laps are typically around 1.5-2 minutes long. If one assumes a 2 minute lap and 250 sensors, that's 1kB/s per sensor on average.

[1]: https://www.racecar-engineering.com/articles/how-data-works-...

Re: Why SQLite is so great for the edge

#124

Post by lanstin » Tue, Jun 06, 2023, 7:19 PM UTC

endisneigh wrote: post #72

Who exactly is using SQLite in production for non-trivial usecases (>10K QPS)?

I use it to provide persistence to an in memory counter server. It is written to in batches of 4096 (total 25k/second). For restarting he server, the server gets a signal, stops listening, flushes the remaining writes to SQLite, and exits. On start up, it reads the SQLite then starts processing accepted reads. It was a couple hundred lines of go and makes the server much better operationally. It is not involved in the request servicing, just this back up persistence.

Re: Why SQLite is so great for the edge

#125

Post by kazinator » Tue, Jun 06, 2023, 8:28 PM UTC

magicalhippo wrote: post #123

kazinator wrote: post #113

Earlier quoted context omitted.

A sound wave moves 85 mm in that time at Mach 1, around 340 m/s. That doesn't mean we need to sample a sound that has no components above 20 Hz, using 4000 samples per second. The propagation speed can be misleading. All that matters is whether there are oscillations in the suspension that go up to 1800 Hz, not how fast the car is going forward. If there are, those would have to be harmonics. You'd think would be bey…

> All that matters is whether there are oscillations in the suspension that go up to 1800 Hz Surely you care about more than just oscillations, for example how exactly the suspension compresses during breaking. If you've seen slow-mo shots from high-speed race cars riding a curb, it can be quite violent with lots of movement. In this article[1] about Formula 1, they state they sample vibration data at 200kHz. This is…

> how exactly the suspension compresses during breaking

The entire graph of suspension vs time should be well below the frequency range. It doesn't matter whether we are looking for frequency domain features or time domain features.

(Like someone said upthread, it's probably 4000 events per second as an aggregate from numerous sensors, mutiplexed into one sqlite. Or maybe even a total across the three sqlites.)

Re: Why SQLite is so great for the edge

#126

Post by magicalhippo » Tue, Jun 06, 2023, 8:55 PM UTC

kazinator wrote: post #125

magicalhippo wrote: post #123

Earlier quoted context omitted.

> All that matters is whether there are oscillations in the suspension that go up to 1800 Hz Surely you care about more than just oscillations, for example how exactly the suspension compresses during breaking. If you've seen slow-mo shots from high-speed race cars riding a curb, it can be quite violent with lots of movement. In this article[1] about Formula 1, they state they sample vibration data at 200kHz. This is…

> how exactly the suspension compresses during breaking The entire graph of suspension vs time should be well below the frequency range. It doesn't matter whether we are looking for frequency domain features or time domain features. (Like someone said upthread, it's probably 4000 events per second as an aggregate from numerous sensors, mutiplexed into one sqlite. Or maybe even a total across the three sqlites.)

I found this page[1] which mentions dynoing shocks at 3 inches per second, as that what one could see in the pits. If you want some spatial-temporal resolution for that then you'll want a fairly decent sample rate, no?

Maybe not quite 4kSps is needed but for vibration and such it'd make sense to me.

But sure, could very well be an aggregate rate.

[1]: https://www.speedwaymotors.com/the-toolbox/general-sprint-ca...

Re: Why SQLite is so great for the edge

#127

Post by usrbinbash » Tue, Jun 06, 2023, 10:13 PM UTC

runeks wrote: post #114

usrbinbash wrote: post #50

Earlier quoted context omitted.

> communicating via a Unix socket. Which is already IPC, which already makes it slower than having the data never crossing process boundaries. No benchmark required.

I don’t disagree with this. I disagree with whether these microseconds of difference matter when you’re serving a web page with milliseconds of latency.

There are many applications beyond serving webpages, where the difference matters. Collecting and collating analytics data is just one example.

Re: Why SQLite is so great for the edge

#128

Post by 5Qn8mNbc2FNCiVV » Tue, Jun 06, 2023, 10:31 PM UTC

CSDude wrote: post #70

jgrahamc wrote: post #29

This no need to compile SQLite into your Cloudflare Worker. We provide it native on our platform as D1. And it gives you replication. https://blog.cloudflare.com/d1-turning-it-up-to-11/ Also, I think the idea of “edge” doesn’t make a ton of sense. What we really need is code and data that move around as needed for the best performance. See: https://blog.cloudflare.com/announcing-workers-smart-placeme... What people c…

For our product, (SQL your cloud & saas resources as a service) we want to consider D1, but for security we would want database per tenant. Would that model work for Cloudflare? 1000 databases for 1000 tenants. Or should we use something else with shared tenancy?

Not quite sure understanding SQL your cloud (using their AWS account?) but from what I guess by the consideration of D1: I'd wager you want something like Neon but self-hosted. I am building a similar product where I give each customer a full-blown Postgres (without individual extensions) and that is the solution I landed on with the most flexibility and security after testing out SQLite vm per user (via Fly.io), SQLite on the edge (Worker + S3, before D1 existed) and Postgres cluster per user (via Stackgres)

The nice part is the decoupling. User not using their DB for 5 minutes? Time to idle them and use that compute for another customer.

And it's also really nice that storage becomes way way way easier. Storage is the biggest hurdle when your database count scales with your user count so you can instead build out the compute system once, fix a few issues along the way and from then on just babysit a single big storage cluster.

And if that becomes too big at some point, replicate it, shard the tenants by region or whatever and you "only" have to manage storage a dozen times or something. It's obviously always annoying, but at least the setup is similar enough that you can park on FTE per Storage cluster and serve enough customers to make it worth it

Re: Why SQLite is so great for the edge

#129

Post by xyx0826 » Wed, Jun 07, 2023, 1:46 AM UTC

kazinator wrote: post #83

xyx0826 wrote: post #19

I extensively used SQLite in a telemetry system for an electric race car. The car has an onboard computer, first a Raspberry Pi then a dual core Arm processor. Onboard code logs ~4000 messages a second into three SQLite databases. After a drive session a script merges the three databases into a single SQLite session log. The session log is decoded on a different computer to ~400 columns of time series data again stor…

> None of them could match the flexibility, ease of use, and throughput of SQLite. It sounds unbelievable that you couldn't append some CSV or binary record to file via an open file descriptor more efficiently than inserting into SQLite. (It's clear that to have the data already in SQLite form saves post-race steps.)

The onboard code processes binary data: CAN bus messages and alike, along with their nonbinary metadata. The first version of the code used CSV because they are so trivial to read and write, but CSV as a text format required me to either hex or base64 the bytes which incurred a non-trivial waste of time and space. For a custom binary record I would need to write custom (de)serializers, which I wasn't willing to do given frequent changes in types of data I need to record during development.

SQLite is the only solution for me so far that provides a structured data store for mixed data types, is power loss safe (important to an embedded system,) and is a high-quality and portable industry standard. Let along the SQL language itself, which has proved to be hugely useful for first-pass post processing given the amount of data that I ingest.

Perhaps there are other technology that will work just as well or better: maybe Protocol Buffers and alike? Yet SQLite currently ticks all my boxes and I'm quite happy with it.

Re: Why SQLite is so great for the edge

#130

Post by xyx0826 » Wed, Jun 07, 2023, 1:54 AM UTC

nunuvit wrote: post #59

brtv wrote: post #42

Earlier quoted context omitted.

Interesting! I'm currently working on a system that writes time series data to raw binary files, but we're considering switching to a different file format for the same reasons. Have you considered any other formats, such as hdf5?

The one potential requirement I'll caution you on is resilience against write failures, in case you're collecting time-series data and can't afford to lose a "session" or spend time messing with recovery options. HDF5 is not made for that. Binary and SQLite are better in that respect. SQLite wins on usability against binary and HDF5. https://cyrille.rossant.net/moving-away-hdf5/

I'm not familiar with HDF5 but agree on resiliency. We are a collegiate racing team and our car's power rails aren't stable and redundant at all times, so power loss failure is something I've kept in mind from day one on my telemetry project. SQLite is generally equipped to handle power losses and write thread crashes:

> An SQLite database is highly resistant to corruption. If an application crash, or an operating-system crash, or even a power failure occurs in the middle of a transaction...

Quite often after a run the entire car is turned off and, on next power up, the databases are left as .db and .db-journal files. The code has no problem processing or even continuing on logging with DBs in this state.