Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why SQLite is so great for the edge

blog.turso.tech

Post reply on HN
Print view View original

131–140 of 148 posts

Re: Why SQLite is so great for the edge

#131

Post by xyx0826 »

magicalhippo wrote: post #45
hiatus wrote: post #44

Earlier quoted context omitted.

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

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…

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

#132

Post by xyx0826 »

magicalhippo wrote: post #92
kazinator wrote: post #86

Earlier quoted context omitted.

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.

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 :)

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

#133

Post by kazinator »

magicalhippo wrote: post #126
kazinator wrote: post #125

Earlier quoted context omitted.

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

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

#134

Post by xyx0826 »

sgu999 wrote: post #47
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…

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.

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

#135

Post by mfalcao »

sgt wrote: post #109
remram wrote: post #82

Earlier quoted context omitted.

Slight tangent: Is a UNIX socket faster than TCP over loopback?

Someone should benchmark that. Unix sockets in my mind should be faster as in theory you have less overhead. But the flow using TCP over loopback might have received many more optimizations over the last decades.

Here's a quick benchmark with pgbench:

  $ pgbench -h localhost -p 5432 -b select-only -T 60 -c 10 -j 2 bench
  Password: 
  pgbench (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
  starting vacuum...end.
  transaction type: 
  scaling factor: 1
  query mode: simple
  number of clients: 10
  number of threads: 2
  maximum number of tries: 1
  duration: 60 s
  number of transactions actually processed: 10370147
  number of failed transactions: 0 (0.000%)
  latency average = 0.058 ms
  initial connection time = 54.881 ms
  tps = 172993.008029 (without initial connection time)

  $ pgbench -h /var/run/postgresql -p 5432 -b select-only -T 60 -c 10 -j 2 bench
  pgbench (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
  starting vacuum...end.
  transaction type: 
  scaling factor: 1
  query mode: simple
  number of clients: 10
  number of threads: 2
  maximum number of tries: 1
  duration: 60 s
  number of transactions actually processed: 16890415
  number of failed transactions: 0 (0.000%)
  latency average = 0.036 ms
  initial connection time = 7.186 ms
  tps = 281540.260418 (without initial connection time)
YMMV depending on your workload, but Unix sockets should always be significantly faster.

Re: Why SQLite is so great for the edge

#136

Post by xyx0826 »

Solvency wrote: post #64
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…

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?

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

#137

Post by xyx0826 »

lloydatkinson wrote: post #20
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…

Sounds cool! Any project details anywhere?

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

#138

Post by Solvency »

xyx0826 wrote: post #136
Solvency wrote: post #64

Earlier quoted context omitted.

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?

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…

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

#139

Post by sgt »

mfalcao wrote: post #135
sgt wrote: post #109

Earlier quoted context omitted.

Someone should benchmark that. Unix sockets in my mind should be faster as in theory you have less overhead. But the flow using TCP over loopback might have received many more optimizations over the last decades.

Here's a quick benchmark with pgbench: $ pgbench -h localhost -p 5432 -b select-only -T 60 -c 10 -j 2 bench Password: pgbench (15.3 (Ubuntu 15.3-1.pgdg22.04+1)) starting vacuum...end. transaction type: scaling factor: 1 query mode: simple number of clients: 10 number of threads: 2 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 10370147 number of failed transactions: 0 (0.000%) la…

Very interesting! Thank you for the effort.

Re: Why SQLite is so great for the edge

#140

Post by regularfry »

endisneigh wrote: post #97
regularfry wrote: post #95

Earlier quoted context omitted.

Who exactly is defining triviality purely in terms of request rate?

I think most. You don't need a database to do 1 request a day, for instance.

Request rate doesn't dictate the need for a database. Next?
Post reply on HN