> 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.
> 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.
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.
A bit unrelated, but curious as to why you wrote to three separate databases only later to merge them.
[1]: https://www.keysight.com/blogs/tech/sim-des/2021/06/10/autom...
Curious to see how far off I am :)
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.
Doesn't seem unreasonable to want to sample at that rate, especially for tweaking suspension and such.
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.
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-...
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.)
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...
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.)
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.
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.
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.
* 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
I was really pleased to add it to my toolkit.
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.
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.
[0]: https://code.kx.com/dashboards/
[1]: https://www.aemelectronics.com/products/software-aemnet/aemd...
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.)
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.
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().