[HELP] SQLite storage terribly slow for IoT-Gateway. What are my options?

[DataScienceIsCool]

Hi, I have already described my problem once in another question (https://github.com/thingsboard/thingsboard/issues/6880), sadly with no response. Also posting here in the gateway project makes more sense.

Now I have recreated a much simpler situation with the same problem: SQLite is a bottleneck for IoT-Gateay. In the setup guide there are 3 storage types available: Memory, File and SQLite. For testing I use a script, which publishes a small payload 10 times per second. Here are the results.

Memory:

File:

SQLite:

Memory and file storage consistently get the 10 datapoints. SQLite however only gets around 40%. And this is with a very small payload {'SN': 'TestMaschine', 'v': str(random.random()), 'testest': '2', 'test': '3', 'TS_MIL': t, 'daten': {'unit': []}}

The problem I have is that I have quite large payloads in reality (around 3000 characters). In this case Memory and File do start to lag behind (while still managing to bring all 10 datapoints/s into TB) That's why I thought the SQLite storage is the best fit for me (espcially because it says "recommended for more speed" in the setup guide). But with this result SQLite and apparently the whole IoT-Gateway is not practical for me.

So what are my alternatives? Do I really need to look into PE and "Integrations"? Would this actually solve my problem? Or is there even a way to make it work with Gateway?

Thanks

Environment

• OS: Ubuntu 20.04
• ThingsBoard: 3.4

[WingedKitten]

I have been wondering why use SQLite, equipment more access to the data will be lost after, may be the same problem. When I use memory and file data is normal

[samson0v]

Hi @MarkusCropZone, if you have a high payload system, I recommend you to use memory storage but use the version from the master branch because it has some performance fixes. If it doesn't help, then I think it will be better to use integrations. We will fix the documentation description of storages.

Thanks for your interest in ThingsBoard IoT Gateway!

[DataScienceIsCool]

Hi, thank you for the reply. I switched to Integrations yesterday and the performance seems sufficient (100 Datapoints per second with realistic payload are all received). Though it was a bit annoying to recreate the python Data converter in JS. When using memory I noticed that data will eventually arrive by chunks in TB which is why I was sceptical of that option. But maybe with the performance fixes you mention it would be worth a shot.

May I ask how Integration handles the storage? With memory (but optimized?) as well? Are there performance benefits Integrations provides which cannot be achieved with Gateway-Infrastructure?

Thank you!

[imbeacon]

Hi @MarkusCropZone ,

Actually, there are a big difference between the gateway and integrations. Integrations designed to get maximal performance, but the gateway designed to get as much as possible customization. Integrations communicate with ThingsBoard using GRPC and starts as a microservice, they uses only file storage for data persistence. The gateway uses MQTT protocol to communicate with ThingsBoard. Integrations are Java-based instead of Python-based gateway, it also slows down the gateway performance. So for highly load projects it is better to use integrations, but it order to get some specific protocols and create some not so highly loaded projects, it is possible to use the gateway. We will increase the gateway performance in the future, because we are continue working with the gateway and trying to improve it.

[samson0v]

Hi @MarkusCropZone, can we close the issue?

[DataScienceIsCool]

Oh yes. The issue can be closed. Thank you for your support. It was very helpful!