3 ms·

I would have appreciated some more details on the benchmark. SQLite has notoriously slow writes in the default journal mode, but proper configuration and WAL/WA

I would have appreciated some more details on the benchmark. SQLite has notoriously slow writes in the default journal mode, but proper configuration and WAL/WAL2 mode[0] should be a starting point for any comparison. It's the first time I've heard of Isar[1] though. I'm always surprised at how many solid-but-underused Apache projects are out there, chugging along. [0] https://phiresky.github.io/blog/2020/sqlite-performance-tuning/ https://phiresky.github.io/blog/2020/sqlite-performance-tuni... [1] https://isar.dev/ https://isar.dev/

tehlike 3y ago
Yeah code snippets for the benchmark would also be useful.
- vishnumohandas 3y ago
  Hello! OP here. Snippets are available @ https://github.com/ente-io/edge-db-benchmarks/blob/main/lib/main.dart https://github.com/ente-io/edge-db-benchmarks/blob/main/lib/...
  - yellow_lead 3y ago
    You don't seem to be using the WAL or any other SQLite optimizations. https://github.com/ente-io/edge-db-benchmarks/blob/main/lib/databases/sqlite_db.dart https://github.com/ente-io/edge-db-benchmarks/blob/main/lib/... Any reason for that?
    - slingnow 3y ago
      My guess is because they are an SQLite beginner, but for engagement purposes on their blog they had to write something that sounds very authoritative, with a title like "SQLite Isn't Enough".
      - tehlike 3y ago
        That's a bit cynical. They may or may not be a newbie, but there is always something for everyone to learn, and saying they did this for engagement is something you don't know.
    - matharmin 3y ago
      The sqlite_async lib uses WAL mode by default.
      - yellow_lead 3y ago
        My bad, that's great. https://flutterawesome.com/high-performance-asynchronous-interface-for-sqlite-on-dart/ https://flutterawesome.com/high-performance-asynchronous-int...
liuliu 3y ago
Agreed. From the look at it, a few things: WAL is not enabled, insertion is not wrapped in transactions. I did a benchmark a while ago and if everything done right it is much faster: https://dflat.io/benchmark/ https://dflat.io/benchmark/ Also, even if it is done right, inserting 100,000 of 512 byte objects will be around 50MiB, which will be the point to trigger checkpointing WAL file into the main db, which can further slow down SQLite.
vishnumohandas 3y ago
More than writes, reads is what we struggled with. Within reads the most expensive part was the re-construction of models from the rows returned by SQLite. While deserialization is not the responsibility of the database, the overall throughput with SQLite was too low for it to serve our usecase.
- cogman10 3y ago
  https://github.com/ente-io/edge-db-benchmarks/blob/43273607d7c55982693d498f10df184a06ed5930/lib/databases/sqlite_db.dart#L84 https://github.com/ente-io/edge-db-benchmarks/blob/43273607d... If this is how you are doing reads, this is your problem. Limit/offset reads are slow. You'll get much better results if you do a proper pagination implementation. This article describes the problem and solution that'd give you much better read results. https://use-the-index-luke.com/sql/partial-results/fetch-next-page https://use-the-index-luke.com/sql/partial-results/fetch-nex... (don't forget page 2 https://use-the-index-luke.com/sql/partial-results/window-functions https://use-the-index-luke.com/sql/partial-results/window-fu... )
  - vishnumohandas 3y ago
    Hey yes, thank you for pointing it out! Removing the offsets has unfortunately not sped things up, since it's the step post reading the entries (deserialization) that is the bottleneck[1]. Since SQLite does not support Lists, Protobufs came across as the best way to serialize the data at hand. If there's more native way to solve this problem within SQLite, please let me know! [1]: https://news.ycombinator.com/item?id=39290784 https://news.ycombinator.com/item?id=39290784
