5 ms·

> 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

by hiatus 3y ago

> 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.

magicalhippo 3y ago
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/automotive-ethernet-the-future-of-in-vehicle-networking https://www.keysight.com/blogs/tech/sim-des/2021/06/10/autom...
- kazinator 3y ago
  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.
  - magicalhippo 3y ago
    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 :)
    - kazinator 3y ago
      > 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.
      - deleted 3y ago
        [deleted]
      - magicalhippo 3y ago
        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.
        kazinator 3y ago
        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.
        magicalhippo 3y ago
        > 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-in-formula-1/ https://www.racecar-engineering.com/articles/how-data-works-...
        3y ago
    - xyx0826 3y ago
      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.
  - deleted 3y ago
    [deleted]
- xyx0826 3y ago
  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.
Cthulhu_ 3y ago
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.
- nirimda 3y ago
  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.
mbreese 3y ago
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.

by hiatus 3y ago

magicalhippo 3y ago

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/automotive-ethernet-the-future-of-in-vehicle-networking https://www.keysight.com/blogs/tech/sim-des/2021/06/10/autom...

kazinator 3y ago
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.
- magicalhippo 3y ago
  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 :)
  - kazinator 3y ago
    > 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.
    - deleted 3y ago
      [deleted]
    - magicalhippo 3y ago
      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.
      - kazinator 3y ago
        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.
        magicalhippo 3y ago
        > 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-in-formula-1/ https://www.racecar-engineering.com/articles/how-data-works-...
        3y ago
  - xyx0826 3y ago
    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.
- deleted 3y ago
  [deleted]
xyx0826 3y ago
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.

Cthulhu_ 3y ago

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.

nirimda 3y ago
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.

mbreese 3y ago

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.