6 ms·

Doesn't pass the sniff test. 19 seconds sounds like the performance of 100,000 separate transactions. I bet a crisp $10 bill they've forgotten to wrap all of th

Doesn't pass the sniff test. 19 seconds sounds like the performance of 100,000 separate transactions. I bet a crisp $10 bill they've forgotten to wrap all of this in a transaction. In SQLite it makes a huge difference. I struggle to think of a way that you could make SQLite take 19 seconds to write and read 100,000 rows otherwise.

vishnumohandas 3y ago
Hey, the writes were batched[1]. What is expensive here is the serialization. [1]: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d7c55982693d498f10df184a06ed5930/lib/databases/sqlite_db.dart#L65-L66 https://github.com/ente-io/edge-db-benchmarks/blob/43273607d... Edit: Updated the permalink
- electroly 3y ago
  This is the smoking gun, unfortunately; I was right. This code is not correct. You're doing a single insert per transaction (which is the same thing you'd get if you hadn't used an explicit transaction at all). What you want is a single transaction with ALL of the inserts in it. Do it again with a single transaction and you'll find it massively improves the performance. This is a common pitfall for beginners. Edit: You've changed the link after I wrote this reply. Did you fix the code? Is the blog post using the original code or this updated code?
  - vishnumohandas 3y ago
    My bad, I linked to the wrong portion of the code. Here's the updated link[1]. We are executing the entire write in a single batch query. From what we observed, and have documented, it is the serialization that is causing the bottleneck, not the write itself. Also, more than writes, it is the latency associated with deserializing the read data into models that pushed us to look at options outside SQLite. [1]: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d7c55982693d498f10df184a06ed5930/lib/databases/sqlite_db.dart#L65-L66 https://github.com/ente-io/edge-db-benchmarks/blob/43273607d...
    - electroly 3y ago
      Why are you comparing DB engines if the bottleneck is the serialization? You're the one doing the serialization! You could store the embedding directly into SQLite columns if you wanted.
      - vishnumohandas 3y ago
        I agree that deserialization is not the responsibility of the database. But as an end consumer, the overall throughput while using SQLite as the DB engine was not sufficient to serve our use case of reading 100k embeddings.
        hinkley 3y ago
        What people are saying is your premise is wrong, and you’re getting defensive. Either of those would not be an interesting problem except that your title is calling out a particular and beloved database as being the culprit. Which feels a lot like defamation. Even if deserialization is the bottleneck, bottlenecks are relative and don’t have to be singular. You can have multiple bottlenecks and being blind to one because of PEBCAK can still skew results and thus conclusions. The people you’re responding to want you to fix your code and run it again. I think that’s reasonable. And maybe deserves a follow up post or a rework of the existing one.
        vishnumohandas 3y ago
        Sorry if I sounded defensive! I have nothing against SQLite, but I unfortunately haven't yet understood how to write and read Lists more efficiently. Once I do, I'll definitely post a follow up :)
        vishnumohandas 3y ago
        Update: https://ente.io/blog/tech/sqlite-objectbox-isar#update https://ente.io/blog/tech/sqlite-objectbox-isar#update TL;DR: Removing Protobuf and directly serializing the list to a blob helped!
        3y ago
  - slingnow 3y ago
    You're asking too many questions. Didn't you read the title? SQLite isn't enough! /s
  - cooljacob204 3y ago
    Just because you could increase the performance by putting everything into a transaction doesn't make it right to benchmark with. Most people won't be inserting large amounts in a single transaction. Ex webserver with many concurrent clients inserting.
