4 ms·

The code for my most recent case is here: https://github.com/pedrocr/syncer/ https://github.com/pedrocr/syncer/ The db code itself is here: https://github.co

The code for my most recent case is here: https://github.com/pedrocr/syncer/ https://github.com/pedrocr/syncer/ The db code itself is here: https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090f8c81178f736cd80739/src/backingstore/metadatadb.rs https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090... WAL is being used but that's not enough to get a mixed INSERT/SELECT to not completely starve reads without first caching INSERT and batching it. >In the single-node case, I have yet to see SQLite (when optimized) run slower than any other SQL implementation. I do agree that SQLite with 100% defaults will easily be overtaken by Postgres, and that if your use case requires that more than 1 node persist transactional data you should definitely be using Postgres. This is single node with WAL. If there's something I've missed I'd love to see it (or get a PR). But the research I did when I wrote the code seemed to indicate this was a known limitation of sqlite.

bob1029 7y ago
Is there a reason you are managing locking at the application layer on the SQLite connection object? https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090f8c81178f736cd80739/src/backingstore/metadatadb.rs#L108 https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090... This is going to absolutely destroy your throughput. I am not sure about go, but in .Net, the underlying SQLite connection instance is inherently thread-safe and can be shared across execution contexts. If you are unable to share a single SQLite connection instance between multiple logical parallel execution contexts in go, I would recommend just creating a new SQLite connection in each case where you need one (e.g. at the beginning of every method currently performing a lock on connection). The only thing you need to remember with this approach is that the synchronous=normal pragma needs to be set each time you new up a connection. This is an extremely fast operation (doesn't touch disk IIRC), so you shouldn't worry too much about any extra overhead here.
- pedrocr 7y ago
  It's a simplification for the threading implementation. Spawning a new connection per thread would work but then I'd probably have to deal with failed transactions. The bottleneck wasn't there though, INSERT performance will just naturally have that behavior: https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite https://stackoverflow.com/questions/1711631/improve-insert-p... Without batching several INSERTs into a single transaction you can't do very many of them which is why I ended up doing a caching layer and batching them.
  - bob1029 7y ago
    I would recommend giving it a try regardless. This is the exact approach we use today and we are seeing zero issues. I am not sure why you think you will see failed transactions. It is a perfectly legitimate/recommended approach to create a new connection per logical transaction.
    - pedrocr 7y ago
      If the locking is moved to the database concurrent transactions can happen. If those touch the same rows one of them will have to fail to maintain consistency. The bottleneck wasn't there though. Even a single thread doing INSERTs is more than enough to slow down the database. You probably don't have a write-heavy workload.
      - pdimitar 7y ago
        Well, sqlite is not recommended for write-heavy workflows anyway. I have the same impressions as your parent commenter: passing around an sqlite connection handle is thread-safe and always will be faster than application-level locking. If you do however have a write-heavy workflow then it's likely time to replace sqlite.
        pedrocr 7y ago
        >I have the same impressions as your parent commenter: passing around an sqlite connection handle is thread-safe and always will be faster than application-level locking. I'm not debating that. It's just that that's not the current bottleneck so no point in dealing with the extra complexity. The current solution can saturate my SSD with low CPU usage. >If you do however have a write-heavy workflow then it's likely time to replace sqlite. That's definitely true. But there's no other SQL database to embed that I know of. That's why I mentioned wanting postgres as a lib. I know of other options that are non-SQL and will require extra rework. If there's an SQL option I'm all ears.
        pdimitar 7y ago

bob1029 7y ago

Is there a reason you are managing locking at the application layer on the SQLite connection object? https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090f8c81178f736cd80739/src/backingstore/metadatadb.rs#L108 https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090... This is going to absolutely destroy your throughput. I am not sure about go, but in .Net, the underlying SQLite connection instance is inherently thread-safe and can be shared across execution contexts. If you are unable to share a single SQLite connection instance between multiple logical parallel execution contexts in go, I would recommend just creating a new SQLite connection in each case where you need one (e.g. at the beginning of every method currently performing a lock on connection). The only thing you need to remember with this approach is that the synchronous=normal pragma needs to be set each time you new up a connection. This is an extremely fast operation (doesn't touch disk IIRC), so you shouldn't worry too much about any extra overhead here.

pedrocr 7y ago
It's a simplification for the threading implementation. Spawning a new connection per thread would work but then I'd probably have to deal with failed transactions. The bottleneck wasn't there though, INSERT performance will just naturally have that behavior: https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite https://stackoverflow.com/questions/1711631/improve-insert-p... Without batching several INSERTs into a single transaction you can't do very many of them which is why I ended up doing a caching layer and batching them.
- bob1029 7y ago
  I would recommend giving it a try regardless. This is the exact approach we use today and we are seeing zero issues. I am not sure why you think you will see failed transactions. It is a perfectly legitimate/recommended approach to create a new connection per logical transaction.
  - pedrocr 7y ago
    If the locking is moved to the database concurrent transactions can happen. If those touch the same rows one of them will have to fail to maintain consistency. The bottleneck wasn't there though. Even a single thread doing INSERTs is more than enough to slow down the database. You probably don't have a write-heavy workload.
    - pdimitar 7y ago
      Well, sqlite is not recommended for write-heavy workflows anyway. I have the same impressions as your parent commenter: passing around an sqlite connection handle is thread-safe and always will be faster than application-level locking. If you do however have a write-heavy workflow then it's likely time to replace sqlite.
      - pedrocr 7y ago
        >I have the same impressions as your parent commenter: passing around an sqlite connection handle is thread-safe and always will be faster than application-level locking. I'm not debating that. It's just that that's not the current bottleneck so no point in dealing with the extra complexity. The current solution can saturate my SSD with low CPU usage. >If you do however have a write-heavy workflow then it's likely time to replace sqlite. That's definitely true. But there's no other SQL database to embed that I know of. That's why I mentioned wanting postgres as a lib. I know of other options that are non-SQL and will require extra rework. If there's an SQL option I'm all ears.
        pdimitar 7y ago