search

eigenein / my-iot-rs

Yet another home automation (alpha)
https://eigenein.github.io/my-iot-rs/html
MIT License
32 stars 5 forks source link

Upserts are too slow #97

Closed eigenein closed 4 years ago

eigenein commented 4 years ago

It spends up to 250ms per single message, that's very much. Real-time services such as YouLess suffer from that.

https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite

Jun 24 18:42:09 raspberrypi my-iot[9847]: 2020-06-24 18:42:09.338 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::production_low = Energy(3602822.4)
Jun 24 18:42:09 raspberrypi my-iot[9847]: 2020-06-24 18:42:09.575 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::production_high = Energy(7996503.600000001)
Jun 24 18:42:09 raspberrypi my-iot[9847]: 2020-06-24 18:42:09.686 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::consumption_gas = Volume(3568.798)
Jun 24 18:42:09 raspberrypi my-iot[9847]: 2020-06-24 18:42:09.883 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::nett = Energy(12924604.8)
Jun 24 18:42:10 raspberrypi my-iot[9847]: 2020-06-24 18:42:10.037 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::power = Power(-756.0)
Jun 24 18:42:10 raspberrypi my-iot[9847]: 2020-06-24 18:42:10.120 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::consumption_low = Energy(13883403.600000001)
Jun 24 18:42:10 raspberrypi my-iot[9847]: 2020-06-24 18:42:10.269 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::consumption_high = Energy(10640527.200000001)
Jun 24 18:42:10 raspberrypi my-iot[9847]: 2020-06-24 18:42:10.310 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::production_low = Energy(3602822.4)
Jun 24 18:42:10 raspberrypi my-iot[9847]: 2020-06-24 18:42:10.361 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::production_high = Energy(7996503.600000001)
Jun 24 18:42:10 raspberrypi my-iot[9847]: 2020-06-24 18:42:10.517 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::consumption_gas = Volume(3568.798)
Jun 24 18:42:10 raspberrypi my-iot[9847]: 2020-06-24 18:42:10.562 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::nett = Energy(12924601.2)
Jun 24 18:42:10 raspberrypi my-iot[9847]: 2020-06-24 18:42:10.771 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::power = Power(-747.0)
Jun 24 18:42:10 raspberrypi my-iot[9847]: 2020-06-24 18:42:10.979 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::consumption_low = Energy(13883403.600000001)
Jun 24 18:42:11 raspberrypi my-iot[9847]: 2020-06-24 18:42:11.087 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::consumption_high = Energy(10640527.200000001)
Jun 24 18:42:11 raspberrypi my-iot[9847]: 2020-06-24 18:42:11.298 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::production_low = Energy(3602822.4)
Jun 24 18:42:11 raspberrypi my-iot[9847]: 2020-06-24 18:42:11.504 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::production_high = Energy(7996507.2)
eigenein commented 4 years ago 
Jun 24 19:28:12 raspberrypi my-iot[10555]: 2020-06-24 19:28:12.824 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::nett = Energy(12922858.8)
Jun 24 19:28:12 raspberrypi my-iot[10555]: 2020-06-24 19:28:12.896 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::power = Power(-438.0)
Jun 24 19:28:12 raspberrypi my-iot[10555]: 2020-06-24 19:28:12.954 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::consumption_low = Energy(13883403.600000001)
Jun 24 19:28:13 raspberrypi my-iot[10555]: 2020-06-24 19:28:13.021 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::consumption_high = Energy(10640527.200000001)
Jun 24 19:28:13 raspberrypi my-iot[10555]: 2020-06-24 19:28:13.086 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::production_low = Energy(3602822.4)
Jun 24 19:28:13 raspberrypi my-iot[10555]: 2020-06-24 19:28:13.173 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::production_high = Energy(7998249.6)
Jun 24 19:28:13 raspberrypi my-iot[10555]: 2020-06-24 19:28:13.229 [INFO] (5) my_iot::core::persistence::thread: [ReadLogged] youless::consumption_gas = Volume(3568.868)
eigenein commented 4 years ago

Much better:

Jun 26 14:15:01 raspberrypi my-iot[13533]: 2020-06-26 14:15:01.344 [INFO] (6) my_iot::core::persistence::thread: Upserting a bulk of 9 messages…
Jun 26 14:15:01 raspberrypi my-iot[13533]: 2020-06-26 14:15:01.510 [INFO] (6) my_iot::core::persistence::thread: Took 166.4ms.

Jun 26 14:15:02 raspberrypi my-iot[13533]: 2020-06-26 14:15:02.515 [INFO] (6) my_iot::core::persistence::thread: Upserting a bulk of 15 messages…
Jun 26 14:15:02 raspberrypi my-iot[13533]: 2020-06-26 14:15:02.667 [INFO] (6) my_iot::core::persistence::thread: Took 146.9ms.

Jun 26 14:15:36 raspberrypi my-iot[13533]: 2020-06-26 14:15:36.164 [INFO] (6) my_iot::core::persistence::thread: Upserting a bulk of 14 messages…
Jun 26 14:15:36 raspberrypi my-iot[13533]: 2020-06-26 14:15:36.314 [INFO] (6) my_iot::core::persistence::thread: Took 149.8ms.

Jun 26 14:15:38 raspberrypi my-iot[13533]: 2020-06-26 14:15:38.565 [INFO] (6) my_iot::core::persistence::thread: Upserting a bulk of 7 messages…
Jun 26 14:15:38 raspberrypi my-iot[13533]: 2020-06-26 14:15:38.708 [INFO] (6) my_iot::core::persistence::thread: Took 142.8ms.