3 ms·

Does SQLite really start choking? I feel like the issue is on configuration/ code side rather than on the database itself. It should handle thousands of writes

by nicce 2y ago

Does SQLite really start choking? I feel like the issue is on configuration/ code side rather than on the database itself. It should handle thousands of writes per second, even on older Raspberry. For this use-case, you can even likely implement own writer queue for every sensor type, and then batch insert them.

CT4u8798 2y ago
I would like further detail about this also. I suspect that SQLite wasn't choking and it was something else. I cannot imagine a single home being able to overwhelm SQLite [0]. Unless the OP has a couple thousand devices all looking to write to it at the same time and they cannot queue and take turns. [0] https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html
jmmv 2y ago
Came here to question this too. I have trouble with this claim because I do not see how a bunch of sensors could produce so much data as to overwhelm a Raspberry Pi... Maybe the problem was having the database on a super-slow micro-SD card?
- baq 2y ago
  Yeah the microSD will die eventually even if it’s a high endurance one - but it’s fine for a small installation, I’ve been running a quality one until I really started building out the sensor network. With just switches it was fine. Now running on a mid tier usb3 ssd.
- ImaCake 2y ago
  Low power sensors are only being polled every few minutes anyway. I have a dozen temp/humidity sensors and several dozen lights etc and my HA on a Pi 4 has no lag issues from it.
mvip 2y ago
Author here. The problem with SQLite is when you start having a lot of sensors. That's when SQLite failed to keep up on a Pi. Mind you, this was back on Pi 3s. It might be better today, and I know there has been performance improvements to SQLite, but for me moving to Influx for time series and MySQL for the database solved things. Also, even on the best SD cards, you will eventually break them if you write this much.

by nicce 2y ago

CT4u8798 2y ago

I would like further detail about this also. I suspect that SQLite wasn't choking and it was something else. I cannot imagine a single home being able to overwhelm SQLite [0]. Unless the OP has a couple thousand devices all looking to write to it at the same time and they cannot queue and take turns. [0] https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html

jmmv 2y ago

Came here to question this too. I have trouble with this claim because I do not see how a bunch of sensors could produce so much data as to overwhelm a Raspberry Pi... Maybe the problem was having the database on a super-slow micro-SD card?

baq 2y ago
Yeah the microSD will die eventually even if it’s a high endurance one - but it’s fine for a small installation, I’ve been running a quality one until I really started building out the sensor network. With just switches it was fine. Now running on a mid tier usb3 ssd.
ImaCake 2y ago
Low power sensors are only being polled every few minutes anyway. I have a dozen temp/humidity sensors and several dozen lights etc and my HA on a Pi 4 has no lag issues from it.

mvip 2y ago

Author here. The problem with SQLite is when you start having a lot of sensors. That's when SQLite failed to keep up on a Pi. Mind you, this was back on Pi 3s. It might be better today, and I know there has been performance improvements to SQLite, but for me moving to Influx for time series and MySQL for the database solved things. Also, even on the best SD cards, you will eventually break them if you write this much.