- skowalak 3y ago
  You have changed the code snippets in your link. The original link highlighted this snippet: Future<void> insertEmbedding(EmbeddingProto embedding) async { final db = await _database; await db.writeTransaction((tx) async { await tx.execute('INSERT INTO $tableName ($columnEmbedding) values(?)', [embedding.writeToBuffer()]); }); } Now highlighted: Future<void> insertMultipleEmbeddings(List<EmbeddingProto> embeddings) async { final db = await _database; final inputs = embeddings.map((e) => [e.writeToBuffer()]).toList(); await db.executeBatch( 'INSERT INTO $tableName ($columnEmbedding) values(?)', inputs); } Did you post the wrong link the first time and corrected your mistake? Or did you change the snippet because @electroly was right about the code being inefficient?
  - vishnumohandas 3y ago
    The code was always calling the latter[1], I had pasted the incorrect link. [1]: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d7c55982693d498f10df184a06ed5930/lib/main.dart#L31 https://github.com/ente-io/edge-db-benchmarks/blob/43273607d...
    - skowalak 3y ago
      Thank you for pointing that out! It does make more sense, too. :)
- deleted 3y ago
  [deleted]
spacedcowboy 3y ago
Way back in the day, like a decade or more ago when I was working on Aperture, we increased the speed of the database by dropping CoreData and moving to an embedded SQLite with tuning set up for the application. We had a test database of 1,000,000 images, each with large amounts (several hundred) of metadata items - EXIF and the like, all of which could be queried. We had a target time of 2 seconds to get any query back. Most of the time, we hit that mark using the raw SQLite database approach. So this was: - On far older hardware, we still used spinning rust for $deity sake... - Seems like much more data - Seems like much more metadata And it was still very fast (relatively speaking - a couple of seconds is still a long wait-time, and then there was all the UI overhead on top). I think the author might be missing a trick or two when running SQLite, as mentioned in their write-ups, we found it faster than the filesystem as a storage medium...
- traceroute66 3y ago
  > when I was working on Aperture As a long-term Aperture user, I concur with the above sentiments. It was an impressive real-world use of SQLite by Adobe. Hence I agree with the conclusion that if SQLite is "slow" its usually a PBCAK error.
  - jitl 3y ago
    Aperture was Apple’s pro competitor to Lightroom, not made by Adobe.
    - traceroute66 3y ago
      > Aperture was Apple’s pro competitor to Lightroom, not made by Adobe. My bad, apologies. Although in my defence, Lightroom also uses SQLite as backend, although clearly I'm not sure how extensive it is compared to Apple Aperture.
hinkley 3y ago
Full table scans?
charles_f 3y ago
I second it. I'm regularly using SQLite to look and format data across millions of rows, it's much faster than that, but you have to use transactions and optimize queries. It sounds to me like they didn't read the manual and are blaming the tool.
- vishnumohandas 3y ago
  Hello, OP here! The code we used for running the benchmarks is available here[1]. If you have suggestions on how we could speed up the deserialization during reads, please do share. We'd be happy to go back to SQLite. [1]: https://github.com/ente-io/edge-db-benchmarks https://github.com/ente-io/edge-db-benchmarks
  - maskros 3y ago
    The embeddings are just a flat array of floating point numbers. Try typed arrays/buffers to read and write the typed arrays directly as BLOB data. Protobuf and JSON and other serialization schemes are way overkill for something that should be as simple as a memcpy.
    - vishnumohandas 3y ago
      Yes, this[1] did the job, thank you! [1]: https://news.ycombinator.com/item?id=39291411 https://news.ycombinator.com/item?id=39291411
matharmin 3y ago
Author of the sqlite_async package here. There isn't anything obviously wrong with how the lib is used - the writes are properly batched in a single transaction, but the performance is much slower than expected. From a quick look, garbage collection appears to be a big overhead - it could be related to how data is passed between isolates. In this case, it may actually be faster to do writes in smaller batches, since the current approach may use a lot of memory for the batch. I'll have to test further to see whether that helps, or whether there are other optimizations for this case.
- matharmin 3y ago
  From some further testing: 1. With a batch size of 10k, there is a negligible difference in write performance on my machine (with SQLite taking around 10% longer). 2. Reads are still slower, with the majority of the time spent in decoding. There are probably faster ways to convert List<double> into a Uint8List - probably using the typed_data library. I haven't tested this yet. With larger batch sizes, memory usage becomes a problem with the current sqlite_async batch implementation. There can probably be improved in the library, but a workaround is to split the writes into smaller batches. Overall, you should be able to get very similar performance between Isar and SQLite, but there is something to say for Isar providing better performance for the default/obvious way of doing things in this case.

