News for hackers. Stuff that matters.
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.