5 ms·

It does allow multiple applications to access the same database at the same time, but when you do so it really hurts performance. I noticed this when i wrote a

It does allow multiple applications to access the same database at the same time, but when you do so it really hurts performance. I noticed this when i wrote a web crawler in go and used sqlite as the backend. As soon as i connect using the command line interface, it slows down significantly. Just something to bear in mind if you want to use it with multiple processes!

iampims 7y ago
Even with WAL enabled?
- FpUser 7y ago
  Let's say there are concurrent writes to a single table with few indexes. Process of updating said table and indices eventually boils down to update of a single B+ tree from a multiple threads. In general until serialized it is physically impossible without corrupting the tree. Sure there might be other specially crafted implementation of table more friendly to concurrent writes but there will be trade-offs. There are no miracles in the world. As for particular case with WAL, all it does in this particular scenario is act as a queue. If your database load is spiky then it can even out load and make an impression of faster response. Under constant load it will not speed up the things and will internally serialize all actual updates
  - catalogia 7y ago
    If you need concurrent/parallel writes, SQLite is not the right tool for you. You may as well lament that hammers are no good for driving screws.
    - FpUser 7y ago
      Can you please point me to where did I say anything about SQLite being right tool for concurrent write? My point was that true concurrent writes to a generic database table with indexes are are physically impossible in a general case disregarding what database it is.
    - HelloNurse 7y ago
      Some concurrent writes are "a transaction can wait for others to finish, up to how long it takes for a person to grow bored" and some concurrent writes are "if a transaction cannot finish without waiting the application is already struggling with a severe performance problem" (for example, because it needs multiple concurrent writes on different disks to keep up with incoming data).
- dana321 7y ago
  Yes, using WAL. I tuned all of the settings that i could at the time.
cheez 7y ago
I use it for real time data (around 22G/month, compressed) and I can do all sorts of filthy stuff with the database while the real time processes are running. Increasing the retry timeout can help, using WAL can help. I'm sure you've tried all this though.
GordonS 7y ago
Presumably multiple readers are fine, with only multiple writers being an issue? I vaguely recall trying to use multiple threads to write to an SQLite DB some years ago, and I think it actually locked the entire file for writes. I might remembering wrongly, but I think I switched to reader/writer locks in c# instead, and seeing a huge perf boost.
- lvh 7y ago
  WAL mode means readers do not block writers and a writer does not block readers. If it locked the entire database you likely weren’t using WAL.
  - GordonS 7y ago
    I was definitely using WAL - I know I mentioned using reader/write locks, but that was so the C# locking mechanism worked the way I wanted; I realise that SQLite doesn't block readers on writes. My point, not well made :), was that a lightweight locking mechanism worked much faster than SQLite's file-based locking mechanism. This was on Windows, mind, so things might be very different on Linux.
pdimitar 7y ago
Centralise your writes to be only done through a single thread/process and then you can read from as many threads/processes you like with no noticeable difference in performance (at least for the several hobby dataset importing projects I tried).
- pilif 7y ago
  «just write your own server on top of serverless embedded SQLite to get to acceptable performance without needing a client/server database» (Honestly, once you are at the point where concurrency causes performance issues with SQLite, you are better off moving to databases designed to handle concurrency rather than trying to cobble together your own workaround - you have reached the point where the drawback of SQLite‘s architecture outweigh its advantages and the advantages of other databases architectures outweigh their drawbacks)
  - pdimitar 7y ago
    Sure, I don't disagree. I've ditched sqlite for Postgres many times. It's just that for 99% of the projects I ever worked on, writes are like 100x less than reads so wrapping the writes in a queue of sorts has been quite okay and performant. It can and it has been coming to a point when it's easier to use a full-blown database server, too. I was simply pointing out that for a lot of classic workflows wrapping/centralising writes works quite fine.
  - logicallee 7y ago
    As you quoted, people in this discussion are saying that writing a dummy program that has its own sqlite and does nothing but pass messages to it that it receives from all other processes on the system that need to talk to that sqlite file results in much better performance than accessing that file "directly" from the separate processes. so if everyone's saying this, is there such a standard dummy program?
    - aikah 7y ago
      > As you quoted, people in this discussion are saying that writing a dummy program that has its own sqlite and does nothing but pass messages to it that it receives from all other processes on the system that need to talk to that sqlite file results in much better performance than accessing that file "directly" from the separate processes. And use what protocol for inter-process communication with the daemon managing SQLite? At the end of the day you just created the equivalent of a server anyway...