vishnumohandas 3y ago

Hey, the writes were batched[1]. What is expensive here is the serialization. [1]: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d7c55982693d498f10df184a06ed5930/lib/databases/sqlite_db.dart#L65-L66 https://github.com/ente-io/edge-db-benchmarks/blob/43273607d... Edit: Updated the permalink

electroly 3y ago
This is the smoking gun, unfortunately; I was right. This code is not correct. You're doing a single insert per transaction (which is the same thing you'd get if you hadn't used an explicit transaction at all). What you want is a single transaction with ALL of the inserts in it. Do it again with a single transaction and you'll find it massively improves the performance. This is a common pitfall for beginners. Edit: You've changed the link after I wrote this reply. Did you fix the code? Is the blog post using the original code or this updated code?
- vishnumohandas 3y ago
  My bad, I linked to the wrong portion of the code. Here's the updated link[1]. We are executing the entire write in a single batch query. From what we observed, and have documented, it is the serialization that is causing the bottleneck, not the write itself. Also, more than writes, it is the latency associated with deserializing the read data into models that pushed us to look at options outside SQLite. [1]: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d7c55982693d498f10df184a06ed5930/lib/databases/sqlite_db.dart#L65-L66 https://github.com/ente-io/edge-db-benchmarks/blob/43273607d...
  - electroly 3y ago
    Why are you comparing DB engines if the bottleneck is the serialization? You're the one doing the serialization! You could store the embedding directly into SQLite columns if you wanted.
    - vishnumohandas 3y ago
      I agree that deserialization is not the responsibility of the database. But as an end consumer, the overall throughput while using SQLite as the DB engine was not sufficient to serve our use case of reading 100k embeddings.
      - hinkley 3y ago
        What people are saying is your premise is wrong, and you’re getting defensive. Either of those would not be an interesting problem except that your title is calling out a particular and beloved database as being the culprit. Which feels a lot like defamation. Even if deserialization is the bottleneck, bottlenecks are relative and don’t have to be singular. You can have multiple bottlenecks and being blind to one because of PEBCAK can still skew results and thus conclusions. The people you’re responding to want you to fix your code and run it again. I think that’s reasonable. And maybe deserves a follow up post or a rework of the existing one.
        vishnumohandas 3y ago
        Sorry if I sounded defensive! I have nothing against SQLite, but I unfortunately haven't yet understood how to write and read Lists more efficiently. Once I do, I'll definitely post a follow up :)
        vishnumohandas 3y ago
        Update: https://ente.io/blog/tech/sqlite-objectbox-isar#update https://ente.io/blog/tech/sqlite-objectbox-isar#update TL;DR: Removing Protobuf and directly serializing the list to a blob helped!
      - 3y ago
- slingnow 3y ago
  You're asking too many questions. Didn't you read the title? SQLite isn't enough! /s
- cooljacob204 3y ago
  Just because you could increase the performance by putting everything into a transaction doesn't make it right to benchmark with. Most people won't be inserting large amounts in a single transaction. Ex webserver with many concurrent clients inserting.
skowalak 3y ago
You have changed the code snippets in your link. The original link highlighted this snippet: Future<void> insertEmbedding(EmbeddingProto embedding) async { final db = await _database; await db.writeTransaction((tx) async { await tx.execute('INSERT INTO $tableName ($columnEmbedding) values(?)', [embedding.writeToBuffer()]); }); } Now highlighted: Future<void> insertMultipleEmbeddings(List<EmbeddingProto> embeddings) async { final db = await _database; final inputs = embeddings.map((e) => [e.writeToBuffer()]).toList(); await db.executeBatch( 'INSERT INTO $tableName ($columnEmbedding) values(?)', inputs); } Did you post the wrong link the first time and corrected your mistake? Or did you change the snippet because @electroly was right about the code being inefficient?
- vishnumohandas 3y ago
  The code was always calling the latter[1], I had pasted the incorrect link. [1]: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d7c55982693d498f10df184a06ed5930/lib/main.dart#L31 https://github.com/ente-io/edge-db-benchmarks/blob/43273607d...
  - skowalak 3y ago
    Thank you for pointing that out! It does make more sense, too. :)