chasil 3y ago
WAL mode does introduce a number of important usage limitations. https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf “To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.” “It is not possible to change the page size after entering WAL mode.” “In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.” https://www.sqlite.org/lang_attach.html https://www.sqlite.org/lang_attach.html “SQLite does not guarantee ACID consistency with ATTACH DATABASE in WAL mode. “Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.”
bawolff 3y ago
Isn't WAL more about concurrent performance? I'd be surprised if its all that much faster when there is only a single thread doing anything. Still dont think this is sqlite's fault. Serialization is something outside sqlite and a cost you have to pay no matter what you are using.

tehlike 3y ago

Yeah code snippets for the benchmark would also be useful.

vishnumohandas 3y ago
Hello! OP here. Snippets are available @ https://github.com/ente-io/edge-db-benchmarks/blob/main/lib/main.dart https://github.com/ente-io/edge-db-benchmarks/blob/main/lib/...
- yellow_lead 3y ago
  You don't seem to be using the WAL or any other SQLite optimizations. https://github.com/ente-io/edge-db-benchmarks/blob/main/lib/databases/sqlite_db.dart https://github.com/ente-io/edge-db-benchmarks/blob/main/lib/... Any reason for that?
  - slingnow 3y ago
    My guess is because they are an SQLite beginner, but for engagement purposes on their blog they had to write something that sounds very authoritative, with a title like "SQLite Isn't Enough".
    - tehlike 3y ago
      That's a bit cynical. They may or may not be a newbie, but there is always something for everyone to learn, and saying they did this for engagement is something you don't know.
  - matharmin 3y ago
    The sqlite_async lib uses WAL mode by default.
    - yellow_lead 3y ago
      My bad, that's great. https://flutterawesome.com/high-performance-asynchronous-interface-for-sqlite-on-dart/ https://flutterawesome.com/high-performance-asynchronous-int...

liuliu 3y ago

Agreed. From the look at it, a few things: WAL is not enabled, insertion is not wrapped in transactions. I did a benchmark a while ago and if everything done right it is much faster: https://dflat.io/benchmark/ https://dflat.io/benchmark/ Also, even if it is done right, inserting 100,000 of 512 byte objects will be around 50MiB, which will be the point to trigger checkpointing WAL file into the main db, which can further slow down SQLite.

vishnumohandas 3y ago

More than writes, reads is what we struggled with. Within reads the most expensive part was the re-construction of models from the rows returned by SQLite. While deserialization is not the responsibility of the database, the overall throughput with SQLite was too low for it to serve our usecase.

cogman10 3y ago
https://github.com/ente-io/edge-db-benchmarks/blob/43273607d7c55982693d498f10df184a06ed5930/lib/databases/sqlite_db.dart#L84 https://github.com/ente-io/edge-db-benchmarks/blob/43273607d... If this is how you are doing reads, this is your problem. Limit/offset reads are slow. You'll get much better results if you do a proper pagination implementation. This article describes the problem and solution that'd give you much better read results. https://use-the-index-luke.com/sql/partial-results/fetch-next-page https://use-the-index-luke.com/sql/partial-results/fetch-nex... (don't forget page 2 https://use-the-index-luke.com/sql/partial-results/window-functions https://use-the-index-luke.com/sql/partial-results/window-fu... )
- vishnumohandas 3y ago
  Hey yes, thank you for pointing it out! Removing the offsets has unfortunately not sped things up, since it's the step post reading the entries (deserialization) that is the bottleneck[1]. Since SQLite does not support Lists, Protobufs came across as the best way to serialize the data at hand. If there's more native way to solve this problem within SQLite, please let me know! [1]: https://news.ycombinator.com/item?id=39290784 https://news.ycombinator.com/item?id=39290784

chasil 3y ago

WAL mode does introduce a number of important usage limitations. https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf “To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.” “It is not possible to change the page size after entering WAL mode.” “In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.” https://www.sqlite.org/lang_attach.html https://www.sqlite.org/lang_attach.html “SQLite does not guarantee ACID consistency with ATTACH DATABASE in WAL mode. “Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.”

bawolff 3y ago

Isn't WAL more about concurrent performance? I'd be surprised if its all that much faster when there is only a single thread doing anything. Still dont think this is sqlite's fault. Serialization is something outside sqlite and a cost you have to pay no matter what you are using.