bob1029 7y ago
Multiple processes is an instant anti-pattern for a single SQLite database. I would stop and reconsider your approach before trying to build this solution using it. The way I see it there are 3 options: 1) Implement another process which will have exclusive ownership of the shared SQLite database, and then use some IPC scheme to delegate database operations from multiple processes. 2) Give each process its own copy of a SQLite db if there is no effective shared state between these processes (I.e. you are just map-reducing web crawler results). Upon completion of each process you could aggregate each into a final combined db. 3) Use a hosted database solution such as Postgres. The bigger question for me would be what are you going to do with this data once you collect it. If you plan on having another series of processes that then use the SQLite db to provide reporting views or execute business logic, I think a hosted solution might be a better option. If scalability is a serious concern, option 2 is probably your best bet.
- vdfs 7y ago
  It's ironic how using a severless database require your app to be the server and do top-level access management to the database
  - bob1029 7y ago
    It may be ironic but there are benefits to rolling your own. Having your persistence layer talk in terms of your business models instead of raw SQL could be seen as a benefit in some contexts. The process which is the "server" can be written to allow for relaxed consistency based on specific business operation being performed (e.g. no need for transactions around log entries). This could be used to leverage substantial performance gains. The benefits afforded by guarantees of exclusivity between application and database are difficult to overstate. These advantages of purpose-built functionality would also extend into the arena of handling replication and clustering. I.e. multiple distributed processes each with independent databases synchronized via some custom protocol that operates in terms of specific business models and processes.
    - chadcmulligan 7y ago

iampims 7y ago

Even with WAL enabled?

FpUser 7y ago
Let's say there are concurrent writes to a single table with few indexes. Process of updating said table and indices eventually boils down to update of a single B+ tree from a multiple threads. In general until serialized it is physically impossible without corrupting the tree. Sure there might be other specially crafted implementation of table more friendly to concurrent writes but there will be trade-offs. There are no miracles in the world. As for particular case with WAL, all it does in this particular scenario is act as a queue. If your database load is spiky then it can even out load and make an impression of faster response. Under constant load it will not speed up the things and will internally serialize all actual updates
- catalogia 7y ago
  If you need concurrent/parallel writes, SQLite is not the right tool for you. You may as well lament that hammers are no good for driving screws.
  - FpUser 7y ago
    Can you please point me to where did I say anything about SQLite being right tool for concurrent write? My point was that true concurrent writes to a generic database table with indexes are are physically impossible in a general case disregarding what database it is.
  - HelloNurse 7y ago
    Some concurrent writes are "a transaction can wait for others to finish, up to how long it takes for a person to grow bored" and some concurrent writes are "if a transaction cannot finish without waiting the application is already struggling with a severe performance problem" (for example, because it needs multiple concurrent writes on different disks to keep up with incoming data).
dana321 7y ago
Yes, using WAL. I tuned all of the settings that i could at the time.

cheez 7y ago

I use it for real time data (around 22G/month, compressed) and I can do all sorts of filthy stuff with the database while the real time processes are running. Increasing the retry timeout can help, using WAL can help. I'm sure you've tried all this though.

GordonS 7y ago