deleted 3y ago
[deleted]

spacedcowboy 3y ago

Way back in the day, like a decade or more ago when I was working on Aperture, we increased the speed of the database by dropping CoreData and moving to an embedded SQLite with tuning set up for the application. We had a test database of 1,000,000 images, each with large amounts (several hundred) of metadata items - EXIF and the like, all of which could be queried. We had a target time of 2 seconds to get any query back. Most of the time, we hit that mark using the raw SQLite database approach. So this was: - On far older hardware, we still used spinning rust for $deity sake... - Seems like much more data - Seems like much more metadata And it was still very fast (relatively speaking - a couple of seconds is still a long wait-time, and then there was all the UI overhead on top). I think the author might be missing a trick or two when running SQLite, as mentioned in their write-ups, we found it faster than the filesystem as a storage medium...

traceroute66 3y ago
> when I was working on Aperture As a long-term Aperture user, I concur with the above sentiments. It was an impressive real-world use of SQLite by Adobe. Hence I agree with the conclusion that if SQLite is "slow" its usually a PBCAK error.
- jitl 3y ago
  Aperture was Apple’s pro competitor to Lightroom, not made by Adobe.
  - traceroute66 3y ago
    > Aperture was Apple’s pro competitor to Lightroom, not made by Adobe. My bad, apologies. Although in my defence, Lightroom also uses SQLite as backend, although clearly I'm not sure how extensive it is compared to Apple Aperture.

hinkley 3y ago

Full table scans?

charles_f 3y ago

I second it. I'm regularly using SQLite to look and format data across millions of rows, it's much faster than that, but you have to use transactions and optimize queries. It sounds to me like they didn't read the manual and are blaming the tool.

vishnumohandas 3y ago
Hello, OP here! The code we used for running the benchmarks is available here[1]. If you have suggestions on how we could speed up the deserialization during reads, please do share. We'd be happy to go back to SQLite. [1]: https://github.com/ente-io/edge-db-benchmarks https://github.com/ente-io/edge-db-benchmarks
- maskros 3y ago
  The embeddings are just a flat array of floating point numbers. Try typed arrays/buffers to read and write the typed arrays directly as BLOB data. Protobuf and JSON and other serialization schemes are way overkill for something that should be as simple as a memcpy.
  - vishnumohandas 3y ago
    Yes, this[1] did the job, thank you! [1]: https://news.ycombinator.com/item?id=39291411 https://news.ycombinator.com/item?id=39291411

matharmin 3y ago

Author of the sqlite_async package here. There isn't anything obviously wrong with how the lib is used - the writes are properly batched in a single transaction, but the performance is much slower than expected. From a quick look, garbage collection appears to be a big overhead - it could be related to how data is passed between isolates. In this case, it may actually be faster to do writes in smaller batches, since the current approach may use a lot of memory for the batch. I'll have to test further to see whether that helps, or whether there are other optimizations for this case.

matharmin 3y ago
From some further testing: 1. With a batch size of 10k, there is a negligible difference in write performance on my machine (with SQLite taking around 10% longer). 2. Reads are still slower, with the majority of the time spent in decoding. There are probably faster ways to convert List<double> into a Uint8List - probably using the typed_data library. I haven't tested this yet. With larger batch sizes, memory usage becomes a problem with the current sqlite_async batch implementation. There can probably be improved in the library, but a workaround is to split the writes into smaller batches. Overall, you should be able to get very similar performance between Isar and SQLite, but there is something to say for Isar providing better performance for the default/obvious way of doing things in this case.