OriginTrail
SQLite database produces a lot of storage writes
Expected behavior
System.db is not a huge database. It should be humble and not eat up to much resources, it's just a small database.
Actual behavior
With default settings SQLite does a lot of storage reads and writes.
Steps to reproduce the problem
I've looked onto my nodes (node process only, excluding ArangoDB) and they write about 310MB per hour on average and read 4GB per hour on average (and it's all about system.db). Which is quite a lot for a database which total size is 1GB-2GB.
I tried to follow this guide (https://phiresky.github.io/blog/2020/sqlite-performance-tuning/), but it seems that only one option available for me is to change journal_mode. All other options seem to stay changed only within one connection, so they can be configured within node code only.
I've tried to set journal_mode to WAL (instead of default DELETE) and the total amount of writes done by system.db dropped about 2x-3x. Which is quite substantial.
So my request is to check on SQLite options as there are simple ways how its resource consumption can be optimized significantly. Like changing journal_mode to WAL (or maybe MEMORY, depends on backup process) or running "PRAGMA optimize". Increasing cache size from default 2MB also could help (PRAGMA cache_size), as well as switching from FULL synchronous to NORMAL.
Disclaimer
Please be aware that the issue reported on a public repository allows everyone to see your node logs, node details, and contact details. If you have any sensitive information, feel free to share it by sending an email to [email protected].
Also running things like vacuum; reindex; pragma optimize; on regular basis can reduce read IOPS workload generated by node. I've just ran them on nodes with system.db 1GB-2GB and read IOPS dropped about 2x.
hey @botnumberseven thanks for the thorough description and research on the subject. We intend to introduce significant improvements on the operational database implementation in the next major release (v6)
We are also going to attempt to tackle this issue with v5 in the direction you suggested. Thanks!
This issue is not relevant because it was for v5 and current version of OT-node is v6.
