News for hackers. Stuff that matters.
Re:Why SQLite is so great for the edge by xyx0826 on Tuesday June 06, 2023 @04:36AM (#36209896)
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 stored in SQLite. This data is then finally converted into a proprietary format for visual analysis.

Prior to SQLite I tried CSV files and raw binary formats. None of them could match the flexibility, ease of use, and throughput of SQLite. My setup has been in use for several weeks now and has processed numerous rows of data. As the SQLite project outlines on its website:

> SQLite does not compete with client/server databases. SQLite competes with fopen().

Re:Why SQLite is so great for the edge by nitwit005 on Tuesday June 06, 2023 @04:40AM (#36209920)
This article isn't talking about the edge as embedded systems. It means cloudflare workers and the like.
Re:Why SQLite is so great for the edge by lloydatkinson on Tuesday June 06, 2023 @04:40AM (#36209919)
Sounds cool! Any project details anywhere?
Re:Why SQLite is so great for the edge by xyx0826 on Tuesday June 06, 2023 @11:07PM (#36222386)
Sadly my team is pretty light on public tech blogs, but there are plenty of projects like this one! Copying from my comment above:

> The system is for a Formula SAE (FSAE) Electric style race car. FSAE is a collegiate competition, and I'm part of a US Pacific Northwest team. Our car uses a 550 V battery and is 4WD. Most of our electronics and firmware/software are in-house besides motors and inverters. I lead the telemetry project and work with 1-2 other members on the system.

> The system consists of an on-vehicle computer (Raspberry Pi or NXP i.MX) and a ground station (Rockchip RK3588S). We use Python heavily and the real-time dashboard part is done with InfluxDB and Grafana. Both computers and any user devices are on a Wi-Fi network made with a long-range WISP access point.

> Our team is unfortunately very light on public tech blogs and how I wished I can change that... There is plenty of information on FSAE electric cars, vehicle telemetry, and both on the internet and they were a source of inspiration as I designed my own solution.

Re:Why SQLite is so great for the edge by brtv on Tuesday June 06, 2023 @06:37AM (#36210688)
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?
Re:Why SQLite is so great for the edge by nunuvit on Tuesday June 06, 2023 @09:08AM (#36212096)
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/

Re:Why SQLite is so great for the edge by xyx0826 on Tuesday June 06, 2023 @09:54PM (#36221832)
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.

Re:Why SQLite is so great for the edge by nunuvit on Wednesday June 07, 2023 @07:14AM (#36225416)
HDF5 is more for storing and exchanging numerical simulation data. It doesn't have to be resilient to write failures because worst-case scenario you rerun the simulation or try again to copy the data into the file.

SQLite writes are "atomic" transactions. After writing new data, it goes back to the index and registers that new data has been written using a single instruction. That's why interrupting it in the middle of a write doesn't result in partial data or a corrupted index.

Re:Why SQLite is so great for the edge by hiatus on Tuesday June 06, 2023 @06:55AM (#36210832)
> 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.

A bit unrelated, but curious as to why you wrote to three separate databases only later to merge them.

Re:Why SQLite is so great for the edge by magicalhippo on Tuesday June 06, 2023 @07:12AM (#36210947)
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 easier.

[1]: https://www.keysight.com/blogs/tech/sim-des/2021/06/10/autom...

Re:Why SQLite is so great for the edge by kazinator on Tuesday June 06, 2023 @11:42AM (#36214566)
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.
Re:Why SQLite is so great for the edge by magicalhippo on Tuesday June 06, 2023 @12:21PM (#36215139)
Good question. As a non-racer who enjoys watching racing, I'd imagine at least some accelerometers/gyros and load cells if they got them, and possibly Pitot tubes or similar for measuring aerodynamics when testing, to get a detailed understanding on the forces on the car and how it behaves. Motor currents probably up there too, to catch current spikes.

Curious to see how far off I am :)

Re:Why SQLite is so great for the edge by kazinator on Tuesday June 06, 2023 @12:54PM (#36215704)
> detailed understanding on the forces on the car and how it behaves

But at 4000 samples per second? You'd think twenty would be plenty.

NyQuist: 4000 samples per second means we are looking to get information about an up to 1800 Hz signal. A6 on the piano keyboard (excluding harmonics).

Sorry, I mean Nyquist. I was confused by NyQuil, the night time flu medicine.

Re:Why SQLite is so great for the edge by magicalhippo on Tuesday June 06, 2023 @01:32PM (#36216275)
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.

Re:Why SQLite is so great for the edge by kazinator on Tuesday June 06, 2023 @02:21PM (#36216993)
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 beyond the frequency response of the suspension. Suspensions are heavy, bulky components and are heavily dampened (which is a kind of low-pass filter).

If the suspension moves anywhere near 17 mm per sample (250 km/h vertically), that car is in serious trouble.

Re:Why SQLite is so great for the edge by magicalhippo on Tuesday June 06, 2023 @03:16PM (#36217720)
> 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 by kazinator on Tuesday June 06, 2023 @04:28PM (#36218682)
> 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 by magicalhippo on Tuesday June 06, 2023 @04:55PM (#36219000)
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 by kazinator on Tuesday June 06, 2023 @10:44PM (#36222230)
3 inches per second is very slow. I think shocks compress quite a bit faster when you hit a speedbump in a parking lot.

The sample rate you need to reconstruct the motion of shocks is entirely determined by Nyquist, just like sampling audio or any other signal.

You need some 2.2x the highest frequency you want to capture, and make sure you filter out anything above that (if it exists).

If there is nothing above 20 Hz in the suspension's movement, or nothing you're interested in, then you need a 44 Hz sample rate. (More if you implement oversampling, but not 10 times more let alone 100.)

Re:Why SQLite is so great for the edge by lfowles on Tuesday June 06, 2023 @02:25PM (#36217036)
I read it as an aggregate 4000 samples per second -- so across 40 sensors that's only 100sps
Re:Why SQLite is so great for the edge by xyx0826 on Tuesday June 06, 2023 @10:11PM (#36221967)
All that and more! Sensors like shock potentiometers, strain gauges, temperatures from all over the car, IMU readings ([ag][xyz]), and electrical measurements like HV/LV voltage, current, power, energy, as well as controls like motor commands and speeds, controller states, etc.

Lots of folks put work into a car and they all have metrics of interest to tell how their systems perform. An ideal telemetry system for us captures all this information and makes it available both in real time and analysis.

Re:Why SQLite is so great for the edge by xyx0826 on Tuesday June 06, 2023 @10:01PM (#36221896)
Precisely so! There are two CAN buses and one UART port to log from, and the Python code spawns subprocesses for each of them. They each create or open a SQLite database named after their bus/port and get to work.

This setup is easy to put together but might not be optimal. I don't have a lot of experience with SQLite performance tuning, and I wonder if it will be faster to have worker threads pass everything thru IPC to a writer thread, which batches rows and writes to a centralized database.

Re:Why SQLite is so great for the edge by Cthulhu_ on Tuesday June 06, 2023 @09:04AM (#36212033)
One of SQLite's weaknesses is it's not client-server, so it's ill-advised to have multiple applications try to use a single sqlite server. Concurrency is another issue.
Re:Why SQLite is so great for the edge by nirimda on Tuesday June 06, 2023 @09:44AM (#36212640)
One of SQLite's strengths is that it's not client-server, so there's no single sqlite server. It is simply not possible to have multiple applications try to use a single sqlite server, because you cannot use what does not exist. No sane person would ever say "it's ill-advised to have multiple ambassadors try to speak to the King of the USA", and likewise no sane person should ever say "it's ill-advised to have multiple applications try to use a single sqlite server".

It is however possible to have multiple applications access the same sqlite database on the same filesystem (i.e. locally, not via network access, not via remounts or symlinks or other forms of synonyms) and it will sort out the locking for you. Depending on what you need to do, there's I think three possible strategies you can use so it can probably handle your concurrency needs faster than Postgres via the network can. For instance, this particular task is write-heavy, and it would benefit from a different setting than a read-heavy application.

There are certainly occasions when Postgres or MySQL or MS SQL or Oracle is a better choice than SQLite, and sometimes concurrency contributes to that decision. But it's unlikely to be the case that you had an SQLite solution that worked really well with one process reading and writing from the database, and now all of a sudden you need a second or third application to use the database concurrently with the first, and you find you need to switch to a client-server model And even if sometimes you need to take specific steps (like adopting a different locking model), you also have to take specific steps to run Postgres (e.g. using a pool to speed up connections and limit the number of concurrent connections because each connection is a separate process). It's a matter of knowing your tools.

Re:Why SQLite is so great for the edge by mbreese on Tuesday June 06, 2023 @09:16AM (#36212190)
I’m going to take a guess that the rationale is file locking. If you have three separate processes using three separate database files, you can write all the data you want, as fast as you can, without worrying about file locking. SQLite can handle three processes writing at the same time, but it can be faster to do it split into separate files and then merge after the fact.
Re:Why SQLite is so great for the edge by sgu999 on Tuesday June 06, 2023 @07:35AM (#36211143)
Other have asked, but I'd also be very interested in knowing more if you can share more details. I had to spent a non trivial amount of time convincing others to use SQLite instead of dumb files in similar situations, stories like yours are pure gold.
Re:Why SQLite is so great for the edge by Lex-2008 on Tuesday June 06, 2023 @10:10AM (#36213057)
Haven't seen these links mentioned elsewhere in this thread, but in case you haven't seen them yet:

* SQLite is 35% Faster Than The Filesystem [1]

* Writing to files safely is hard [2], and SQLite goes to great details to ensure consistency in case of application, OS, or computer crash [3]

[1]: https://sqlite.org/fasterthanfs.html

[2]: https://danluu.com/deconstruct-files/

[3]: https://sqlite.org/atomiccommit.html

Re:Why SQLite is so great for the edge by rongenre on Tuesday June 06, 2023 @01:58PM (#36216667)
I've had a huge amount of success using sqlite to collect and analyze logs. The obvious approach would have been to dump enormous csv or json files, but doing in sqllite gave me (a) much better reliability, and (b) the ability to come up with post-hoc analysis approaches without changing the main process.

I was really pleased to add it to my toolkit.

Re:Why SQLite is so great for the edge by xyx0826 on Tuesday June 06, 2023 @10:46PM (#36222244)
There are a lot of SQLite praises in this thread, on the internet and on SQLite's website so here are a couple of reasons that drove me to choose it. My experience with traditional formats like CSV or raw binary is that they have a somewhat lower overhead to "get working" but are quite difficult to scale and adapt to changing needs, a phase most projects are bound to go through.

SQLite's reputation and adoption makes me comfortable to assume its operational correctness so I can focus on data itself, not reader/writer logics for it. Even though a SQLite database's schema may change, I'm glad knowing that the database file can still be easily opened and read many years into the future, something that custom formats may not guarantee.

On CSV: Syntax and library differences aside, SQLite almost works like a drop-in replacement for CSV since a single table database can be essentially treated as a glorified spreadsheet/CSV if wished. My post processing data pipeline is built with pandas so it is virtually the same for me to read a SQLite database vs. a CSV file.

Re:Why SQLite is so great for the edge by Solvency on Tuesday June 06, 2023 @09:39AM (#36212570)
I'd love to know more about this electric race car, the telemetry system, technology behind it, etc. Do you have a blog (or can you recommend a place) where you do deeper on this subject matter?
Re:Why SQLite is so great for the edge by xyx0826 on Tuesday June 06, 2023 @11:04PM (#36222373)
The system is for a Formula SAE (FSAE) Electric style race car. FSAE is a collegiate competition, and I'm part of a US Pacific Northwest team. Our car uses a 550 V battery and is 4WD. Most of our electronics and firmware/software are in-house besides motors and inverters. I lead the telemetry project and work with 1-2 other members on the system.

The system consists of an on-vehicle computer (Raspberry Pi or NXP i.MX) and a ground station (Rockchip RK3588S). We use Python heavily and the real-time dashboard part is done with InfluxDB and Grafana. Both computers and any user devices are on a Wi-Fi network made with a long-range WISP access point.

Our team is unfortunately very light on public tech blogs and how I wished I can change that... There is plenty of information on FSAE electric cars, vehicle telemetry, and both on the internet and they were a source of inspiration as I designed my own solution.

Re:Why SQLite is so great for the edge by Solvency on Wednesday June 07, 2023 @12:44AM (#36222921)
Awesome. If you dont mind me asking, what sort of dashboarding/visualization software do you use? Highchart homebrewn web apps? Some closed source auto software? What's that world look like? Always been curious about it from a front-end perspective.
Re:Why SQLite is so great for the edge by xyx0826 on Wednesday June 07, 2023 @01:34PM (#36230185)
Real time data is fed into an InfluxDB instance and shown on Grafana hosted on the ground station. This data is downsampled to cope with the volume of raw traffic. We also tried some solutions used by other teams like KX [0] but decided to go with a FOSS stack. A post processing pipeline converts original data to a however proprietary format used by AEMdata [1], a proprietary data analysis tool from our ECU vendor and used somewhat commonly in the racing scene.

[0]: https://code.kx.com/dashboards/

[1]: https://www.aemelectronics.com/products/software-aemnet/aemd...

Re:Why SQLite is so great for the edge by kazinator on Tuesday June 06, 2023 @11:23AM (#36214258)
> 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.)

Re:Why SQLite is so great for the edge by regularfry on Tuesday June 06, 2023 @12:21PM (#36215135)
It's an AND statement, not an OR statement. You can append to a CSV file efficiently, but while flexibility is context-dependent and ease of use is very subjective, we can assume the GP knows their own use case).
Re:Why SQLite is so great for the edge by xyx0826 on Tuesday June 06, 2023 @09:46PM (#36221746)
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.