simpleiot
simpleiot copied to clipboard
simpleiot
investigate switching store to sqlite
It appears there is a CGO free port of sqlite available now for all the architectures we are interested in:
https://pkg.go.dev/modernc.org/sqlite
RiscV is missing but will probably be added at some point, but there is probably no practical need to support RiscV right now until more platforms are available.
Reasons for switching:
- sqlite file format is stable and the author seems committed to keeping it this way. Genji does not claim to be stable, and it is difficult to support updates when the underlying data format is changing.
- sqlite is very high quality and proven
- potentially allow us to do more
- I have not been overly impressed with the performance of Genji/bolt
Questions/concerns:
- sqlite is not a document DB, so how would we store the points in the node entries? One option would be to simply encode them as protobuf or JSON in a TEXT field.
- https://community.esri.com/t5/arcgis-appstudio-blog/working-with-json-in-sqlite-databases/ba-p/895655
- https://sqlite.org/json1.html
- is the C -> Go port of sqlite stable?
@kraj thoughts?
I'm also very impressed with https://litestream.io/ -- it seems this fits in exactly with the values of SIOT.
cc: @bminer
genji has recently switched from bolt to pebble -- this is another example of the churn in the project that probably makes it not the best for SIOT as the the SIOT project matures and finds more use cases.
Yeah I saw the litestream project. Pretty neat! I think SQLite is a great solution if you want RDBMS-like data storage.
I still feel that the future is API / library-based embedded databases (i.e. BoltDB and the like), but we have to build systems that work now.
@bminer as the project develops, I'm realizing the query features (SQL, etc) of the database are not that important because I'm read caching everything anyway right now. Maybe SQLite would be enough faster than Genji that this would no longer be necessary, but the nature of how it works seems like you really need a cache as you are continually walking up/down the graph when points are received to update hashes, etc. I'm boiling my priorities down to 3 things right now:
- stable file format
- stability
- performance
Anything else is just icing on the cake.
It would be interesting to compare the performance of bbolt and sqlite -- that is probably the next step as raw bolt would be fine as well.
my 2cents
read caching is more of short term workaround that many systems are using these days because the databases are so last decade. I think its a bad for system to lock on to resources like this in long run, on high end systems it might work ok where DRAM and persistent storage is converging with optane etc. on embedded systems those technologies are still far fetched.
We are also creating a C program dependency, I think thats not a good thing for future. C programs no matter how solid are inherently insecure. These do have maintenance costs.
Churn in moderation is generally good for projects. I would worry about projects not respecting there API contracts and causing too much trouble for their consumers.
@kraj to be fair, Genji gives plenty of warning -- this is on their README:
To date it has not been a big issue as SIOT has been largely an experiment and its API has been changing a lot too. However, now we have real deployments and more coming, so need to push toward stable.
We are also creating a C program dependency, I think thats not a good thing for future. C programs no matter how solid are inherently insecure. These do have maintenance costs.
I'm not sure if you noticed -- https://pkg.go.dev/modernc.org/sqlite is pure Go as they have a process to translate C into Go, including parts of glibc that are needed. See the dependencies:
https://pkg.go.dev/modernc.org/sqlite?tab=imports
https://pkg.go.dev/modernc.org/libc
I've not dug down to what is actually doing the C -> Go translation.
I feel sqlite is plenty stable due to their testing approach, however, not sure how reliable this C -> Go translation is.
boltdb is probably OK for edge devices, but there have been issues with it:
https://news.ycombinator.com/item?id=30015913
the bbolt fork has supposedly fixed the above, and maintains the project. Probably OK, but I think sqlite gets a completely different level of engineering effort and optimization than bbolt. Bbolt is probably OK though for this project. I'd still like to benchmark to understand if there is a significant performance difference.
Here's my $0.02.
When picking a database you want to consider the following (both in the average case and at worst case like peak load):
- Dataset size (MB)
- Reads per second? Read from RAM or disk? How much compute needed per read?
- Writes per second? How often do we need to write to disk (i.e. durability vs. performance trade-off)? How much compute needed per write? Ratio of reads to writes is quite important.
- Data structures -- Does my data naturally map into the data structures exposed by the database? (i.e. hierarchical data or graphs don't naturally fit into tables and rows)
- How important is data validation?
- How often will data schema change?
Many databases (i.e. RDBMS) employ B+ tree indexes that are optimized for disk reads because disk I/O for read queries is the bottleneck. This is certainly not always the case, and I doubt it's the case for SIOT since the data is all cached in RAM anyway.
If data is really small (under 1 MB), it's probably fast enough to encode / decode everything into a JSON blob, so you can just take a snapshot of RAM every so often (maybe after every write, rate limiting to once per second?), serialize to disk and then fsync.
So, IMO it's really a tough problem to settle on a database!
@cbrake - Regarding the article about BoltDB's freelist... I had been wondering about why the author decided to store the freelist the way he did vs. having bitmap blocks that get allocated when file size grows. Alas, I don't think this is a major problem until you scale to quite large databases. Which reminds me... it's quite fascinating that database persistence layers and file systems are more alike than I first realized.
Thanks for the input -- I think there are two scenarios:
- edge devices -- probably does not matter much what we use -- could even use json like you said, but if were have an embedded DB anyway, no reason not to use it.
- servers/cloud devices where we have 100,000 devices connecting. If each devices requires 100K of storage, that is a total of ~ 1GB -- not a huge amount. Caching would probably need to become more intelligent at this point -- dynamic where stuff is cached as it is read vs everything at once on startup, but could still easily be cached in RAM on a reasonable sized server.
The neat thing is that even for large IoT systems, we can still likely get by with an embedded database, which greatly simplifies ops, and is probably a lot faster.
A lot of applications are moving to sqlite for their datastore. I noticed the latest version of Audacity now uses a single sqlite file for its storage format instead of a directory of files. This is pretty neat as they are storing large blobs of audio files, so sqlite is obviously pretty good at this type of thing.
Might want to consider Ent.
https://github.com/ent/ent
For edge use SQLite For cloud use PostgreSQL Or ise SQLite on both with light stream for Automatic backup - restore and. GlobAl master slave setup . Litestream are designing for this master slave. setup that matches how fly.io does global PostgreSQL.
The other thing about Ent is that it’s designed and modelled as a graph. Importantly you can have recursive nodes . So it’s quite a no s match to the way siot models data.
the other aspect I like with ent is that they are not going away , very responsive team and they have Atlas for auto migration. Have had a very nice experience with ent . The number of projects using Ent is quite large now. The SQLite and pure golang SQLite both work fine with ent too.
And you can use ent SQLite with litestream to gain the HA aspects of litestream.
Also Ent generates an openapi, GRPC and graphql API. I have used the latter 2 and they it’s been easy sailing .
Also there is a cgo free SQLite driver too
https://github.com/glebarez/go-sqlite
Thanks for all the pointers!
Also there is a cgo free SQLite driver too
https://github.com/glebarez/go-sqlite
What is the difference between the above and modernc.org/sqlite? I've used modernc.org/sqlite recently and it seems to work.
Thanks for all the pointers!
Also there is a cgo free SQLite driver too https://github.com/glebarez/go-sqlite
What is the difference between the above and modernc.org/sqlite? I've used modernc.org/sqlite recently and it seems to work.
I don’t know off the top of my head. Probably worth asking the maintainer of that repo as I reckon he will know in details.
https://github.com/glebarez/go-sqlite#L35 Seems to indicate it’s an embedded lib , but ask the maintainer
@gedw99 Here is the response:
modernc.org/sqlite consists of:
- pure go implementation of SQLite (original C codebase is regularly transpiled into Go with great tools from modernc.org)
- implementation for golang database/sql driver interface as described in https://pkg.go.dev/database/sql/driver
glebarez/go-sqlite: is just a fork of modernc/sqlite version with slight changes in the driver part (2), to behave more closely to mattn’s CGo version (https://github.com/mattn/go-sqlite3). this slight changes allowed it to be fully compatible with GORM (the GORM driver is in https://github.com/glebarez/sqlite)
So the only reason I had to fork modernc is to make pure go GORM driver for sqlite.
If you just need a golang’s database/sql driver, just stick to modernc original repo.
BR, Gleb.
making progress on switch to SQLite -- liking it so far. A little more tedious to get all the sql stuff correct. Refraining from using any SQL abstraction libs at this point. Putting points in their own table also opens up some interesting possibilities to put historical points in another table -- when updating a point, if it changes, then put the old point in the history table. I don't see any easy way to put all points in the same table and yet easily extract the current state without a lot of slowdown.
initial performance tests are encouraging:
old
new
I'm not doing any caching in the sqlite version. In the genji version we are caching everything for reads. Not doing hash calculation for sqlite version yet, but that should not add a lot for single root node updates.
making progress with this:
https://github.com/simpleiot/simpleiot/pull/404
More test has been added. Need to get rules and other things working -- perhaps moved to clients, and the upstream functionality working again. I'd really like to rethink the upstream hash mechanism as I feel that is one of the slowest things in the system as you have to read so much data every time a point is written. We could avoid this by not calculating hashes for repetitive points (ones that repeat every x amount of time) so it's not a big deal if you miss one every now and then as another will be coming along soon. But maybe there is something like a position independent, cumulative hash .... :-)
Opens the door for using litestream or litefs to have HA and scaling !!
Works really well with a nats broker in front of it
@gedw99 yes, looking forward to trying litestream. What do you think of embeddeding litestream to make it easy to configure and setup backups?
embedding makes sense because we need 1 LiteStream ( https://github.com/benbjohnson/litestream) for 1 sqlite, so might as well be all in one. Might need a channel to control Litestream in terms of start, stop, signals perhaps, but thats not a biggy.
I am looking into LiteFS ( https://github.com/superfly/litefs ) It is a slightly different beast than Litestream in that LiteFS is using FuseFS. I think the reason is so that they can mount it via Serverless and have more control.
Its terms of scale out, we would need a broker so that reads to go the readonly and write go to the read/write instance.