Presumably multiple readers are fine, with only multiple writers being an issue? I vaguely recall trying to use multiple threads to write to an SQLite DB some years ago, and I think it actually locked the entire file for writes. I might remembering wrongly, but I think I switched to reader/writer locks in c# instead, and seeing a huge perf boost.

lvh 7y ago
WAL mode means readers do not block writers and a writer does not block readers. If it locked the entire database you likely weren’t using WAL.
- GordonS 7y ago
  I was definitely using WAL - I know I mentioned using reader/write locks, but that was so the C# locking mechanism worked the way I wanted; I realise that SQLite doesn't block readers on writes. My point, not well made :), was that a lightweight locking mechanism worked much faster than SQLite's file-based locking mechanism. This was on Windows, mind, so things might be very different on Linux.

pdimitar 7y ago

Centralise your writes to be only done through a single thread/process and then you can read from as many threads/processes you like with no noticeable difference in performance (at least for the several hobby dataset importing projects I tried).

pilif 7y ago
«just write your own server on top of serverless embedded SQLite to get to acceptable performance without needing a client/server database» (Honestly, once you are at the point where concurrency causes performance issues with SQLite, you are better off moving to databases designed to handle concurrency rather than trying to cobble together your own workaround - you have reached the point where the drawback of SQLite‘s architecture outweigh its advantages and the advantages of other databases architectures outweigh their drawbacks)
- pdimitar 7y ago
  Sure, I don't disagree. I've ditched sqlite for Postgres many times. It's just that for 99% of the projects I ever worked on, writes are like 100x less than reads so wrapping the writes in a queue of sorts has been quite okay and performant. It can and it has been coming to a point when it's easier to use a full-blown database server, too. I was simply pointing out that for a lot of classic workflows wrapping/centralising writes works quite fine.
- logicallee 7y ago
  As you quoted, people in this discussion are saying that writing a dummy program that has its own sqlite and does nothing but pass messages to it that it receives from all other processes on the system that need to talk to that sqlite file results in much better performance than accessing that file "directly" from the separate processes. so if everyone's saying this, is there such a standard dummy program?
  - aikah 7y ago
    > As you quoted, people in this discussion are saying that writing a dummy program that has its own sqlite and does nothing but pass messages to it that it receives from all other processes on the system that need to talk to that sqlite file results in much better performance than accessing that file "directly" from the separate processes. And use what protocol for inter-process communication with the daemon managing SQLite? At the end of the day you just created the equivalent of a server anyway...

bob1029 7y ago

Multiple processes is an instant anti-pattern for a single SQLite database. I would stop and reconsider your approach before trying to build this solution using it. The way I see it there are 3 options: 1) Implement another process which will have exclusive ownership of the shared SQLite database, and then use some IPC scheme to delegate database operations from multiple processes. 2) Give each process its own copy of a SQLite db if there is no effective shared state between these processes (I.e. you are just map-reducing web crawler results). Upon completion of each process you could aggregate each into a final combined db. 3) Use a hosted database solution such as Postgres. The bigger question for me would be what are you going to do with this data once you collect it. If you plan on having another series of processes that then use the SQLite db to provide reporting views or execute business logic, I think a hosted solution might be a better option. If scalability is a serious concern, option 2 is probably your best bet.

vdfs 7y ago
It's ironic how using a severless database require your app to be the server and do top-level access management to the database
- bob1029 7y ago
  It may be ironic but there are benefits to rolling your own. Having your persistence layer talk in terms of your business models instead of raw SQL could be seen as a benefit in some contexts. The process which is the "server" can be written to allow for relaxed consistency based on specific business operation being performed (e.g. no need for transactions around log entries). This could be used to leverage substantial performance gains. The benefits afforded by guarantees of exclusivity between application and database are difficult to overstate. These advantages of purpose-built functionality would also extend into the arena of handling replication and clustering. I.e. multiple distributed processes each with independent databases synchronized via some custom protocol that operates in terms of specific business models and processes.
  - chadcmulligan 7y ago