3 ms·

The reason I still use files rather then SQLite is that I don't know how SQLite handles concurrency. For example I have a PHP app that is used by 10k users a d

by TekMol 9y ago

The reason I still use files rather then SQLite is that I don't know how SQLite handles concurrency. For example I have a PHP app that is used by 10k users a day and it happily handles 100k tmp files in a single directory. On each request, it checks the file age via filemtime() and if new enough, includes the tmp file with a simple include(). (I write PHP arrays into the tmp files). If too old, it recalculates the data and writes it via fopen(), fputs() and fclose(). This migh be archaic but it has been working fine for years and never gave me any problems. Somehow I would expect that if I simply replaced it with SQLite, I would run into concurrency problems.

ifdefdebug 9y ago
"If it works, don't fix it". But if you really need to change your design for some reason, sqlite should handle 10k users per day just fine with simple transactions.
rcarmo 9y ago
I've been using SQLite in WAL mode for years in similar scenarios (well, except for PHP) without any hitches.
wyoung2 9y ago
> I don't know how SQLite handles concurrency. Why remain in ignorance? There are several articles in the SQLite documentation that address concurrency: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html https://www.sqlite.org/lockingv3.html https://www.sqlite.org/lockingv3.html https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html > Somehow I would expect that if I simply replaced it with SQLite, I would run into concurrency problems. SQLite wouldn't really be ACID-compliant if multiple threads were enough to defeat the Durability guarantee, would it? That's not to say that there are no concurrency problems in SQLite, but that they're more in the way of potential bottlenecks than data corruption risks. For a reader-heavy application like yours, I suspect SQLite will perform just as well or better than your existing solution. If you're solely after speed, I'm not sure it would be worth rewriting your app to use SQLite. This technique's value is simply in the benefit it gives when you were already going to use SQLite for some other reason. If you just want a 35% speed boost, wait a few months or buy a faster SSD. Both are going to be easier and cheaper than rewriting the data storage layer of your application. That said, maybe there are other things in SQLite that you could use. Easy schema changes, full-text searching, more advanced indexing than the filesystem allows, etc. If you go for one of those, then the extra speed is a nice bonus.
- TekMol 9y ago
  SQLite wouldn't really be ACID-compliant if multiple threads were enough to defeat the Durability guarantee, would it? I'm not so much concerned about durability. More about SQLite not responding to "SELECT v FROM t WHERE id=123" with value v but instead with something like "Error: v is currently being written by another process. Try again later" or something. No idea if that is a realistic scenario. I'm kind of surprised I don't have this kind of problem with my filebased solution. What happens if process A reads from a file while process B writes it? No clue.
  - wyoung2 9y ago
    > What happens if process A reads from a file while process B writes it? If process B started first, the writer blocks access to the table being written to, so the reader waits for the writer to complete. There are timeout and retry behaviors, but within those configurable limits, that's what happens. You can make SQLite behave as you worry about if you set the retries to 0 and timeout to 0, but that's not the default.

by TekMol 9y ago

ifdefdebug 9y ago

"If it works, don't fix it". But if you really need to change your design for some reason, sqlite should handle 10k users per day just fine with simple transactions.

rcarmo 9y ago

I've been using SQLite in WAL mode for years in similar scenarios (well, except for PHP) without any hitches.

wyoung2 9y ago

> I don't know how SQLite handles concurrency. Why remain in ignorance? There are several articles in the SQLite documentation that address concurrency: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html https://www.sqlite.org/lockingv3.html https://www.sqlite.org/lockingv3.html https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html > Somehow I would expect that if I simply replaced it with SQLite, I would run into concurrency problems. SQLite wouldn't really be ACID-compliant if multiple threads were enough to defeat the Durability guarantee, would it? That's not to say that there are no concurrency problems in SQLite, but that they're more in the way of potential bottlenecks than data corruption risks. For a reader-heavy application like yours, I suspect SQLite will perform just as well or better than your existing solution. If you're solely after speed, I'm not sure it would be worth rewriting your app to use SQLite. This technique's value is simply in the benefit it gives when you were already going to use SQLite for some other reason. If you just want a 35% speed boost, wait a few months or buy a faster SSD. Both are going to be easier and cheaper than rewriting the data storage layer of your application. That said, maybe there are other things in SQLite that you could use. Easy schema changes, full-text searching, more advanced indexing than the filesystem allows, etc. If you go for one of those, then the extra speed is a nice bonus.

TekMol 9y ago
SQLite wouldn't really be ACID-compliant if multiple threads were enough to defeat the Durability guarantee, would it? I'm not so much concerned about durability. More about SQLite not responding to "SELECT v FROM t WHERE id=123" with value v but instead with something like "Error: v is currently being written by another process. Try again later" or something. No idea if that is a realistic scenario. I'm kind of surprised I don't have this kind of problem with my filebased solution. What happens if process A reads from a file while process B writes it? No clue.
- wyoung2 9y ago
  > What happens if process A reads from a file while process B writes it? If process B started first, the writer blocks access to the table being written to, so the reader waits for the writer to complete. There are timeout and retry behaviors, but within those configurable limits, that's what happens. You can make SQLite behave as you worry about if you set the retries to 0 and timeout to 0, but that's not the default.