5 ms·

(genuinely asking) why not SQLite by default?

lxpz 9mo ago
We were not able to get good enough performance compared to LMDB. We will work on this more though, there are probably many ways performance can be increased by reducing load on the KV store.
- skrtskrt 9mo ago
  Could you use something like Fly's Corrosion to shard and distribute the SQLite data? It uses a CRDT reconciliation, which is familiar for Garage.
  - lxpz 9mo ago
    Garage already shards data by itself if you add more nodes, and it is indeed a viable path to increasing throughput.
- tensor 9mo ago
  Keep in mind that write safety comes with performance penalties. You can turn off write protections and many databases will be super fast, but easily corrupt.
- srcreigh 9mo ago
  Did you try WITHOUT ROWID? Your sqlite implementation[1] uses a BLOB primary key. In SQLite, this means each operation requires 2 b-tree traversals: The BLOB->rowid tree and the rowid->data tree. If you use WITHOUT ROWID, you traverse only the BLOB->data tree. Looking up lexicographically similar keys gets a huge performance boost since sqlite can scan a B-Tree node and the data is contiguous. Your current implementation is chasing pointers to random locations in a different b-tree. I'm not sure exactly whether on disk size would get smaller or larger. It probably depends on the key size and value size compared to the 64 bit rowids. This is probably a well studied question you could find the answer to. [1]: https://git.deuxfleurs.fr/Deuxfleurs/garage/src/commit/4efc8bac07df7fd96ea5f3df7052e70f5577c753/src/db/sqlite_adapter.rs https://git.deuxfleurs.fr/Deuxfleurs/garage/src/commit/4efc8...
  - lxpz 9mo ago
    Very interesting, thank you. It would probably make sense for most tables but not all of them because some are holding large CRDT values.
  - asa400 9mo ago
    Other than knowing this about SQLite beforehand, is there any way one could discover that this is happening through tracing?
- rapnie 9mo ago
  I learned that Turso apparently have plans for a rewrite of libsql [0] in Rust, and create a more 'hackable' SQLite alternative altogether. It was apparently discussed in this Developer Voices [1] video, which I haven't yet watched. [0] https://github.com/tursodatabase/libsql https://github.com/tursodatabase/libsql [1] https://www.youtube.com/watch?v=1JHOY0zqNBY https://www.youtube.com/watch?v=1JHOY0zqNBY

lxpz 9mo ago

We were not able to get good enough performance compared to LMDB. We will work on this more though, there are probably many ways performance can be increased by reducing load on the KV store.

skrtskrt 9mo ago
Could you use something like Fly's Corrosion to shard and distribute the SQLite data? It uses a CRDT reconciliation, which is familiar for Garage.
- lxpz 9mo ago
  Garage already shards data by itself if you add more nodes, and it is indeed a viable path to increasing throughput.
tensor 9mo ago
Keep in mind that write safety comes with performance penalties. You can turn off write protections and many databases will be super fast, but easily corrupt.
srcreigh 9mo ago
Did you try WITHOUT ROWID? Your sqlite implementation[1] uses a BLOB primary key. In SQLite, this means each operation requires 2 b-tree traversals: The BLOB->rowid tree and the rowid->data tree. If you use WITHOUT ROWID, you traverse only the BLOB->data tree. Looking up lexicographically similar keys gets a huge performance boost since sqlite can scan a B-Tree node and the data is contiguous. Your current implementation is chasing pointers to random locations in a different b-tree. I'm not sure exactly whether on disk size would get smaller or larger. It probably depends on the key size and value size compared to the 64 bit rowids. This is probably a well studied question you could find the answer to. [1]: https://git.deuxfleurs.fr/Deuxfleurs/garage/src/commit/4efc8bac07df7fd96ea5f3df7052e70f5577c753/src/db/sqlite_adapter.rs https://git.deuxfleurs.fr/Deuxfleurs/garage/src/commit/4efc8...
- lxpz 9mo ago
  Very interesting, thank you. It would probably make sense for most tables but not all of them because some are holding large CRDT values.
- asa400 9mo ago
  Other than knowing this about SQLite beforehand, is there any way one could discover that this is happening through tracing?
rapnie 9mo ago
I learned that Turso apparently have plans for a rewrite of libsql [0] in Rust, and create a more 'hackable' SQLite alternative altogether. It was apparently discussed in this Developer Voices [1] video, which I haven't yet watched. [0] https://github.com/tursodatabase/libsql https://github.com/tursodatabase/libsql [1] https://www.youtube.com/watch?v=1JHOY0zqNBY https://www.youtube.com/watch?v=1JHOY0zqNBY